scan-filter results is wrong

[fzhedu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql -uroot -P52324 -h172.16.4.97 -D subquery_agg_decimal3000

2. What did you expect to see? (Required)

from mysql

mysql> SELECT `col_bigint_unsigned` AS field1 FROM C AS table1 WHERE ( table1 . `pk` > 17 AND table1 . `pk` < ( 17 + 50 ) AND table1 . `col_decimal_20_20_unsigned_not_null` > table1 . `col_decimal_30_key` ) order by 1;
+----------------------+
| field1               |
+----------------------+
|                 NULL |
|                 NULL |
|                 NULL |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|  1937955214652866560 |
|  7579558172864544768 |
| 17922356192097599488 |
+----------------------+
15 rows in set (0.01 sec)

from tidb+ tikv

mysql> set @@tidb_isolation_read_engines='tikv,tidb'; set @@tidb_allow_mpp=0;
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> desc SELECT `col_bigint_unsigned` AS field1 FROM C AS table1 WHERE ( table1 . `pk` > 17 AND table1 . `pk` < ( 17 + 50 ) AND table1 . `col_decimal_20_20_unsigned_not_null` > table1 . `col_decimal_30_key` ) order by 1;
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                                                                                     |
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------+
| Sort_5                       | 1.34    | root      |               | subquery_agg_decimal3000.c.col_bigint_unsigned                                                                    |
| └─Projection_7               | 1.34    | root      |               | subquery_agg_decimal3000.c.col_bigint_unsigned                                                                    |
|   └─TableReader_10           | 1.34    | root      |               | data:Selection_9                                                                                                  |
|     └─Selection_9            | 1.34    | cop[tikv] |               | gt(subquery_agg_decimal3000.c.col_decimal_20_20_unsigned_not_null, subquery_agg_decimal3000.c.col_decimal_30_key) |
|       └─TableRangeScan_8     | 1.68    | cop[tikv] | table:table1  | range:(17,67), keep order:false, stats:pseudo                                                                     |
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.04 sec)

mysql>  SELECT `col_bigint_unsigned` AS field1 FROM C AS table1 WHERE ( table1 . `pk` > 17 AND table1 . `pk` < ( 17 + 50 ) AND table1 . `col_decimal_20_20_unsigned_not_null` > table1 . `col_decimal_30_key` ) order by 1;
+----------------------+
| field1               |
+----------------------+
|                 NULL |
|                 NULL |
|                 NULL |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|  1937955214652866560 |
|  7579558172864544768 |
| 17922356192097599488 |
+----------------------+
15 rows in set (0.04 sec)

3. What did you see instead (Required)

from tiflash

mysql>  SELECT `col_bigint_unsigned` AS field1 FROM C AS table1 WHERE ( table1 . `pk` > 17 AND table1 . `pk` < ( 17 + 50 ) AND table1 . `col_decimal_20_20_unsigned_not_null` > table1 . `col_decimal_30_key` ) order by 1;
+----------------------+
| field1               |
+----------------------+
|                 NULL |
|                 NULL |
|                 NULL |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|                    0 |
|  1937955214652866560 |
| 17922356192097599488 |
+----------------------+
15 rows in set (0.04 sec)

mysql> desc SELECT `col_bigint_unsigned` AS field1 FROM C AS table1 WHERE ( table1 . `pk` > 17 AND table1 . `pk` < ( 17 + 50 ) AND table1 . `col_decimal_20_20_unsigned_not_null` > table1 . `col_decimal_30_key` ) order by 1;
+-------------------------------+---------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------+
| id                            | estRows | task         | access object | operator info                                                                                                     |
+-------------------------------+---------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------+
| Sort_5                        | 1.34    | root         |               | subquery_agg_decimal3000.c.col_bigint_unsigned                                                                    |
| └─Projection_7                | 1.34    | root         |               | subquery_agg_decimal3000.c.col_bigint_unsigned                                                                    |
|   └─TableReader_13            | 1.34    | root         |               | data:Selection_12                                                                                                 |
|     └─Selection_12            | 1.34    | cop[tiflash] |               | gt(subquery_agg_decimal3000.c.col_decimal_20_20_unsigned_not_null, subquery_agg_decimal3000.c.col_decimal_30_key) |
|       └─TableRangeScan_11     | 1.68    | cop[tiflash] | table:table1  | range:(17,67), keep order:false, stats:pseudo                                                                     |
+-------------------------------+---------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------+

4. What is your TiFlash version? (Required)

nightly

[windtalker]

