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.

Sign up for GitHub

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

Jump to bottom

NULL value generated by left outer join cannot be filtered by other condition on the join #45379

Closed
pcqz opened this issue Jul 16, 2023 · 3 comments
Closed

NULL value generated by left outer join cannot be filtered by other condition on the join #45379

pcqz opened this issue Jul 16, 2023 · 3 comments
Labels
may-affects-5.2 This bug maybe affects 5.2.x versions. may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-6.5 may-affects-7.1 severity/critical sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@pcqz
Copy link

pcqz commented Jul 16, 2023 •
edited by kennytm
Loading

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1(id int, a varchar(10), b varchar(10), dt datetime);
insert into t1 values(1, 1001, 2001, '2023-07-16 17:01:00'),(2, 1002, 2002, '2023-07-16 17:02:00'),(3, 1003, 2003, '2023-07-16 17:03:00');
create table t2(id int, b varchar(10), c varchar(10));
insert into t2 values(1,2001,1111),(2,2002,1111),(3,2003,1111);
create table t3(id int, a varchar(10), d varchar(10));
insert into t3 values(1,1001,2222),(2,1002,2222);

create view v(dt, c, d1) as 
  select 
    t1.dt as dt, 
    t2.c as c, 
    case when t2.c=1 then 1 when t2.c=2 then 2 else t3.d end as d1 
  from t1 
  left join t3 on t1.a=t3.a 
  join t2 on t2.b=t1.b;
select * from v 
where c='1111' and d1='2222' 
and dt between '2023-07-16 17:00:00' and '2023-07-16 17:04:00';

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

+---------------------+------+------+
| dt                  | c    | d1   |
+---------------------+------+------+
| 2023-07-16 17:01:00 | 1111 | 2222 |
| 2023-07-16 17:02:00 | 1111 | 2222 |
+---------------------+------+------+

3. What did you see instead (Required)

+---------------------+------+------+
| dt                  | c    | d1   |
+---------------------+------+------+
| 2023-07-16 17:01:00 | 1111 | 2222 |
| 2023-07-16 17:02:00 | 1111 | 2222 |
| 2023-07-16 17:03:00 | 1111 | NULL |
+---------------------+------+------+

> explain select * from v where c='1111' and d1='2222' and dt between '2023-07-16 17:00:00' and '2023-07-16 17:04:00';
+--------------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | estRows  | task      | access object | operator info                                                                                                                                                                     |
+--------------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_15                        | 15.61    | root      |               | test.t1.dt, test.t2.c, case(eq(cast(test.t2.c, double BINARY), 1), 1, eq(cast(test.t2.c, double BINARY), 2), 2, test.t3.d)->Column#14                                             |
| └─Projection_16                      | 15.61    | root      |               | test.t1.dt, test.t3.d, test.t2.c                                                                                                                                                  |
|   └─HashJoin_18                      | 15.61    | root      |               | left outer join, equal:[eq(test.t1.a, test.t3.a)], other cond:eq(case(eq(cast(test.t2.c, double BINARY), 1), "1", eq(cast(test.t2.c, double BINARY), 2), "2", test.t3.d), "2222") |
|     ├─HashJoin_20(Build)             | 12.49    | root      |               | inner join, equal:[eq(test.t2.b, test.t1.b)]                                                                                                                                      |
|     │ ├─TableReader_23(Build)        | 9.99     | root      |               | data:Selection_22                                                                                                                                                                 |
|     │ │ └─Selection_22               | 9.99     | cop[tikv] |               | eq(test.t2.c, "1111"), not(isnull(test.t2.b))                                                                                                                                     |
|     │ │   └─TableFullScan_21         | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                                                                                    |
|     │ └─TableReader_26(Probe)        | 249.75   | root      |               | data:Selection_25                                                                                                                                                                 |
|     │   └─Selection_25               | 249.75   | cop[tikv] |               | ge(test.t1.dt, 2023-07-16 17:00:00.000000), le(test.t1.dt, 2023-07-16 17:04:00.000000), not(isnull(test.t1.b))                                                                    |
|     │     └─TableFullScan_24         | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                                                                    |
|     └─TableReader_29(Probe)          | 9990.00  | root      |               | data:Selection_28                                                                                                                                                                 |
|       └─Selection_28                 | 9990.00  | cop[tikv] |               | not(isnull(test.t3.a))                                                                                                                                                            |
|         └─TableFullScan_27           | 10000.00 | cop[tikv] | table:t3      | keep order:false, stats:pseudo                                                                                                                                                    |
+--------------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

4. What is your TiDB version? (Required)

v7.1.0
@pcqz pcqz added the type/bug The issue is confirmed as a bug. label Jul 16, 2023
@aytrack
Copy link
Contributor

