Subquery bug

Hello,

I think I've found a bug in 4.1.3 nested sub query handling.

Test case:

--Creating and populating child and father tables
---------------------------------
mysql> create table child(id int,f_id int,val varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> create table father(id int,val varchar(10));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into father values(1,'A'),(2,'B');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into child values(1,1,'A'),(2,1,'B'),(3,2,'C'),(4,2,'D');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0


--Buggy sub query (should it report an error in nested sub query (column f_id
does not exists in father table))
-------------------------------------
mysql> select f_id,count(*) from child where f_id in (select f_id from father)
group by f_id;
+------+----------+
| f_id | count(*) |
+------+----------+
| 1 | 2 |
| 2 | 2 |
+------+----------+
2 rows in set (0.00 sec)

--The nested query alone produces error as expected.
-------------------------------------
mysql> select f_id from father;
ERROR 1054 (42S22): Unknown column 'f_id' in 'field list'

Regards,
--
Saulius Gurklys


p.s. Test case (queries only):

create table child(id int,f_id int,val varchar(10));

create table father(id int,val varchar(10));

insert into father values(1,'A'),(2,'B');

insert into child values(1,1,'A'),(2,1,'B'),(3,2,'C'),(4,2,'D');

select f_id,count(*) from child where f_id in (select f_id from father) group by
f_id;

select f_id from father;

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Subquery bug

Hi!

On Thu, Jul 08, 2004 at 10:40:23AM +0300, SaGu wrote:
> Hello,
>
> I think I've found a bug in 4.1.3 nested sub query handling.
>
> Test case:
>
> --Creating and populating child and father tables
> ---------------------------------
> mysql> create table child(id int,f_id int,val varchar(10));
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> create table father(id int,val varchar(10));
> Query OK, 0 rows affected (0.05 sec)
>
> mysql> insert into father values(1,'A'),(2,'B');
> Query OK, 2 rows affected (0.00 sec)
> Records: 2 Duplicates: 0 Warnings: 0
>
> mysql> insert into child values(1,1,'A'),(2,1,'B'),(3,2,'C'),(4,2,'D');
> Query OK, 4 rows affected (0.00 sec)
> Records: 4 Duplicates: 0 Warnings: 0
>
>
> --Buggy sub query (should it report an error in nested sub query (column
> f_id does not exists in father table))
> -------------------------------------
> mysql> select f_id,count(*) from child where f_id in (select f_id from
> father) group by f_id;
> +------+----------+
> | f_id | count(*) |
> +------+----------+
> | 1 | 2 |
> | 2 | 2 |
> +------+----------+
> 2 rows in set (0.00 sec)
>
> --The nested query alone produces error as expected.
> -------------------------------------
> mysql> select f_id from father;
> ERROR 1054 (42S22): Unknown column 'f_id' in 'field list'

f_id resolved as external field of outer query in case of subquery, to
see it, run, please, following commands:

explain EXTENDED select f_id,count(*) from child where f_id in (select
f_id from father) group by f_id;
show warnings;

You will see warning about resolving f_id in outer query.

[skip]

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org