It looks like the there is a bug in TiFlash's decimal compare

mysql>  select v1,v2, v1 > v2 from test;
+------------------------+----------------------+---------+
| v1                     | v2                   | v1 > v2 |
+------------------------+----------------------+---------+
| 0.00000000000000000000 |  2585910611040796672 |       1 |
| 0.00000000000000000000 | -1901644942657191936 |       0 |
+------------------------+----------------------+---------+
2 rows in set (0.04 sec)

mysql> desc test;
+-------+----------------+------+------+---------+-------+
| Field | Type           | Null | Key  | Default | Extra |
+-------+----------------+------+------+---------+-------+
| id    | int(11)        | YES  |      | NULL    |       |
| v1    | decimal(20,20) | NO   |      | NULL    |       |
| v2    | decimal(30,0)  | YES  |      | NULL    |       |
+-------+----------------+------+------+---------+-------+
3 rows in set (0.03 sec)

[fzhedu]

may be the same problem, to check
from tiflash

mysql> SELECT  `col_decimal_65_unsigned_not_null_key` AS field1 , `col_decimal_30_10_unsigned_not_null` AS field2 , `col_decimal_65_unsigned_not_null_key` AS field3 FROM BB AS table1 WHERE ( table1 . `col_decimal_key` = 182 OR ( ( table1 . `col_int_key` = 206 AND ( table1 . `col_decimal_65_10_key` = 224 AND ( table1 . `col_decimal_key` = table1 . `col_decimal_key` AND ( table1 . `pk` = 62 OR ( table1 . `col_int_key` = 181 AND table1 . `col_int_key` = 133 ) ) ) ) ) OR table1 . `col_decimal_20_20_not_null` > table1 . `col_decimal_20_20_unsigned` ) AND table1 . `col_decimal_20_20_unsigned` >= table1 . `col_bigint_not_null_key` )  GROUP BY field1, field2, field3;
+---------------------+--------------+---------------------+
| field1              | field2       | field3              |
+---------------------+--------------+---------------------+
|                   0 | 0.0000000000 |                   0 |
| 3001367676665724928 | 0.0000000000 | 3001367676665724928 |
+---------------------+--------------+---------------------+

from mysql

mysql> SELECT  `col_decimal_65_unsigned_not_null_key` AS field1 , `col_decimal_30_10_unsigned_not_null` AS field2 , `col_decimal_65_unsigned_not_null_key` AS field3 FROM BB AS table1 WHERE ( table1 . `col_decimal_key` = 182 OR ( ( table1 . `col_int_key` = 206 AND ( table1 . `col_decimal_65_10_key` = 224 AND ( table1 . `col_decimal_key` = table1 . `col_decimal_key` AND ( table1 . `pk` = 62 OR ( table1 . `col_int_key` = 181 AND table1 . `col_int_key` = 133 ) ) ) ) ) OR table1 . `col_decimal_20_20_not_null` > table1 . `col_decimal_20_20_unsigned` ) AND table1 . `col_decimal_20_20_unsigned` >= table1 . `col_bigint_not_null_key` )  GROUP BY field1, field2, field3;
+---------------------+--------------+---------------------+
| field1              | field2       | field3              |
+---------------------+--------------+---------------------+
| 3001367676665724928 | 0.0000000000 | 3001367676665724928 |
+---------------------+--------------+---------------------+

[fzhedu]

may be the same problem, to check

mysql> SELECT  MIN( `col_bigint_key` ) AS field1, MAX( `col_bigint_key` ) AS field2, `col_int_not_null` AS field3 , `col_decimal_20_20_not_null` AS field4 , `col_decimal_unsigned_key` AS field5 , `col_decimal_unsigned` AS field6 FROM BB AS table1 WHERE table1 . `col_decimal_30_not_null` >= table1 . `col_decimal_20_20_not_null`  GROUP BY field3, field4, field5, field6;
+----------------------+----------------------+-------------+-------------------------+------------+------------+
| field1               | field2               | field3      | field4                  | field5     | field6     |
+----------------------+----------------------+-------------+-------------------------+------------+------------+
| -2479231594867458048 | -2479231594867458048 |  1381302272 |  0.99999999999999999999 |          0 |          0 |
|  3710966092953288704 |  3710966092953288704 |           0 | -0.99999999999999999999 |       NULL |          0 |
|                    0 |                    0 |           0 |  0.99999999999999999999 |       NULL | 9999999999 |
|  5854398040604934144 |  5854398040604934144 | -2147483648 |  0.00000000000000000000 | 9999999999 |       NULL |
|                 NULL |                 NULL | -1907359744 |  0.00000000000000000000 |          0 |          0 |
|                    0 |                    0 | -2147483648 |  0.00000000000000000000 |          0 |       NULL |
|                 NULL |                 NULL |  1746796544 | -0.99999999999999999999 |          0 | 9999999999 |
+----------------------+----------------------+-------------+-------------------------+------------+------------+
7 rows in set (0.05 sec)

