Description:
MySQL 9.7.0: for a DECIMAL column, once a row with a NULL value has been evaluated, "col BETWEEN a AND b" is reported as NULL for every subsequently evaluated non-NULL row, instead of 0/1.
Smoking gun (deterministic, single engine): with ORDER BY c ASC the NULL row is evaluated first and poisons all later rows; with ORDER BY c DESC the NULL row is evaluated last and the result is fully correct (0/1). A per-row predicate whose value depends on which rows were evaluated earlier is impossible for a correct engine. INT and DOUBLE columns are correct in both orders, and the equivalent "c >= a AND c <= b" rewrite is also correct on 9.7.0.
This is a regression: correct on MySQL 9.6.0 in every case.
Root cause: sql/item_cmpfunc.cc, Item_func_between::val_int(), DECIMAL_RESULT branch. null_value is assigned only on the NULL/error path ("if (dec == nullptr) { null_value = args[0]->null_value; return 0; }"); the non-NULL fast path returns the comparison result WITHOUT resetting null_value to false. A stale "true" left by a prior NULL row is then read as the result of the next non-NULL row. The sibling branches reset null_value on every call (STRING; INT via compare_between_int_result; DOUBLE: "if ((null_value = args[0]->null_value)) return 0;"). Regressed by commit 888dfe739741 (Bug#38949150: Simplify Item::val_decimal()). The development/trunk source still resets null_value unconditionally.
Note: an indexed range scan on the BETWEEN can mask this (COUNT(*) WHERE col BETWEEN ... may look fine); it surfaces wherever null_value is read directly -- SELECT projection, views, generated columns.
How to repeat:
SELECT VERSION(); -- 9.7.0
CREATE TABLE tdc(c DECIMAL(10,0));
INSERT INTO tdc VALUES (1),(2),(5),(10),(NULL);
-- ASC: the NULL row is evaluated first and poisons all later rows
SELECT c, c BETWEEN 2 AND 5 AS b FROM tdc ORDER BY c;
-- 9.7.0: (NULL,NULL),(1,NULL),(2,NULL),(5,NULL),(10,NULL) [WRONG]
-- 9.6.0: (NULL,NULL),(1,0), (2,1), (5,1), (10,0) [correct]
-- DESC: the NULL row is evaluated last -> correct even on 9.7.0 (proves order-dependence)
SELECT c, c BETWEEN 2 AND 5 AS b FROM tdc ORDER BY c DESC;
-- 9.7.0: (10,0),(5,1),(2,1),(1,0),(NULL,NULL) [correct]
-- The equivalent rewrite is correct on 9.7.0:
SELECT c, (c >= 2 AND c <= 5) AS b FROM tdc ORDER BY c;
Suggested fix:
In sql/item_cmpfunc.cc, Item_func_between::val_int(), DECIMAL_RESULT branch, reset null_value = false on the non-NULL path before returning the comparison result (mirroring the DOUBLE/INT/STRING branches). Equivalently, assign null_value = args[0]->null_value unconditionally right after evaluating args[0], as the trunk source does. Regressed in commit 888dfe739741 (Bug#38949150).
Description: MySQL 9.7.0: for a DECIMAL column, once a row with a NULL value has been evaluated, "col BETWEEN a AND b" is reported as NULL for every subsequently evaluated non-NULL row, instead of 0/1. Smoking gun (deterministic, single engine): with ORDER BY c ASC the NULL row is evaluated first and poisons all later rows; with ORDER BY c DESC the NULL row is evaluated last and the result is fully correct (0/1). A per-row predicate whose value depends on which rows were evaluated earlier is impossible for a correct engine. INT and DOUBLE columns are correct in both orders, and the equivalent "c >= a AND c <= b" rewrite is also correct on 9.7.0. This is a regression: correct on MySQL 9.6.0 in every case. Root cause: sql/item_cmpfunc.cc, Item_func_between::val_int(), DECIMAL_RESULT branch. null_value is assigned only on the NULL/error path ("if (dec == nullptr) { null_value = args[0]->null_value; return 0; }"); the non-NULL fast path returns the comparison result WITHOUT resetting null_value to false. A stale "true" left by a prior NULL row is then read as the result of the next non-NULL row. The sibling branches reset null_value on every call (STRING; INT via compare_between_int_result; DOUBLE: "if ((null_value = args[0]->null_value)) return 0;"). Regressed by commit 888dfe739741 (Bug#38949150: Simplify Item::val_decimal()). The development/trunk source still resets null_value unconditionally. Note: an indexed range scan on the BETWEEN can mask this (COUNT(*) WHERE col BETWEEN ... may look fine); it surfaces wherever null_value is read directly -- SELECT projection, views, generated columns. How to repeat: SELECT VERSION(); -- 9.7.0 CREATE TABLE tdc(c DECIMAL(10,0)); INSERT INTO tdc VALUES (1),(2),(5),(10),(NULL); -- ASC: the NULL row is evaluated first and poisons all later rows SELECT c, c BETWEEN 2 AND 5 AS b FROM tdc ORDER BY c; -- 9.7.0: (NULL,NULL),(1,NULL),(2,NULL),(5,NULL),(10,NULL) [WRONG] -- 9.6.0: (NULL,NULL),(1,0), (2,1), (5,1), (10,0) [correct] -- DESC: the NULL row is evaluated last -> correct even on 9.7.0 (proves order-dependence) SELECT c, c BETWEEN 2 AND 5 AS b FROM tdc ORDER BY c DESC; -- 9.7.0: (10,0),(5,1),(2,1),(1,0),(NULL,NULL) [correct] -- The equivalent rewrite is correct on 9.7.0: SELECT c, (c >= 2 AND c <= 5) AS b FROM tdc ORDER BY c; Suggested fix: In sql/item_cmpfunc.cc, Item_func_between::val_int(), DECIMAL_RESULT branch, reset null_value = false on the non-NULL path before returning the comparison result (mirroring the DOUBLE/INT/STRING branches). Equivalently, assign null_value = args[0]->null_value unconditionally right after evaluating args[0], as the trunk source does. Regressed in commit 888dfe739741 (Bug#38949150).