dml: select result is not compatible with MySQL while joining a subquery with a where clause evaluated to false

[PragmaTwice]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1 (i1 integer, c1 char);
insert into t1 values (2, 'a'), (1, 'b'), (3, 'c'), (0, null);
create table t2 (i2 integer, c2 char, f2 float);
insert into t2 values (0, 'c', null), (1, null, 0.1), (3, 'b', 0.01), (2, 'q', 0.12), (null, 'a', -0.1), (null, null, null);
SELECT count(_tmp_2._tmp_8) FROM t1 AS _tmp_1 JOIN (SELECT count(_tmp_3.i2) AS _tmp_8 FROM t2 AS _tmp_3 JOIN (SELECT count(_tmp_6.i2) AS _tmp_7 FROM t1 AS _tmp_5 JOIN t2 AS _tmp_6 WHERE _tmp_5.c1!=1 AND _tmp_6.c2>100 ORDER BY _tmp_5.i1) AS _tmp_4 GROUP BY _tmp_3.c2) AS _tmp_2;

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

in MySQL:

+----------------------+
| count(_tmp_2._tmp_8) |
+----------------------+
|                   20 |
+----------------------+
1 row in set, 4 warnings (0.00 sec)

3. What did you see instead (Required)

in TiDB:

+----------------------+
| count(_tmp_2._tmp_8) |
+----------------------+
|                    0 |
+----------------------+
1 row in set, 7 warnings (0.00 sec)

The result is zero even type of t1.c1 and t2.c2 (char) is replaced with integer, so it is not due to truncated values (message in warnings)

4. What is your TiDB version? (Required)

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()
                                                                                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-2968-g19e100c27-dirty
Edition: Community
Git Commit Hash: 19e100c27a37601d674df625034ebddb5196a299
Git Branch: master
UTC Build Time: 2021-05-23 15:35:12
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[XuHuaiyu]

A simplified case:

tidb> desc analyze SELECT count(*) FROM t2 _tmp_3 JOIN ( SELECT COUNT(_tmp_6.i2) AS _tmp_7 FROM t1 _tmp_5 JOIN t2 _tmp_6 WHERE _tmp_5.c1 != 1 AND _tmp_6.c2 > 100  ) _tmp_4 ;
+----------------------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+-----------+---------+
| id                                     | estRows | actRows | task      | access object | execution info                                                                                                                     | operator info                            | memory    | disk    |
+----------------------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+-----------+---------+
| StreamAgg_15                           | 1.00    | 1       | root      |               | time:287.3µs, loops:2                                                                                                              | funcs:count(1)->Column#13                | 8 Bytes   | N/A     |
| └─HashJoin_33                          | 6.00    | 0       | root      |               | time:284.8µs, loops:1, build_hash_table:{total:204.3µs, fetch:204.3µs, build:0s}                                                   | CARTESIAN inner join                     | 0 Bytes   | 0 Bytes |
|   ├─Projection_20(Build)               | 1.00    | 0       | root      |               | time:105.3µs, loops:1, Concurrency:OFF                                                                                             | 1->Column#19                             | 372 Bytes | N/A     |
|   │ └─StreamAgg_22                     | 1.00    | 0       | root      |               | time:103.7µs, loops:1                                                                                                              | funcs:firstrow(1)->Column#18             | 16 Bytes  | N/A     |
|   │   └─HashJoin_32                    | 15.36   | 0       | root      |               | time:102.5µs, loops:1, build_hash_table:{total:111.9µs, fetch:111.9µs, build:0s}                                                   | CARTESIAN inner join                     | 0 Bytes   | 0 Bytes |
|   │     ├─TableReader_27(Build)        | 3.20    | 3       | root      |               | time:26.6µs, loops:1, cop_task: {num: 1, max: 228.5µs, proc_keys: 0, rpc_num: 1, rpc_time: 213.3µs, copr_cache_hit_ratio: 0.00}    | data:Selection_26                        | 297 Bytes | N/A     |
|   │     │ └─Selection_26               | 3.20    | 3       | cop[tikv] |               | tikv_task:{time:51.2µs, loops:4}                                                                                                   | ne(cast(test.t1.c1, double BINARY), 1)   | N/A       | N/A     |
|   │     │   └─TableFullScan_25         | 4.00    | 4       | cop[tikv] | table:_tmp_5  | tikv_task:{time:5.17µs, loops:4}                                                                                                   | keep order:false, stats:pseudo           | N/A       | N/A     |
|   │     └─TableReader_30(Probe)        | 4.80    | 0       | root      |               | time:59.1µs, loops:1, cop_task: {num: 1, max: 178.6µs, proc_keys: 0, rpc_num: 1, rpc_time: 171.5µs, copr_cache_hit_ratio: 0.00}    | data:Selection_29                        | 327 Bytes | N/A     |
|   │       └─Selection_29               | 4.80    | 0       | cop[tikv] |               | tikv_task:{time:78.9µs, loops:6}                                                                                                   | gt(cast(test.t2.c2, double BINARY), 100) | N/A       | N/A     |
|   │         └─TableFullScan_28         | 6.00    | 6       | cop[tikv] | table:_tmp_6  | tikv_task:{time:4.04µs, loops:6}                                                                                                   | keep order:false, stats:pseudo           | N/A       | N/A     |
|   └─TableReader_19(Probe)              | 6.00    | 6       | root      |               | time:138.7µs, loops:1, cop_task: {num: 1, max: 150µs, proc_keys: 0, rpc_num: 1, rpc_time: 127.2µs, copr_cache_hit_ratio: 0.00}     | data:TableFullScan_18                    | 160 Bytes | N/A     |
|     └─TableFullScan_18                 | 6.00    | 6       | cop[tikv] | table:_tmp_3  | tikv_task:{time:4.57µs, loops:6}                                                                                                   | keep order:false, stats:pseudo           | N/A       | N/A     |
+----------------------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+-----------+---------+
13 rows in set, 7 warnings (0.00 sec)

