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

LP:1007981 - Wrong result for a hash index look-up if the index is unique and the key is NULL

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      The MySQL reference (http://dev.mysql.com/doc/refman/5.0/en/create-index.html) says:
      A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

      According to the above a look-up into a unique index over a nullable column may return several records if the key value is null.
      However iMariaDB 5.1/5.2/5.3/5.5 always returns not more than one row when using a null key to perform a look-up into an unique hash index. This can be seen from the following:

      MariaDB [test]> CREATE TABLE t1
      -> (
      -> pk INT PRIMARY KEY,
      -> val INT,
      -> UNIQUE KEY USING HASH(val)
      -> ) ENGINE=MEMORY;
      Query OK, 0 rows affected (0.01 sec)

      MariaDB [test]> INSERT INTO t1 VALUES (1, NULL);
      Query OK, 1 row affected (0.00 sec)

      MariaDB [test]> INSERT INTO t1 VALUES (2, NULL);
      Query OK, 1 row affected (0.00 sec)

      MariaDB [test]> INSERT INTO t1 VALUES (3, 1);
      Query OK, 1 row affected (0.00 sec)

      MariaDB [test]> INSERT INTO t1 VALUES (4, NULL);
      Query OK, 1 row affected (0.00 sec)

      MariaDB [test]> EXPLAIN SELECT * FROM t1 WHERE val IS NULL;
      --------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      --------------------------------------------------------------------+

      1 SIMPLE t1 ref val val 5 const 1 Using where

      --------------------------------------------------------------------+
      1 row in set (0.01 sec)

      MariaDB [test]> SELECT * FROM t1 WHERE val IS NULL;
      --------+

      pk val

      --------+

      4 NULL

      --------+
      1 row in set (0.00 sec)

      MariaDB [test]> SELECT * FROM t1;
      --------+

      pk val

      --------+

      1 NULL
      2 NULL
      3 1
      4 NULL

      --------+
      4 rows in set (0.00 sec)

      (see also http://bugs.mysql.com/bug.php?id=44771)

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            igor Igor Babaev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.