Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-424

Queryplan for a query using SESSION schema view is incorrectly put in statement cache. This could cause incorrect plan getting executed later if a temp. table is created with that name.

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.1.1.0, 10.2.1.6
    • Fix Version/s: 10.1.2.1, 10.2.1.6
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      generic

      Description

      See DERBY-405 for some discussion related this issue. I suspect this is because of statement caching. Derby doesn't seem to recognise we have a temporary table that just overloaded an existing physical table.

      It would have been good to avoid permanent tables/views/synonyms in SESSION schema. Not sure what we should do now about this, though.

      ij> create view session.st1 as select * from t;
      0 rows inserted/updated/deleted
      ij> select * from session.st1;
      I |J |K
      -----------------------------------
      1 |1 |NULL
      2 |2 |NULL
      3 |3 |NULL
      4 |4 |NULL

      4 rows selected
      ij> select * from t;
      I |J |K
      -----------------------------------
      1 |1 |NULL
      2 |2 |NULL
      3 |3 |NULL
      4 |4 |NULL

      4 rows selected
      ij> declare global temporary table st1(c11 int, c12 int) on commit preserve rows
      not logged;
      0 rows inserted/updated/deleted
      ij> select * from session.st1;
      I |J |K
      -----------------------------------
      1 |1 |NULL
      2 |2 |NULL
      3 |3 |NULL
      4 |4 |NULL

      4 rows selected
      ij> select * from session.st1; <==== This statement has an extra space between FROM and session.st1
      C11 |C12
      -----------------------

      0 rows selected

        Attachments

        1. Derby424SessionSchemaCaching.txt
          12 kB
          Mamta A. Satoor

          Activity

            People

            • Assignee:
              mamtas Mamta A. Satoor
              Reporter:
              bandaram Satheesh E. Bandaram
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: