CODEWITHSUNDEEP
mysqlunionlimitmariadb
level20peon
level20peon

Reputation: 37

Strange behaviour of MariaDB when using LIMIT in combination with UNION

SELECT `foo` 
FROM `table`
WHERE `bar` = 'def'
LIMIT 1500;

... returns 1500 rows for the condition (def).

(SELECT `foo` 
FROM `table`
WHERE `bar` = 'abc'
LIMIT 1000)
UNION
(SELECT `foo` 
FROM `table`
WHERE `bar` = 'def'
LIMIT 1500)

... returns 2498 rows, 1498 for the second condition (def).

So as you can see by the output of the first query, there definitely are enough records for condition "def" to return 1500 rows. Nevertheless, there are too few records returned for that condition when used in combination with UNION.

Does anyone have a pointer for me to track down this behaviour?

Upvotes: 1

Views: 265

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

The number of rows could be reduced because UNION select only distinct result ..but you can use UNION ALL for get all the result form both select included also rows witn same values 

(SELECT `foo` 
 FROM `table`
 WHERE `bar` = 'abc'
 LIMIT 1000)
UNION ALL 
(SELECT `foo` 
 FROM `table`
 WHERE `bar` = 'def'
 LIMIT 1500)

Upvotes: 1

Related Questions