tidb
tidb copied to clipboard
Published
20 hours ago •
pingcap
pingcap
Incorrect query result may caused by REGEXP_LIKE function
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE t0(c0 BOOL);
REPLACE INTO t0(c0) VALUES (false), (true);
CREATE VIEW v0(c0) AS SELECT (REGEXP_LIKE(t0.c0, t0.c0)) FROM t0 WHERE t0.c0 GROUP BY t0.c0 HAVING 1;
SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL));
SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
2. What did you expect to see? (Required)
mysql> select * from v0;
+------+
| c0 |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
Since v0 is 1, the condition of sql2 AND v0.c0 evaluated to be True, sql1 and sql2 should have the same result
SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)); --sql1
SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0; --sql2
3. What did you see instead (Required)
sql1 and sql2 return different result
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL));
+------+
| c0 |
+------+
| 0 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 1 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
4. What is your TiDB version? (Required)
TiDB-v7.6.0
@winoros It seems related to projection_eliminate
mysql> CREATE TABLE t0(c0 BOOL);
Query OK, 0 rows affected (0.13 sec)
mysql> REPLACE INTO t0(c0) VALUES (false), (true);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CREATE VIEW v0(c0) AS SELECT (REGEXP_LIKE(t0.c0, t0.c0)) FROM t0 WHERE t0.c0 GROUP BY t0.c0 HAVING 1;
Query OK, 0 rows affected (0.12 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 1 |
| 1 |
+------+
2 rows in set, 3 warnings (0.01 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 1 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 1 |
| 1 |
+------+
2 rows in set, 3 warnings (0.01 sec)
mysql> insert into mysql.opt_rule_blacklist values('projection_eliminate');
Query OK, 1 row affected (0.00 sec)
mysql> admin reload opt_rule_blacklist;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 0 |
| 1 |
+------+
2 rows in set, 3 warnings (0.01 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 0 |
| 1 |
+------+
I guess that wrong result comes from the planner actually output v0.c0 instead of t0.c0.
And also this bug seems only exists in v7.6.0, I try v7.5.0 and nightly(v8.0.0-alpha), non of them can reproduce this bug.
Even in v7.6.0, it can sometimes get the right results
mysql> delete from mysql.opt_rule_blacklist;
Query OK, 1 row affected (0.00 sec)
mysql> admin reload opt_rule_blacklist;
Query OK, 0 rows affected (0.01 sec)
mysql> drop view v0;
Query OK, 0 rows affected (0.29 sec)
mysql> drop table t0;
Query OK, 0 rows affected (0.30 sec)
mysql> CREATE TABLE t0(c0 BOOL);
Query OK, 0 rows affected (0.13 sec)
mysql> REPLACE INTO t0(c0) VALUES (false), (true);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CREATE VIEW v0(c0) AS SELECT (REGEXP_LIKE(t0.c0, t0.c0)) FROM t0 WHERE t0.c0 GROUP BY t0.c0 HAVING 1;
Query OK, 0 rows affected (0.12 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 1 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 1 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT t0.c0 as t_c0, v0.c0 as v_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+------+
| t_c0 | v_c0 |
+------+------+
| 0 | 1 |
| 1 | 1 |
+------+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT t0.c0 as t_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| t_c0 |
+------+
| 1 |
| 1 |
+------+
2 rows in set, 3 warnings (0.01 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 1 |
| 1 |
+------+
2 rows in set, 3 warnings (0.01 sec)
mysql> SELECT t0.c0 as t_c0, v0.c0 as v_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+------+
| t_c0 | v_c0 |
+------+------+
| 0 | 1 |
| 1 | 1 |
+------+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT t0.c0 as t_c0, v0.c0 as v_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+------+
| t_c0 | v_c0 |
+------+------+
| 0 | 1 |
| 1 | 1 |
+------+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT t0.c0 as t_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| t_c0 |
+------+
| 1 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> explain SELECT t0.c0 as t_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------------------------------------+-------------+-----------+---------------+---------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+-------------+-----------+---------------+---------------------------------------------------------------------------------------------+
| HashJoin_16 | 27265706.67 | root | | CARTESIAN inner join |
| ├─Selection_17(Build) | 3408.21 | root | | or(istrue_with_null(cast(subtime("2001-11-28 06", "252 10"), double BINARY)), 1) |
| │ └─HashAgg_24 | 4260.27 | root | | group by:test.t0.c0, funcs:firstrow(Column#9)->Column#8 |
| │ └─TableReader_25 | 4260.27 | root | | data:HashAgg_19 |
| │ └─HashAgg_19 | 4260.27 | cop[tikv] | | group by:test.t0.c0, funcs:firstrow(1)->Column#9 |
| │ └─Selection_23 | 5325.33 | cop[tikv] | | regexp_like(cast(test.t0.c0, var_string(20)), cast(test.t0.c0, var_string(20))), test.t0.c0 |
| │ └─TableFullScan_22 | 10000.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
| └─Selection_29(Probe) | 8000.00 | root | | or(istrue_with_null(cast(subtime("2001-11-28 06", "252 10"), double BINARY)), 1) |
| └─TableReader_31 | 10000.00 | root | | data:TableFullScan_30 |
| └─TableFullScan_30 | 10000.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
+------------------------------------+-------------+-----------+---------------+---------------------------------------------------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)
mysql> SELECT t0.c0 as t_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| t_c0 |
+------+
| 1 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> explain SELECT t0.c0 as t_c0, v0.c0 as v_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+----------------------------------------+-------------+-----------+---------------+---------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------------+-------------+-----------+---------------+---------------------------------------------------------------------------------------------+
| Projection_14 | 27265706.67 | root | | test.t0.c0, Column#3 |
| └─HashJoin_16 | 27265706.67 | root | | CARTESIAN inner join |
| ├─Selection_17(Build) | 3408.21 | root | | or(istrue_with_null(cast(subtime("2001-11-28 06", "252 10"), double BINARY)), 1) |
| │ └─Projection_18 | 4260.27 | root | | regexp_like(cast(test.t0.c0, var_string(20)), cast(test.t0.c0, var_string(20)))->Column#3 |
| │ └─HashAgg_24 | 4260.27 | root | | group by:test.t0.c0, funcs:firstrow(test.t0.c0)->test.t0.c0 |
| │ └─TableReader_25 | 4260.27 | root | | data:HashAgg_19 |
| │ └─HashAgg_19 | 4260.27 | cop[tikv] | | group by:test.t0.c0, |
| │ └─Selection_23 | 5325.33 | cop[tikv] | | regexp_like(cast(test.t0.c0, var_string(20)), cast(test.t0.c0, var_string(20))), test.t0.c0 |
| │ └─TableFullScan_22 | 10000.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
| └─Selection_29(Probe) | 8000.00 | root | | or(istrue_with_null(cast(subtime("2001-11-28 06", "252 10"), double BINARY)), 1) |
| └─TableReader_31 | 10000.00 | root | | data:TableFullScan_30 |
| └─TableFullScan_30 | 10000.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
+----------------------------------------+-------------+-----------+---------------+---------------------------------------------------------------------------------------------+
12 rows in set, 1 warning (0.00 sec)
mysql> SELECT t0.c0 as t_c0, v0.c0 as v_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+------+
| t_c0 | v_c0 |
+------+------+
| 0 | 1 |
| 1 | 1 |
+------+------+
2 rows in set, 3 warnings (0.01 sec)
mysql> SELECT t0.c0 as t_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| t_c0 |
+------+
| 0 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT t0.c0 as t_c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| t_c0 |
+------+
| 0 |
| 1 |
+------+
2 rows in set, 3 warnings (0.01 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 0 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 0 |
| 1 |
+------+
2 rows in set, 3 warnings (0.01 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 0 |
| 1 |
+------+
2 rows in set, 3 warnings (0.01 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 0 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT t0.c0 FROM v0, t0 WHERE (SUBTIME('2001-11-28 06', '252 10') OR ('' IS NOT NULL)) AND v0.c0;
+------+
| c0 |
+------+
| 0 |
| 1 |
+------+
2 rows in set, 3 warnings (0.00 sec)
You can see after run this queries and some other query multiple times, the original query seem return the right answer stablely.
