MySQL - using UNION with LIMIT

Question

I noticed that

(SELECT title, relavency, 'search1' as source FROM search1
ORDER BY relavency DESC
LIMIT 10)
UNION 
(SELECT title, relavency, 'search2' as source FROM search2
ORDER BY relavency DESC
LIMIT 10)
ORDER BY relavency DESC 
LIMIT 10

the last LIMIT 10 does not work. What for?

the mistake was

"Error code 1064: ... for use next to" LIMIT 1000 "

How come mysql workbench detects LIMIT 10 as LIMIT 1000, but if it's 1000 it still works?

Answer 1

Your request can be rewritten using the aliases of the nested subqueries. This should work for you:

SELECT u.* FROM (
    (SELECT s1.title, s1.relavency, 'search1' as source FROM search1 AS s1
    ORDER BY s1.relavency DESC
    LIMIT 10)
        UNION 
    (SELECT s2.title, s2.relavency, 'search2' as source FROM search2 AS s2
    ORDER BY s2.relavency DESC
    LIMIT 10)
) AS u ORDER BY u.relavency DESC 
LIMIT 10

FYI: "", , .