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

Wrong result: "unsigned_col = '-1'" returns the row with value 0 when the column is indexed

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 12.3.3, 13.0.2, 13.1.1
    • N/A
    • Optimizer
    • None
    • Platform: Ubuntu 24.04, x86_64
    • Not for Release Notes
    • Comparing an indexed DOUBLE UNSIGNED column with a negative number given as a string, such as c = '-1', could return the rows whose value is 0. The same comparison with a numeric constant, or without the index, returned no rows.

    Description

      Comparing an indexed DOUBLE UNSIGNED column with the string constant '-1' returns the row whose value is 0. 0 = '-1' is false. The numeric constant -1 and the same query with IGNORE INDEX both return the correct empty set.

      How to reproduce

      CREATE TABLE t(c DOUBLE UNSIGNED, KEY(c));
      INSERT INTO t VALUES (0), (5);
       
      SELECT 0 = '-1';
      -- 0
       
      SELECT c FROM t IGNORE INDEX (c) WHERE c = '-1';
      -- Empty set   correct
       
      SELECT c FROM t WHERE c = -1;
      -- Empty set   correct
       
      SELECT c FROM t WHERE c = '-1';
      -- expected: Empty set
      -- actual:   0
      

      Notes

      INT UNSIGNED PRIMARY KEY, REAL UNSIGNED PRIMARY KEY and DECIMAL UNSIGNED with a secondary key did not show the problem in my tests; DOUBLE UNSIGNED with a secondary key does.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Ann C Ann C
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.