WITH statement on MySQL syntax error 1064

Question

I am trying to use a WITH statement so that I can use the sum of 2 columns later on but I keep getting hit with an ERROR 1064 (42000): You have an error in your SQL syntax

I fiddled around with different ways of using the statement with no success so I am starting to think that it's perhaps because of my MySQL version (select @@version says its '5.7.38-0ubuntu0.18.04.1').

My command :

WITH timings AS (
SELECT id, timestart, timestart+timeduration AS timeend FROM events WHERE userid = 1000
)
SELECT * FROM timings WHERE timeend > ...

Could someone perhaps enlighten me on my mistaken or perhaps give me an alternative for the handling of a sum of columns. Thank you in advance.

CTE in your query simply decreases the performance, because it forces the server to perform excess work. Use immediate SELECT id, timestart, timestart+timeduration AS timeend FROM events WHERE userid = 1000 AND (timestart+timeduration) > ... or SELECT id, timestart, timestart+timeduration AS timeend FROM events WHERE userid = 1000 HAVING timeend > ... — Akina

Jetto Martínez Jetto Martínez · Accepted Answer · 2022-07-28T13:44:52

Common Table Expressions (aka CTE or Withs) were introduced in Mysql 8. So yes, you either update your DB engine (I'd recommend that) or rewrite your CTE as a subquery:

SELECT 
    * 
FROM (
    SELECT 
        id, 
        timestart, 
        timestart+timeduration AS timeend 
    FROM events 
    WHERE userid = 1000
) timings 
WHERE timeend > ...

WITH statement on MySQL syntax error 1064

1 Answers