aytrack commented Jul 17, 2023

it about join reorder

[15:10:48]TiDB root:test> select * from v where c='1111' and d1='2222' and dt between '2023-07-16 17:00:00' and '2023-07-16 17:04:00';
+---------------------+------+------+
| dt                  | c    | d1   |
+---------------------+------+------+
| 2023-07-16 17:01:00 | 1111 | 2222 |
| 2023-07-16 17:02:00 | 1111 | 2222 |
+---------------------+------+------+
2 rows in set
Time: 0.008s
[15:10:50]TiDB root:test> explain select * from v where c='1111' and d1='2222' and dt between '2023-07-16 17:00:00' and '2023-07-16 17:04:00';
+-------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                            | estRows | task      | access object | operator info                                                                                                                                                                |
+-------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_14                 | 0.00    | root      |               | test.t1.dt, test.t2.c, case(eq(cast(test.t2.c, double BINARY), 1), 1, eq(cast(test.t2.c, double BINARY), 2), 2, test.t3.d)->Column#14                                        |
| └─Projection_15               | 0.00    | root      |               | test.t1.dt, test.t3.d, test.t2.c                                                                                                                                             |
|   └─HashJoin_17               | 0.00    | root      |               | inner join, equal:[eq(test.t2.b, test.t1.b)], other cond:eq(case(eq(cast(test.t2.c, double BINARY), 1), "1", eq(cast(test.t2.c, double BINARY), 2), "2", test.t3.d), "2222") |
|     ├─TableReader_20(Build)   | 0.00    | root      |               | data:Selection_19                                                                                                                                                            |
|     │ └─Selection_19          | 0.00    | cop[tikv] |               | eq(test.t2.c, "1111"), not(isnull(test.t2.b))                                                                                                                                |
|     │   └─TableFullScan_18    | 3.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                                                                               |
|     └─HashJoin_22(Probe)      | 0.09    | root      |               | left outer join, equal:[eq(test.t1.a, test.t3.a)]                                                                                                                            |
|       ├─TableReader_25(Build) | 0.07    | root      |               | data:Selection_24                                                                                                                                                            |
|       │ └─Selection_24        | 0.07    | cop[tikv] |               | ge(test.t1.dt, 2023-07-16 17:00:00.000000), le(test.t1.dt, 2023-07-16 17:04:00.000000), not(isnull(test.t1.b))                                                               |
|       │   └─TableFullScan_23  | 3.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                                                               |
|       └─TableReader_28(Probe) | 2.00    | root      |               | data:Selection_27                                                                                                                                                            |
|         └─Selection_27        | 2.00    | cop[tikv] |               | not(isnull(test.t3.a))                                                                                                                                                       |
|           └─TableFullScan_26  | 2.00    | cop[tikv] | table:t3      | keep order:false, stats:pseudo                                                                                                                                               |
+-------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set
Time: 0.018s
[15:10:56]TiDB root:test> set tidb_enable_outer_join_reorder = 'ON';
Query OK, 0 rows affected
Time: 0.001s
[15:11:03]TiDB root:test> select * from v where c='1111' and d1='2222' and dt between '2023-07-16 17:00:00' and '2023-07-16 17:04:00';
+---------------------+------+--------+
| dt                  | c    | d1     |
+---------------------+------+--------+
| 2023-07-16 17:01:00 | 1111 | 2222   |
| 2023-07-16 17:02:00 | 1111 | 2222   |
| 2023-07-16 17:03:00 | 1111 | <null> |
+---------------------+------+--------+
3 rows in set
Time: 0.007s
[15:11:07]TiDB root:test> explain select * from v where c='1111' and d1='2222' and dt between '2023-07-16 17:00:00' and '2023-07-16 17:04:00';
+-------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                            | estRows | task      | access object | operator info                                                                                                                                                                     |
+-------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_15                 | 0.00    | root      |               | test.t1.dt, test.t2.c, case(eq(cast(test.t2.c, double BINARY), 1), 1, eq(cast(test.t2.c, double BINARY), 2), 2, test.t3.d)->Column#14                                             |
| └─Projection_16               | 0.00    | root      |               | test.t1.dt, test.t3.d, test.t2.c                                                                                                                                                  |
|   └─HashJoin_18               | 0.00    | root      |               | left outer join, equal:[eq(test.t1.a, test.t3.a)], other cond:eq(case(eq(cast(test.t2.c, double BINARY), 1), "1", eq(cast(test.t2.c, double BINARY), 2), "2", test.t3.d), "2222") |
|     ├─HashJoin_20(Build)      | 0.00    | root      |               | inner join, equal:[eq(test.t2.b, test.t1.b)]                                                                                                                                      |
|     │ ├─TableReader_23(Build) | 0.00    | root      |               | data:Selection_22                                                                                                                                                                 |
|     │ │ └─Selection_22        | 0.00    | cop[tikv] |               | eq(test.t2.c, "1111"), not(isnull(test.t2.b))                                                                                                                                     |
|     │ │   └─TableFullScan_21  | 3.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                                                                                    |
|     │ └─TableReader_26(Probe) | 0.07    | root      |               | data:Selection_25                                                                                                                                                                 |
|     │   └─Selection_25        | 0.07    | cop[tikv] |               | ge(test.t1.dt, 2023-07-16 17:00:00.000000), le(test.t1.dt, 2023-07-16 17:04:00.000000), not(isnull(test.t1.b))                                                                    |
|     │     └─TableFullScan_24  | 3.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                                                                    |
|     └─TableReader_29(Probe)   | 2.00    | root      |               | data:Selection_28                                                                                                                                                                 |
|       └─Selection_28          | 2.00    | cop[tikv] |               | not(isnull(test.t3.a))                                                                                                                                                            |
|         └─TableFullScan_27    | 2.00    | cop[tikv] | table:t3      | keep order:false, stats:pseudo                                                                                                                                                    |
+-------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set
Time: 0.006s

