bug: datetime SELECT statement did not query the test result

[shangyanwen]

Describe the problem

CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL)engine=stonedb;
INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
##The error results are as follows
Empty set (0.00 sec)

Expected behavior

##The following is：innodb test result
SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
+---------------------+------------+
| a                   | b          |
+---------------------+------------+
| 2001-01-01 00:00:00 | 2001-01-01 |
+---------------------+------------+
1 row in set (0.00 sec)

How To Reproduce

CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL)engine=stonedb;
INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';

Environment

1. StoneDB for mysql5.7 (release)
2. Ubuntu 20.04.4

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[hustjieke]

Wrong execute plan on and contition:

mysql> explain SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where with pushed condition ((`test`.`t1`.`a` = '2001-01-01 00:00:00') and ('2001-01-01 00:00:00' = `test`.`t1`.`b`) and (`test`.`t1`.`b` = '2001-01-01'))(t0) Pckrows: 1, susp. 1 (0 empty 0 full). Conditions: 3
1 row in set, 1 warning (2.83 sec)

[shangyanwen]

stonedb 5.7_v1.0.1,Regression This bug still exists

[duanfuxiang0]

ACK

[duanfuxiang0]

simplifying the sql to SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b; the result is wrong because the Optimizer turns the original filter condition(a='2001-01-01 00:00:00' AND a=b) into a='2001-01-01 00:00:00' AND b=2001-01-01 00:00:00, and b is a string type, value is 2001-01-01(!='2001-01-01 00:00:00'), while innodb is correct because its term contains extra information (Arg_comparator::compare_datetime), tianmu's CQTerm does not have this information.
This query can currently be supported by modifying the sql to

SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01';

[shangyanwen]

As described in the above research and development, the verification passed