[DAG]Inconsistent Filter in year and string type comparison

[Novemser]

Known affected branch:

dev_master

SQL:

select  count(1)  from full_data_type_table  where tp_year <> 'PingCAP'

Column tp_year here is random year number.

TiSpark:

0

Spark on JDBC
TiDB:

2003

TiSpark Plan:

== Physical Plan ==
*HashAggregate(keys=[], functions=[count(1)])
+- Exchange SinglePartition
   +- *HashAggregate(keys=[], functions=[partial_count(1)])
      +- *Project
         +- *Filter null
            +- TiDB CoprocessorRDD{[table: full_data_type_table] , Ranges: Start:[-9223372036854775808], End: [9223372036854775807], Columns: [tp_year], Aggregates: Not(IsNull([tp_year]))}

Spark on JDBC plan:

== Physical Plan ==
*HashAggregate(keys=[], functions=[count(1)])
+- Exchange SinglePartition
   +- *HashAggregate(keys=[], functions=[partial_count(1)])
      +- *Project
         +- *Filter NOT (cast(tp_year#84 as string) = PingCAP)
            +- *Scan JDBCRelation(tispark_test.full_data_type_table) [numPartitions=1] [tp_year#84] PushedFiltrs: [*IsNotNull(tp_year)], ReadSchema: struct<>

TiDB plan:

+---------------+-------------+---------------+------+-----------------------------------------------------------------+-------+
| id            | parents     | children      | task | operator info                                                   | count |
+---------------+-------------+---------------+------+-----------------------------------------------------------------+-------+
| TableScan_6   | Selection_7 |               | cop  | table:full_data_type_table, range:(-inf,+inf), keep order:false |     2 |
| Selection_7   | HashAgg_5   | TableScan_6   | cop  | ne(tispark_test.full_data_type_table.tp_year, 0)                |     2 |
| HashAgg_5     |             | Selection_7   | cop  | type:complete, funcs:count(1)                                   |     1 |
| TableReader_9 | HashAgg_8   |               | root | data:HashAgg_5                                                  |     1 |
| HashAgg_8     |             | TableReader_9 | root | type:final, funcs:count(col_0)                                  |     1 |
+---------------+-------------+---------------+------+-----------------------------------------------------------------+-------+

TiSpark Version:

pre-dag-100-gf550205-CLI-pre-dag-64-g75599c1

TiSpark plan's Filter may not be correct.

[Novemser]

select  count(1)  from full_data_type_table  where cast(tp_year as string) <> 'PingCAP'

Show the same result 2003 both in TISpark and Spark JDBC.
TiSpark plan:

== Physical Plan ==
*HashAggregate(keys=[], functions=[count(1)])
+- Exchange SinglePartition
   +- *HashAggregate(keys=[], functions=[partial_count(1)])
      +- *Project
         +- *Filter NOT (cast(tp_year#7L as string) = 2017)
            +- TiDB CoprocessorRDD{[table: full_data_type_table] , Ranges: Start:[-9223372036854775808], End: [9223372036854775807], Columns: [tp_year], Aggregates: Not(IsNull([tp_year]))}

Spark JDBC plan:

== Physical Plan ==
*HashAggregate(keys=[], functions=[count(1)])
+- Exchange SinglePartition
   +- *HashAggregate(keys=[], functions=[partial_count(1)])
      +- *Project
         +- *Filter NOT (cast(tp_year#84 as string) = 2017)
            +- *Scan JDBCRelation(tispark_test.full_data_type_table) [numPartitions=1] [tp_year#84] PushedFilters: [*IsNotNull(tp_year)], ReadSchema: struct<>

[birdstorm]

The issue is caused by different type conversion by Spark and TiSpark from mysql because in Both Spark and TiSpark, year is not an existing type.

For Spark, year type in mysql is casted as date. Thus it is used and shown as, for instance, 2017-01-01. Comparison between date and string addressed above is made by casting date to string.

For TiSpark, year type is decoded as long. When comparing long and sting, string will be casted to long implicitly. And if cast fails, its result will be null, which is addressed in this issue.

Not sure if we should change our decode to match Spark's, our current behavior is consistent with that in mysql. @ilovesoup