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

Incorrect result for expression with the <=> operator and IS NULL

Details

    Description

      Hi everyone,

      On the latest trunk version (0308de9), I could observe a bug, as demonstrated by the following test case:

      CREATE TABLE t0(c0 INT);
      INSERT INTO t0 VALUES (1);
      SELECT (c0 > (NULL <=> 0)) IS NULL FROM t0; -- expected: 0, actual: 1
      

      The expression seems to work correctly when it is used in a WHERE condition:

      SELECT * FROM t0 WHERE (c0 > (NULL <=> 0)) IS NULL; -- no row is fetched
      

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            Thanks! Reproducible on MariaDB 5.5-10.4 (also the same way on Mysql 5.6-8.0.16)

            MariaDB [test]> create table t0 (c0 int);
            Query OK, 0 rows affected (0.025 sec)
             
            MariaDB [test]> insert into t0 values (1),(0),(null);
            Query OK, 3 rows affected (0.001 sec)
            Records: 3  Duplicates: 0  Warnings: 0
             
            MariaDB [test]> select (c0 > (null <=> 0)) is null from t0;
            +-----------------------------+
            | (c0 > (null <=> 0)) is null |
            +-----------------------------+
            |                           1 |
            |                           1 |
            |                           1 |
            +-----------------------------+
            3 rows in set (0.000 sec)
             
            MariaDB [test]> select c0, c0 > (null <=> 0), (c0 > (null <=> 0)) is null, (1 > (null <=> 0)) is null  from t0;
            +------+-------------------+-----------------------------+----------------------------+
            | c0   | c0 > (null <=> 0) | (c0 > (null <=> 0)) is null | (1 > (null <=> 0)) is null |
            +------+-------------------+-----------------------------+----------------------------+
            |    1 |                 1 |                           1 |                          0 |
            |    0 |                 0 |                           1 |                          0 |
            | NULL |              NULL |                           1 |                          0 |
            +------+-------------------+-----------------------------+----------------------------+
            

            alice Alice Sherepa added a comment - Thanks! Reproducible on MariaDB 5.5-10.4 (also the same way on Mysql 5.6-8.0.16) MariaDB [test]> create table t0 (c0 int); Query OK, 0 rows affected (0.025 sec)   MariaDB [test]> insert into t0 values (1),(0),(null); Query OK, 3 rows affected (0.001 sec) Records: 3 Duplicates: 0 Warnings: 0   MariaDB [test]> select (c0 > (null <=> 0)) is null from t0; +-----------------------------+ | (c0 > (null <=> 0)) is null | +-----------------------------+ | 1 | | 1 | | 1 | +-----------------------------+ 3 rows in set (0.000 sec)   MariaDB [test]> select c0, c0 > (null <=> 0), (c0 > (null <=> 0)) is null, (1 > (null <=> 0)) is null from t0; +------+-------------------+-----------------------------+----------------------------+ | c0 | c0 > (null <=> 0) | (c0 > (null <=> 0)) is null | (1 > (null <=> 0)) is null | +------+-------------------+-----------------------------+----------------------------+ | 1 | 1 | 1 | 0 | | 0 | 0 | 1 | 0 | | NULL | NULL | 1 | 0 | +------+-------------------+-----------------------------+----------------------------+
            mrigger Manuel Rigger added a comment -

            Thanks a lot for verifying the bug!

            mrigger Manuel Rigger added a comment - Thanks a lot for verifying the bug!
            bar Alexander Barkov added a comment - - edited Sanja, please review a patch: https://github.com/MariaDB/server/commit/54d3a80028873405097f8cd259de21448d244c35

            Patch is OK to push, but remove additional # around version marker in tests (it just works better on merge). I.e. instead of:

            --echo #
            --echo # End of XX.YY tests
            --echo #
            

            just do

             

            --echo # End of XX.YY tests

            {echo}
            sanja Oleksandr Byelkin added a comment - Patch is OK to push, but remove additional # around version marker in tests (it just works better on merge). I.e. instead of: --echo # --echo # End of XX.YY tests --echo # just do   --echo # End of XX.YY tests {echo}

            People

              bar Alexander Barkov
              mrigger Manuel Rigger
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.