SQL Syntax and MySQL 8 Compatibility

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: sql语法与mysql8兼容性

| username: ShawnYan

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

There is a SQL query that runs in MySQL 8 but reports an error in TiDB, involving syntax compatibility issues?

WITH cte1 AS 
(
SELECT 5 AS num, 1 AS id
),
cte2 AS 
(
SELECT '*' AS op
),
cte3 AS
(
SELECT *
FROM cte1, cte2),
cte4 AS
(
SELECT a.*, t.id AS id2, t.num AS num2
FROM cte3 a
LEFT JOIN LATERAL (
SELECT *
FROM cte1 b
WHERE a.id <> b.id) AS t ON 1=1
)
, cte5 AS 
(
SELECT a.*, b.op AS op2
FROM cte4 a, cte2 b
)
, cte6 AS (
SELECT a.*, t.id AS id3, t.num AS num3
FROM cte5 a
LEFT JOIN LATERAL (
SELECT *
FROM cte1 b
WHERE b.id <> a.id AND b.id <> a.id2) AS t ON 1=1
)
, cte7 AS 
(
SELECT a.*, b.op AS op3
FROM cte6 a, cte2 b
)
, cte8 AS (
SELECT a.*, t.id AS id4, t.num AS num4
FROM cte7 a
LEFT JOIN LATERAL (
SELECT *
FROM cte1 b
WHERE b.id <> a.id AND b.id <> a.id2 AND b.id <> a.id3) AS t ON 1=1
)
SELECT 1;

MySQL 8.0.32:

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

TiDB 7.5:

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 17 column 20 near "(
| username: Kongdom | Original post link

LEFT JOIN LATERAL
It seems that this syntax is not supported~ Related issues

| username: hey-hoho | Original post link

From the issue posted by Kong, it looks like the compatibility requirement is already on its way.

| username: zhanggame1 | Original post link

Since MySQL 8.0.14, derived tables support the LATERAL keyword prefix, which allows derived tables to reference other tables in the FROM clause. TiDB does not support this.

| username: ShawnYan | Original post link

After removing the LATERAL subquery, there is actually no t, but it doesn’t report an error in MySQL, while it does in TiDB. Why is that?

MySQL:

mysql> WITH cte1 AS
    -> (
    -> SELECT 5 AS num, 1 AS id
    -> ),
    -> cte2 AS
    -> (
    -> SELECT '*' AS op
    -> ),
    -> cte3 AS
    -> (
    -> SELECT *
    -> FROM cte1, cte2),
    -> cte4 AS
    -> (
    -> SELECT a.*, t.id AS id2, t.num AS num2
    -> FROM cte3 a
    -> )
    -> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

TiDB:

MySQL [s1]> WITH cte1 AS
    -> (
    -> SELECT 5 AS num, 1 AS id
    -> ),
    -> cte2 AS
    -> (
    -> SELECT '*' AS op
    -> ),
    -> cte3 AS
    -> (
    -> SELECT *
    -> FROM cte1, cte2),
    -> cte4 AS
    -> (
    -> SELECT a.*, t.id AS id2, t.num AS num2
    -> FROM cte3 a
    -> )
    -> SELECT 1;
ERROR 1054 (42S22): Unknown column 't.id' in 'field list'
| username: 小龙虾爱大龙虾 | Original post link

Yes, there’s no t table, so why doesn’t MySQL report an error? Check the result of cte4 in your MySQL.

| username: Kongdom | Original post link

It should be that MySQL did some preprocessing. SELECT 1 does not report an error, but SELECT * FROM cte4 reports the same error.

WITH cte1 AS (SELECT 5 AS num, 1 AS id)
,cte2 AS (SELECT '*' AS op)
,cte3 AS (SELECT * FROM cte1, cte2)
,cte4 AS (SELECT a.*, t.id AS id2, t.num AS num2 FROM cte3 a)
SELECT * FROM cte4;

SQL Error [1054] [42S22]: Unknown column 't.id' in 'field list'
| username: 随缘天空 | Original post link

What is the version of your TiDB cluster?

| username: Kongdom | Original post link

Currently, all posts will have this version tag. v7.5.0
image

| username: ShawnYan | Original post link

The error is logical, this time it should just be a MySQL issue.

| username: Kongdom | Original post link

:yum: Indeed, there should be an error here. Overall, this handling mechanism in MySQL does more harm than good.

| username: 江湖故人 | Original post link

Have you checked under MySQL with show tables to confirm that there is no table named t?

| username: ShawnYan | Original post link

Yes, a bug has been reported.

| username: ShawnYan | Original post link

Confirmed, there isn’t any.

| username: 江湖故人 | Original post link

It’s possible that after the MySQL optimizer rewrites, it ignores all those previous queries.

| username: 随缘天空 | Original post link

Sorry, I didn’t see it clearly. Isn’t this version compatible with MySQL 8?

| username: ShawnYan | Original post link

It is compatible, but some features added after MySQL 8 are also being gradually implemented. However, it is not a complete copy, such as foreign keys and certain sql_modes.

| username: dba远航 | Original post link

The LEFT JOIN LATERAL syntax is not supported.

| username: 随缘天空 | Original post link

Yes, aiming to be like the experts.

| username: ShawnYan | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.