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

Different result for logically equivalent queries using STRAIGHT_JOIN and comma join

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.11, 12.2.1
    • N/A
    • Optimizer, Storage Engine - InnoDB
    • None
    • Ubuntu 24.04 LTS x86_64 docker image mariadb:12.2-rc
    • Not for Release Notes

    Description

      Run the following statements. The first query returns one row, while the second query returns an empty set.
      The two queries are logically equivalent but produce different result sets depending on the join syntax.

      CREATE TABLE t0 (a INT);
      		 
      CREATE TABLE t1 (b REAL ZEROFILL, PRIMARY KEY (b));
      		 
       
      		 
      INSERT INTO t0 VALUES (1);
      		 
      INSERT INTO t1 VALUES (0);
      		 
       
      		 
      SELECT t0.a, t1.b FROM t0 STRAIGHT_JOIN t1 WHERE ('-1' = (t1.b));
      		 
      -- returns 1 row: (1, 0000000000000000000000) 
       
      		 
      SELECT t0.a, t1.b FROM t0, t1 WHERE ('-1' = (t1.b));
      		 
      -- returns an empty set

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36249 Equivalent straight-join queries return different results

          • Major - Major loss of function.
          • Confirmed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-39200 Equivalent STRAIGHT_JOIN with swapped join order returns different row counts

          • Major - Major loss of function.
          • Closed

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              asdWang Weipeng Wang
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.