1064 error in mariadb 10.3.15 but not on mysql ver 5.5.62

Question

I was hoping someone might be able to point out which part of the mysql query might be causing the syntax error.

This query was working on our previous server running mysql 5.5.62 but is now showing an error on mariadb version 10.3.15

SELECT p.product_id
    ,p.product_name
    ,p.product_thumb_image
FROM jos_vm_product AS p
WHERE p.product_sku = ' w '
    AND p.product_publish = 'Y'
LIMIT 0,10

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_name LIKE ' w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE (p.product_name LIKE '% w %')
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_sku LIKE 'w%'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_desc LIKE '% w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_s_desc LIKE '% w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE ((p.product_name LIKE '%w%'))
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

I would hope for the query to run successfully but it outputs an error 1064 - you have an error in your SQL syntax...

`LIMIT` without `ORDER BY` makes little sense. – The Impaler Jul 02 '19 at 00:04 — The Impaler, Jul 02 '19 at 00:04

score 0 · Answer 1 · answered Jul 02 '19 at 00:04

You can use LIMIT only in the last subquery of a UNION. Maybe MySQL [wrongfully] allows you to run this query, but MariaDB rejects it.

Solution? Just enclose the first query in parenthesis, as in:

( -- enclosing parenthesis
SELECT p.product_id
    ,p.product_name
    ,p.product_thumb_image
FROM jos_vm_product AS p
WHERE p.product_sku = ' w '
    AND p.product_publish = 'Y'
LIMIT 0,10
) -- enclosing parenthesis

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_name LIKE ' w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE (p.product_name LIKE '% w %')
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_sku LIKE 'w%'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_desc LIKE '% w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_s_desc LIKE '% w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE ((p.product_name LIKE '%w%'))
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

Note: Please consider that LIMIT-ing rows without ORDER-ing them may yield random filtering. Is that what you want?

1064 error in mariadb 10.3.15 but not on mysql ver 5.5.62

1 Answers1