As we can see from StreamAgg_22, count(_tmp_6.i2) is rewritten to firstrow(1) wrongly.

[eurekaka]

Minimal reproduction:

MySQL [test]> create table t(a int);
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> insert into t values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [test]> SELECT count(1) FROM t JOIN (SELECT count(1) FROM t WHERE false) AS tmp;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

MySQL [test]> explain SELECT count(1) FROM t JOIN (SELECT count(1) FROM t WHERE false) AS tmp;
+-------------------------------+----------+-----------+---------------+--------------------------------+
| id                            | estRows  | task      | access object | operator info                  |
+-------------------------------+----------+-----------+---------------+--------------------------------+
| HashAgg_13                    | 1.00     | root      |               | funcs:count(1)->Column#6       |
| └─HashJoin_17                 | 10000.00 | root      |               | CARTESIAN inner join           |
|   ├─Projection_18(Build)      | 1.00     | root      |               | 1->Column#12                   |
|   │ └─StreamAgg_20            | 1.00     | root      |               | funcs:firstrow(1)->Column#11   |
|   │   └─TableDual_22          | 0.00     | root      |               | rows:0                         |
|   └─TableReader_24(Probe)     | 10000.00 | root      |               | data:TableFullScan_23          |
|     └─TableFullScan_23        | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------------+----------+-----------+---------------+--------------------------------+
7 rows in set (0.01 sec)

MySQL [test]> SELECT * FROM t JOIN (SELECT count(1) FROM t WHERE false) AS tmp;
+------+----------+
| a    | count(1) |
+------+----------+
|    1 |        0 |
|    2 |        0 |
+------+----------+
2 rows in set (0.00 sec)

MySQL [test]> explain SELECT * FROM t JOIN (SELECT count(1) FROM t WHERE false) AS tmp;
+-------------------------------+---------+-----------+---------------+--------------------------------+
| id                            | estRows | task      | access object | operator info                  |
+-------------------------------+---------+-----------+---------------+--------------------------------+
| Projection_12                 | 2.00    | root      |               | test.t.a, Column#5             |
| └─HashJoin_14                 | 2.00    | root      |               | CARTESIAN inner join           |
|   ├─StreamAgg_16(Build)       | 1.00    | root      |               | funcs:count(1)->Column#5       |
|   │ └─TableDual_18            | 0.00    | root      |               | rows:0                         |
|   └─TableReader_20(Probe)     | 2.00    | root      |               | data:TableFullScan_19          |
|     └─TableFullScan_19        | 2.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+---------------+--------------------------------+
6 rows in set (0.00 sec)

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

[eurekaka]

A complementary fix for the original case in the description: #25289