Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
PragmaTwice opened this issue May 24, 2021 · 4 comments · Fixed by #24937
Assignees
Labels
severity/critical sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@PragmaTwice
Copy link
Contributor

PragmaTwice commented May 24, 2021

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)
@PragmaTwice PragmaTwice added the type/bug The issue is confirmed as a bug. label May 24, 2021
@PragmaTwice PragmaTwice changed the title dml: select result is not compatible with MySQL while joining a subquery with a where clause evaluated to false dml: select result is not compatible with MySQL while joining a subquery with a where clause with truncated values May 25, 2021
@PragmaTwice PragmaTwice changed the title dml: select result is not compatible with MySQL while joining a subquery with a where clause with truncated values dml: select result is not compatible with MySQL while joining a subquery with a where clause evaluated to false May 25, 2021
@XuHuaiyu
Copy link
Contributor

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.

@XuHuaiyu XuHuaiyu added sig/planner SIG: Planner and removed sig/sql-infra SIG: SQL Infra labels May 27, 2021
@eurekaka
Copy link
Contributor

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
Copy link
Contributor

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
Copy link
Contributor

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
severity/critical sig/planner SIG: Planner type/bug The issue is confirmed as a bug.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants