[
https://issues.apache.org/jira/browse/DERBY-5779?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13413056#comment-13413056
]
Rick Hillegas commented on DERBY-5779:
--------------------------------------
Another case which needs to be forbidden:
Derby trips over a compiler assertion if an argument to a VTI/tableFunction in
a subquery in the FROM list references another table in the FROM list. The
following script shows this:
connect 'jdbc:derby:memory:db;create=true';
create function lowerCaseRow( contents varchar( 32672 ) )
returns table
(
contents varchar( 32672 )
)
language java parameter style DERBY_JDBC_RESULT_SET no sql
external name
'org.apache.derbyTesting.functionTests.tests.lang.TableFunctionTest.lowerCaseRow';
create table t1 (a int);
-- ok
select tt.*
from
sys.systables systabs,
( select * from table (syscs_diag.space_table( 'T1' )) as t2 ) tt
where systabs.tabletype = 'T' and systabs.tableid = tt.tableid;
select tt.*
from
sys.systables systabs,
( select * from table (lowerCaseRow( 'T1' )) as t2 ) tt
where systabs.tabletype = 'T' and systabs.tablename = tt.contents;
-- raises compiler assertions
select tt.*
from
sys.systables systabs,
( select * from table (syscs_diag.space_table( systabs.tablename )) as
t2 ) tt
where systabs.tabletype = 'T' and systabs.tableid = tt.tableid;
select tt.*
from
sys.systables systabs,
( select * from table (lowerCaseRow( systabs.tablename )) as t2 ) tt
where systabs.tabletype = 'T' and systabs.tablename = tt.contents;
> Table functions should only accept arguments which are constant in their
> query block.
> -------------------------------------------------------------------------------------
>
> Key: DERBY-5779
> URL: https://issues.apache.org/jira/browse/DERBY-5779
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.9.1.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Fix For: 10.10.0.0
>
> Attachments: derby-5779-01-ab-forbidReferencesInQueryBlock.diff,
> derby-5779-02-aa-forbidReferencesToJoinedTables.diff,
> derby-5779-03-aa-moreTests.diff, releaseNote.html
>
>
> Derby lets you invoke a table function in the FROM list of a query, passing
> in arguments built out of columns in other tables in the FROM list. This
> syntax is illegal and the resulting queries have no meaning under the SQL
> Standard. See the discussion on DERBY-5554. We should forbid this syntax.
> Similar syntax involving correlated subqueries in the FROM list is already
> forbidden. Fixing this will create a backward incompatibility which requires
> a release note.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
