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 <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__   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/[email protected]