Y
Y
yurasek2015-09-10 15:52:44
MySQL
yurasek, 2015-09-10 15:52:44

Why does a query not work correctly when using UNION in a stored procedure in MySQL?

If everything works correctly in a stored procedure with such a query (one number and NULL are returned):

begin
 select min(time) t from params where counter_id = 11858 and time >= 3629232000000 union 
 select max(time) t from params where counter_id = 11858 and time < 3629232000000;
end

Result:
  • 3630948355222
  • (NULL)

But with an additional select, the code in the stored procedure returns the result of only the second select (after UNION):
begin
 select * from (
  select max(time) t from params where counter_id = 11858 and time < 3629232000000 union 
  select min(time) t from params where counter_id = 11858 and time >= 3629232000000) a;
end

Result:
  • (NULL)

Swapping selects between UNIONs in the last code only changes the single result returned.
I can't understand why this is happening?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
D
Dmitry Kovalsky, 2015-09-10
@dmitryKovalskiy

UPD:

--Был мусор... 

IF EXISTS(select MAX(time) FROM params WHERE...)
BEGIN 
--Выборка
END
ELSE
BEGIN 
SELECT MIN(time) FROM params WHERE...
END

Solution in the forehead.

Y
yurasek, 2015-09-10
@yurasek

Here is an example of Explain for a query without a problem:
Here is an example of Explain for a query with a problem:
Here is a strange query that returns the correct (desired) result:
Here is a query that works correctly, returning 1 and NULL:
By experience, we managed to find out the following: the problem with union is repeated only then when the columns counter_id and time are indexed with order counter_id > time and counter_id < time.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question