Bug #94086 The description of "13.2.10.2 JOIN Syntax" chapter is imperfect
Submitted: 28 Jan 2019 2:57 Modified: 20 Mar 2019 15:35
Reporter: JianJun Shi Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:8.0.13 OS:Linux
Assigned to: CPU Architecture:Any

[28 Jan 2019 2:57] JianJun Shi
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;
[20 Mar 2019 15:35] MySQL Verification Team
Hi,

Thank you for your bug report.

However, in the current and latest 8.0 manual in the paragraph 13.2.10.2, we have the following syntax:

joined_table:
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
    .
    .
    .

join_specification:
    ON search_condition
  | USING (join_column_list)

Hence, seems that our 8.0 manual is now quite correct .

You can always find our latest 8.0 manual here:

https://dev.mysql.com/doc/refman/8.0/en/