from mysql

mysql> SELECT  MIN( `col_bigint_key` ) AS field1, MAX( `col_bigint_key` ) AS field2, `col_int_not_null` AS field3 , `col_decimal_20_20_not_null` AS field4 , `col_decimal_unsigned_key` AS field5 , `col_decimal_unsigned` AS field6 FROM BB AS table1 WHERE table1 . `col_decimal_30_not_null` >= table1 . `col_decimal_20_20_not_null`  GROUP BY field3, field4, field5, field6;
+----------------------+----------------------+-------------+-------------------------+------------+------------+
| field1               | field2               | field3      | field4                  | field5     | field6     |
+----------------------+----------------------+-------------+-------------------------+------------+------------+
|                    0 |                    0 | -2147483648 |  0.00000000000000000000 |          0 |       NULL |
|  5854398040604934144 |  5854398040604934144 | -2147483648 |  0.00000000000000000000 | 9999999999 |       NULL |
| -2479231594867458048 | -2479231594867458048 |  1381302272 |  0.99999999999999999999 |          0 |          0 |
|                 NULL |                 NULL |  1746796544 | -0.99999999999999999999 |          0 | 9999999999 |
|                 NULL |                 NULL | -1907359744 |  0.00000000000000000000 |          0 |          0 |
|                    0 |                    0 |           0 |  0.99999999999999999999 |       NULL | 9999999999 |
+----------------------+----------------------+-------------+-------------------------+------------+------------+
6 rows in set (0.00 sec)

[fzhedu]

may be the same problem, to check

from tiflash

mysql> SELECT    COUNT(  table1 . `col_int_unsigned` ) AS field1 FROM ( A AS table1 INNER JOIN D AS table2 ON (table2 . `col_decimal_30_10_key` >= table1 . `col_decimal_20_20_not_null` ) )    ;
+--------+
| field1 |
+--------+
|   3408 |
+--------+
1 row in set (0.06 sec)

from mysql

mysql> SELECT    COUNT(  table1 . `col_int_unsigned` ) AS field1 FROM ( A AS table1 INNER JOIN D AS table2 ON (table2 . `col_decimal_30_10_key` >= table1 . `col_decimal_20_20_not_null` ) )    ;
+--------+
| field1 |
+--------+
|   3584 |
+--------+
1 row in set (0.00 sec)

[fzhedu]

SELECT col_decimal_65_not_null_key AS field1 FROM D AS table1 WHERE table1 . col_bigint_not_null_key >= table1 . col_decimal_20_20 GROUP BY field1;

[fzhedu]

SELECT table2 . col_decimal_65_not_null_key AS field1 , table1 . col_decimal_65_10_not_null_key AS field2 FROM ( C AS table1 STRAIGHT_JOIN C AS table2 ON (table2 . col_decimal_20_20 = table1 . col_int_key ) ) WHERE table2 . col_bigint <> 227 GROUP BY field1, field2 HAVING field2 < -8.16 ;

[fzhedu]

SELECT col_decimal_65_unsigned_not_null_key AS field1 , col_decimal_30_10_unsigned_not_null AS field2 , col_decimal_65_unsigned_not_null_key AS field3 FROM BB AS table1 WHERE ( table1 . col_decimal_key = 182 OR ( ( table1 . col_int_key = 206 AND ( table1 . col_decimal_65_10_key = 224 AND ( table1 . col_ decimal_key = table1 . col_decimal_key AND ( table1 . pk = 62 OR ( table1 . col_int_key = 181 AND table1 . col_int_key = 133 ) ) ) ) ) OR table1 . col_decimal_20_20_not_null > table1 . col_decimal_20_20_unsigned ) AND table1 . col_decimal_20_20_unsigned >= table1 . col_bigint_not_null_key ) GROUP BY field1, field2, field3;

[fzhedu]

