Description:
"13.2.10.2 JOIN Syntax" chapter in the "MySQL 8.0 Reference Manual" was describe as following:
...
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON conditional_expr
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
join_condition:
ON conditional_expr
| USING (column_list)
...
From my point of view,it mains if the function of "STRAIGHT_JOIN" has join condition, it can not combine with "USING (column_list)" but "ON conditional_expr".
Actually, 8.0.13 version go against this rule. It is in contrast to 5.7 version, the latter accord with this rule.
How to repeat:
-- prepare for data
use test;
CREATE TABLE IF NOT EXISTS tbtest_part_key (
id INT,
tinyint_field TINYINT,
smallint_field SMALLINT,
bigint_field BIGINT,
float_field FLOAT,
datetime_field DATETIME,
timestamp_field TIMESTAMP,
char_field CHAR(8),
varchar_field VARCHAR(10),
text_field TEXT(20),
PRIMARY KEY (id)
)ENGINE=Innodb
PARTITION BY KEY(id) PARTITIONS 4;
CREATE TABLE IF NOT EXISTS tbtest_normal (
id INT,
tinyint_field TINYINT,
smallint_field SMALLINT,
bigint_field BIGINT,
float_field FLOAT,
datetime_field DATETIME,
timestamp_field TIMESTAMP,
char_field CHAR(8),
varchar_field VARCHAR(10),
text_field TEXT(20),
PRIMARY KEY (id)
)ENGINE=Innodb ;
DELIMITER //
DROP PROCEDURE IF EXISTS load_data_multilist;
CREATE PROCEDURE load_data_multilist( id_count INT)
BEGIN
SET @id = 1;
SET @tinyint_f = 1;
SET @smallint_f = 1;
SET @bigint_f = 1;
SET @float_f = 1;
SET @lsql = '';
WHILE @id <= id_count DO
SET @datetime_f = '2018-08-08';
SET @timestamp_f = '2018-08-08';
SET @char_f = @id ;
SET @varchar_f = concat(repeat('a',10-length(@id)),@id);
SET @text_f = concat(repeat('a',20-length(@id)),@id);
SET @lsql = CONCAT('(',@id,',', @tinyint_f,',', @smallint_f,',', @bigint_f,',', @float_f,',\'', @datetime_f,'\',\'', @timestamp_f,'\',\'', @char_f,'\',\'', @varchar_f,'\',\'', @text_f,'\'),',@lsql);
IF @id % 20000 = 0 THEN
SET @lsql=left(@lsql,CHAR_LENGTH(@lsql)-1);
SET @lsql = CONCAT('INSERT INTO tbtest_normal VALUES',@lsql,';');
PREPARE st_insert FROM @lsql;
EXECUTE st_insert ;
commit;
SET @lsql = '';
DEALLOCATE PREPARE st_insert;
END IF;
SET @id = @id + 1;
SET @float_f = @float_f + 1;
SET @bigint_f = @bigint_f + 1;
END WHILE;
INSERT INTO tbtest_part_key SELECT * from tbtest_normal;
COMMIT;
END //
DELIMITER ;
CREATE OR REPLACE VIEW v_tbtest_key as SELECT * FROM tbtest_part_key ;
call test.load_data_multilist(40000);
-- select_join
use test;
SELECT a.id,b.bigint_field,c.text_field ,(( (null IS not UNKNOWN) not BETWEEN (500 DIV 456) AND (1*10) ) IS TRUE) as col1 ,((CEIL(1*10.00001)) - (RAND(MOD((select tinyint_field from test.tbtest_part_2 where id=1),2)))) as col2 ,(SPACE(REPEAT('asdf',2))) as col3 ,(('2019-09-01 18:04:59') + INTERVAL (1|1) SECOND ) as col4 FROM test.tbtest_part_key a straight_join test.v_tbtest_key b USING (id) left join test.tbtest_normal c ON (b.id=c.id) WHERE (b.id=1302 or b.id=1402 or b.id=1502 or b.id=1602 or b.id=1702 or b.id=1802 or b.id=1902 or b.id=2002) order by a.id,b.bigint_field,c.text_field;
Description: "13.2.10.2 JOIN Syntax" chapter in the "MySQL 8.0 Reference Manual" was describe as following: ... join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition] | table_reference STRAIGHT_JOIN table_factor | table_reference STRAIGHT_JOIN table_factor ON conditional_expr | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor join_condition: ON conditional_expr | USING (column_list) ... From my point of view,it mains if the function of "STRAIGHT_JOIN" has join condition, it can not combine with "USING (column_list)" but "ON conditional_expr". Actually, 8.0.13 version go against this rule. It is in contrast to 5.7 version, the latter accord with this rule. How to repeat: -- prepare for data use test; CREATE TABLE IF NOT EXISTS tbtest_part_key ( id INT, tinyint_field TINYINT, smallint_field SMALLINT, bigint_field BIGINT, float_field FLOAT, datetime_field DATETIME, timestamp_field TIMESTAMP, char_field CHAR(8), varchar_field VARCHAR(10), text_field TEXT(20), PRIMARY KEY (id) )ENGINE=Innodb PARTITION BY KEY(id) PARTITIONS 4; CREATE TABLE IF NOT EXISTS tbtest_normal ( id INT, tinyint_field TINYINT, smallint_field SMALLINT, bigint_field BIGINT, float_field FLOAT, datetime_field DATETIME, timestamp_field TIMESTAMP, char_field CHAR(8), varchar_field VARCHAR(10), text_field TEXT(20), PRIMARY KEY (id) )ENGINE=Innodb ; DELIMITER // DROP PROCEDURE IF EXISTS load_data_multilist; CREATE PROCEDURE load_data_multilist( id_count INT) BEGIN SET @id = 1; SET @tinyint_f = 1; SET @smallint_f = 1; SET @bigint_f = 1; SET @float_f = 1; SET @lsql = ''; WHILE @id <= id_count DO SET @datetime_f = '2018-08-08'; SET @timestamp_f = '2018-08-08'; SET @char_f = @id ; SET @varchar_f = concat(repeat('a',10-length(@id)),@id); SET @text_f = concat(repeat('a',20-length(@id)),@id); SET @lsql = CONCAT('(',@id,',', @tinyint_f,',', @smallint_f,',', @bigint_f,',', @float_f,',\'', @datetime_f,'\',\'', @timestamp_f,'\',\'', @char_f,'\',\'', @varchar_f,'\',\'', @text_f,'\'),',@lsql); IF @id % 20000 = 0 THEN SET @lsql=left(@lsql,CHAR_LENGTH(@lsql)-1); SET @lsql = CONCAT('INSERT INTO tbtest_normal VALUES',@lsql,';'); PREPARE st_insert FROM @lsql; EXECUTE st_insert ; commit; SET @lsql = ''; DEALLOCATE PREPARE st_insert; END IF; SET @id = @id + 1; SET @float_f = @float_f + 1; SET @bigint_f = @bigint_f + 1; END WHILE; INSERT INTO tbtest_part_key SELECT * from tbtest_normal; COMMIT; END // DELIMITER ; CREATE OR REPLACE VIEW v_tbtest_key as SELECT * FROM tbtest_part_key ; call test.load_data_multilist(40000); -- select_join use test; SELECT a.id,b.bigint_field,c.text_field ,(( (null IS not UNKNOWN) not BETWEEN (500 DIV 456) AND (1*10) ) IS TRUE) as col1 ,((CEIL(1*10.00001)) - (RAND(MOD((select tinyint_field from test.tbtest_part_2 where id=1),2)))) as col2 ,(SPACE(REPEAT('asdf',2))) as col3 ,(('2019-09-01 18:04:59') + INTERVAL (1|1) SECOND ) as col4 FROM test.tbtest_part_key a straight_join test.v_tbtest_key b USING (id) left join test.tbtest_normal c ON (b.id=c.id) WHERE (b.id=1302 or b.id=1402 or b.id=1502 or b.id=1602 or b.id=1702 or b.id=1802 or b.id=1902 or b.id=2002) order by a.id,b.bigint_field,c.text_field;