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

GREATEST() and LEAST() malfunction for NULL

Details

    Description

      Hi everyone,

      On the latest trunk version (0308de9), GREATEST() and LEAST() seem to malfunction for NULL values:

      CREATE TABLE t0(c0 INT);
      		 
      INSERT INTO t0 VALUES (1);
      		 
      SELECT * FROM t0 WHERE GREATEST(c0, NULL); -- unexpected: row is fetched

      I would not expect the row to be fetched, because the WHERE condition should evaluate to NULL for that row:

      SELECT GREATEST(c0, NULL) FROM t0; -- NULL

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21029 Incorrect result for expression with the <=> operator and IS NULL

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment - - edited

            Thank you for the report!
            Reproducible on MariaDB 5.5-10.4 (and Mysql 5.6.40,5.7.26, but works correct in 8.0.16)

            MariaDB [test]> SELECT 5 from dual WHERE LEAST(1, NULL); 
            +---+
            		 
            | 5 |
            		 
            +---+
            		 
            | 5 |
            		 
            +---+
            		 
            1 row in set (0.000 sec)
            		 
             
            		 
            MariaDB [test]> SELECT LEAST(1, NULL); 
            +----------------+
            		 
            | LEAST(1, NULL) |
            		 
            +----------------+
            		 
            |           NULL |
            		 
            +----------------+
            		 
            1 row in set (0.000 sec)
            		 
             
            		 
            MariaDB [test]> create table t0 (c0 int);
            		 
            Query OK, 0 rows affected (0.023 sec)
            		 
             
            		 
            MariaDB [test]> insert into t0 values (1),(0),(null);
            		 
            Query OK, 3 rows affected (0.000 sec)
            		 
            Records: 3  Duplicates: 0  Warnings: 0
            		 
             
            		 
            MariaDB [test]> SELECT * FROM t0 WHERE GREATEST(c0, NULL); 
            +------+
            		 
            | c0   |
            		 
            +------+
            		 
            |    1 |
            		 
            +------+
            		 
            1 row in set (0.000 sec)

            alice Alice Sherepa added a comment - - edited Thank you for the report! Reproducible on MariaDB 5.5-10.4 (and Mysql 5.6.40,5.7.26, but works correct in 8.0.16) MariaDB [test]> SELECT 5 from dual WHERE LEAST(1, NULL); +---+ | 5 | +---+ | 5 | +---+ 1 row in set (0.000 sec)   MariaDB [test]> SELECT LEAST(1, NULL); +----------------+ | LEAST(1, NULL) | +----------------+ | NULL | +----------------+ 1 row in set (0.000 sec)   MariaDB [test]> create table t0 (c0 int); Query OK, 0 rows affected (0.023 sec)   MariaDB [test]> insert into t0 values (1),(0),(null); Query OK, 3 rows affected (0.000 sec) Records: 3 Duplicates: 0 Warnings: 0   MariaDB [test]> SELECT * FROM t0 WHERE GREATEST(c0, NULL); +------+ | c0 | +------+ | 1 | +------+ 1 row in set (0.000 sec)
            bar Alexander Barkov added a comment -

            The same problem is repeatable with the DOUBLE data type:

            CREATE OR REPLACE TABLE t0(c0 DOUBLE);
            		 
            INSERT INTO t0 VALUES (1);
            		 
            SELECT * FROM t0 WHERE GREATEST(c0, NULL); 


            +------+
            		 
            | c0   |
            		 
            +------+
            		 
            |    1 |
            		 
            +------+

            bar Alexander Barkov added a comment - The same problem is repeatable with the DOUBLE data type: CREATE OR REPLACE TABLE t0(c0 DOUBLE ); INSERT INTO t0 VALUES (1); SELECT * FROM t0 WHERE GREATEST(c0, NULL ); +------+ | c0 | +------+ | 1 | +------+

            People

              bar Alexander Barkov
              mrigger Manuel Rigger
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.