Bug in ALL/ANY/SOME/IN operators and NULL value ?

Hi,

I'm wondering whether the following is a bug.

How-to-repeat :

mysql> SELECT 1 as a,1=ALL(SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE
a=0);
+---+------------------------------------------------------- --+
| a | 1=ALL(SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE a=0) |
+---+------------------------------------------------------- --+
| 1 | 1 |
+---+------------------------------------------------------- --+
1 row in set (0.00 sec)

The second column should be NULL ?

mysql> SELECT 1 as a,1=SOME(SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE
a=0);
+---+------------------------------------------------------- ---+
| a | 1=SOME(SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE a=0) |
+---+------------------------------------------------------- ---+
| 1 | 0 |
+---+------------------------------------------------------- ---+
1 row in set (0.00 sec)

Same here.

mysql> SELECT 1 as a,1 IN (SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE
a=0);
+---+------------------------------------------------------- --+
| a | 1 IN (SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE a=0) |
+---+------------------------------------------------------- --+
| 1 | 0 |
+---+------------------------------------------------------- --+
1 row in set (0.00 sec)

Here too ?

Indeed :

mysql> SELECT 1 as a,(SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE a=0);
+---+----------------------------------------------------+
| a | (SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE a=0) |
+---+----------------------------------------------------+
| 1 | NULL |
+---+----------------------------------------------------+
1 row in set (0.00 sec)

And :

mysql> SELECT 1=ALL(SELECT NULL);
+--------------------+
| 1=ALL(SELECT NULL) |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT 1=SOME(SELECT NULL);
+---------------------+
| 1=SOME(SELECT NULL) |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT 1 IN (SELECT NULL);
+--------------------+
| 1 IN (SELECT NULL) |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)

BTW, the <=> operator seems to not be supported with ALL/ANY/SOME ?


mysql> SELECT 1<=>ALL(SELECT NULL);
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'ALL(SELECT NULL)' at line 1

Regards,
Jocelyn


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13167@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in ALL/ANY/SOME/IN operators and NULL value ?

Hi!

On Sat, Nov 30, 2002 at 03:33:47PM +0100, jocelyn fournier wrote:
> Hi,
>
> I'm wondering whether the following is a bug.
>
> How-to-repeat :
>
> mysql> SELECT 1 as a,1=ALL(SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE
> a=0);
> +---+------------------------------------------------------- --+
> | a | 1=ALL(SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE a=0) |
> +---+------------------------------------------------------- --+
> | 1 | 1 |
> +---+------------------------------------------------------- --+
> 1 row in set (0.00 sec)
>
> The second column should be NULL ?

according SQL 99 standard ALL/ANY return TRUE on empty set.

But, your question help us to find other bugs in ALL/ANY implementation and
we will fix it.

> mysql> SELECT 1 as a,1=SOME(SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE
> a=0);
> +---+------------------------------------------------------- ---+
> | a | 1=SOME(SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE a=0) |
> +---+------------------------------------------------------- ---+
> | 1 | 0 |
> +---+------------------------------------------------------- ---+
> 1 row in set (0.00 sec)
>
> Same here.
>
> mysql> SELECT 1 as a,1 IN (SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE
> a=0);
> +---+------------------------------------------------------- --+
> | a | 1 IN (SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE a=0) |
> +---+------------------------------------------------------- --+
> | 1 | 0 |
> +---+------------------------------------------------------- --+
> 1 row in set (0.00 sec)
>
> Here too ?
>
> Indeed :
>
> mysql> SELECT 1 as a,(SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE a=0);
> +---+----------------------------------------------------+
> | a | (SELECT 1 IN (SELECT 1) FROM (SELECT 1) WHERE a=0) |
> +---+----------------------------------------------------+
> | 1 | NULL |
> +---+----------------------------------------------------+
> 1 row in set (0.00 sec)

single value select should return NULL on empty set.

> And :
>
> mysql> SELECT 1=ALL(SELECT NULL);
> +--------------------+
> | 1=ALL(SELECT NULL) |
> +--------------------+
> | NULL |
> +--------------------+
> 1 row in set (0.00 sec)

it is OK

> mysql> SELECT 1=SOME(SELECT NULL);
> +---------------------+
> | 1=SOME(SELECT NULL) |
> +---------------------+
> | NULL |
> +---------------------+
> 1 row in set (0.00 sec)

this is OK

> mysql> SELECT 1 IN (SELECT NULL);
> +--------------------+
> | 1 IN (SELECT NULL) |
> +--------------------+
> | NULL |
> +--------------------+
> 1 row in set (0.00 sec)

this is OK

> BTW, the <=> operator seems to not be supported with ALL/ANY/SOME ?

yes.
AFAIK, <=> is not standard operation.

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13185@lists.mysql.com
To unsubscribe, e-mail