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

Inconsistent result: "a.x <= b.y" is true in EXISTS but the equivalent JOIN returns no row (REAL vs BIGINT UNSIGNED, Range checked for each record)

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3, 13.0.2, 13.1.1
    • 10.11, 11.4, 11.8, 12.3, 13.1
    • Optimizer
    • Platform: Ubuntu 24.04, x86_64
    • Hide
      A join that compared a DOUBLE column with a BIGINT UNSIGNED key column could return no rows for values near 2^63 when the plan used "Range checked for each record", although the same comparison was true in the select list and in an EXISTS subquery.
      Show
      A join that compared a DOUBLE column with a BIGINT UNSIGNED key column could return no rows for values near 2^63 when the plan used "Range checked for each record", although the same comparison was true in the select list and in an EXISTS subquery.

    Description

      The predicate a.x <= b.y with a.x REAL = 9223372036854775807 (stored as 9.223372036854776e18) and b.y BIGINT UNSIGNED = 9223372036854775807 evaluates to 1 as an expression and in EXISTS, but the JOIN using the same predicate returns no row. The JOIN plan reads b with "Range checked for each record".

      How to reproduce

      CREATE TABLE a(x REAL UNIQUE NOT NULL);
      CREATE TABLE b(y BIGINT UNSIGNED PRIMARY KEY);
      INSERT INTO a VALUES (9223372036854775807);
      INSERT INTO b VALUES (0), (9223372036854775807);
       
      SELECT a.x, b.y, a.x <= b.y FROM a, b;
      -- 9.223372036854776e18  0                    0
      -- 9.223372036854776e18  9223372036854775807  1
       
      SELECT b.y FROM b WHERE EXISTS (SELECT 1 FROM a WHERE a.x <= b.y);
      -- 9223372036854775807
       
      SELECT b.y FROM b JOIN a ON a.x <= b.y;
      -- expected: 9223372036854775807   (same predicate)
      -- actual:   Empty set
       
      EXPLAIN SELECT b.y FROM b JOIN a ON a.x <= b.y;
      -- a: ALL
      -- b: ALL  possible_keys=PRIMARY  Extra: Range checked for each record (index map: 0x1)
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              Ann C Ann C
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.