db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Satheesh Bandaram (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-405) SYNONYM should not be allowed in SESSION schema because that can confusion wih temporary tables
Date Thu, 30 Jun 2005 17:24:57 GMT
    [ http://issues.apache.org/jira/browse/DERBY-405?page=comments#action_12314789 ] 

Satheesh Bandaram commented on DERBY-405:
-----------------------------------------

The behavior here is same as with views. Suspect this is because of statement caching. We
don'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

> SYNONYM should not be allowed in SESSION schema because that can confusion wih temporary
tables
> -----------------------------------------------------------------------------------------------
>
>          Key: DERBY-405
>          URL: http://issues.apache.org/jira/browse/DERBY-405
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>      Fix For: 10.2.0.0

>
> A user should not be able to create a synonym in SESSION schema eg
> create synonym session.st1 for app.t1;
> select * from session.st1; -- refers to app.t1
> declare global temporary table st1(c11 int, c12 int) on commit preserve rows not logged;
> select * from session.st1; -- refers to app.t1, is that right?
> I think the last select * from session.st1 should goto temporary table st1 since any
references to objects in SESSION schema should first find a match against a temporary table
if one by that name exists. 
> We already discourage users from defining objects other than temporary table in SESSION
schema in the reference manual "Using SESSION as the schema name of a physical table will
not cause an error, but is discouraged. The SESSION schema name should be reserved for the
temporary table schema." But, IMHO, it will be nice to enforce this wherever possible in Derby
code rather than leaving it upto the users to avoid confusion.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message