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

Wrong result with cross Join given join order

    XMLWordPrintable

Details

    Description

      DROP TABLE t1;
      DROP TABLE t2;
       
      CREATE TABLE `t1` (
      	`t1_seq` INT NOT NULL, 
      	`c1` VARCHAR(10) NOT NULL ,
      	PRIMARY KEY (`t1_seq`) USING BTREE
      ) ENGINE=INNODB;
       
      CREATE TABLE `t2` (
      	`t2_seq` INT NOT NULL,
      	`t1_seq` INT NOT NULL,
      	`c2` VARCHAR(10) NOT NULL ,
      	PRIMARY KEY (`t2_seq`, `t1_seq`) USING BTREE
      ) ENGINE=INNODB;
       
      INSERT INTO t1 VALUES(1, 'A');
      INSERT INTO t2 VALUES(1, 1, 'T2-1-1');
      INSERT INTO t2 VALUES(2, 1, 'T2-1-2');
      INSERT INTO t2 VALUES(3, 1, 'T2-1-3');
       
      MariaDB [testdb]> SELECT @@version;
      +-----------------+
      | @@version       |
      +-----------------+
      | 10.11.2-MariaDB |
      +-----------------+
      1 ROW IN SET (0.000 sec)
       
      SELECT LPAD(@rownum := @rownum + 1, 8, 0) AS str_num
           , t1.t1_seq
           , t2.t2_seq
           , t1.c1
           , t2.c2
        FROM t1 
             INNER JOIN t2 ON (t1.t1_seq = t2.t1_seq)
             CROSS JOIN ( SELECT @rownum := 0 ) X
      ; 
       
      +------+-------------+------------+--------+---------------+------+---------+------+------+-------------------------------------------------+
      | id   | select_type | TABLE      | TYPE   | possible_keys | KEY  | key_len | ref  | ROWS | Extra                                           |
      +------+-------------+------------+--------+---------------+------+---------+------+------+-------------------------------------------------+
      |    1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL | 1    |                                                 |
      |    1 | PRIMARY     | t1         | ALL    | PRIMARY       | NULL | NULL    | NULL | 1    |                                                 |
      |    1 | PRIMARY     | t2         | ALL    | NULL          | NULL | NULL    | NULL | 3    | USING WHERE; USING JOIN buffer (flat, BNL JOIN) |
      |    2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | NO TABLES used                                  |
      +------+-------------+------------+--------+---------------+------+---------+------+------+-------------------------------------------------+
      4 ROWS IN SET (0.000 sec)
       
      +----------+--------+--------+----+--------+
      | str_num  | t1_seq | t2_seq | c1 | c2     |
      +----------+--------+--------+----+--------+
      | 00000001 |      1 |      1 | A  | T2-1-1 |
      | 00000002 |      1 |      2 | A  | T2-1-2 |
      | 00000003 |      1 |      3 | A  | T2-1-3 |
      +----------+--------+--------+----+--------+
      3 ROWS IN SET (0.002 sec)
      

      STRAIGHT_JOIN:

      SELECT STRAIGHT_JOIN LPAD(@rownum := @rownum + 1, 8, 0) AS str_num
           , t1.t1_seq
           , t2.t2_seq
           , t1.c1
           , t2.c2
        FROM t1 
             INNER JOIN t2 ON (t1.t1_seq = t2.t1_seq)
             CROSS JOIN ( SELECT @rownum := 0 ) X
      ; 
       
      +------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
      | id   | select_type | TABLE      | TYPE | possible_keys | KEY  | key_len | ref  | ROWS | Extra                                           |
      +------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
      |    1 | PRIMARY     | t1         | ALL  | PRIMARY       | NULL | NULL    | NULL | 1    |                                                 |
      |    1 | PRIMARY     | t2         | ALL  | NULL          | NULL | NULL    | NULL | 3    | USING WHERE; USING JOIN buffer (flat, BNL JOIN) |
      |    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 1    | USING JOIN buffer (incremental, BNL JOIN)       |
      |    2 | DERIVED     | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NO TABLES used                                  |
      +------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
      4 ROWS IN SET (0.000 sec)
       
      MariaDB [lololdb]> 
       
      +----------+--------+--------+----+--------+
      | str_num  | t1_seq | t2_seq | c1 | c2     |
      +----------+--------+--------+----+--------+
      | 00000001 |      1 |      1 | A  | T2-1-1 |
      | 00000002 |      1 |      2 | A  | T2-1-2 |
      | 00000003 |      1 |      3 | A  | T2-1-3 |
      | 00000004 |      1 |      1 | A  | T2-1-1 |
      | 00000005 |      1 |      2 | A  | T2-1-2 |
      | 00000006 |      1 |      3 | A  | T2-1-3 |
      +----------+--------+--------+----+--------+
      6 ROWS IN SET (0.001 sec)
       
      MariaDB [testdb]> 
      

      Attachments

        Activity

          People

            Gosselin Dave Gosselin
            sanedl NAMHEE KIM
            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.