I am having unexpected results from MariaDB IF EXISTS… in a stored procedure. I created a minimal example to demonstrate the issue:
PROCEDURE testdb.testSearchResults(IN pParam1 INT, IN pParam2 CHAR(2))
BEGIN
IF EXISTS(SELECT COUNT(*) FROM test WHERE intval = pParam1 AND text = pParam2) THEN
SELECT 'Found' AS result;
ELSE
SELECT 'Not Found' AS result;
END IF;
END
No matter what the contents of the file "test" or the parameters I give to the call, this procedure always returns "Found".
Am I doing something wrong?
Thanks.
Best Answer
SELECT COUNT(*)
always returns at least one record (maybe with zero). SoIF EXISTS
is always true.You may:
Replace
SELECT COUNT(*) ...
withSELECT 1 ...
Replace
IF EXISTS(SELECT ...
withIF 0 < (SELECT ...
I think that 1st variant is more fast. It only checks does at least one record exists, and so it must stop searching after first record found, whereas 2nd query must obtain all matched records, count them, and then compare the result with zero.