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

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

          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
            mrigger Manuel Rigger created issue -
            alice Alice Sherepa made changes -
            Field Original Value New Value
            Affects Version/s 5.5 [ 15800 ]
            Affects Version/s 10.0 [ 16000 ]
            Affects Version/s 10.1 [ 16100 ]
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            alice Alice Sherepa made changes -
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            Assignee Igor Babaev [ igor ]
            igor Igor Babaev (Inactive) made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            igor Igor Babaev (Inactive) made changes -
            Assignee Igor Babaev [ igor ] Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2019-12-16 12:46:36.0 2019-12-16 12:46:36.108
            bar Alexander Barkov made changes -
            Component/s Data types [ 13906 ]
            Fix Version/s 5.5.67 [ 23404 ]
            Fix Version/s 10.1.44 [ 23912 ]
            Fix Version/s 10.2.31 [ 24017 ]
            Fix Version/s 10.3.22 [ 24018 ]
            Fix Version/s 10.4.12 [ 24019 ]
            Fix Version/s 10.5.1 [ 24029 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 101203 ] MariaDB v4 [ 156984 ]

            People

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