exexe exexe - 1 month ago 8
SQL Question

Count in query ruin expected result

Database structure



Database structure

Data



data

Without count in query:



SELECT id, a FROM test ORDER BY a ASC LIMIT 1


Good result: without count

With count in query:



SELECT id, a, COUNT(*) FROM test ORDER BY a ASC LIMIT 1


Bad result: with count

Why is this happening? What is alternative?

Answer

Try

SELECT id, a, 
(SELECT COUNT(*) FROM test) as cnt 
FROM test
ORDER BY a ASC LIMIT 1 

It should give the expected results. Even though I'm pretty sure the optimizer will execute subquery only once, it always makes sense to check execution plan with EXPLAIN .