MySQL: Query weird behaviour - need new row before FROM to succeed

Question

I am stuck with really weird situation. I am generating code from my php editor, and as you know I am getting code non-formatted all in one line.

Here's my code:

SELECT ua.id            AS ua_id, 
       ua.activity_time AS ua_activity_time, 
       ua.user_id       AS ua_user_id, 
       ua.charter_id    AS ua_charter_id, 
       'table'          AS ua_table_name, 
       Concat(Round(Timestampdiff(minute, ua.activity_time, Now()) / 30, 0), '-' 
       , ua.charter_id)   AS delimiter 
FROM   user_activity_charter_update AS ua 
WHERE  ua.activity_time BETWEEN '2018-07-10 00:00:00' AND '2018-08-10 00:00:00'

It reports syntax 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 'user_activity_charter_update AS ua WHERE ua.activity_time BETWEEN '2018-07-10 00' at line 1

But what is more weird when I hit space before FROM and put it in new row, query works fine.

score 1 · Answer 1 · answered Jul 23 '18 at 14:39

1

In first picture you have selected just part of a query, so when you try tu execute the query, you actually trying to execute just selected part. That's the reason of the error.

answered Jul 23 '18 at 14:39

Michał Turczyn

32,028
14
47
69

1

You sir have eagle eyes. – Juan Carlos Oropeza Jul 23 '18 at 14:40
Sorry but actually not, It selects part where error occurs. I have selected whole query and executed, but still it fails. – Lululu Jul 23 '18 at 14:42
@Lululu Try putting `WHERE` clause in one line and give it a run. – Michał Turczyn Jul 23 '18 at 14:45
@MichałTurczyn I have just tried it but still nothing, it only works with `FROM` in new line. – Lululu Jul 23 '18 at 14:47

score 1 · Accepted Answer · answered Jul 24 '18 at 11:41

The problem is in keyword delimter in SELECT statement, because mysql interprets delimiter in your query as command. You can see here more about delimiters. So, if you change keyword delimiter to something else, that is not keyword, it will work fine. :)

Working example:

SELECT ua.id            AS ua_id, 
       ua.activity_time AS ua_activity_time, 
       ua.user_id       AS ua_user_id, 
       ua.charter_id    AS ua_charter_id, 
       'table'          AS ua_table_name, 
       Concat(Round(Timestampdiff(minute, ua.activity_time, Now()) / 30, 0), '-' , ua.charter_id)   AS test_delimiter 
FROM   user_activity_charter_update AS ua 
WHERE  ua.activity_time BETWEEN '2018-07-10 00:00:00' AND '2018-08-10 00:00:00';

Amazing response. It definitely solved my issue. Thanks! – Lululu Jul 24 '18 at 11:54 — Lululu, Jul 24 '18 at 11:54

MySQL: Query weird behaviour - need new row before FROM to succeed

2 Answers2