[CALCITE-3150] NullPointerException when UPPER is repeated and combined with LIKE

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Fix Version/s: 1.24.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Feng Zhu
• Reporter: Mickaël Sauvée
• Created: 27/Jun/19 07:57
• Updated: 18/Jun/21 09:00
• Resolved: 26/Jun/20 17:20

Description

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):

 

@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;");
}

And modify data to hava NULL for a name:

 

 

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),
};

This generates this code:

 

 

/*  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 */             }

 

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).

Issue Links

• duplicates: CALCITE-3224 New RexNode-to-Expression CodeGen Implementation
• is related to: CALCITE-3143 Dividing by NULLIF may cause "ArithmeticException: Division by zero"
• is related to: CALCITE-3142 NullPointerException when rounding a nullable numeric
• relates to: CALCITE-3173 RexNode Code Generation Problem
• links to: GitHub Pull Request #1347