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;