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

UNIQUE constraint causes a query with string comparison to omit a row in the result set

    XMLWordPrintable

    Details

      Description

      Consider the following test case:

      CREATE TABLE t0(c0 INT UNIQUE);
      INSERT INTO t0 VALUES (NULL), (NULL), (NULL), (NULL), (1), (0);
      SELECT * FROM t0 WHERE c0 < '\n2'; -- expected rows with c0=0 and c0=1, actual: only row with c0=0 is fetched
      

      The query only fetches a single row. This is unexpected, because the expression in the WHERE clause evaluates to TRUE for two records:

      SELECT c0 < '\n2' FROM t0; -- evaluates to TRUE for two rows
      

      When removing the UNIQUE constraint from the column, or inserting fewer NULL values, the query works as expected.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: