executor: some unexpected behaviors of TopN #15718

zhongzc · 2020-03-26T07:01:17Z

Bug Report

1. What did you do?

Some logical operations in TopN executor are not working well.

Data: https://github.com/tikv/copr-test/blob/master/push-down-test/prepare/0_data.sql
standalone TiDB (wtih mocktikv)

Order by first column with 1 column.

mysql root@127.0.0.1:test> SELECT `col_decimal` AND `col_varchar_1` AS field1, `col_decimal`, `col_varchar_1`, 1 FROM `table1000_int_autoinc` ORDER BY
                            field1 LIMIT 10;                                                                                                          
+--------+-------------+---------------+---+
| field1 | col_decimal | col_varchar_1 | 1 |
+--------+-------------+---------------+---+
| 0      | 53131       | 0             | 1 |
| <null> | 7           | <null>        | 1 |
| 0      | 54712       | r             | 1 |
| <null> | <null>      | 2             | 1 |
| 0      | 0           | 2             | 1 |
| 0      | <null>      | w             | 1 |
| 0      | 704905216   | j             | 1 |
| 0      | 0           | o             | 1 |
| 0      | 1514078208  | g             | 1 |
| 0      | 7           | g             | 1 |
+--------+-------------+---------------+---+
10 rows in set
Time: 0.066s

Order by first column w/o 1 column.

mysql root@127.0.0.1:test> SELECT `col_decimal` AND `col_varchar_1` AS field1, `col_decimal`, `col_varchar_1` FROM `table1000_int_autoinc` ORDER BY fi
                           eld1 LIMIT 10;                                                                                                             
+--------+-------------+---------------+
| field1 | col_decimal | col_varchar_1 |
+--------+-------------+---------------+
| 0      | 7           | <null>        |
| 0      | 54712       | r             |
| 0      | <null>      | 2             |
| 0      | 0           | 2             |
| 0      | <null>      | w             |
| 0      | 704905216   | j             |
| 0      | 0           | o             |
| 0      | 1514078208  | g             |
| 0      | 7           | g             |
| 0      | 53131       | 0             |
+--------+-------------+---------------+

2. What did you expect to see?

What MySQL does:

mysql root@127.0.0.1:test> SELECT `col_decimal` AND `col_varchar_1` AS field1, `col_decimal`, `col_varchar_1`, 1 FROM `table1000_int_autoinc` ORDER BY
                            field1 LIMIT 10;                                                                                                          
+--------+-------------+---------------+---+
| field1 | col_decimal | col_varchar_1 | 1 |
+--------+-------------+---------------+---+
| <null> | <null>      | 2             | 1 |
| <null> | -589168640  | <null>        | 1 |
| <null> | <null>      | 2             | 1 |
| <null> | <null>      | 2             | 1 |
| <null> | 7           | <null>        | 1 |
| <null> | <null>      | 1             | 1 |
| <null> | 1616052224  | <null>        | 1 |
| <null> | <null>      | <null>        | 1 |
| <null> | <null>      | 5             | 1 |
| <null> | 7           | <null>        | 1 |
+--------+-------------+---------------+---+
10 rows in set
Time: 0.023s

mysql root@127.0.0.1:test> SELECT `col_decimal` AND `col_varchar_1` AS field1, `col_decimal`, `col_varchar_1` FROM `table1000_int_autoinc` ORDER BY fi
                           eld1 LIMIT 10;                                                                                                             
+--------+-------------+---------------+
| field1 | col_decimal | col_varchar_1 |
+--------+-------------+---------------+
| <null> | <null>      | 2             |
| <null> | -589168640  | <null>        |
| <null> | <null>      | 2             |
| <null> | <null>      | 2             |
| <null> | 7           | <null>        |
| <null> | <null>      | 1             |
| <null> | 1616052224  | <null>        |
| <null> | <null>      | <null>        |
| <null> | <null>      | 5             |
| <null> | 7           | <null>        |
+--------+-------------+---------------+
10 rows in set
Time: 0.024s

