db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5779) Table functions should only accept arguments which are constant in their query block.
Date Thu, 12 Jul 2012 18:39:34 GMT

    [ 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

        

Mime
View raw message