You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Satheesh Bandaram (JIRA)" <de...@db.apache.org> on 2005/06/15 01:39:54 UTC
[jira] Created: (DERBY-362) Some of the DDL checks to prevent incorrect temporary table use is incorrect. These may incorrectly stop access to valid permanant tables in SESSION schema.
Some of the DDL checks to prevent incorrect temporary table use is incorrect. These may incorrectly stop access to valid permanant tables in SESSION schema.
------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: DERBY-362
URL: http://issues.apache.org/jira/browse/DERBY-362
Project: Derby
Type: Bug
Components: SQL
Versions: 10.0.2.1
Environment: Generic
Reporter: Satheesh Bandaram
Use of temporary tables in some DDLs is incorrect. For example, it should not be possible to create a view on a temporary table or create a trigger. But the current code checks prevent valid uses of permanent tables in SESSION schema, mistaking them for temporary tables. Need to check all DDL statements and temporary table usage errors.
ij> create table session.permTable ( i int);
0 rows inserted/updated/deleted
ij> create view view1 as select * from session.permTable;
ERROR XCL51: The requested function can not reference tables in SESSION schema.
ij> create trigger trig1 after insert on session.permTable for each row mode db2
sql insert into t1 values (1);
ERROR XCL51: The requested function can not reference tables in SESSION schema.
ij> create index indSession on session.permTable (i); <============ Works correctly.
0 rows inserted/updated/deleted
--
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
[jira] Commented: (DERBY-362) Some of the DDL checks to prevent incorrect temporary table use is incorrect. These may incorrectly stop access to valid permanant tables in SESSION schema.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-362?page=comments#action_12313673 ]
Satheesh Bandaram commented on DERBY-362:
-----------------------------------------
I am marking this as an improvement, instead of a bug.
Fixing this problem is not trivial and may have related issues. Discussed this with Dan briefly. The summary of the discussion was that fixing this may cause issues with statement cache, for example. A view created as SELECT * FROM SESSION.T1 may need multiple plans in different connections, depending on whether SESSION.T1 is a permanent table or a temporary table for that connection.
It was felt the current behavior is best left as is.
> Some of the DDL checks to prevent incorrect temporary table use is incorrect. These may incorrectly stop access to valid permanant tables in SESSION schema.
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-362
> URL: http://issues.apache.org/jira/browse/DERBY-362
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.1
> Environment: Generic
> Reporter: Satheesh Bandaram
>
> Use of temporary tables in some DDLs is incorrect. For example, it should not be possible to create a view on a temporary table or create a trigger. But the current code checks prevent valid uses of permanent tables in SESSION schema, mistaking them for temporary tables. Need to check all DDL statements and temporary table usage errors.
> ij> create table session.permTable ( i int);
> 0 rows inserted/updated/deleted
> ij> create view view1 as select * from session.permTable;
> ERROR XCL51: The requested function can not reference tables in SESSION schema.
> ij> create trigger trig1 after insert on session.permTable for each row mode db2
> sql insert into t1 values (1);
> ERROR XCL51: The requested function can not reference tables in SESSION schema.
> ij> create index indSession on session.permTable (i); <============ Works correctly.
> 0 rows inserted/updated/deleted
--
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
[jira] Updated: (DERBY-362) Some of the DDL checks to prevent incorrect temporary table use is incorrect. These may incorrectly stop access to valid permanant tables in SESSION schema.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-362?page=all ]
Satheesh Bandaram updated DERBY-362:
------------------------------------
type: Improvement (was: Bug)
Priority: Minor (was: Major)
> Some of the DDL checks to prevent incorrect temporary table use is incorrect. These may incorrectly stop access to valid permanant tables in SESSION schema.
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-362
> URL: http://issues.apache.org/jira/browse/DERBY-362
> Project: Derby
> Type: Improvement
> Components: SQL
> Versions: 10.0.2.1
> Environment: Generic
> Reporter: Satheesh Bandaram
> Priority: Minor
>
> Use of temporary tables in some DDLs is incorrect. For example, it should not be possible to create a view on a temporary table or create a trigger. But the current code checks prevent valid uses of permanent tables in SESSION schema, mistaking them for temporary tables. Need to check all DDL statements and temporary table usage errors.
> ij> create table session.permTable ( i int);
> 0 rows inserted/updated/deleted
> ij> create view view1 as select * from session.permTable;
> ERROR XCL51: The requested function can not reference tables in SESSION schema.
> ij> create trigger trig1 after insert on session.permTable for each row mode db2
> sql insert into t1 values (1);
> ERROR XCL51: The requested function can not reference tables in SESSION schema.
> ij> create index indSession on session.permTable (i); <============ Works correctly.
> 0 rows inserted/updated/deleted
--
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
[jira] Commented: (DERBY-362) Some of the DDL checks to prevent incorrect temporary table use is incorrect. These may incorrectly stop access to valid permanant tables in SESSION schema.
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-362?page=comments#action_12313683 ]
Mamta A. Satoor commented on DERBY-362:
---------------------------------------
The behavior you are noticing with views and triggers is the documented behavior. "DECLARE GLOBAL TEMPORARY TABLE statement" is documented at
http://incubator.apache.org/derby/docs/ref/rrefdeclaretemptable.html. At the bottom of the page, there is a section called "Restrictions Specific to Derby" and it says following for views and triggers.
Derby does not support the following on temporary tables:
index support
triggers and views on SESSION schema tables (including physical tables and temporary tables) <===================
If we choose to not support SYNONYMS on temporary table, that we should add it the list along with index support etc. If we choose not to support SYNONYMS on any SESSION schema table, then we should add it to the list alongwith triggers and views restriction.
Also, the reason the index creation passed is that it was created on a permanent table in SESSION schema. If it was attempted on a temporary table, there would be an exception thrown (which is inline with the documentation, where it says that there is no index support on temporary tables).
> Some of the DDL checks to prevent incorrect temporary table use is incorrect. These may incorrectly stop access to valid permanant tables in SESSION schema.
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-362
> URL: http://issues.apache.org/jira/browse/DERBY-362
> Project: Derby
> Type: Improvement
> Components: SQL
> Versions: 10.0.2.1
> Environment: Generic
> Reporter: Satheesh Bandaram
> Priority: Minor
>
> Use of temporary tables in some DDLs is incorrect. For example, it should not be possible to create a view on a temporary table or create a trigger. But the current code checks prevent valid uses of permanent tables in SESSION schema, mistaking them for temporary tables. Need to check all DDL statements and temporary table usage errors.
> ij> create table session.permTable ( i int);
> 0 rows inserted/updated/deleted
> ij> create view view1 as select * from session.permTable;
> ERROR XCL51: The requested function can not reference tables in SESSION schema.
> ij> create trigger trig1 after insert on session.permTable for each row mode db2
> sql insert into t1 values (1);
> ERROR XCL51: The requested function can not reference tables in SESSION schema.
> ij> create index indSession on session.permTable (i); <============ Works correctly.
> 0 rows inserted/updated/deleted
--
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