Execution order of an non-typical My-SQL Query

user_8257914 0
Viewed 39

Assuming we've got table with following schema:

create table Test(id integer, title varchar(100));
insert into Test(id, title) values(1, "Hello");
insert into Test(id, title) values(20, "Hi");

Non-typical query in that case would be :

select * from Test where id= 20 < 2 AND SLEEP(1000000000) ;
-- sleep is being executed
select * from Test where id= 20 > 2 AND SLEEP(1000000000) ;
-- sleep is not being executed and nothing is going to be display
select * from Test where id = 20  > 0;
-- shows only one record , where id=20 /// "Hi"
select * from Test where id = 20  > -1;
-- shows whole table structure

Prior to this example, my question is what is happening in that specify moment of comparsion. Why it acting different if id=20 > 0 than id = 20 > -1.

execution mysql sql
1 Answers

Here is the query in question:

SELECT *
FROM Test
WHERE id = 20 > -1;

The reason the above returns the entire table is that the WHERE clause actually contains a range comparison, and is being evaluated as this:

SELECT *
FROM Test
WHERE (id = 20) > -1;

The expression (id = 20) will evaluate to either 1, if true, or 0, if false. In either case, both zero and one will always be greater than -1, hence this query will always return all records.

user_18632290
Related