SELECT MAX( col_decimal_20_20_unsigned_not_null_key) AS field1 , col_decimal_30_10_unsigned_not_null_key AS field2 , col_int_key AS field3 FROM CC AS table1 WHERE ( ( table1 . pk > 101 AND table1 . pk < ( 101 + 59 ) OR ( table1 . col_decimal_30_10_key IN (41) OR ( table1 . col_decimal_65_10_key NOT BE
TWEEN 101 AND ( 101 + 213 ) OR table1 . col_decimal_20_20_key NOT BETWEEN 101 AND ( 101 + 245 ) ) ) ) AND table1 . col_decimal_20_20_unsigned < table1 . col_bigint_key AND table1 . col_decimal_65_unsigned_not_null_key > -8.16 ) GROUP BY field2, field3;

[fzhedu]

SELECT table1 . col_decimal_65_10_unsigned_key = SOME ( SELECT 7 FROM DUAL ) AS field1 , ( table1 . col_decimal_65_not_null , table1 . col_bigint_unsigned_not_null ) NOT IN ( SELECT SUBQUERY2_t1 . col_bigint_unsign ed AS SUBQUERY2_field1 , SUBQUERY2_t1 . col_decimal_65_10_unsigned AS SUBQUERY2_field2 FROM C AS SUBQUERY2_t1 ) AS field2 , ( ( table2 . col_decimal_65 ) * ( table2 . col_decimal_not_null_key ) ) AS field3 , table1 . col_decimal_65_10_not_null <= SOME ( SELECT 2 FROM DUAL ) AS field4 , table1 . col_decimal_unsigned >= ALL ( SELECT SUBQUERY4_t1 . col_decimal_65_10_unsigned_not_null_key AS SUBQUERY4_field1 FROM ( DD AS SUBQUERY4_t1 STRAIGHT_JOIN CC AS SUBQUERY4_t2 ON (SUBQUERY4_t2 . col_decimal_key = SUBQUERY4_t1 . col_decimal_65_10_unsigned ) ) WHERE SUBQUERY4_t1 . col_int_not_null != ( SELECT AVG( CHILD_SUBQUERY1_t1 . col_tinyint_unsigned ) AS CHILD_SUBQUERY1_field1 FROM C AS CHILD_SUBQUERY1_t1 WHERE ( CHILD_SUBQUERY1_t1 . col_decimal_not_null = CHILD_SUBQUERY1_t1 . col_decimal_30_10_not_null OR CHILD_SUBQUERY1_t1 . col_tinyint_unsigned_key <= CHILD_SUBQUERY1_t1 . col_decimal_20_20_unsigned_not_null_key ) ) ) AS field5 FROM ( C AS table1 STRAIGHT_JOIN ( SELECT SUBQUERY5_t1 . * FROM DD AS SUBQUERY5_t1 WHERE SUBQUERY5_t1 . col_tinyint_not_null <= SUBQUERY5_t1 . col_decimal_30_10_unsigned_not_null ) AS table2 ON (table2 . col_decimal_20_20_key = table1 . col_tinyint_unsigned ) ) WHERE ( ( 1, -5 ) IN ( SELECT SUBQUERY6_t1 . col_decimal_65_10_not_null AS SUBQUERY6_field1 , SUBQUERY6_t1 . col_tinyint_unsigned_key AS SUBQUERY6_field2 FROM ( B AS SUBQUERY6_t1 LEFT OUTER JOIN BB AS SUBQUERY6_t2 ON (SUBQUERY6_t2 . col_int_key = SUBQUERY6_t1 . col_tinyint_not_null ) ) ) ) OR table1 . col_decimal_20_20_unsigned < table1 . col_decimal_unsigned GROUP BY field1, field2, field3, field4, field5 ;

[fzhedu]

SELECT ( table1 . col_bigint , table1 . col_decimal_30_10_not_null_key ) NOT IN ( SELECT 6 , 6 UNION SELECT 7, -8.16 ) AS field1 , max( table2 . col_bigint_unsigned_key ) AS field2 FROM ( BB AS table1 STRAIGHT_JOIN BB AS table2 ON (table2 . col_tinyint_unsigned_not_null = table1 . col_decimal_20_20_key ) ) WHERE ( table1 . col_decimal_not_null IN ( SELECT SUBQUERY2_t1 . col_decimal_65_10_not_null AS SUBQUERY2_field1 FROM A AS SUBQUERY2_t1 WHERE SUBQUERY2_t1 . col_decimal_20_20_not_null_key != table1 . col_bigint_unsigned_key AND SUBQUERY2_t1 . col_decimal_65_10_unsigned > 3 ) ) OR ( table1 . col_decimal_20_20_unsigned IS NOT NULL AND table1 . col_bigint_unsigned > table2 . col_decimal_30_10_not_null_key ) GROUP BY field1 ;