[CALCITE-5141] Incomplete implicit type conversion for insert values

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.33.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Jiajun Xie
• Reporter: Jiajun Xie
• Created: 08/May/22 09:21
• Updated: 07/Feb/23 22:33
• Resolved: 10/Dec/22 08:53

Description

• Target table schema

      // Register "T1" table.
      final MockTable t1 =
          MockTable.create(this, tSchema, "T1", false, 7.0, null);
      t1.addColumn("t1_varchar20", f.varchar20Type, true);
      t1.addColumn("t1_smallint", f.smallintType);
      t1.addColumn("t1_int", f.intType);
      t1.addColumn("t1_bigint", f.bigintType);
      t1.addColumn("t1_float", f.floatType);
      t1.addColumn("t1_double", f.doubleType);
      t1.addColumn("t1_decimal", f.decimalType);
      t1.addColumn("t1_timestamp", f.timestampType);
      t1.addColumn("t1_date", f.dateType);
      t1.addColumn("t1_binary", f.binaryType);
      t1.addColumn("t1_boolean", f.booleanType);
      registerTable(t1);
  

• Insert query

  insert into t1 values 
  ('a', 1, 1.0, 0, 0, 0, 0, TIMESTAMP '2021-11-28 00:00:00', date '2021-11-28', x'0A', false), 
  ('b', 2,  2, 0, 0, 0, 0, TIMESTAMP '2021-11-28 00:00:00', date '2021-11-28', x'0A', false), 
  ('c', CAST(3 AS SMALLINT),  3.0, 0, 0, 0, 0, TIMESTAMP '2021-11-28 00:00:00', date '2021-11-28', x'0A', false), 
  ('d', 4, 4.0, 0, 0, 0, 0, TIMESTAMP '2021-11-28 00:00:00', date '2021-11-28', x'0A', false), 
  ('e', 5, 5.0, 0, 0, 0, 0, TIMESTAMP '2021-11-28 00:00:00', date '2021-11-28', x'0A', false)
  

• Incorrect converted plan: the data type is not matched with table schema

  LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT], flattened=[false])
    LogicalValues(tuples=[[
    { 'a', 1, 1, 0, 0, 0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false },
    { 'b', 2, 2, 0, 0, 0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false },
    { 'c', 3, ^3.0^, 0, 0, 0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false },
    { 'd', 4, ^4.0^, 0, 0, 0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false },
    { 'e', 5, ^5.0^, 0, 0, 0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false }]])
              // Here are some numbers that are not integers

• Correct converted plan

LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT], flattened=[false])
  LogicalValues(tuples=[[
{ 'a', 1, 1, 0, 0, 0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false },
 { 'b', 2, 2, 0, 0, 0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false },
 { 'c', 3, 3, 0, 0, 0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false },
 { 'd', 4, 4, 0, 0, 0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false }, 
{ 'e', 5, 5, 0, 0, 0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false }]])

Issue Links

• Blocked: CALCITE-4897 Implicit type conversion is not complete for set operation in DML
• Blocked: CALCITE-5382 Values are not casted to correct type in insertion
• links to: GitHub Pull Request #2847