summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/main/range.result19
-rw-r--r--mysql-test/main/range.test18
-rw-r--r--mysql-test/main/range_mrr_icp.result19
-rw-r--r--sql/sql_select.cc14
4 files changed, 64 insertions, 6 deletions
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index aca500bf038..81098118abd 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -3635,6 +3635,25 @@ SELECT * FROM t1 LEFT JOIN t2 ON a = pk WHERE b >= 0 AND pk IS NULL;
a pk b
DROP TABLE t1, t2;
SET @@optimizer_switch= @save_optimizer_switch;
+
+# MDEV-28858 Wrong result with table elimination combined with
+# not_null_range_scan
+#
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10,1),(null,2);
+CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+SET @save_optimizer_switch= @@optimizer_switch;
+SET optimizer_switch= 'not_null_range_scan=on';
+SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
+b
+2
+SET optimizer_switch= 'not_null_range_scan=off';
+SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
+b
+2
+SET @@optimizer_switch=@save_optimizer_switch;
+drop table t1,t2;
#
# End of 10.5 tests
#
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index 6a2e318e732..a785726cb22 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2492,6 +2492,24 @@ DROP TABLE t1, t2;
SET @@optimizer_switch= @save_optimizer_switch;
+--echo
+--echo # MDEV-28858 Wrong result with table elimination combined with
+--echo # not_null_range_scan
+--echo #
+
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10,1),(null,2);
+CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+
+SET @save_optimizer_switch= @@optimizer_switch;
+SET optimizer_switch= 'not_null_range_scan=on';
+SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
+SET optimizer_switch= 'not_null_range_scan=off';
+SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
+SET @@optimizer_switch=@save_optimizer_switch;
+drop table t1,t2;
+
--echo #
--echo # End of 10.5 tests
--echo #
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index eb4daf7fbc8..e68af8545cf 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -3624,6 +3624,25 @@ SELECT * FROM t1 LEFT JOIN t2 ON a = pk WHERE b >= 0 AND pk IS NULL;
a pk b
DROP TABLE t1, t2;
SET @@optimizer_switch= @save_optimizer_switch;
+
+# MDEV-28858 Wrong result with table elimination combined with
+# not_null_range_scan
+#
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10,1),(null,2);
+CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+SET @save_optimizer_switch= @@optimizer_switch;
+SET optimizer_switch= 'not_null_range_scan=on';
+SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
+b
+2
+SET optimizer_switch= 'not_null_range_scan=off';
+SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
+b
+2
+SET @@optimizer_switch=@save_optimizer_switch;
+drop table t1,t2;
#
# End of 10.5 tests
#
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b4e3a547773..cedf640e4ad 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -29573,11 +29573,12 @@ bool build_notnull_conds_for_range_scans(JOIN *join, Item *cond,
DBUG_ENTER("build_notnull_conds_for_range_scans");
- for (JOIN_TAB *s= join->join_tab + join->const_tables ;
+ for (JOIN_TAB *s= join->join_tab;
s < join->join_tab + join->table_count ; s++)
{
/* Clear all needed bitmaps to mark found fields */
- if (allowed & s->table->map)
+ if ((allowed & s->table->map) &&
+ !(s->table->map && join->const_table_map))
bitmap_clear_all(&s->table->tmp_set);
}
@@ -29592,17 +29593,18 @@ bool build_notnull_conds_for_range_scans(JOIN *join, Item *cond,
For each table t from 'allowed' build a conjunction of NOT NULL predicates
constructed for all found fields if they are included in some indexes.
If the construction of the conjunction succeeds attach the formula to
- t->table->notnull_cond. The condition will be used to look for complementary
- range scans.
+ t->table->notnull_cond. The condition will be used to look for
+ complementary range scans.
*/
- for (JOIN_TAB *s= join->join_tab + join->const_tables ;
+ for (JOIN_TAB *s= join->join_tab ;
s < join->join_tab + join->table_count ; s++)
{
TABLE *tab= s->table;
List<Item> notnull_list;
Item *notnull_cond= 0;
- if (!(allowed & tab->map))
+ if (!(allowed & tab->map) ||
+ (s->table->map && join->const_table_map))
continue;
for (Field** field_ptr= tab->field; *field_ptr; field_ptr++)