@aytrack
Copy link
Contributor

aytrack commented Jul 17, 2023 •
edited
Loading

another thing is when i use mysql client it works well but use mycli got the wrong result like above

mysql> select @@tidb_enable_outer_join_reorder;
+----------------------------------+
| @@tidb_enable_outer_join_reorder |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.01 sec)

mysql> select * from v where c='1111' and d1='2222' and dt between '2023-07-16 17:00:00' and '2023-07-16 17:04:00';
+---------------------+------+------+
| dt                  | c    | d1   |
+---------------------+------+------+
| 2023-07-16 17:01:00 | 1111 | 2222 |
| 2023-07-16 17:02:00 | 1111 | 2222 |
+---------------------+------+------+
2 rows in set (0.01 sec)

mysql> explain select * from v where c='1111' and d1='2222' and dt between '2023-07-16 17:00:00' and '2023-07-16 17:04:00';
+--------------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | estRows | task      | access object | operator info                                                                                                                          |
+--------------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------+
| Projection_15                        | 0.00    | root      |               | test.t1.dt, test.t2.c, case(eq(cast(test.t2.c, double BINARY), 1), 1, eq(cast(test.t2.c, double BINARY), 2), 2, test.t3.d)->Column#14  |
| └─Projection_16                      | 0.00    | root      |               | test.t1.dt, test.t3.d, test.t2.c                                                                                                       |
|   └─HashJoin_18                      | 0.00    | root      |               | inner join, equal:[eq(test.t1.b, test.t2.b)]                                                                                           |
|     ├─HashJoin_20(Build)             | 0.00    | root      |               | inner join, equal:[eq(test.t3.a, test.t1.a)]                                                                                           |
|     │ ├─TableReader_23(Build)        | 0.00    | root      |               | data:Selection_22                                                                                                                      |
|     │ │ └─Selection_22               | 0.00    | cop[tikv] |               | eq(test.t3.d, "2222"), not(isnull(test.t3.a))                                                                                          |
|     │ │   └─TableFullScan_21         | 2.00    | cop[tikv] | table:t3      | keep order:false, stats:pseudo                                                                                                         |
|     │ └─TableReader_26(Probe)        | 0.07    | root      |               | data:Selection_25                                                                                                                      |
|     │   └─Selection_25               | 0.07    | cop[tikv] |               | ge(test.t1.dt, 2023-07-16 17:00:00.000000), le(test.t1.dt, 2023-07-16 17:04:00.000000), not(isnull(test.t1.a)), not(isnull(test.t1.b)) |
|     │     └─TableFullScan_24         | 3.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                         |
|     └─TableReader_29(Probe)          | 0.00    | root      |               | data:Selection_28                                                                                                                      |
|       └─Selection_28                 | 0.00    | cop[tikv] |               | eq(test.t2.c, "1111"), not(isnull(test.t2.b))                                                                                          |
|         └─TableFullScan_27           | 3.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                                         |
+--------------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)

@ti-chi-bot ti-chi-bot bot added may-affects-5.2 This bug maybe affects 5.2.x versions. may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-6.5 may-affects-7.1 labels Jul 17, 2023
@pcqz
Copy link
Author

pcqz commented Jul 17, 2023

Should be fixed by #44424

@pcqz pcqz closed this as completed Jul 17, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
may-affects-5.2 This bug maybe affects 5.2.x versions. may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-6.5 may-affects-7.1 severity/critical sig/planner SIG: Planner type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@aytrack @pcqz