[ https://issues.apache.org/jira/browse/DERBY-3872?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12632395#action_12632395 ]
Mamta A. Satoor commented on DERBY-3872: ---------------------------------------- I have some more finidings to share on Query1 select q1."DEPTNO" from DEPTTAB q1, EMPTAB q2 -- DERBY-PROPERTIES joinStrategy = HASH where ( q2."DEPT_DEPTNO" = q1."DEPTNO") GROUP BY q1."DEPTNO" HAVING 1 = ( select q3."SALARY" from EMPTAB q3 where ( q3."EMPID" = q1."DEPTNO") ) ; The query as written above results in NPE but if I change the order of tables in the outer query's from list as shown below, there is no NPE select q1."DEPTNO" from EMPTAB q2, DEPTTAB q1 -- DERBY-PROPERTIES joinStrategy = HASH where ( q2."DEPT_DEPTNO" = q1."DEPTNO") GROUP BY q1."DEPTNO" HAVING 1 = ( select q3."SALARY" from EMPTAB q3 where ( q3."EMPID" = q1."DEPTNO") ) ; It appears that changing the order of the tables makes Derby bypass the sort avoidance code. To be more specific, impl.sql.compile.GroupByNode:init method has following check if (index == glSize) { isInSortedOrder = childResult.isOrderedOn(crs, true, (Vector)null); } This piece of code assigns true to isInSortedOrder if DEPTTAB is first in the from list of query. The code comment in JoinNode.isOrdered which is what gets called by the code segment above has following interesting comment /* RESOLVE - easiest thing for now is to only consider the leftmost child */ This is why if I change the order of the tables in the from list, DEPTTAB is no more the leftmost child and hence isInSortedOrder ends up getting a value of FALSE when DEPTTAB is not the first table in the from list. (This piece of code so far is in Derby's query compile code.) At query execution time, we check this flag is impl.sql.execute.GroupedAggregateResultSet:openCore as shown below /* If this is an in-order group by then we do not need the sorter. * (We can do the aggregation ourselves.) * We save a clone of the first row so that subsequent next()s * do not overwrite the saved row. */ if (isInSortedOrder) { currSortedRow = getNextRowFromRS(); if (currSortedRow != null) { currSortedRow = (ExecIndexRow)currSortedRow.getClone(); initializeVectorAggregation(currSortedRow); } } else { /* ** Load up the sorter */ scanController = loadSorter(); } If isInSortOrder is found to be true, we go through the code path which causes us to run into NPE. I need to do more debugging but I think what happens in this code path is we set the current row associated with various internal resutsets hanging off of the Activation object and most of these current row end up being NULL and later on, the code is looking for specific column value in the current row not expecting the current row to be NULL. I will also try to compare the code flow in 10.2 codeline vs trunk because the query in question works fine in 10.2 no matter how the tables are listed the from list in the query. > NullPoinerException thrown when INTEGER function used as a predicate in a > WHERE clause of a SELECT .. GROUP BY .. HAVING statement > ----------------------------------------------------------------------------------------------------------------------------------- > > Key: DERBY-3872 > URL: https://issues.apache.org/jira/browse/DERBY-3872 > Project: Derby > Issue Type: Bug > Affects Versions: 10.3.3.0 > Reporter: Stan Bradbury > Assignee: Mamta A. Satoor > Attachments: NPE_Reproduction.sql, QueryWithoutTruePred.out, > QueryWithTruePred.out > > > Use attached SQL script to create two tables , execute the following SQL and > throw the exception and stack trace below. NOTE: removing the 'always true' > clause '.. ( integer (1.1) = 1) .." from the SQL and the query does not fail. > Releated?? > select q1."DEPTNO" from DEPTTAB q1, EMPTAB q2 where ( integer (1.1) = 1) > and ( q2."DEPT_DEPTNO" = q1."DEPTNO") > GROUP BY q1."DEPTNO" > HAVING max( q2."SALARY") >= ( select q3."SALARY" from EMPTAB q3 where ( > q3."EMPID" = q1."DEPTNO") ) > ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while > evaluating an expression. > . . .derby.iapi.error.StandardException.newException > . . .derby.iapi.error.StandardException.unexpectedUserException > . . .derby.impl.services.reflect.DirectCall.invoke > . . .derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore > . . .derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow > . . .derby.impl.jdbc.EmbedResultSet.movePosition > . . .derby.impl.jdbc.EmbedResultSet.next > . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults > . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults > . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults > . . .derby.tools.JDBCDisplayUtil.DisplayResults > . . .derby.impl.tools.ij.utilMain.displayResult > . . .derby.impl.tools.ij.utilMain.doCatch > . . .derby.impl.tools.ij.utilMain.runScriptGuts > . . .derby.impl.tools.ij.utilMain.go > . . .derby.impl.tools.ij.Main.go > . . .derby.impl.tools.ij.Main.mainCore > . . .derby.impl.tools.ij.Main14.main > . . .derby.tools.ij.main > Caused by: java.lang.NullPointerException > . . .derby.iapi.types.NumberDataType.compare > . . .derby.impl.store.access.btree.ControlRow.compareIndexRowFromPageToKey > . . .derby.impl.store.access.btree.ControlRow.searchForEntry > . . .derby.impl.store.access.btree.LeafControlRow.search > . . .derby.impl.store.access.btree.BTreeScan.positionAtStartForForwardScan > . . .derby.impl.store.access.btree.BTreeForwardScan.positionAtStartPosition > . . .derby.impl.store.access.btree.BTreeForwardScan.fetchRows > . . .derby.impl.store.access.btree.BTreeScan.fetchNext > . . .derby.impl.sql.execute.TableScanResultSet.getNextRowCore > . . .derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextRowCore > . . .derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore > . . .derby.impl.sql.execute.OnceResultSet.getNextRowCore > . . .derby.exe.ac601a400fx011cx480cx5eacx00000010d8100.g0 > . . .derby.exe.ac601a400fx011cx480cx5eacx00000010d8100.e6 > ... 17 more > ============= begin nested exception, level (1) =========== > java.lang.NullPointerException > . . .derby.iapi.types.NumberDataType.compare > . . .derby.impl.store.access.btree.ControlRow.compareIndexRowFromPageToKey > . . .derby.impl.store.access.btree.ControlRow.searchForEntry > . . .derby.impl.store.access.btree.LeafControlRow.search > . . .derby.impl.store.access.btree.BTreeScan.positionAtStartForForwardScan > . . .derby.impl.store.access.btree.BTreeForwardScan.positionAtStartPosition > . . .derby.impl.store.access.btree.BTreeForwardScan.fetchRows > . . .derby.impl.store.access.btree.BTreeScan.fetchNext > . . .derby.impl.sql.execute.TableScanResultSet.getNextRowCore > . . .derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextRowCore > . . .derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore > . . .derby.impl.sql.execute.OnceResultSet.getNextRowCore > . . .derby.exe.ac601a400fx011cx480cx5eacx00000010d8100.g0 > . . .derby.exe.ac601a400fx011cx480cx5eacx00000010d8100.e6 > . . .derby.impl.services.reflect.DirectCall.invoke > . . .derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore > . . .derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow > . . .derby.impl.jdbc.EmbedResultSet.movePosition > . . .derby.impl.jdbc.EmbedResultSet.next > . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults > . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults > . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults > . . .derby.tools.JDBCDisplayUtil.DisplayResults > . . .derby.impl.tools.ij.utilMain.displayResult > . . .derby.impl.tools.ij.utilMain.doCatch > . . .derby.impl.tools.ij.utilMain.runScriptGuts > . . .derby.impl.tools.ij.utilMain.go > . . .derby.impl.tools.ij.Main.go > . . .derby.impl.tools.ij.Main.mainCore > . . .derby.impl.tools.ij.Main14.main > . . .derby.tools.ij.main > ============= end nested exception, level (1) =========== > Cleanup action completed -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.