NullPointerException with LTRIM, RTRIM and 2-argument SUBSTR() call in GROUP BY clause.

• Key: DERBY-2008
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Yip Ng
• Reporter: A B
• Component/s: SQL
• Affects Version/s: 10.2.1.6, 10.3.1.4
• Fix Version/s: 10.2.2.0, 10.3.1.4

Description

The following query, which fails with a syntax error in 10.1.3 (because functions are not allowed in a GROUP BY expression for 10.1) fails with a NullPointerException in 10.2 and in the codeline (10.3):

create table dt (vc varchar(30));
insert into dt values ('1928-09-21'), ('1903-12-08');
select substr(vc, 3) from dt group by substr(vc, 3);

Result (on trunk) is:

java.lang.NullPointerException
at org.apache.derby.impl.sql.compile.TernaryOperatorNode.isEquivalent(TernaryOperatorNode.java:924)
at org.apache.derby.impl.sql.compile.GroupByList.findGroupingColumn(GroupByList.java:244)
at org.apache.derby.impl.sql.compile.VerifyAggregateExpressionsVisitor.skipChildren(VerifyAggregateExpressionsVisitor.java:146)
at org.apache.derby.impl.sql.compile.TernaryOperatorNode.accept(TernaryOperatorNode.java:497)
at org.apache.derby.impl.sql.compile.ResultColumn.accept(ResultColumn.java:1515)
at org.apache.derby.impl.sql.compile.QueryTreeNodeVector.accept(QueryTreeNodeVector.java:159)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:619)
at org.apache.derby.impl.sql.compile.FromSubquery.bindExpressions(FromSubquery.java:262)
at org.apache.derby.impl.sql.compile.FromList.bindExpressions(FromList.java:337)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:500)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:249)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:162)
at org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java:253)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:345)
at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:119)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:745)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:568)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:517)

Comments

[Yip Ng]

Attaching patch derby2008-trunk-diff01.txt for DERBY-2008. For SUBSTR function, there can be 2 or 3 arguments, and in the case of 2-args, the rightOperand of the TernaryOperatorNode will be null. In its isEquivalent() method, it did not take care of this case; thus, the NPE. derbyall + junit suite pass.

[Yip Ng]

Patch available for review. [Testing jira email]

[Bryan Pendleton]

Thanks for the patch, Yip! It looks good to me. The code change is clear and
well-commented. The new test cases fail without the code change, and they
pass once the code change is applied. Your explanation of the problem makes sense.

I intend to commit this patch tomorrow. If anybody else is reviewing it, please let us know.

[Bryan Pendleton]

Hi Yip, I was just wondering: I see that TernaryOperatorNode is used for a few
other cases, besides just SUBSTR: TRIM, LOCATE, LIKE, TIMESTAMPADD,
and TIMESTAMPDIFF all seem to be TernaryOperatorNodes.

How does your patch affect the behavior of these functions?

I suppose that if it had any affect at all, it would be to prevent similar NPE bugs
when one of these functions was used in an way which provoked an isEquivalent()
call for a case where the RHS of the Ternary was NULL.

I think the patch is fine, I'm just wondering whether it fixes any more problems
than just the SUBSTR-in-a-GROUP-BY case that is noted in the description.

[Bryan Pendleton]

Committed derby2008-trunk-diff01.txt to subversion as revision 468696.

[Yip Ng]

Thanks Bryan for the review. I am trying out other functions/expression with group by currently to see if other problems arise. I found another problem with NULLIF and I have filed a jira issue(DERBY-2014) for this. If I find more problems, I'll just generalize that jira entry with group by expression in general.

[Yip Ng]

Hi Bryan, besides the SUBSTR function, the patch also fixes NPE for the trim functions (LTRIM, RTRIM).

In 10.2:
ij> create table t1 (c1 char(10));
0 rows inserted/updated/deleted
ij> insert into t1 values '123', 'abc';
2 rows inserted/updated/deleted
ij> select rtrim(c1) from t1 group by rtrim(c1);
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
...
ij> select ltrim(c1) from t1 group by ltrim(c1);
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

From what I see from the sqlgrammar.jj file, the other functions seem to have the third argument filled with a non-null value when its node is constructed, so the NPE won't occur in those cases. I'll add the trim functions testcases in DERBY-2014.

[Yip Ng]

Interesting. This executed successfully due to the fact that the third parameter's is supplied with a value of 1 implicitly if the argument is not specified.

ij> create table t1 (c1 varchar(10));
0 rows inserted/updated/deleted
ij> insert into t1 values 'abc', '123';
2 rows inserted/updated/deleted
ij> select locate(c1, 'abc') from t1 group by locate(c1, 'abc', 1);
1
-----------
0
1

2 rows selected

[Rick Hillegas]

Ported to 10.2 branch at subversion revision 480219.