What part of my query is invalid with MySQL 5.7?-CodePudding

I have created the following query:

WITH 
cte1 AS(SELECT Sender trader, TokenAddress, TokenId, SUM(Price) price FROM OpenSeaTrade GROUP BY 1, 2, 3), 
cte2 AS(SELECT Receiver trader, TokenAddress, TokenId, SUM(Price) price FROM OpenSeaTrade GROUP BY 1, 2, 3) SELECT trader, 
cte2.price - cte1.price profit
FROM cte1
JOIN cte2 USING(trader, TokenAddress, TokenId) 
WHERE cte2.price - cte1.price > 1000000000000000000
ORDER BY profit
LIMIT 5000

It works during development but after testing it on the production server (which is uses MySQL 5.7) I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cte1 AS(SELECT Sender trader, TokenAddress, TokenId, SUM(Price) Price ' at line 2

I have a hard time figuring out the cause, any help would be greatly appreciated.

CodePudding user response：

This way it will run on MySql 5.7:

SELECT trader, cte2.price - cte1.price profit
FROM (SELECT Sender trader, TokenAddress, TokenId, SUM(Price) price 
      FROM OpenSeaTrade 
      GROUP BY 1, 2, 3) AS cte1
JOIN (SELECT Receiver trader, TokenAddress, TokenId, SUM(Price) price 
      FROM OpenSeaTrade 
      GROUP BY 1, 2, 3) AS cte2 USING(trader, TokenAddress, TokenId) 
WHERE cte2.price - cte1.price > 1000000000000000000
ORDER BY profit
LIMIT 5000;