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

Equivalent straight-join queries return different results

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.7.2, 10.5, 10.6, 10.11, 11.4, 11.8
    • 10.11, 11.4, 11.8
    • Optimizer
    • None
    • docker

    Description

      docker run -it --name mariadb-11.7.2 -p 3307:3306 -e MARIADB_ROOT_PASSWORD=root mariadb:11.7.2 
       
      		 
      CREATE TABLE t0(c0 FLOAT ZEROFILL ) ENGINE = HEAP;
      		 
      CREATE TABLE IF NOT EXISTS t1 LIKE t0;
      		 
      INSERT INTO t0(c0) VALUES(0),(-0);
      		 
      CREATE INDEX i1 ON t0(c0);
      		 
      INSERT INTO t1(c0) VALUES('-0');
      		 
       
      		 
      SELECT ALL t0.c0  FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = t1.c0; 
      +--------------+
      		 
      | c0           |
      		 
      +--------------+
      		 
      | 000000000000 |
      		 
      | 000000000000 |
      		 
      +--------------+
      		 
      SELECT ALL t0.c0  FROM t1 STRAIGHT_JOIN t0 ON t0.c0 = t1.c0;
      		 
      Empty set (0.00 sec)

      Attachments

        Activity

          alice Alice Sherepa added a comment -

          Thank you! I repeated as described on 10.5-11.8. Float + Memory engine + "-0"

          CREATE TABLE t0 ( c0 float  , KEY (c0)) engine=memory;
          		 
          INSERT INTO t0 VALUES(0),(-0);
          		 
           
          		 
          CREATE TABLE t1 ( c0 float  ) engine=memory;
          		 
          INSERT INTO t1 VALUES('-0');
          		 
           
          SELECT  t0.c0  FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = t1.c0; 
          SELECT  t0.c0  FROM t1 STRAIGHT_JOIN t0 ON t0.c0 = t1.c0;
          		 
           
          		 
          drop table t0,t1;


          MariaDB [test]> SELECT  t0.c0  FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = t1.c0; 
          +------+
          		 
          | c0   |
          		 
          +------+
          		 
          |    0 |
          		 
          |    0 |
          		 
          +------+
          		 
          2 rows in set (0,001 sec)
          		 
           
          		 
          MariaDB [test]> SELECT  t0.c0  FROM t1 STRAIGHT_JOIN t0 ON t0.c0 = t1.c0;
          		 
          Empty set (0,001 sec)
          		 
           
          		 
          MariaDB [test]> explain extended SELECT  t0.c0  FROM t1 STRAIGHT_JOIN t0 ON t0.c0 = t1.c0;
          		 
          +------+-------------+-------+--------+---------------+------+---------+-------+------+----------+-------+
          		 
          | id   | select_type | table | type   | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
          		 
          +------+-------------+-------+--------+---------------+------+---------+-------+------+----------+-------+
          		 
          |    1 | SIMPLE      | t1    | system | NULL          | NULL | NULL    | NULL  | 1    |   100.00 |       |
          		 
          |    1 | SIMPLE      | t0    | ref    | c0            | c0   | 5       | const | 2    |   100.00 |       |
          		 
          +------+-------------+-------+--------+---------------+------+---------+-------+------+----------+-------+
          		 
          2 rows in set, 1 warning (0,003 sec)
          		 
           
          		 
          Note (Code 1003): select `test`.`t0`.`c0` AS `c0` from `test`.`t0` where `test`.`t0`.`c0` = -0
          		 
          MariaDB [test]> 
          MariaDB [test]> explain extended SELECT  t0.c0  FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = t1.c0; 
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
          		 
          | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
          		 
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
          		 
          |    1 | SIMPLE      | t0    | ALL  | c0            | NULL | NULL    | NULL | 2    |   100.00 |                                                 |
          		 
          |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 | Using where; Using join buffer (flat, BNL join) |
          		 
          +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
          		 
          2 rows in set, 1 warning (0,003 sec)
          		 
           
          		 
          Note (Code 1003): select `test`.`t0`.`c0` AS `c0` from `test`.`t0` straight_join `test`.`t1` where `test`.`t1`.`c0` = `test`.`t0`.`c0`

          alice Alice Sherepa added a comment - Thank you! I repeated as described on 10.5-11.8. Float + Memory engine + "-0" CREATE TABLE t0 ( c0 float , KEY (c0)) engine=memory; INSERT INTO t0 VALUES (0),(-0);   CREATE TABLE t1 ( c0 float ) engine=memory; INSERT INTO t1 VALUES ( '-0' ); SELECT t0.c0 FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = t1.c0; SELECT t0.c0 FROM t1 STRAIGHT_JOIN t0 ON t0.c0 = t1.c0;   drop table t0,t1; MariaDB [test]> SELECT t0.c0 FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = t1.c0; +------+ | c0 | +------+ | 0 | | 0 | +------+ 2 rows in set (0,001 sec)   MariaDB [test]> SELECT t0.c0 FROM t1 STRAIGHT_JOIN t0 ON t0.c0 = t1.c0; Empty set (0,001 sec)   MariaDB [test]> explain extended SELECT t0.c0 FROM t1 STRAIGHT_JOIN t0 ON t0.c0 = t1.c0; +------+-------------+-------+--------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+--------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | | | 1 | SIMPLE | t0 | ref | c0 | c0 | 5 | const | 2 | 100.00 | | +------+-------------+-------+--------+---------------+------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0,003 sec)   Note (Code 1003): select `test`.`t0`.`c0` AS `c0` from `test`.`t0` where `test`.`t0`.`c0` = -0 MariaDB [test]> MariaDB [test]> explain extended SELECT t0.c0 FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = t1.c0; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | 1 | SIMPLE | t0 | ALL | c0 | NULL | NULL | NULL | 2 | 100.00 | | | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ 2 rows in set, 1 warning (0,003 sec)   Note (Code 1003): select `test`.`t0`.`c0` AS `c0` from `test`.`t0` straight_join `test`.`t1` where `test`.`t1`.`c0` = `test`.`t0`.`c0`

          People

            psergei Sergei Petrunia
            jinhui lai jinhui lai
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.