3. What version of TiDB are you using? (`tidb-server -V` or run `select tidb_version();` on TiDB)

mysql root@127.0.0.1:test> select tidb_version();                                                                                                     
+-------------------------------------------------------------------+
| tidb_version()                                                    |
+-------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-89-g5cf0c3e2d                      |
| Git Commit Hash: 5cf0c3e2d4b12610b3be092a4e785488875b90bb         |
| Git Branch: master                                                |
| UTC Build Time: 2020-03-26 06:56:55                               |
| GoVersion: go1.13.8                                               |
| Race Enabled: false                                               |
| TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 |
| Check Table Before Drop: false                                    |
+-------------------------------------------------------------------+
1 row in set
Time: 0.018s

The text was updated successfully, but these errors were encountered:

XuHuaiyu · 2020-03-26T14:08:32Z

What about the result when using tikv?

zhongzc · 2020-03-26T14:34:23Z

@XuHuaiyu

mysql>  SELECT `col_decimal` AND `col_varchar_1` AS field1, `col_decimal`, `col_varchar_1`, 1 FROM `table1000_int_autoinc` ORDER BY field1 LIMIT 10;   
+--------+-------------+---------------+---+
| field1 | col_decimal | col_varchar_1 | 1 |
+--------+-------------+---------------+---+
|   NULL |           7 | NULL          | 1 |
|      0 |       54712 | r             | 1 |
|   NULL |        NULL | 2             | 1 |
|      0 |           0 | 2             | 1 |
|      0 |        NULL | w             | 1 |
|      0 |   704905216 | j             | 1 |
|      0 |           0 | o             | 1 |
|      0 |  1514078208 | g             | 1 |
|      0 |           7 | g             | 1 |
|      0 |       53131 | 0             | 1 |
+--------+-------------+---------------+---+
10 rows in set, 75 warnings (0.06 sec)

mysql> SELECT `col_decimal` AND `col_varchar_1` AS field1, `col_decimal`, `col_varchar_1` FROM `table1000_int_autoinc` ORDER BY field1 LIMIT 10;   
+--------+-------------+---------------+
| field1 | col_decimal | col_varchar_1 |
+--------+-------------+---------------+
|      0 |           7 | NULL          |
|      0 |       54712 | r             |
|      0 |        NULL | 2             |
|      0 |           0 | 2             |
|      0 |        NULL | w             |
|      0 |   704905216 | j             |
|      0 |           0 | o             |
|      0 |  1514078208 | g             |
|      0 |           7 | g             |
|      0 |       53131 | 0             |
+--------+-------------+---------------+
10 rows in set, 76 warnings (0.06 sec)

Reminiscent · 2020-03-30T03:32:42Z

minor case:

In my TiDB version(after fix the problom from expression evaltion):
With TiKV(the result same with mockTiKV)

MySQL [test]> create table tt(a decimal(10, 0), b varchar(1));
Query OK, 0 rows affected (0.28 sec)

MySQL [test]> insert into tt values(0, '2'), (7, null), (7, '3'), (NULL, 'w'), (NULL, '2'), (NULL, '2'), (NULL, NULL), (7, 'f');
Query OK, 8 rows affected (0.12 sec)
Records: 8  Duplicates: 0  Warnings: 0

MySQL [test]> select a and b as c, a, b from tt order by c limit 4;
+---+------+------+
| c | a    | b    |
+---+------+------+
| NULL |    7 | NULL |
| NULL | NULL | 2    |
| 0 |    0 | 2    |
| 0 | NULL | w    |
+---+------+------+
4 rows in set, 4 warnings (0.00 sec)

MySQL [test]> select a and b as c, a, b from tt order by c;
+---+------+------+
| c | a    | b    |
+---+------+------+
| NULL | NULL | NULL |
| NULL |    7 | NULL |
| NULL | NULL | 2    |
| NULL | NULL | 2    |
| 0 | NULL | w    |
| 0 |    0 | 2    |
| 0 |    7 | f    |
| 1 |    7 | 3    |
+---+------+------+
8 rows in set, 2 warnings (0.01 sec)

