[
https://issues.apache.org/jira/browse/DERBY-5554?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13281057#comment-13281057
]
Rick Hillegas commented on DERBY-5554:
--------------------------------------
I believe that chapter and verse on this syntax can be found in part 2 of the
SQL Standard, section 7.6 (<table reference>), Syntax Rule 6.a:
"If TR is contained in a <from clause> FC with no intervening <query
expression>, then the scope clause SC of TR is the <select statement: single
row> or innermost <query specification> that contains FC. The scope of a range
variable of TR is the <select list>, <where clause>, <group by clause>, <having
clause>, and <window clause> of SC, together with every <lateral derived table>
that is simply contained in FC and is preceded by TR, and every <collection
derived table> that is simply contained in FC and is preceded by TR, and the
<join condition> of all <joined table>s contained in SC that contain TR. If SC
is the <query specification> that is the <query expression body> of a simple
table query STQ, then the scope of a range variable of TR also includes the
<order by clause> of STQ."
When applied to Bryan's original query, this says that the systabs table
reference can be mentioned in the query's SELECT list, WHERE clause, GROUP BY
clause, HAVING clause, WINDOW clause, and ORDER BY clause. The systabs table
reference may be mentioned in the FROM clause only as follows:
a) in some syntax which Derby does not support (lateral derived tables and
collection derived tables)
b) in the ON clause of a JOIN to another tabular data set.
The sample SYSCS_DIAG.SPACE_TABLE syntax given in the Reference Manual is
illegal and has no meaning.
I don't see how to write a legal query which expresses Bryan's intent. We could
consider adding a TABLEID column to the SPACE_TABLE output and change the vti's
arguments so that they accept the % wildcard (like the metadata calls). Then
something like this might capture Bryan's intent:
SELECT T2.*, systabs.*, syscgs.conglomeratenumber
FROM
SYS.SYSTABLES systabs, sys.sysconglomerates syscgs,
TABLE (SYSCS_DIAG.SPACE_TABLE( '%' )) AS T2
WHERE systabs.tabletype = 'T'
and systabs.tableid = syscgs.tableid
and systabs.tableid = t2.tableid;
> NullPointerException in generated VTI code
> ------------------------------------------
>
> Key: DERBY-5554
> URL: https://issues.apache.org/jira/browse/DERBY-5554
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4, 10.4.1.3, 10.5.1.1, 10.6.1.0, 10.7.1.1,
> 10.8.2.2
> Reporter: Bryan Pendleton
> Labels: derby_triage10_9
>
> With the latest 10.8.2.2 binary distribution, the following short script
> produces a NullPointerException:
> java -Dij.exceptionTrace=true org.apache.derby.tools.ij
> ij> connect 'jdbc:derby:brydb;create=true';
> ij> create table t1 (a int);
> ij> SELECT T2.*, systabs.*, syscgs.conglomeratenumber
> FROM
> SYS.SYSTABLES systabs, sys.sysconglomerates syscgs,
> TABLE (SYSCS_DIAG.SPACE_TABLE(systabs.tablename)) AS T2
> WHERE systabs.tabletype = 'T' and systabs.tableid = syscgs.tableid;
> The exception trace is pasted below:
> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while
> evaluating an expression.
> java.sql.SQLException: The exception 'java.lang.NullPointerException' was
> thrown while evaluating an expression.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.main(Unknown Source)
> at org.apache.derby.tools.ij.main(Unknown Source)
> Caused by: java.sql.SQLException: The exception
> 'java.lang.NullPointerException' was thrown while evaluating an expression.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
> ... 18 more
> Caused by: java.sql.SQLException: Java exception: ':
> java.lang.NullPointerException'.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
> ... 15 more
> Caused by: java.lang.NullPointerException
> at
> org.apache.derby.exe.acf81e0010x0134x6972x0511x0000033820000.g0(Unknown
> Source)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:616)
> at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.VTIResultSet.openCore(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openRight(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown Source)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
> Source)
> ... 11 more
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
> java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.main(Unknown Source)
> at org.apache.derby.tools.ij.main(Unknown Source)
> Caused by: java.sql.SQLException: Java exception: ':
> java.lang.NullPointerException'.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
> ... 19 more
> Caused by: java.lang.NullPointerException
> at
> org.apache.derby.exe.acf81e0010x0134x6972x0511x0000033820000.g0(Unknown
> Source)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:616)
> at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.VTIResultSet.openCore(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openRight(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown Source)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
> Source)
> ... 11 more
--
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
