[
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