In mysql5.7:

mysql> create table tt(a decimal(10, 0), b varchar(1));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tt values(0, '2'), (7, null), (7, '3'), (NULL, 'w'), (NULL, '2'), (NULL, '2'), (NULL, NULL), (7, 'f'); 

Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select a and b as c, a, b from tt order by c limit 4;
+------+------+------+
| c    | a    | b    |
+------+------+------+
| NULL |    7 | NULL |
| NULL | NULL | 2    |
| NULL | NULL | NULL |
| NULL | NULL | 2    |
+------+------+------+
4 rows in set, 2 warnings (0.00 sec)

mysql> select a and b as c, a, b from tt order by c;
+------+------+------+
| c    | a    | b    |
+------+------+------+
| NULL |    7 | NULL |
| NULL | NULL | 2    |
| NULL | NULL | 2    |
| NULL | NULL | NULL |
|    0 |    0 | 2    |
|    0 | NULL | w    |
|    0 |    7 | f    |
|    1 |    7 | 3    |
+------+------+------+
8 rows in set, 4 warnings (0.00 sec)

zhongzc added the type/bug The issue is confirmed as a bug. label Mar 26, 2020

zz-jason added the sig/execution SIG execution label Mar 26, 2020

XuHuaiyu self-assigned this Mar 26, 2020

XuHuaiyu assigned Reminiscent Mar 27, 2020

This was referenced Mar 31, 2020

expression: fix incorrect result of logical operators for vectorized expression evaluation #15891

Merged

expression: fix the bug that cannot perform vectorized expression evaluation #15902

Merged

sre-bot mentioned this issue Mar 31, 2020

expression: fix incorrect result of logical operators for vectorized expression evaluation (#15891) #15907

Merged

Reminiscent mentioned this issue Mar 31, 2020

expression: fix incorrect result of logical operators when isTrue/ isFalse function is pushed down #15926

Closed

sre-bot mentioned this issue Apr 1, 2020

expression: fix the bug that cannot perform vectorized expression evaluation (#15902) #15956

Merged

Reminiscent mentioned this issue Apr 2, 2020

OR clause on FLOAT/DOUBLE column unexpectedly evaluates to TRUE #15987

Closed

Reminiscent mentioned this issue Apr 17, 2020

expression: split isTrue and isFalse expression sig #16542

Merged

zhongzc mentioned this issue Apr 17, 2020

tidb_query: add is true/false keep null ScalarFuncSig tikv/tikv#7532

Merged

sre-bot closed this as completed in tikv/tikv#7532 Apr 20, 2020

sre-bot mentioned this issue Apr 20, 2020

expression: split isTrue and isFalse expression sig (#16542) #16630

Merged

zz-jason unassigned XuHuaiyu Apr 20, 2020

sre-bot added the severity/moderate label Aug 10, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

executor: some unexpected behaviors of TopN #15718

executor: some unexpected behaviors of TopN #15718

zhongzc commented Mar 26, 2020 •

edited

Loading

XuHuaiyu commented Mar 26, 2020

zhongzc commented Mar 26, 2020

Reminiscent commented Mar 30, 2020 •

edited

Loading

executor: some unexpected behaviors of TopN #15718

executor: some unexpected behaviors of TopN #15718

Comments

zhongzc commented Mar 26, 2020 • edited Loading

Bug Report

1. What did you do?

2. What did you expect to see?

3. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

XuHuaiyu commented Mar 26, 2020

zhongzc commented Mar 26, 2020

Reminiscent commented Mar 30, 2020 • edited Loading

zhongzc commented Mar 26, 2020 •

edited

Loading

3. What version of TiDB are you using? (`tidb-server -V` or run `select tidb_version();` on TiDB)

Reminiscent commented Mar 30, 2020 •

edited

Loading