summaryrefslogtreecommitdiff
path: root/mysql-test/main/range.test
diff options
context:
space:
mode:
authorMonty <monty@mariadb.org>2022-06-15 23:49:09 +0300
committerMonty <monty@mariadb.org>2022-06-16 00:12:11 +0300
commit674842bee0a564a2d94e99c4e1319a716aa10aa9 (patch)
treedc1371820ac7210ef63cfe43a56723bf3d7e6641 /mysql-test/main/range.test
parent27309fc6b0833d7a74baab4afaeef1dcb14307c8 (diff)
downloadmariadb-git-674842bee0a564a2d94e99c4e1319a716aa10aa9.tar.gz
MDEV-28858 Wrong result with table elimination combined with not_null_range_scan
The bug was that build_notnull_conds_for_range_scans() did not take into account the join_tab is not yet sorted with constant tables first. Fixed the bug by testing explicitely if a table is a const table.
Diffstat (limited to 'mysql-test/main/range.test')
-rw-r--r--mysql-test/main/range.test18
1 files changed, 18 insertions, 0 deletions
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 #