Bug #119727 wrong result for 'value between a and null' for certain temporal types

• Submitted: 20 Jan 10:11
• Modified: 20 Jan 11:56
• Reporter: Kaiwang CHen (OCA)
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[20 Jan 10:11] Kaiwang CHen

Description:
Item_func_between::val_int() gets NULL when any operand involved in comparison is NULL. However,
temporal values of different types might lead to incompatible numeric formats in comparison, which breaks
temporal semantics and leads to wrong results.

// cmp_type REAL_RESULT

    if (!args[1]->null_value && !args[2]->null_value)
      return (longlong)((value >= a && value <= b) != negated);
    if (args[1]->null_value && args[2]->null_value)
      null_value = true;
    else if (args[1]->null_value) {
      null_value = value <= b;  // not null if false range.
    } else {
      null_value = value >= a; // XXX: Both examples execute this statement.
    }

How to repeat:
-- case 1: different types in comparison

create table t (value date, a timestamp, b year);
insert into t values ('2019-12-25', '2017-05-24', null);
-- 0, expect NULL,
-- because temporal comparison '2019-12-25' >= '2017-05-24' should be true,
-- which leads to comparing '2019-12-25' against null thus getting NULL
select value between a and b from t;
-- 0
select value <= a from t;

-- case 2: same types in comparison

create table t (value date, a date, b year);
insert into t values ('2019-12-25', '2017-05-24', null);
-- NULL
select value between a and b from t;
-- 0
select value <= a from t;

Suggested fix:
Comparing temporal values with BETWEEN should also promote to the same numeric format.

Note: cmp_type == INT_RESULT also does not set null_value properly.

[20 Jan 11:56] Roy Lyseng

Thank you for the bug report.
Verified as described.

[27 Jan 18:37] Khaled Alam

PR: https://github.com/mysql/mysql-server/pull/644

[30 Jan 17:23] OCA Admin

Contribution submitted via Github - Bug#119727: Fix wrong result for 'value BETWEEN a AND NULL' with temporal types 
(*) Contribution by Khaled Alam (Github khaledalam, mysql-server/pull/644#issuecomment-3824183119): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_3215736675.txt (text/plain), 6.67 KiB.