[CALCITE-1387] CAST(ITEM() as INTEGER) throws RuntimeException in Runtime when return value of ITEM() is null

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.9.0
• Fix Version/s: 1.10.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Jungtaek Lim
• Created: 27/Sep/16 05:48
• Updated: 10/Oct/16 22:38
• Resolved: 10/Oct/16 22:38

Description

Suppose we're defining the following table:

.field("ID", SqlTypeName.INTEGER)
.field("MAPFIELD", SqlTypeName.ANY)
.field("NESTEDMAPFIELD", SqlTypeName.ANY)
.field("ARRAYFIELD", SqlTypeName.ANY)

and query like this:

SELECT * FROM <TBL> WHERE CAST(MAPFIELD['a'] AS INTEGER) = 1

When ITEM returns null (because MAPFIELD['a'] is really null, or there's no key 'a'), CAST throws RuntimeException, saying "cannot convert null to int".

Generated code block is here:

outputValues[0] = inp1_ == null ? (Boolean) null : Boolean.valueOf(org.apache.calcite.runtime.SqlFunctions.toInt(org.apache.calcite.runtime.SqlFunctions.item(inp1_, "a")) == 2);

Btw, CAST(COALESCE(MAPFIELD['a'], -1) AS INTEGER) throws
java.lang.RuntimeException: org.codehaus.commons.compiler.CompileException: Incompatible expression types "java.lang.Object" and "int".

Code for where expression is below:

  final Object inp1_ = (context.values)[1];
  outputValues[0] = inp1_ != null && inp1_ == null ? (Boolean) null : Boolean.valueOf(org.apache.calcite.runtime.SqlFunctions.toInt(inp1_ != null ? org.apache.calcite.runtime.SqlFunctions.item(inp1_, "a") : -1) == 2);

CAST(COALESCE(MAPFIELD['a'], CAST(-1 AS ANY)) AS INTEGER) is generating same code block and also throws same Exception above.

Issue Links

• Is contained by: CALCITE-1386 ITEM operator ignores the value type of the collection, assigns to Object variable