Query that works with MariaDB but fails with a syntax error on MySQL

Question

This query works on MariaDB, but not MySQL. I get a 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 'integer) HOUR

This is a really dense query. So, even though I've looked through the manual I've had trouble identify which function is actually causing the issue.

SELECT  1 AS one
FROM sched_walks INNER JOIN walks ON walks.id = sched_walks.walk_id
WHERE sched_walks.d_acc = 1
  AND sched_walks.docent_id = 6
  AND (sched_walks.id != 714)
  AND (
    scheduled_for_utc <= '2018-03-17 14:34:31.000000'
      AND
    DATE_ADD(
      scheduled_for_utc,
      INTERVAL CONVERT(REPLACE(COALESCE(sched_duration, walks.duration), ' hours', ''), integer) HOUR
    ) >= '2018-03-17 12:34:31.000000'
  )
LIMIT 1

Gordon Linoff · Accepted Answer · 2018-03-14 13:55:33Z

2

Use cast() instead:

  INTERVAL CAST(REPLACE(COALESCE(sched_duration, walks.duration), ' hours', '') as signed) HOUR

Actually, you can use convert() . . . the key is using signed or unsigned.

Or just use silent conversion:

  INTERVAL COALESCE(sched_duration, walks.duration) + 0 HOUR

edited Mar 14, 2018 at 13:55

answered Mar 14, 2018 at 13:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rebecca Meritz Over a year ago

This fails on MariaDB. Is there anything that works for both?

Gordon Linoff Over a year ago

Both of these should work in both databases. The integer is optional but signed and unsigned should work. (Note: I just fixed a typo in the second answer.)

Collectives™ on Stack Overflow

Query that works with MariaDB but fails with a syntax error on MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related