Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1754

In Csv adapter, convert DATE and TIME values to int, and TIMESTAMP values to long

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.12.0
    • Fix Version/s: 1.13.0
    • Component/s: csv-adapter
    • Labels:
      None

      Description

      Queries grouping by the timestampadd function throw ClassCastException. Using CsvTest:

          checkSql("model","select count(*),  JOINEDAT from EMPS group by JOINEDAT ");
      

      works well, however

          checkSql("model","select count(*), {fn timestampadd(SQL_TSI_DAY, 1, JOINEDAT) }  from EMPS group by {fn timestampadd(SQL_TSI_DAY, 1, JOINEDAT ) } ");
      

      throws

      Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to java.sql.Date
      	at Baz$3.apply(ANONYMOUS.java:88)
      	at Baz$3.apply(ANONYMOUS.java)
      	at org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:832)
      	at org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:761)
      	at org.apache.calcite.linq4j.DefaultEnumerable.groupBy(DefaultEnumerable.java:302)
      	at Baz.bind(Baz.java:70)
      	at org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:331)
      	at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:294)
      	at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:553)
      	at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:544)
      	at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:193)
      	at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:67)
      	at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:44)
      	at org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:607)
      	at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:600)
      	at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:615)
      	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:148)
      
      

      the generated code is:

      /*   1 */ public static class Record1_0 implements java.io.Serializable {
      /*   2 */   public long f0;
      /*   3 */   public Record1_0() {}
      /*   4 */   public boolean equals(Object o) {
      /*   5 */     if (this == o) {
      /*   6 */       return true;
      /*   7 */     }
      /*   8 */     if (!(o instanceof Record1_0)) {
      /*   9 */       return false;
      /*  10 */     }
      /*  11 */     return this.f0 == ((Record1_0) o).f0;
      /*  12 */   }
      /*  13 */ 
      /*  14 */   public int hashCode() {
      /*  15 */     int h = 0;
      /*  16 */     h = org.apache.calcite.runtime.Utilities.hash(h, this.f0);
      /*  17 */     return h;
      /*  18 */   }
      /*  19 */ 
      /*  20 */   public int compareTo(Record1_0 that) {
      /*  21 */     final int c;
      /*  22 */     c = org.apache.calcite.runtime.Utilities.compare(this.f0, that.f0);
      /*  23 */     if (c != 0) {
      /*  24 */       return c;
      /*  25 */     }
      /*  26 */     return 0;
      /*  27 */   }
      /*  28 */ 
      /*  29 */   public String toString() {
      /*  30 */     return "{f0=" + this.f0 + "}";
      /*  31 */   }
      /*  32 */ 
      /*  33 */ }
      /*  34 */ 
      /*  35 */ org.apache.calcite.DataContext root;
      /*  36 */ 
      /*  37 */ public org.apache.calcite.linq4j.Enumerable bind(final org.apache.calcite.DataContext root0) {
      /*  38 */   root = root0;
      /*  39 */   final org.apache.calcite.rel.RelNode v0stashed = (org.apache.calcite.rel.RelNode) root.get("v0stashed");
      /*  40 */   final org.apache.calcite.interpreter.Interpreter interpreter = new org.apache.calcite.interpreter.Interpreter(
      /*  41 */     root,
      /*  42 */     v0stashed);
      /*  43 */   final org.apache.calcite.linq4j.AbstractEnumerable child = new org.apache.calcite.linq4j.AbstractEnumerable(){
      /*  44 */     public org.apache.calcite.linq4j.Enumerator enumerator() {
      /*  45 */       return new org.apache.calcite.linq4j.Enumerator(){
      /*  46 */           public final org.apache.calcite.linq4j.Enumerator inputEnumerator = interpreter.enumerator();
      /*  47 */           public void reset() {
      /*  48 */             inputEnumerator.reset();
      /*  49 */           }
      /*  50 */ 
      /*  51 */           public boolean moveNext() {
      /*  52 */             return inputEnumerator.moveNext();
      /*  53 */           }
      /*  54 */ 
      /*  55 */           public void close() {
      /*  56 */             inputEnumerator.close();
      /*  57 */           }
      /*  58 */ 
      /*  59 */           public Object current() {
      /*  60 */             final Object[] current = (Object[]) inputEnumerator.current();
      /*  61 */             return (java.sql.Date) current[9] == null ? (Integer) null : Integer.valueOf(org.apache.calcite.runtime.SqlFunctions.toInt(current[9]) + (int) $L4J$C$86400000L_1_86400000L);
      /*  62 */           }
      /*  63 */ 
      /*  64 */           static final long $L4J$C$86400000L_1 = 86400000L * 1;
      /*  65 */           static final long $L4J$C$86400000L_1_86400000L = $L4J$C$86400000L_1 / 86400000L;
      /*  66 */         };
      /*  67 */     }
      /*  68 */ 
      /*  69 */   };
      /*  70 */   final org.apache.calcite.linq4j.Enumerable _inputEnumerable = child.groupBy(org.apache.calcite.linq4j.function.Functions.identitySelector(), new org.apache.calcite.linq4j.function.Function0() {
      /*  71 */     public Object apply() {
      /*  72 */       long COUNTa0s0;
      /*  73 */       COUNTa0s0 = 0L;
      /*  74 */       Record1_0 record0;
      /*  75 */       record0 = new Record1_0();
      /*  76 */       record0.f0 = COUNTa0s0;
      /*  77 */       return record0;
      /*  78 */     }
      /*  79 */   }
      /*  80 */   , new org.apache.calcite.linq4j.function.Function2() {
      /*  81 */     public Record1_0 apply(Record1_0 acc, java.sql.Date in) {
      /*  82 */       acc.f0++;
      /*  83 */       return acc;
      /*  84 */     }
      /*  85 */     public Record1_0 apply(Object acc, Object in) {
      /*  86 */       return apply(
      /*  87 */         (Record1_0) acc,
      /*  88 */         (java.sql.Date) in);
      /*  89 */     }
      /*  90 */   }
      /*  91 */   , new org.apache.calcite.linq4j.function.Function2() {
      /*  92 */     public Object[] apply(java.sql.Date key, Record1_0 acc) {
      /*  93 */       return new Object[] {
      /*  94 */           key,
      /*  95 */           acc.f0};
      /*  96 */     }
      /*  97 */     public Object[] apply(Object key, Object acc) {
      /*  98 */       return apply(
      /*  99 */         (java.sql.Date) key,
      /* 100 */         (Record1_0) acc);
      /* 101 */     }
      /* 102 */   }
      /* 103 */   );
      /* 104 */   return new org.apache.calcite.linq4j.AbstractEnumerable(){
      /* 105 */       public org.apache.calcite.linq4j.Enumerator enumerator() {
      /* 106 */         return new org.apache.calcite.linq4j.Enumerator(){
      /* 107 */             public final org.apache.calcite.linq4j.Enumerator inputEnumerator = _inputEnumerable.enumerator();
      /* 108 */             public void reset() {
      /* 109 */               inputEnumerator.reset();
      /* 110 */             }
      /* 111 */ 
      /* 112 */             public boolean moveNext() {
      /* 113 */               return inputEnumerator.moveNext();
      /* 114 */             }
      /* 115 */ 
      /* 116 */             public void close() {
      /* 117 */               inputEnumerator.close();
      /* 118 */             }
      /* 119 */ 
      /* 120 */             public Object current() {
      /* 121 */               final Object[] current = (Object[]) inputEnumerator.current();
      /* 122 */               return new Object[] {
      /* 123 */                   current[1],
      /* 124 */                   current[0]};
      /* 125 */             }
      /* 126 */ 
      /* 127 */           };
      /* 128 */       }
      /* 129 */ 
      /* 130 */     };
      /* 131 */ }
      /* 132 */ 
      /* 133 */ 
      /* 134 */ public Class getElementType() {
      /* 135 */   return java.lang.Object[].class;
      /* 136 */ }
      /* 137 */ 
      /* 138 */ 
      

        Issue Links

          Activity

          Hide
          mahongbin hongbin ma added a comment -

          Julian Hyde The issue reminds me that you have spoken of "I'd rather that we convert java.sql.Date values when they enter the system" in multiple issues like https://issues.apache.org/jira/browse/CALCITE-1629, https://issues.apache.org/jira/browse/CALCITE-1569. Will "convert java.sql.Date values when they enter the system" be the silver bullet to all these similar issues? Can you please further elaborate on this?

          Show
          mahongbin hongbin ma added a comment - Julian Hyde The issue reminds me that you have spoken of "I'd rather that we convert java.sql.Date values when they enter the system" in multiple issues like https://issues.apache.org/jira/browse/CALCITE-1629 , https://issues.apache.org/jira/browse/CALCITE-1569 . Will "convert java.sql.Date values when they enter the system" be the silver bullet to all these similar issues? Can you please further elaborate on this?
          Hide
          julianhyde Julian Hyde added a comment -

          When the CSV adapter returns values of a column whose SQL type is DATE, the values should be represented as Java objects of type Integer, because that is how Calcite's enumerable mode represents DATEs.

          Similarly, TIME should be Integer, and TIMESTAMP should be Long.

          Fix the CSV adapter and I think all these problems will go away. (May need to fix the new file adapter also.)

          Show
          julianhyde Julian Hyde added a comment - When the CSV adapter returns values of a column whose SQL type is DATE, the values should be represented as Java objects of type Integer, because that is how Calcite's enumerable mode represents DATEs. Similarly, TIME should be Integer, and TIMESTAMP should be Long. Fix the CSV adapter and I think all these problems will go away. (May need to fix the new file adapter also.)
          Hide
          mahongbin hongbin ma added a comment - - edited

          I suppose JDBCTest is irrelavant to CSV adapter. when I add following to JDBCTest:

            /** Tests a timestamp literal against JDBC data source. */
            @Test public void testJdbcTimestampadd() {
              CalciteAssert.that()
                  .with(CalciteAssert.Config.JDBC_FOODMART)
                  .query("select {fn timestampadd(SQL_TSI_DAY, 1, \"hire_date\") }  "
                      + "   from \"foodmart\".\"employee\"  ").runs();
            }
          

          exception is thrown.

          So I'm wondering if it's a adapter issue.(I assume by "CSV adapter return values" you mean `inputEnumerator.current();` in line 60 of generated code above)
          If it's a adapter issue, the exception should have been thrown in line 61, rather than line 88.
          In line 88, the code is trying to cast Object in (whose type is Integer) to Date, is it normal? what do you expect line 88 look like?

          I'm trying to fix the issue so it's best if you could kindly offer more details

          Show
          mahongbin hongbin ma added a comment - - edited I suppose JDBCTest is irrelavant to CSV adapter. when I add following to JDBCTest: /** Tests a timestamp literal against JDBC data source. */ @Test public void testJdbcTimestampadd() { CalciteAssert.that() .with(CalciteAssert.Config.JDBC_FOODMART) .query( "select {fn timestampadd(SQL_TSI_DAY, 1, \" hire_date\ ") } " + " from \" foodmart\ ".\" employee\ " " ).runs(); } exception is thrown. So I'm wondering if it's a adapter issue.(I assume by "CSV adapter return values" you mean `inputEnumerator.current();` in line 60 of generated code above) If it's a adapter issue, the exception should have been thrown in line 61, rather than line 88. In line 88, the code is trying to cast Object in (whose type is Integer) to Date, is it normal? what do you expect line 88 look like? I'm trying to fix the issue so it's best if you could kindly offer more details
          Hide
          mahongbin hongbin ma added a comment -

          Julian Hyde hope to get some feedback&comment from your side, I'm drafting a patch for this recently.

          Show
          mahongbin hongbin ma added a comment - Julian Hyde hope to get some feedback&comment from your side, I'm drafting a patch for this recently.
          Hide
          mahongbin hongbin ma added a comment -

          Just succeeded to convert java.sql.Date values when they enter the system, and as Julian Hyde expected the problem goes away. I'll submit the patch soon after code cleanup

          Show
          mahongbin hongbin ma added a comment - Just succeeded to convert java.sql.Date values when they enter the system, and as Julian Hyde expected the problem goes away. I'll submit the patch soon after code cleanup
          Hide
          mahongbin hongbin ma added a comment - - edited

          patch attached. I also added a `BuiltInMethod.ADD_MONTHS_INT` to solve the "cannot cast Integer to Long issue" when running

          select count(*), {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT) } from EMPS group by {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT ) }
          

          The above test case can be found in org.apache.calcite.test.CsvTest#testGroupbyTimestampadd

          Show
          mahongbin hongbin ma added a comment - - edited patch attached. I also added a `BuiltInMethod.ADD_MONTHS_INT` to solve the "cannot cast Integer to Long issue" when running select count(*), {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT) } from EMPS group by {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT ) } The above test case can be found in org.apache.calcite.test.CsvTest#testGroupbyTimestampadd
          Show
          mahongbin hongbin ma added a comment - PR at https://github.com/apache/calcite/pull/432
          Hide
          julianhyde Julian Hyde added a comment -

          Reviewing now (along with CALCITE-1639).

          Show
          julianhyde Julian Hyde added a comment - Reviewing now (along with CALCITE-1639 ).
          Hide
          julianhyde Julian Hyde added a comment -
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/cc839874 . Thanks for the PR, hongbin ma !
          Hide
          jcamachorodriguez Jesus Camacho Rodriguez added a comment -

          Resolved in release 1.13.0 (2017-06-26).

          Show
          jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.13.0 (2017-06-26).

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              mahongbin hongbin ma
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development