Tidb: Incorrect result for an UNION query and a generated column

[mrigger]

Consider the following statements:

CREATE TABLE t0(c0 INT, c1 TEXT AS (0.9));
INSERT INTO t0(c0) VALUES (0);
SELECT 0 FROM t0 WHERE false UNION SELECT 0 FROM t0 WHERE NOT t0.c1; -- expected: {0}, actual: {}

Unexpectedly, TiDB does not fetch a row with the value 0. Removing SELECT 0 FROM t0 WHERE false UNION from the query results in the correct result being computed:

CREATE TABLE t0(c0 INT, c1 TEXT AS (0.9));
INSERT INTO t0(c0) VALUES (0);
SELECT 0 FROM t0 WHERE NOT t0.c1; -- {0}

I found this bug with the following environment:

mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.12
Git Commit Hash: 8c4696b3f3408c61dd7454204ddd67c93501609a
Git Branch: heads/refs/tags/v3.0.12
UTC Build Time: 2020-03-16 09:56:22
GoVersion: go version go1.13 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[SunRunAway]

Hi, @mrigger
I've checked the case you provided with different versions, here's the result. It seems that they did not work as you described.

W/ TiDB v3.0.12

mysql> drop table if exists t0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t0(c0 INT, c1 TEXT AS (0.9));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t0(c0) VALUES (0);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT 0 FROM t0 WHERE false UNION SELECT 0 FROM t0 WHERE NOT t0.c1;
Empty set (0.00 sec)

mysql> SELECT 0 FROM t0 WHERE NOT t0.c1;
+------+
| 0    |
+------+
|    0 |
+------+
1 row in set, 4 warnings (0.00 sec)

W/ TiDB master branch (94fb1bf18d49f7421b44296ce67fb230975abd0c)

mysql> drop table if exists t0;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t0(c0 INT, c1 TEXT AS (0.9));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t0(c0) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT 0 FROM t0 WHERE false UNION SELECT 0 FROM t0 WHERE NOT t0.c1;
Empty set (0.00 sec)

mysql> SELECT 0 FROM t0 WHERE NOT t0.c1;
Empty set (0.00 sec)

W/ MySQL 8.0.19

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

mysql> drop table if exists t0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t0(c0 INT, c1 TEXT AS (0.9));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t0(c0) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT 0 FROM t0 WHERE false UNION SELECT 0 FROM t0 WHERE NOT t0.c1;
Empty set (0.00 sec)

mysql> SELECT 0 FROM t0 WHERE NOT t0.c1;
Empty set (0.00 sec)

W/ MySQL 5.7.29

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.29    |
+-----------+
1 row in set (0.00 sec)

mysql> drop table if exists t0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t0(c0 INT, c1 TEXT AS (0.9));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t0(c0) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT 0 FROM t0 WHERE false UNION SELECT 0 FROM t0 WHERE NOT t0.c1;
Empty set (0.00 sec)

mysql> SELECT 0 FROM t0 WHERE NOT t0.c1;
Empty set (0.00 sec)

[SunRunAway]

TiDB v3.0.12 gives a different result with SQLSELECT 0 FROM t0 WHERE NOT t0.c1;
I'll take a look at that.

[SunRunAway]

TiDB v3.0.12 gives a different result with SQLSELECT 0 FROM t0 WHERE NOT t0.c1;
I'll take a look at that.

We should cherry-pick https://github.com/pingcap/tidb/pull/10498 to release branch.

[SunRunAway]

TiDB v3.0.12 gives a different result with SQLSELECT 0 FROM t0 WHERE NOT t0.c1;
I'll take a look at that.

We should cherry-pick #10498 to release branch.

It's done. I've confirmed with the newest commit of release-2.1, release-3.0 and release-3.1 branch.

mysql> SELECT 0 FROM t0 WHERE NOT t0.c1;
Empty set (0.00 sec)

[SunRunAway]

Closing it. Free free to reopen it if you find any suspicious. Thank you for the report.