tidb
tidb copied to clipboard
pingcap
Incorrect plan and result are used when using generated columns
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE t0(id int default 1, c0 NUMERIC UNSIGNED ZEROFILL , c1 DECIMAL UNSIGNED AS (c0) VIRTUAL NOT NULL UNIQUE);
insert ignore into t0(c0) values (null);
select * from t0;
explain select * from t0;
2. What did you expect to see? (Required)
mysql> select * from t0;
+------+------+----+
| id | c0 | c1 |
+------+------+----+
| 1 | NULL | 0 |
+------+------+----+
1 row in set (0.00 sec)
mysql> explain select * from t0;
+---------------------------+----------+-----------+---------------+------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+----------+-----------+---------------+------------------------------------------------+
| Projection_3 | 10000.00 | root | | test.t0.id, test.t0.c0, test.t0.c0->test.t0.c1 |
| └─Projection_6 | 10000.00 | root | | test.t0.id, test.t0.c0 |
| └─TableReader_5 | 10000.00 | root | | data:TableFullScan_4 |
| └─TableFullScan_4 | 10000.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
+---------------------------+----------+-----------+---------------+------------------------------------------------+
4 rows in set (0.00 sec)
3. What did you see instead (Required)
mysql> select * from t0;
+------+------+----+
| id | c0 | c1 |
+------+------+----+
| 1 | 0 | 0 |
+------+------+----+
1 row in set (0.00 sec)
mysql> explain select * from t0;
+---------------------------+----------+-----------+---------------+------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+----------+-----------+---------------+------------------------------------------------+
| Projection_3 | 10000.00 | root | | test.t0.id, test.t0.c1->test.t0.c0, test.t0.c1 |
| └─Projection_6 | 10000.00 | root | | test.t0.id, test.t0.c1 |
| └─TableReader_5 | 10000.00 | root | | data:TableFullScan_4 |
| └─TableFullScan_4 | 10000.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
+---------------------------+----------+-----------+---------------+------------------------------------------------+
4 rows in set (0.00 sec)
4. What is your TiDB version? (Required)
a8281b6d9821b5c16fe2bfc3308c96a9a821e3a3
when I rebase the code to this PR https://github.com/pingcap/tidb/pull/14367
+----+--------+--------+
| id | c0 | c1 |
+----+--------+--------+
| 1 | <null> | <null> |
+----+--------+--------+
+-----------------+----------+-----------+-------------------------------------------------------------+
| id | count | task | operator info |
+-----------------+----------+-----------+-------------------------------------------------------------+
| Projection_3 | 10000.00 | root | test.t0.id, test.t0.c1, test.t0.c1 |
| └─TableReader_5 | 10000.00 | root | data:TableScan_4 |
| └─TableScan_4 | 10000.00 | cop[tikv] | table:t0, range:[-inf,+inf], keep order:false, stats:pseudo |
+-----------------+----------+-----------+-------------------------------------------------------------+
+-------------------------------------------------------------------+
| tidb_version() |
+-------------------------------------------------------------------+
| Release Version: v5.1.0-alpha-80-gbbd5b32c2f |
| Edition: Community |
| Git Commit Hash: bbd5b32c2f539f4a3908f25af3e6e8965b6b58f7 |
| Git Branch: master |
| UTC Build Time: 2025-05-30 00:24:02 |
| GoVersion: go1.16.10 |
| Race Enabled: false |
| TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 |
| Check Table Before Drop: false |
+-------------------------------------------------------------------+
The result is still
+----+----+----+
| id | c0 | c1 |
+----+----+----+
| 1 | 0 | 0 |
+----+----+----+
5f73c822cabbf5dab31f37fa17437c2b041fd516
+----+----+----+
| id | c0 | c1 |
+----+----+----+
| 1 | 0 | 0 |
+----+----+----+
d3fe2187a999cf12a850eaf7ef3fa76327eda2ac
+----+----+----+
| id | c0 | c1 |
+----+----+----+
| 1 | 0 | 0 |
+----+----+----+
+-----------------------+----------+-----------+---------------+------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------+----------+-----------+---------------+------------------------------------+
| Projection_3 | 10000.00 | root | | test.t0.id, test.t0.c1, test.t0.c1 |
| └─Projection_6 | 10000.00 | root | | test.t0.id, test.t0.c1 |
| └─TableReader_5 | 10000.00 | root | | data:TableFullScan_4 |
| └─TableFullScan_4 | 10000.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
+-----------------------+----------+-----------+---------------+------------------------------------+
080bcf2ac5edbc6bffff4b847bb4e4c830a1f408
+----+--------+--------+
| id | c0 | c1 |
+----+--------+--------+
| 1 | <null> | <null> |
+----+--------+--------+
+---------------------+----------+-----------+---------------+------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------+----------+-----------+---------------+------------------------------------+
| Projection_3 | 10000.00 | root | | test.t0.id, test.t0.c1, test.t0.c1 |
| └─TableReader_5 | 10000.00 | root | | data:TableFullScan_4 |
| └─TableFullScan_4 | 10000.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
+---------------------+----------+-----------+---------------+------------------------------------+
I change this test case from VIRTUAL to STORED
CREATE TABLE t0(id int default 1, c0 NUMERIC UNSIGNED ZEROFILL , c1 DECIMAL UNSIGNED AS (c0) STORED NOT NULL UNIQUE);
insert ignore into t0(c0) values (null);
select * from t0;
explain select * from t0;
It will get the right result.