Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12691

Wrong result for: bigint_column BETWEEN time_column AND str_literal

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3
    • Fix Version/s: 10.3, 10.4
    • Component/s: Data types
    • Labels:
      None

      Description

      I run this script:

      CREATE OR REPLACE TABLE t1 (a TIME, b TIME);
      INSERT INTO t1 VALUES ('00:00:20','00:00:00');
      SELECT a BETWEEN b AND '10:00:00' FROM t1;
      

      +----------------------------+
      | a BETWEEN b AND '10:00:00' |
      +----------------------------+
      |                          1 |
      +----------------------------+
      

      It correctly returns 1, because comparison is done as TIME, and '00:00:20' is really between '00:00:00' AND '10:00:00'.

      Now I change the data type for the column a from TIME to INT. Nothing should change: INT, TIME and STRING arguments should be compared as TIME. So BETWEEN should still return 1.

      ALTER TABLE t1 MODIFY a INT;
      SELECT a BETWEEN b AND '10:00:00' FROM t1;
      

      +----------------------------+
      | a BETWEEN b AND '10:00:00' |
      +----------------------------+
      |                          1 |
      +----------------------------+
      

      Works fine so far.

      Now I change the data type for a from INT to BIGINT. Nothing should change. It should still return 1. However, something different happens:

      ALTER TABLE t1 MODIFY a BIGINT;
      SELECT a BETWEEN b AND '10:00:00' FROM t1;
      SHOW WARNINGS;
      

      +----------------------------+
      | a BETWEEN b AND '10:00:00' |
      +----------------------------+
      |                          0 |
      +----------------------------+
      

      +---------+------+----------------------------------------------+
      | Level   | Code | Message                                      |
      +---------+------+----------------------------------------------+
      | Warning | 1292 | Truncated incorrect DOUBLE value: '10:00:00' |
      +---------+------+----------------------------------------------+
      

      Notice, the result changed to 0, and now it returns a warning.
      The expected behavior would be to return 1 without warnings.

      Now if I wrap a into a function, it returns 1 again:

      SELECT COALESCE(a) BETWEEN b AND '10:00:00' FROM t1;
      

      +--------------------------------------+
      | COALESCE(a) BETWEEN b AND '10:00:00' |
      +--------------------------------------+
      |                                    1 |
      +--------------------------------------+
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: