diff options
| author | Monty <monty@mariadb.org> | 2023-01-12 18:45:40 +0200 |
|---|---|---|
| committer | Monty <monty@mariadb.org> | 2023-01-13 14:23:55 +0200 |
| commit | 0595dd0f5674845637135feaa5cb7b28d0dc10a9 (patch) | |
| tree | 9905ce1c0cf31c4c5835e8365abfc422bf626ec1 | |
| parent | 73ecab3d26c055928673a2629f4513aa7008dbf0 (diff) | |
| download | mariadb-git-0595dd0f5674845637135feaa5cb7b28d0dc10a9.tar.gz | |
MDEV-30080 Wrong result with LEFT JOINs involving constant tables
The reason things fails in 10.5 and above is that test_quick_select()
returns -1 (impossible range) for empty tables if there are any
conditions attached.
This didn't happen in 10.4 as the cost for a range was more than for
a table scan with 0 rows and get_key_scan_params() did not create any
range plans and thus did not mark the range as impossible.
The code that checked the 'impossible range' conditions did not take
into account all cases of LEFT JOIN usage.
Adding an extra check if the table is used with an ON condition in case
of 'impossible range' fixes the issue.
| -rw-r--r-- | mysql-test/main/join.result | 17 | ||||
| -rw-r--r-- | mysql-test/main/join.test | 15 | ||||
| -rw-r--r-- | sql/sql_select.cc | 2 |
3 files changed, 33 insertions, 1 deletions
diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index b9543fc2c43..859bc588ed4 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -3407,3 +3407,20 @@ id select_type table type possible_keys key key_len ref rows Extra drop table t1,t2,t3; drop table t1000,t10,t03; # End of 10.3 tests +# +# MDEV-30080 Wrong result with LEFT JOINs involving constant tables +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(1); +CREATE TABLE t3 (c INT PRIMARY KEY) ENGINE=MyISAM; +SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b; +a b c +1 1 NULL +1 1 NULL +SELECT COUNT(*) FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b; +COUNT(*) +2 +DROP TABLE t1, t2, t3; +# End of 10.5 tests diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test index b99f05f7c88..c8bd2886b30 100644 --- a/mysql-test/main/join.test +++ b/mysql-test/main/join.test @@ -1820,3 +1820,18 @@ drop table t1,t2,t3; drop table t1000,t10,t03; --echo # End of 10.3 tests + +--echo # +--echo # MDEV-30080 Wrong result with LEFT JOINs involving constant tables +--echo # + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(1); +CREATE TABLE t3 (c INT PRIMARY KEY) ENGINE=MyISAM; +SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b; +SELECT COUNT(*) FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b; +DROP TABLE t1, t2, t3; + +--echo # End of 10.5 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 98d5ccb7eb2..7bbe5c6a237 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5639,7 +5639,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, caller to abort with a zero row result. */ TABLE_LIST *emb= s->table->pos_in_table_list->embedding; - if (emb && !emb->sj_on_expr) + if (emb && !emb->sj_on_expr && !*s->on_expr_ref) { /* Mark all tables in a multi-table join nest as const */ mark_join_nest_as_const(join, emb, &found_const_table_map, |