From c7206b4a8c250da90b4d093a2c4bd6013bdb0170 Mon Sep 17 00:00:00 2001 From: akcube Date: Sat, 22 Nov 2025 04:19:30 +0530 Subject: [PATCH] Bug#119442 ORDER BY optimization doesn't recognize IS NULL as constant PROBLEM: 1. The optimizer's check_field_is_const() function only recognized equality operators (col = value) as constants for ORDER BY simplification. 2. It failed to recognize that "col IS NULL" also guarantees a single value (NULL) for sorting purposes. 3. This caused unnecessary filesort operations even when covering indexes existed. 4. Example: WHERE a = 1 AND b IS NULL ORDER BY a, b, c would use filesort despite having index (a, b, c). FIX: 1. Extended check_field_is_const() to handle Item_func::ISNULL_FUNC. 2. Added is_isnull_func() helper function to identify IS NULL conditions. 3. Properly handles OR conditions - "IS NULL OR value" remains non-constant, while "IS NULL OR IS NULL" is constant. 4. Stores IS NULL function as sentinel in const_item to ensure consistency across OR branches. 5. Preserves correct NULL ordering semantics (NULLs first in ASC, last in DESC). --- mysql-test/r/group_skip_scan.result | 6 +- mysql-test/r/order_by_all.result | 4 +- mysql-test/r/order_by_icp_mrr.result | 4 +- mysql-test/r/order_by_isnull.result | 259 +++++++++++++++++++++++++++ mysql-test/r/order_by_none.result | 4 +- mysql-test/t/order_by_isnull.test | 219 ++++++++++++++++++++++ sql/sql_select.cc | 62 +++++-- 7 files changed, 534 insertions(+), 24 deletions(-) create mode 100644 mysql-test/r/order_by_isnull.result create mode 100644 mysql-test/t/order_by_isnull.test diff --git a/mysql-test/r/group_skip_scan.result b/mysql-test/r/group_skip_scan.result index 33f5a98229e2..87b10db7db64 100644 --- a/mysql-test/r/group_skip_scan.result +++ b/mysql-test/r/group_skip_scan.result @@ -4316,10 +4316,8 @@ INSERT INTO t1 VALUES (NULL, 1); INSERT INTO t1 VALUES (NULL, 2); EXPLAIN FORMAT=TREE SELECT MIN(f2) FROM t1 WHERE f1 IS NULL GROUP BY f1; EXPLAIN --> Table scan on (rows=1) - -> Temporary table with deduplication (rows=1) - -> Filter: (t1.f1 is null) (rows=1) - -> Covering index skip scan for grouping on t1 using f1 over (f1 = NULL) (rows=1) +-> Filter: (t1.f1 is null) (rows=1) + -> Covering index skip scan for grouping on t1 using f1 over (f1 = NULL) (rows=1) SELECT MIN(f2) FROM t1 WHERE f1 IS NULL GROUP BY f1; MIN(f2) diff --git a/mysql-test/r/order_by_all.result b/mysql-test/r/order_by_all.result index 5252a0488936..2582573310e3 100644 --- a/mysql-test/r/order_by_all.result +++ b/mysql-test/r/order_by_all.result @@ -392,7 +392,7 @@ a b c 1 NULL b explain select * from t1 where a = 1 and b is null order by a desc, b desc; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL ref a a 9 const,const 2 100.00 Using where; Using index; Using filesort +1 SIMPLE t1 NULL ref a a 9 const,const 2 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` is null)) order by `test`.`t1`.`a` desc,`test`.`t1`.`b` desc select * from t1 where a = 1 and b is null order by a desc, b desc; @@ -411,7 +411,7 @@ Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`b` > 0)) order by `test`.`t1`.`a` desc,`test`.`t1`.`b` desc explain select * from t1 where a = 2 and b is null order by a desc,b desc; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL ref a a 9 const,const 1 100.00 Using where; Using index; Using filesort +1 SIMPLE t1 NULL ref a a 9 const,const 1 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`b` is null)) order by `test`.`t1`.`a` desc,`test`.`t1`.`b` desc explain select * from t1 where a = 2 and (b is null or b > 0) order by a diff --git a/mysql-test/r/order_by_icp_mrr.result b/mysql-test/r/order_by_icp_mrr.result index ab039989fa2e..bedf9a44beff 100644 --- a/mysql-test/r/order_by_icp_mrr.result +++ b/mysql-test/r/order_by_icp_mrr.result @@ -392,7 +392,7 @@ a b c 1 NULL b explain select * from t1 where a = 1 and b is null order by a desc, b desc; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL ref a a 9 const,const 2 100.00 Using where; Using index; Using filesort +1 SIMPLE t1 NULL ref a a 9 const,const 2 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` is null)) order by `test`.`t1`.`a` desc,`test`.`t1`.`b` desc select * from t1 where a = 1 and b is null order by a desc, b desc; @@ -411,7 +411,7 @@ Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`b` > 0)) order by `test`.`t1`.`a` desc,`test`.`t1`.`b` desc explain select * from t1 where a = 2 and b is null order by a desc,b desc; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL ref a a 9 const,const 1 100.00 Using where; Using index; Using filesort +1 SIMPLE t1 NULL ref a a 9 const,const 1 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`b` is null)) order by `test`.`t1`.`a` desc,`test`.`t1`.`b` desc explain select * from t1 where a = 2 and (b is null or b > 0) order by a diff --git a/mysql-test/r/order_by_isnull.result b/mysql-test/r/order_by_isnull.result new file mode 100644 index 000000000000..d54cb1217850 --- /dev/null +++ b/mysql-test/r/order_by_isnull.result @@ -0,0 +1,259 @@ +# +# Bug#119442: Optimizer doesn't simplify ORDER BY when field IS NULL +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +id INT PRIMARY KEY AUTO_INCREMENT, +category_id INT NOT NULL, +nullable_date DATETIME DEFAULT NULL, +created_by VARCHAR(50), +status VARCHAR(20), +priority TINYINT, +item_name VARCHAR(100), +description VARCHAR(200), +KEY idx_covering (category_id, nullable_date, created_by, status, priority, item_name, description) +) ENGINE=InnoDB; +INSERT INTO t1 (category_id, nullable_date, created_by, status, priority, item_name, description) VALUES +(100, NULL, 'User1', 'Active', 0, 'Item A', 'Description 1'), +(100, NULL, 'User1', 'Active', 0, 'Item B', 'Description 2'), +(100, NULL, 'User1', 'Active', 0, 'Item C', 'Description 3'), +(100, NULL, 'User1', 'Pending', 0, 'Item D', 'Description 4'), +(100, '2024-01-01 10:00:00', 'User1', 'Active', 0, 'Item E', 'Description 5'), +(200, NULL, 'User2', 'Active', 1, 'Item F', 'Description 6'), +(200, NULL, 'User3', 'Pending', 0, 'Item G', 'Description 7'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +# +# Test 1: Basic IS NULL with ORDER BY - demonstrating covering index lookup +# Covering index is used for IS NULL lookup (category_id, nullable_date) +# but filesort still needed for item_name since intermediate fields aren't constant +# +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, item_name +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 AND nullable_date IS NULL +ORDER BY category_id, nullable_date, item_name; +EXPLAIN +-> Sort: t1.item_name (cost=0.775 rows=4) + -> Filter: (t1.nullable_date is null) (cost=0.775 rows=4) + -> Covering index lookup on t1 using idx_covering (category_id = 100, nullable_date = NULL) (cost=0.775 rows=4) + +# +# Test 2: Compound index with IS NULL - demonstrating NO filesort +# Unlike Test 1, all intermediate index fields are constant, allowing +# the index to provide ordering for item_name without filesort +# +EXPLAIN FORMAT=TREE +SELECT id, category_id, status, priority, item_name, description +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 +AND nullable_date IS NULL +AND created_by = 'User1' + AND status = 'Active' + AND priority = 0 +AND item_name >= 'Item B' +ORDER BY category_id, nullable_date, created_by, status, priority, item_name ASC +LIMIT 10; +EXPLAIN +-> Limit: 10 row(s) (cost=0.702 rows=2) + -> Filter: ((t1.priority = 0) and (t1.`status` = 'Active') and (t1.created_by = 'User1') and (t1.category_id = 100) and (t1.nullable_date is null) and (t1.item_name >= 'Item B')) (cost=0.702 rows=2) + -> Covering index range scan on t1 using idx_covering over (category_id = 100 AND nullable_date = NULL AND created_by = 'User1' AND status = 'Active' AND priority = 0 AND 'Item B' <= item_name) (cost=0.702 rows=2) + +SELECT id, category_id, status, priority, item_name +FROM t1 +WHERE category_id = 100 +AND nullable_date IS NULL +AND created_by = 'User1' + AND status = 'Active' + AND priority = 0 +AND item_name >= 'Item B' +ORDER BY category_id, nullable_date, created_by, status, priority, item_name ASC +LIMIT 10; +id category_id status priority item_name +2 100 Active 0 Item B +3 100 Active 0 Item C +# +# Test 3: Mix of equality and IS NULL with all intermediate fields constant +# All intermediate index fields are constant, so index can provide ordering +# for item_name without filesort (demonstrates IS NULL works like equality) +# +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, created_by, status, priority, item_name +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 AND nullable_date IS NULL AND created_by = 'User1' AND status = 'Active' AND priority = 0 +ORDER BY category_id, nullable_date, created_by, status, priority, item_name; +EXPLAIN +-> Filter: (t1.nullable_date is null) (cost=0.35 rows=1) + -> Covering index lookup on t1 using idx_covering (category_id = 100, nullable_date = NULL, created_by = 'User1', status = 'Active', priority = 0) (cost=0.35 rows=1) + +# +# Test 4: IS NOT NULL should NOT be treated as constant +# This is a negative test - field can have many different non-NULL values +# +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, item_name +FROM t1 +WHERE nullable_date IS NOT NULL +ORDER BY nullable_date, item_name; +EXPLAIN +-> Sort: t1.nullable_date, t1.item_name (cost=0.95 rows=7) + -> Filter: (t1.nullable_date is not null) (cost=0.95 rows=7) + -> Covering index scan on t1 using idx_covering (cost=0.95 rows=7) + +# +# Test 5: Test with optimizer trace to verify "equals_constant_in_where" +# The trace should show that nullable_date is recognized as constant +# +SET optimizer_trace="enabled=on"; +SELECT id, category_id, nullable_date, item_name +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 AND nullable_date IS NULL +ORDER BY category_id, nullable_date, item_name +LIMIT 2; +id category_id nullable_date item_name +1 100 NULL Item A +2 100 NULL Item B +SELECT IF( +LOCATE('"equals_constant_in_where": true', TRACE) > 0, +'PASS: nullable_date recognized as constant', +'FAIL: nullable_date not recognized as constant' +) AS trace_check +FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; +trace_check +PASS: nullable_date recognized as constant +SET optimizer_trace="enabled=off"; +# +# Test 6: OR with IS NULL - field should NOT be treated as constant +# (nullable_date can be NULL OR have a value, so it's not constant) +# +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, item_name +FROM t1 +WHERE category_id = 100 AND (nullable_date IS NULL OR nullable_date > '2024-01-01') +ORDER BY nullable_date, item_name; +EXPLAIN +-> Sort: t1.nullable_date, t1.item_name (cost=1.43 rows=5) + -> Filter: ((t1.category_id = 100) and ((t1.nullable_date is null) or (t1.nullable_date > TIMESTAMP'2024-01-01 00:00:00'))) (cost=1.43 rows=5) + -> Covering index range scan on t1 using idx_covering over (category_id = 100 AND nullable_date = NULL) OR (category_id = 100 AND '2024-01-01 00:00:00' < nullable_date) (cost=1.43 rows=5) + +SELECT id, category_id, nullable_date, item_name +FROM t1 +WHERE category_id = 100 AND (nullable_date IS NULL OR nullable_date > '2024-01-01') +ORDER BY nullable_date, item_name; +id category_id nullable_date item_name +1 100 NULL Item A +2 100 NULL Item B +3 100 NULL Item C +4 100 NULL Item D +5 100 2024-01-01 10:00:00 Item E +# +# Test 7: OR with multiple IS NULL - same field +# This is effectively the same as a single IS NULL, so field IS constant +# +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, item_name +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 AND (nullable_date IS NULL OR nullable_date IS NULL) +ORDER BY category_id, nullable_date, item_name; +EXPLAIN +-> Sort: t1.item_name (cost=0.775 rows=4) + -> Filter: ((t1.nullable_date is null) or (t1.nullable_date is null)) (cost=0.775 rows=4) + -> Covering index lookup on t1 using idx_covering (category_id = 100, nullable_date = NULL) (cost=0.775 rows=4) + +# +# Test 8: Verify IS NULL works with DESC ordering +# When nullable_date IS NULL with other constants, index can still be used +# even with DESC on the non-constant field +# +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, created_by, status, priority, item_name +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 AND nullable_date IS NULL AND created_by = 'User1' AND status = 'Active' AND priority = 0 +ORDER BY category_id, nullable_date, created_by, status, priority, item_name DESC; +EXPLAIN +-> Filter: (t1.nullable_date is null) (cost=0.633 rows=3) + -> Covering index lookup on t1 using idx_covering (category_id = 100, nullable_date = NULL, created_by = 'User1', status = 'Active', priority = 0) (reverse) (cost=0.633 rows=3) + +# +# Test 9: Verify NULL ordering semantics with ASC +# NULLs should appear FIRST with ORDER BY ASC +# +CREATE TABLE t_null_order ( +id INT PRIMARY KEY AUTO_INCREMENT, +val INT, +name VARCHAR(10), +KEY idx_val (val, name) +); +INSERT INTO t_null_order (val, name) VALUES +(NULL, 'null1'), +(NULL, 'null2'), +(5, 'five'), +(10, 'ten'), +(NULL, 'null3'), +(1, 'one'); +ANALYZE TABLE t_null_order; +Table Op Msg_type Msg_text +test.t_null_order analyze status OK +SELECT id, val, name FROM t_null_order ORDER BY val ASC, name ASC; +id val name +1 NULL null1 +2 NULL null2 +5 NULL null3 +6 1 one +3 5 five +4 10 ten +# +# Test 10: Verify NULL ordering semantics with DESC +# NULLs should appear LAST with ORDER BY DESC +# +SELECT id, val, name FROM t_null_order ORDER BY val DESC, name ASC; +id val name +4 10 ten +3 5 five +6 1 one +1 NULL null1 +2 NULL null2 +5 NULL null3 +# +# Test 11: Verify optimization works with DESC and preserves NULL ordering +# With val IS NULL, val is constant, so should only sort by name +# +EXPLAIN FORMAT=TREE +SELECT id, val, name FROM t_null_order FORCE INDEX(idx_val) +WHERE val IS NULL +ORDER BY val DESC, name ASC; +EXPLAIN +-> Filter: (t_null_order.val is null) (cost=0.553 rows=3) + -> Covering index lookup on t_null_order using idx_val (val = NULL) (cost=0.553 rows=3) + +SELECT id, val, name FROM t_null_order FORCE INDEX(idx_val) +WHERE val IS NULL +ORDER BY val DESC, name ASC; +id val name +1 NULL null1 +2 NULL null2 +5 NULL null3 +# +# Test 12: Mixed ASC/DESC with IS NULL constraint +# +EXPLAIN FORMAT=TREE +SELECT id, val, name FROM t_null_order FORCE INDEX(idx_val) +WHERE val IS NULL +ORDER BY val ASC, name DESC; +EXPLAIN +-> Filter: (t_null_order.val is null) (cost=0.553 rows=3) + -> Covering index lookup on t_null_order using idx_val (val = NULL) (reverse) (cost=0.553 rows=3) + +SELECT id, val, name FROM t_null_order FORCE INDEX(idx_val) +WHERE val IS NULL +ORDER BY val ASC, name DESC; +id val name +5 NULL null3 +2 NULL null2 +1 NULL null1 +DROP TABLE t_null_order; +DROP TABLE t1; +# +# End of test for Bug#119442 +# diff --git a/mysql-test/r/order_by_none.result b/mysql-test/r/order_by_none.result index 3c486298c7a0..2021f1c56161 100644 --- a/mysql-test/r/order_by_none.result +++ b/mysql-test/r/order_by_none.result @@ -391,7 +391,7 @@ a b c 1 NULL b explain select * from t1 where a = 1 and b is null order by a desc, b desc; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL ref a a 9 const,const 2 100.00 Using where; Using index; Using filesort +1 SIMPLE t1 NULL ref a a 9 const,const 2 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` is null)) order by `test`.`t1`.`a` desc,`test`.`t1`.`b` desc select * from t1 where a = 1 and b is null order by a desc, b desc; @@ -410,7 +410,7 @@ Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`b` > 0)) order by `test`.`t1`.`a` desc,`test`.`t1`.`b` desc explain select * from t1 where a = 2 and b is null order by a desc,b desc; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL ref a a 9 const,const 1 100.00 Using where; Using index; Using filesort +1 SIMPLE t1 NULL ref a a 9 const,const 1 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 2) and (`test`.`t1`.`b` is null)) order by `test`.`t1`.`a` desc,`test`.`t1`.`b` desc explain select * from t1 where a = 2 and (b is null or b > 0) order by a diff --git a/mysql-test/t/order_by_isnull.test b/mysql-test/t/order_by_isnull.test new file mode 100644 index 000000000000..d6a91e8f4df6 --- /dev/null +++ b/mysql-test/t/order_by_isnull.test @@ -0,0 +1,219 @@ +# Test for Bug#119442: ORDER BY optimization with IS NULL conditions +# The optimizer should treat "field IS NULL" as making the field effectively +# constant for ORDER BY purposes, allowing it to be removed from ORDER BY +# and avoiding unnecessary filesort operations. + +--echo # +--echo # Bug#119442: Optimizer doesn't simplify ORDER BY when field IS NULL +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 ( + id INT PRIMARY KEY AUTO_INCREMENT, + category_id INT NOT NULL, + nullable_date DATETIME DEFAULT NULL, + created_by VARCHAR(50), + status VARCHAR(20), + priority TINYINT, + item_name VARCHAR(100), + description VARCHAR(200), + KEY idx_covering (category_id, nullable_date, created_by, status, priority, item_name, description) +) ENGINE=InnoDB; + +# Insert test data with mix of NULL and non-NULL nullable_date +INSERT INTO t1 (category_id, nullable_date, created_by, status, priority, item_name, description) VALUES +(100, NULL, 'User1', 'Active', 0, 'Item A', 'Description 1'), +(100, NULL, 'User1', 'Active', 0, 'Item B', 'Description 2'), +(100, NULL, 'User1', 'Active', 0, 'Item C', 'Description 3'), +(100, NULL, 'User1', 'Pending', 0, 'Item D', 'Description 4'), +(100, '2024-01-01 10:00:00', 'User1', 'Active', 0, 'Item E', 'Description 5'), +(200, NULL, 'User2', 'Active', 1, 'Item F', 'Description 6'), +(200, NULL, 'User3', 'Pending', 0, 'Item G', 'Description 7'); + +ANALYZE TABLE t1; + +--echo # +--echo # Test 1: Basic IS NULL with ORDER BY - demonstrating covering index lookup +--echo # Covering index is used for IS NULL lookup (category_id, nullable_date) +--echo # but filesort still needed for item_name since intermediate fields aren't constant +--echo # +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, item_name +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 AND nullable_date IS NULL +ORDER BY category_id, nullable_date, item_name; + +--echo # +--echo # Test 2: Compound index with IS NULL - demonstrating NO filesort +--echo # Unlike Test 1, all intermediate index fields are constant, allowing +--echo # the index to provide ordering for item_name without filesort +--echo # +EXPLAIN FORMAT=TREE +SELECT id, category_id, status, priority, item_name, description +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 + AND nullable_date IS NULL + AND created_by = 'User1' + AND status = 'Active' + AND priority = 0 + AND item_name >= 'Item B' +ORDER BY category_id, nullable_date, created_by, status, priority, item_name ASC +LIMIT 10; + +# Verify the query returns correct results +SELECT id, category_id, status, priority, item_name +FROM t1 +WHERE category_id = 100 + AND nullable_date IS NULL + AND created_by = 'User1' + AND status = 'Active' + AND priority = 0 + AND item_name >= 'Item B' +ORDER BY category_id, nullable_date, created_by, status, priority, item_name ASC +LIMIT 10; + +--echo # +--echo # Test 3: Mix of equality and IS NULL with all intermediate fields constant +--echo # All intermediate index fields are constant, so index can provide ordering +--echo # for item_name without filesort (demonstrates IS NULL works like equality) +--echo # +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, created_by, status, priority, item_name +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 AND nullable_date IS NULL AND created_by = 'User1' AND status = 'Active' AND priority = 0 +ORDER BY category_id, nullable_date, created_by, status, priority, item_name; + +--echo # +--echo # Test 4: IS NOT NULL should NOT be treated as constant +--echo # This is a negative test - field can have many different non-NULL values +--echo # +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, item_name +FROM t1 +WHERE nullable_date IS NOT NULL +ORDER BY nullable_date, item_name; + +--echo # +--echo # Test 5: Test with optimizer trace to verify "equals_constant_in_where" +--echo # The trace should show that nullable_date is recognized as constant +--echo # +SET optimizer_trace="enabled=on"; + +SELECT id, category_id, nullable_date, item_name +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 AND nullable_date IS NULL +ORDER BY category_id, nullable_date, item_name +LIMIT 2; + +SELECT IF( + LOCATE('"equals_constant_in_where": true', TRACE) > 0, + 'PASS: nullable_date recognized as constant', + 'FAIL: nullable_date not recognized as constant' +) AS trace_check +FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +SET optimizer_trace="enabled=off"; + +--echo # +--echo # Test 6: OR with IS NULL - field should NOT be treated as constant +--echo # (nullable_date can be NULL OR have a value, so it's not constant) +--echo # +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, item_name +FROM t1 +WHERE category_id = 100 AND (nullable_date IS NULL OR nullable_date > '2024-01-01') +ORDER BY nullable_date, item_name; + +# Verify results are correctly ordered +SELECT id, category_id, nullable_date, item_name +FROM t1 +WHERE category_id = 100 AND (nullable_date IS NULL OR nullable_date > '2024-01-01') +ORDER BY nullable_date, item_name; + +--echo # +--echo # Test 7: OR with multiple IS NULL - same field +--echo # This is effectively the same as a single IS NULL, so field IS constant +--echo # +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, item_name +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 AND (nullable_date IS NULL OR nullable_date IS NULL) +ORDER BY category_id, nullable_date, item_name; + +--echo # +--echo # Test 8: Verify IS NULL works with DESC ordering +--echo # When nullable_date IS NULL with other constants, index can still be used +--echo # even with DESC on the non-constant field +--echo # +EXPLAIN FORMAT=TREE +SELECT id, category_id, nullable_date, created_by, status, priority, item_name +FROM t1 FORCE INDEX(idx_covering) +WHERE category_id = 100 AND nullable_date IS NULL AND created_by = 'User1' AND status = 'Active' AND priority = 0 +ORDER BY category_id, nullable_date, created_by, status, priority, item_name DESC; + +--echo # +--echo # Test 9: Verify NULL ordering semantics with ASC +--echo # NULLs should appear FIRST with ORDER BY ASC +--echo # +CREATE TABLE t_null_order ( + id INT PRIMARY KEY AUTO_INCREMENT, + val INT, + name VARCHAR(10), + KEY idx_val (val, name) +); + +INSERT INTO t_null_order (val, name) VALUES + (NULL, 'null1'), + (NULL, 'null2'), + (5, 'five'), + (10, 'ten'), + (NULL, 'null3'), + (1, 'one'); + +ANALYZE TABLE t_null_order; + +SELECT id, val, name FROM t_null_order ORDER BY val ASC, name ASC; + +--echo # +--echo # Test 10: Verify NULL ordering semantics with DESC +--echo # NULLs should appear LAST with ORDER BY DESC +--echo # +SELECT id, val, name FROM t_null_order ORDER BY val DESC, name ASC; + +--echo # +--echo # Test 11: Verify optimization works with DESC and preserves NULL ordering +--echo # With val IS NULL, val is constant, so should only sort by name +--echo # +EXPLAIN FORMAT=TREE +SELECT id, val, name FROM t_null_order FORCE INDEX(idx_val) +WHERE val IS NULL +ORDER BY val DESC, name ASC; + +# Verify NULLs still appear correctly (all values are NULL, so just ordered by name) +SELECT id, val, name FROM t_null_order FORCE INDEX(idx_val) +WHERE val IS NULL +ORDER BY val DESC, name ASC; + +--echo # +--echo # Test 12: Mixed ASC/DESC with IS NULL constraint +--echo # +EXPLAIN FORMAT=TREE +SELECT id, val, name FROM t_null_order FORCE INDEX(idx_val) +WHERE val IS NULL +ORDER BY val ASC, name DESC; + +SELECT id, val, name FROM t_null_order FORCE INDEX(idx_val) +WHERE val IS NULL +ORDER BY val ASC, name DESC; + +DROP TABLE t_null_order; + +# Cleanup +DROP TABLE t1; + +--echo # +--echo # End of test for Bug#119442 +--echo # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b5886bb54d2c..a4f62565a8c0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3945,6 +3945,16 @@ static bool equal(const Item *i1, const Item *i2, const Field *f2) { return false; } +/* + Return true if the item represents an IS NULL function. +*/ + +static inline bool is_isnull_func(const Item *item) { + return item != nullptr && item->type() == Item::FUNC_ITEM && + down_cast(item)->functype() == + Item_func::ISNULL_FUNC; +} + /** Check if a field is equal to a constant value in a condition @@ -3978,22 +3988,46 @@ bool check_field_is_const(Item *cond, const Item *order_item, } if (cond->type() != Item::FUNC_ITEM) return false; Item_func *const func = down_cast(cond); - if (func->functype() != Item_func::EQUAL_FUNC && - func->functype() != Item_func::EQ_FUNC) - return false; - Item_func_comparison *comp = down_cast(func); - Item *left = comp->arguments()[0]; - Item *right = comp->arguments()[1]; - if (equal(left, order_item, order_field)) { - if (equality_determines_uniqueness(comp, left, right)) { - if (*const_item != nullptr) return right->eq(*const_item); - *const_item = right; + if (func->functype() == Item_func::EQUAL_FUNC || + func->functype() == Item_func::EQ_FUNC) { + Item_func_comparison *comp = down_cast(func); + Item *left = comp->arguments()[0]; + Item *right = comp->arguments()[1]; + Item *candidate_const = nullptr; + + if (equal(left, order_item, order_field)) { + if (equality_determines_uniqueness(comp, left, right)) + candidate_const = right; + } else if (equal(right, order_item, order_field)) { + if (equality_determines_uniqueness(comp, right, left)) + candidate_const = left; + } + + if (candidate_const) { + if (*const_item != nullptr) { + // val = 5 OR val IS NULL is not a constant + if (is_isnull_func(*const_item)) return false; + return candidate_const->eq(*const_item); + } + *const_item = candidate_const; return true; } - } else if (equal(right, order_item, order_field)) { - if (equality_determines_uniqueness(comp, right, left)) { - if (*const_item != nullptr) return left->eq(*const_item); - *const_item = left; + } else if (func->functype() == Item_func::ISNULL_FUNC) { + /* + Handle "field IS NULL" conditions. Since all NULL values are treated + uniformly in ORDER BY (NULLs appear first in ASC, last in DESC), + a field constrained to IS NULL is effectively constant for ordering. + + We store the IS NULL function itself as a sentinel in const_item to + ensure consistency across OR branches. IS NULL is only compatible with + other IS NULL conditions, not with equality conditions. + */ + if (equal(func->arguments()[0], order_item, order_field)) { + if (*const_item != nullptr) { + if (is_isnull_func(*const_item)) return true; // val IS NULL OR val IS NULL is constant + return false; + } + *const_item = func; return true; } }