From 55aaf1e7cb37e2b71a668f955fca6b1f505770ea Mon Sep 17 00:00:00 2001 From: Khaled Alam Date: Tue, 27 Jan 2026 22:31:35 +0400 Subject: [PATCH] Bug#119727: Fix wrong result for 'value BETWEEN a AND NULL' with temporal types --- mysql-test/r/func_between_temporal.result | 65 +++++++++++++++++++++++ mysql-test/t/func_between_temporal.test | 55 +++++++++++++++++++ sql/item_cmpfunc.cc | 10 +++- 3 files changed, 129 insertions(+), 1 deletion(-) create mode 100644 mysql-test/r/func_between_temporal.result create mode 100644 mysql-test/t/func_between_temporal.test diff --git a/mysql-test/r/func_between_temporal.result b/mysql-test/r/func_between_temporal.result new file mode 100644 index 000000000000..b74a5c6a941c --- /dev/null +++ b/mysql-test/r/func_between_temporal.result @@ -0,0 +1,65 @@ +# +# Bug#119727: wrong result for 'value between a and null' for certain +# temporal types +# +# Test case 1: DATE, TIMESTAMP, YEAR(NULL) - was returning 0, should be NULL +CREATE TABLE t1 (value DATE, a TIMESTAMP, b YEAR); +INSERT INTO t1 VALUES ('2019-12-25', '2017-05-24', NULL); +SELECT value BETWEEN a AND b FROM t1; +value BETWEEN a AND b +NULL +DROP TABLE t1; +# Test case 2: DATE, DATE, YEAR(NULL) - was already correct (NULL) +CREATE TABLE t2 (value DATE, a DATE, b YEAR); +INSERT INTO t2 VALUES ('2019-12-25', '2017-05-24', NULL); +SELECT value BETWEEN a AND b FROM t2; +value BETWEEN a AND b +NULL +DROP TABLE t2; +# Test case 3: DATE, DATETIME, YEAR(NULL) - similar to case 1 +CREATE TABLE t3 (value DATE, a DATETIME, b YEAR); +INSERT INTO t3 VALUES ('2019-12-25', '2017-05-24 00:00:00', NULL); +SELECT value BETWEEN a AND b FROM t3; +value BETWEEN a AND b +NULL +DROP TABLE t3; +# Test case 4: Verify that when value < a, result is FALSE (not NULL) +CREATE TABLE t4 (value DATE, a TIMESTAMP, b YEAR); +INSERT INTO t4 VALUES ('2016-01-01', '2017-05-24', NULL); +SELECT value BETWEEN a AND b FROM t4; +value BETWEEN a AND b +0 +DROP TABLE t4; +# Test case 5: TIMESTAMP, DATE, YEAR(NULL) +CREATE TABLE t5 (value TIMESTAMP, a DATE, b YEAR); +INSERT INTO t5 VALUES ('2019-12-25 10:00:00', '2017-05-24', NULL); +SELECT value BETWEEN a AND b FROM t5; +value BETWEEN a AND b +NULL +DROP TABLE t5; +# Test case 6: All three are temporal but none is NULL - verify normal behavior +# Using DATE/DATETIME/TIMESTAMP (without YEAR) to avoid YEAR-DATE conversion edge cases +CREATE TABLE t6 (value DATE, a TIMESTAMP, b DATETIME); +INSERT INTO t6 VALUES ('2019-12-25', '2017-05-24', '2020-12-31 23:59:59'); +SELECT value BETWEEN a AND b FROM t6; +value BETWEEN a AND b +1 +INSERT INTO t6 VALUES ('2021-01-01', '2017-05-24', '2020-12-31 23:59:59'); +SELECT value BETWEEN a AND b FROM t6 WHERE value = '2021-01-01'; +value BETWEEN a AND b +0 +DROP TABLE t6; +# Test case 7: Lower bound is NULL +CREATE TABLE t7 (value DATE, a YEAR, b TIMESTAMP); +INSERT INTO t7 VALUES ('2019-12-25', NULL, '2020-05-24'); +SELECT value BETWEEN a AND b FROM t7; +value BETWEEN a AND b +NULL +DROP TABLE t7; +# Test case 8: Both bounds are NULL +CREATE TABLE t8 (value DATE, a YEAR, b YEAR); +INSERT INTO t8 VALUES ('2019-12-25', NULL, NULL); +SELECT value BETWEEN a AND b FROM t8; +value BETWEEN a AND b +NULL +DROP TABLE t8; diff --git a/mysql-test/t/func_between_temporal.test b/mysql-test/t/func_between_temporal.test new file mode 100644 index 000000000000..7a7263061539 --- /dev/null +++ b/mysql-test/t/func_between_temporal.test @@ -0,0 +1,55 @@ +--echo # +--echo # Bug#119727: wrong result for 'value between a and null' for certain +--echo # temporal types +--echo # + +--echo # Test case 1: DATE, TIMESTAMP, YEAR(NULL) - was returning 0, should be NULL +CREATE TABLE t1 (value DATE, a TIMESTAMP, b YEAR); +INSERT INTO t1 VALUES ('2019-12-25', '2017-05-24', NULL); +SELECT value BETWEEN a AND b FROM t1; +DROP TABLE t1; + +--echo # Test case 2: DATE, DATE, YEAR(NULL) - was already correct (NULL) +CREATE TABLE t2 (value DATE, a DATE, b YEAR); +INSERT INTO t2 VALUES ('2019-12-25', '2017-05-24', NULL); +SELECT value BETWEEN a AND b FROM t2; +DROP TABLE t2; + +--echo # Test case 3: DATE, DATETIME, YEAR(NULL) - similar to case 1 +CREATE TABLE t3 (value DATE, a DATETIME, b YEAR); +INSERT INTO t3 VALUES ('2019-12-25', '2017-05-24 00:00:00', NULL); +SELECT value BETWEEN a AND b FROM t3; +DROP TABLE t3; + +--echo # Test case 4: Verify that when value < a, result is FALSE (not NULL) +CREATE TABLE t4 (value DATE, a TIMESTAMP, b YEAR); +INSERT INTO t4 VALUES ('2016-01-01', '2017-05-24', NULL); +SELECT value BETWEEN a AND b FROM t4; +DROP TABLE t4; + +--echo # Test case 5: TIMESTAMP, DATE, YEAR(NULL) +CREATE TABLE t5 (value TIMESTAMP, a DATE, b YEAR); +INSERT INTO t5 VALUES ('2019-12-25 10:00:00', '2017-05-24', NULL); +SELECT value BETWEEN a AND b FROM t5; +DROP TABLE t5; + +--echo # Test case 6: All three are temporal but none is NULL - verify normal behavior +--echo # Using DATE/DATETIME/TIMESTAMP (without YEAR) to avoid YEAR-DATE conversion edge cases +CREATE TABLE t6 (value DATE, a TIMESTAMP, b DATETIME); +INSERT INTO t6 VALUES ('2019-12-25', '2017-05-24', '2020-12-31 23:59:59'); +SELECT value BETWEEN a AND b FROM t6; +INSERT INTO t6 VALUES ('2021-01-01', '2017-05-24', '2020-12-31 23:59:59'); +SELECT value BETWEEN a AND b FROM t6 WHERE value = '2021-01-01'; +DROP TABLE t6; + +--echo # Test case 7: Lower bound is NULL +CREATE TABLE t7 (value DATE, a YEAR, b TIMESTAMP); +INSERT INTO t7 VALUES ('2019-12-25', NULL, '2020-05-24'); +SELECT value BETWEEN a AND b FROM t7; +DROP TABLE t7; + +--echo # Test case 8: Both bounds are NULL +CREATE TABLE t8 (value DATE, a YEAR, b YEAR); +INSERT INTO t8 VALUES ('2019-12-25', NULL, NULL); +SELECT value BETWEEN a AND b FROM t8; +DROP TABLE t8; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 035bd78ffe0d..747560948869 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -3345,8 +3345,16 @@ bool Item_func_between::resolve_type(THD *thd) { Detect the comparison of DATE/DATETIME items. At least one of items should be a DATE/DATETIME item and other items should return the STRING result. + + Also detect when all three arguments are temporal types (including YEAR). + When YEAR is mixed with DATE/DATETIME/TIMESTAMP, the aggregated cmp_type + becomes REAL_RESULT, but we still need proper temporal comparison to avoid + comparing incompatible numeric formats (e.g., DATE as YYYYMMDD vs + TIMESTAMP as YYYYMMDDHHMMSS). See Bug#119727. */ - if (cmp_type == STRING_RESULT) { + const bool all_temporal = + args[0]->is_temporal() && args[1]->is_temporal() && args[2]->is_temporal(); + if (cmp_type == STRING_RESULT || all_temporal) { for (int i = 0; i < 3; i++) { if (args[i]->is_temporal_with_date()) datetime_items_found++;