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 "(
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'
应该是mysql做了预处理，select 1不报错，select * from cte4也是报一样的错误。
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 错误 [1054] [42S22]: Unknown column 't.id' in 'field list'
确实，这里应该报错。mysql这种处理机制总体来说是弊大于利的。