[jira] [Reopened] (DERBY-5779) Table functions should only accept arguments which are constant in their query block.

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-5779?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas reopened DERBY-5779:
----------------------------------


Re-opening this issue. Another variant of this problem occurs when table functions and diagnostic VTIs are factors in a <joined table> expression and they take arguments built out of columns from other tables in the <joined table> expression. This is another example of violating part 2 of the SQL Standard, section 7.6 (<table reference>), Syntax Rule 6.a. Right now these queries do not even run. They raise NPEs and compiler assertions. We should raise a proper exception when trying to compile this illegal syntax. The following script shows how the bad syntax currently results in NPEs and compiler assertions:

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';

-- NPEs
select tt.* from table(syscs_diag.space_table(st.tablename)) tt join sys.systables st using(tableid);
select tt.* from table( lowerCaseRow(st.tablename)) tt join sys.systables st on tt.contents = st.tablename;

-- Compiler assertions
select tt.* from table(syscs_diag.space_table(st.tablename)) tt right join sys.systables st using(tableid);
select tt.* from table( lowerCaseRow(st.tablename)) tt right join sys.systables st on tt.contents = st.tablename;

                
> 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, 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