[
https://issues.apache.org/jira/browse/CALCITE-3150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16874306#comment-16874306
]
Stamatis Zampetakis commented on CALCITE-3150:
----------------------------------------------
I was curious why the query in the description fails and the one just below
does not.
{code:sql}
SELECT e.name from hr.emps as e WHERE UPPER(e.name) LIKE 'B%'
{code}
It turns out that the inlining optimization that is performed in method
[BlockBuilder.optimize
|https://github.com/apache/calcite/blob/69c8053cd98ec65c55fa1c3b282b076536ab758f/linq4j/src/main/java/org/apache/calcite/linq4j/tree/BlockBuilder.java#L336]
is able to bury many NPE.
+Non-optimized code+
{code:java}
/* 13 */ final org.apache.calcite.test.JdbcTest.Employee
current = (org.apache.calcite.test.JdbcTest.Employee) inputEnumerator.current();
/* 14 */ final String inp2_ = current.name;
/* 15 */ final boolean inp2__unboxed = inp2_ != null;
/* 16 */ final org.apache.calcite.test.JdbcTest.Employee
current0 = (org.apache.calcite.test.JdbcTest.Employee)
inputEnumerator.current();
/* 17 */ final String inp2_0 = current0.name;
/* 18 */ final String v =
org.apache.calcite.runtime.SqlFunctions.upper(inp2_0); // <- NPE
/* 19 */ final boolean v0 = inp2__unboxed &&
org.apache.calcite.runtime.SqlFunctions.like(v, "B%");
/* 20 */ if (v0) {
/* 21 */ return true;
/* 22 */ }
{code}
+Optimized code+
{code:java}
/* 13 */ final String inp2_ =
((org.apache.calcite.test.JdbcTest.Employee) inputEnumerator.current()).name;
/* 14 */ if (inp2_ != null &&
org.apache.calcite.runtime.SqlFunctions.like(org.apache.calcite.runtime.SqlFunctions.upper(inp2_),
"B%")) {
/* 15 */ return true;
/* 16 */ }
{code}
Obviously the optimized code cannot throw the NPE since "v" is inlined inside
like. The interesting fact is that the optimization does not only affect
performance but also changes the semantics of the code.
> NPE in UPPER when repeated and combine with LIKE
> -------------------------------------------------
>
> Key: CALCITE-3150
> URL: https://issues.apache.org/jira/browse/CALCITE-3150
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: next
> Reporter: Mickaël Sauvée
> Priority: Major
>
> Using a query using twice same UPPER with a LIKE, generated code do not
> protect UPPER call.
> If inout is null, then UPPER is call and generates a NPE.
> I've used the following test (in JdbcTest.java):
>
> {code:java}
> @Test
> public void testNPEInUpper() {
> CalciteAssert.hr()
> .query("select e.\"name\" from \"hr\".\"emps\" as e WHERE
> (UPPER(e.\"name\") LIKE 'B%' AND UPPER(e.\"name\") LIKE '%L')" )
> .returnsUnordered("name=Bill;");
> }
> {code}
> And modify data to hava NULL for a name:
>
>
> {code:java}
> public final Employee[] emps = {
> new Employee(100, 10, "Bill", 10000, 1000),
> new Employee(200, 20, "Eric", 8000, 500),
> new Employee(150, 10, null, 7000, null),
> new Employee(110, 10, "Theodore", 11500, 250),
> };
> {code}
> This generates this code:
>
>
> {code:java}
> /* 11 */ public boolean moveNext() {
> /* 12 */ while (inputEnumerator.moveNext()) {
> /* 13 */ final String inp2_ =
> ((org.apache.calcite.test.JdbcTest.Employee) inputEnumerator.current()).name;
> /* 14 */ final String v =
> org.apache.calcite.runtime.SqlFunctions.upper(inp2_);
> /* 15 */ if (inp2_ != null &&
> org.apache.calcite.runtime.SqlFunctions.like(v, "B%") && (inp2_ != null &&
> org.apache.calcite.runtime.SqlFunctions.like(v, "%L"))) {
> /* 16 */ return true;
> /* 17 */ }
> /* 18 */ }
> /* 19 */ return false;
> /* 20 */ }
> {code}
>
> The variable v is computed whenever inp2_ is null. My guess is that v should
> not be inlined, or the function upper
> org.apache.calcite.runtime.SqlFunctions.upper should support null as
> parameter (and return null).
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
