You have an error in your SQL syntax near "ON alias4.f2 = alias11.f2"

[ChenPeng2013]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLES if exists t2, t4, t5, t6, t7, t10, t11;
CREATE TABLE t2(f1 VARCHAR(255) DEFAULT NULL, f2 INT(11) DEFAULT NULL,
  KEY (f1), KEY (f2)) charset latin1 ENGINE=MyISAM;

CREATE TABLE t4(f1 INT(11) DEFAULT NULL, f2 INT(11) DEFAULT NULL, KEY (f1))
charset latin1 ENGINE=MyISAM;
CREATE TABLE t5(f1 INT(11) NOT NULL AUTO_INCREMENT, f2 INT(11) DEFAULT NULL, PRIMARY KEY (f1))
charset latin1 ENGINE=InnoDB;
CREATE TABLE t6(f1 INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (f1))
charset latin1 ENGINE=InnoDB;
CREATE TABLE t7 (f1 VARCHAR(255) DEFAULT NULL)
charset latin1 ENGINE=InnoDB;

CREATE TABLE t10(f1 INT(11) NOT NULL AUTO_INCREMENT,f2 INT(11) DEFAULT NULL,f3 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (f1),KEY (f2),KEY (f3)) charset latin1 ENGINE=MyISAM;

CREATE TABLE t11(f1 INT(11) DEFAULT NULL,f2 VARCHAR(10) DEFAULT NULL,
  KEY (f1),KEY (f2)) charset latin1 ENGINE=InnoDB;

SELECT 1
FROM t4 AS alias4
  LEFT JOIN t5 AS alias5 JOIN t6 AS alias6 ON alias5.f2 = alias6.f1
  LEFT JOIN t7 AS alias7 JOIN t2 AS alias8 ON alias7.f1 = alias8.f1
    ON alias5.f1 = alias8.f2 ON alias4.f2 = alias6.f1
  JOIN t10 AS alias10 JOIN t11 AS alias11 ON alias10.f1 = alias11.f1
    ON alias4.f2 = alias11.f2;

2. What did you expect to see? (Required)

mysql 8.0.21

mysql> SELECT 1
    -> FROM t4 AS alias4
    ->   LEFT JOIN t5 AS alias5 JOIN t6 AS alias6 ON alias5.f2 = alias6.f1
    ->   LEFT JOIN t7 AS alias7 JOIN t2 AS alias8 ON alias7.f1 = alias8.f1
    ->     ON alias5.f1 = alias8.f2 ON alias4.f2 = alias6.f1
    ->   JOIN t10 AS alias10 JOIN t11 AS alias11 ON alias10.f1 = alias11.f1
    ->     ON alias4.f2 = alias11.f2;
Empty set (0.00 sec)

3. What did you see instead (Required)

mysql> SELECT 1
    -> FROM t4 AS alias4
    ->   LEFT JOIN t5 AS alias5 JOIN t6 AS alias6 ON alias5.f2 = alias6.f1
    ->   LEFT JOIN t7 AS alias7 JOIN t2 AS alias8 ON alias7.f1 = alias8.f1
    ->     ON alias5.f1 = alias8.f2 ON alias4.f2 = alias6.f1
    ->   JOIN t10 AS alias10 JOIN t11 AS alias11 ON alias10.f1 = alias11.f1
    ->     ON alias4.f2 = alias11.f2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 7 column 7 near "ON alias4.f2 = alias11.f2"

4. What is your TiDB version? (Required)

master 2f067c0 and release-4.0 dced64d

SIG slack channel

#sig-exec

Score

300

Mentor

• @lzmhhh123

[ti-challenge-bot]

This issue does not belong to any SIG.

Details

Tip : Currently, we only support sig labels starting with sig/, maybe you should add this type of label.

Warning: None

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

[ti-challenge-bot]

This issue does not belong to any SIG.

Details

Tip : Currently, we only support sig labels starting with sig/, maybe you should add this type of label.

Warning: None