Bug with subselect and IS NULL/NOT NULL in MySQL-4.1

Hi,

How-to-repeat :

SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

=> It should return an empty set ?

SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
Empty set (0.01 sec)

=> It should not return an empty set ;)

As well :

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

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

Some weird results :

mysql> SELECT (SELECT 1 UNION ALL SELECT 1) IS NULL;
ERROR 1240: Subselect returns more than 1 record

ok, but :

mysql> SELECT (SELECT 1 UNION ALL SELECT 1) IS NOT NULL;
+-------------------------------------------+
| (SELECT 1 UNION ALL SELECT 1) IS NOT NULL |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in set (0.00 sec)

does not return any error ??

However :

SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a)<=>NULL;
Empty set (0.00 sec)

seems to be ok.

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-thread13284@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with subselect and IS NULL/NOT NULL in MySQL-4.1

On Sunday 15 December 2002 05:26, Jocelyn Fournier wrote:

Hi,

>
> SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
> +------+
>

Hmm, I'm not sure but it should be not allowed to select from a derived
tablename/alias in the where clause.

mysql> select (select 1) as a from (select 1) as b where (select 1) is NULL;
Empty set (0.00 sec)

works fine.

Regards

Georg


filter: how-to-repeat


------------------------------------------------------------ ---------
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-thread13285@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with subselect and IS NULL/NOT NULL in MySQL-4.1

Hi Georg,

AFAIK, it's a standard DEPENDANT SUBSELECT, and I already reported bug
reports with DEPENDANT SELECT in the WHERE clause ;).
Moreover :

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

mysql> SELECT (SELECT 2) as a FROM (SELECT 1) b WHERE (SELECT a)=1;
Empty set (0.00 sec)

mysql> SELECT (SELECT 2) as a FROM (SELECT 1) b WHERE (SELECT a)=0;
Empty set (0.00 sec)

works fine, as well as with the "<=>" operator, so it's really a problem
with IS NULL / NOT NULL and subselect :).

BTW :

select (select 1) as a from (select 1) as b where (select 1) is NULL;
Empty set (0.00 sec)

select (select 1) as a from (select 1) as b where (select 1) is NOT NULL;
Empty set (0.00 sec)

;)

Regards,
Jocelyn

----- Original Message -----
From: "Georg Richter"
To: "Jocelyn Fournier" ;
Sent: Sunday, December 15, 2002 5:32 AM
Subject: Re: Bug with subselect and IS NULL/NOT NULL in MySQL-4.1


> On Sunday 15 December 2002 05:26, Jocelyn Fournier wrote:
>
> Hi,
>
> >
> > SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
> > +------+
> >
>
> Hmm, I'm not sure but it should be not allowed to select from a derived
> tablename/alias in the where clause.
>
> mysql> select (select 1) as a from (select 1) as b where (select 1) is
NULL;
> Empty set (0.00 sec)
>
> works fine.
>
> Regards
>
> Georg
>
>
> filter: how-to-repeat
>
>
> ------------------------------------------------------------ ---------
> 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-thread13285@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
>


------------------------------------------------------------ ---------
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-thread13287@lists.mysql.com
To unsubscribe, e-mail

Re: Bug with subselect and IS NULL/NOT NULL in MySQL-4.1

Hi!

On Sun, Dec 15, 2002 at 04:26:29AM -0000, Jocelyn Fournier wrote:
> Hi,
>
> How-to-repeat :
>
> SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
> +------+
> | a |
> +------+
> | 1 |
> +------+
> 1 row in set (0.00 sec)
>
> => It should return an empty set ?
>
> SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
> Empty set (0.01 sec)
>
> => It should not return an empty set ;)

Thank you for bugreport! I fixed this bug (patch is commited & pushed).

diff -Nrc a/sql/item_subselect.h b/sql/item_subselect.h
*** a/sql/item_subselect.h Sun Dec 15 13:42:16 2002
--- b/sql/item_subselect.h Sun Dec 15 13:42:16 2002
***************
*** 75,81 ****
bool assigned() { return value_assigned; }
void assigned(bool a) { value_assigned= a; }
enum Type type() const;
! bool is_null() { return null_value; }
bool fix_fields(THD *thd, TABLE_LIST *tables, Item **ref);
virtual void fix_length_and_dec();
table_map used_tables() const;
--- 75,85 ----
bool assigned() { return value_assigned; }
void assigned(bool a) { value_assigned= a; }
enum Type type() const;
! bool is_null()
! {
! val_int();
! return null_value;
! }
bool fix_fields(THD *thd, TABLE_LIST *tables, Item **ref);
virtual void fix_length_and_dec();
table_map used_tables() const;

--
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-thread13286@lists.mysql.com
To unsubscribe, e-mail