MySQL Bugs: #96090: select exists return wrong result while subquery contains limit offset.

Bug #96090	select exists return wrong result while subquery contains limit offset.
Submitted:	4 Jul 2019 8:01	Modified:	9 Jul 2019 18:19
Reporter:	Carol WU	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.26 MySQL Community Server	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	exists offset

Description:
(select * from tt )union all( select * from t limit 1 offset 1);
+------+
| pk   |
+------+
|    2 |
+------+
select exists((select * from tt )union all( select * from t limit 1 offset 1)) b;
+---+
| b |
+---+
| 0 |
+---+

becuase subquery can return 1 row result, query 'select exists' should returned 1.

How to repeat:
create table t (pk int);
insert into t values(1),(2);
create table tt(pk int);
(select * from tt )union all( select * from t limit 1 offset 1);
+------+
| pk   |
+------+
|    2 |
+------+
select exists((select * from tt )union all( select * from t limit 1 offset 1)) b;
+---+
| b |
+---+
| 0 |
+---+

Hi Mrs. Wu,

Thank you for your bug report.

However, this is not a bug, for several reasons.

First of all, this has nothing to do with Query Cache.

Next, you are using an old release of 5.7 and you are supposed to report only bugs on the latest release available, which is 5.7.26.

Most important of all, your syntax of using EXISTS is completely wrong. In the manner that you are using it , it can only return 0 or 1, so the result is correct. Our Reference Manual has the entire chapter dedicated to those types of nested queries.

Hi  Sinisa Milivojevic,

Thanks for your report. 
 In this case,  I think it should reture 1 ,  not 0 。 Becuse there is 1 row return in the subquery. 

Could you help check again?

Hi,

Returning 0 instead of 1, does look like a bug. However, can you try something else.

Can you try using EXISTS the way that it is intended, something like:

SELECT * FROM some_table WHERE EXISTS...........

Let us know whether it works ......

hi, 
Sure.

with belowing show,  there is 1 row from Q1. But the Q2 returns empty, when using Q1 in the select * from t where exists(Q1)  syntax.

create table t (pk int);
insert into t values(1),(2);
create table tt(pk int);
Q0: select * from t;
+------+
| pk   |
+------+
|    1 |
|    2 |
+------+
Q1: (select * from tt )union all( select * from t limit 1 offset 1);
+------+
| pk   |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

Q2: select * from t where exists ((select * from tt )union all( select * from t limit 1 offset 1))
Empty set (0.01 sec)

hi, 
Sure.

with belowing show,  there is 1 row from Q1. But the Q2 returns empty, when using Q1 in the select * from t where exists(Q1)  syntax.

create table t (pk int);
insert into t values(1),(2);
create table tt(pk int);
Q0: select * from t;
+------+
| pk   |
+------+
|    1 |
|    2 |
+------+
Q1: (select * from tt )union all( select * from t limit 1 offset 1);
+------+
| pk   |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

Q2: select * from t where exists ((select * from tt )union all( select * from t limit 1 offset 1))
Empty set (0.01 sec)

Hi,

Thank you for your feedback.

I do notice that you did not do one thing that we asked you. 5.7.17 is an old release. Does 5.7.26 makes the same error ???

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use est
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> (select * from tt )union all( select * from t limit 1 offset 1);
+------+
| pk   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select exists((select * from tt )union all( select * from t limit 1 offset 1)) b;
+---+
| b |
+---+
| 0 |
+---+
1 row in set (0.00 sec)

Hi,

MySQL 5.7 truly does not return any results, but latest MySQL 8.0 works just fine:

+------+
| pk   |
+------+
|    1 |
|    2 |
+------+

I will verify this bug, but only for 5.7. That means that if the patch for fixing this bug is too big, then this bug could be closed without fixing.

Thank you for your contribution.