[jira] Updated: (DERBY-2352) Assertion Failure with order by and group by expression

["Bryan Pendleton (JIRA)" <ji...@apache.org>]

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

Bryan Pendleton updated DERBY-2352:
-----------------------------------

    Attachment: substrBind.diff

Attached is substrBind.diff, an alternate idea for a patch.

I found myself interested in why the rowAllocator method for

   select substr('abc', 1) from d2352 order by substr('abc', 1); 

is different from the rowAllocator method that is generated for

   select ltrim('abc') from d2352 order by ltrim('abc'); 

The SUBSTR version generates 

     e5.setColumn(1, getDataValueFactory().getNullChar(null)); 

while the LTRIM version generates

     e5.setColumn(1, getDataValueFactory().getNullVarchar(null)); 

The generation activity happens in ResultColumnList.generateHolderMethod,
so I stepped through that code to see what was different in the two cases.
ResultColumnList.generateHolderMethod() calls ResultColumn.generateHolder()
for each result column, and that code in turn generates a null of the
appropriate type, where the type is given by the DataTypeDescriptor in
the ResultColumn, and controls the behavior of CharTypeCompiler.nullMethodName.

During bind processing, ResultColumn.bindResultColumnToExpression() sets
the ResultColumn type to the type of its expression, which in both cases
is a TernaryOperatorNode.

The TernaryOperatorNode's type is determined during bind processing. For
the TRIM expression, TernaryOperatorNode.trimBind() is called, and it
chooses a VARCHAR type except in some cases involving BLOBs.

   TypeId	resultType = TypeId.getBuiltInTypeId(Types.VARCHAR);

For the SUBSTR expression, TernaryOperatorNode.substrBind() is called, and
it has a completely different set of logic for choosing the result type,
which is driven by the type of the value on which we're calling SUBSTR:

   resultType = receiverType = receiver.getTypeId();

In the case of SUBSTR('abc', 1), "receiver" is a CharConstantNode, and its
typeId is a CHAR type, so the resultType is set to CHAR, not VARCHAR.

The technique of substrBind() -- using the receiver's type -- also handles
CLOB/BLOB types, but it has the result of choosing CHAR in cases where
trimBind() chooses VARCHAR instead.

So it seems that an alternate solution for DERBY-2352 could be to modify
TernaryOperatorNode.substrBind() so that it chooses the result type using
a set of rules more like those used by TernaryOperatorNode.trimBind().
The attached 'substrBind.diff' implements those alternate rules, and the
resulting code successfully processes the repro script, and the generated
row allocator method now matches the code generated in the TRIM cases.

I think this is a cleaner approach than 'substringReturnsChar.diff', because
I think that the intent of the system is for SUBSTR to return a VARCHAR
type. Feedback about whether one change seems better or worse, or any other
feedback on these topics, would be most welcome.



> Assertion Failure with order by and group by expression
> -------------------------------------------------------
>
>                 Key: DERBY-2352
>                 URL: https://issues.apache.org/jira/browse/DERBY-2352
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.2.0, 10.3.1.4
>         Environment: Any
>            Reporter: Yip Ng
>            Assignee: Bryan Pendleton
>         Attachments: substrBind.diff, substringReturnsChar.diff
>
>
> Assertion failure with order by expression:
> ij> select substr('abc', 1) from t1 order by substr('abc', 1);
> ERROR XJ001: Java exception: 'ASSERT FAILED col1.getClass() (class org.apache.derby.iapi.types.SQLVarchar) expected to be the same as col2.getClass() (class org.apache.derby.iapi.types.SQLChar): org.apache.derby.shared.common.sanity.AssertFailure'.
> Stacktrace:
> org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED col1.getClass() (class org.apache.derby.iapi.types.SQLVarchar) expected to be the same as col2.getClass() (class org.apache.derby.iapi.types.SQLChar)
> 	at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:149)
> 	at org.apache.derby.impl.store.access.sort.MergeSort.checkColumnTypes(MergeSort.java:472)
> 	at org.apache.derby.impl.store.access.sort.MergeInserter.insert(MergeInserter.java:106)
> 	at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(SortResultSet.java:318)
> 	at org.apache.derby.impl.sql.execute.SortResultSet.openCore(SortResultSet.java:269)
> 	at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(ProjectRestrictResultSet.java:169)
> 	at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(BasicNoPutResultSetImpl.java:260)
> 	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:358)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1182)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:585)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:517)
> 	at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:321)
> 	at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:517)
> 	at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:370)
> 	at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:268)
> 	at org.apache.derby.impl.tools.ij.Main.go(Main.java:204)
> 	at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:170)
> 	at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:56)
> 	at org.apache.derby.tools.ij.main(ij.java:71)

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.