Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.11.2, 10.3.38, 10.4.28, 10.6.12
-
CentOS 7.9, MariaDB 10.3 ~ 10.11
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]>
|