| Bug #8827 | Bug in nested SELECT: SELECT ... WHERE X IN (SELECT...) | ||
|---|---|---|---|
| Submitted: | 27 Feb 2005 10:35 | Modified: | 27 Jun 2005 8:00 |
| Reporter: | Grzegorz Laszczak | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.10 | OS: | Linux (Linux, Windows) |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[27 Feb 2005 16:05]
Jorge del Conde
Hi Can you please upload your tar_records table so that we can test this bug ? Thanks a lot!
[28 Feb 2005 18:22]
Grzegorz Laszczak
Thank you for quick answer... I've uploaded all tables needed for demonstration (tar_records, adm_numery, adm_num_grup, adm_grupy;). Archiv contains output of mysqldump. Have a good time Grzegorz
[11 Mar 2005 9:34]
Grzegorz Laszczak
Hi! Have you got my tables? Could you repeat my bug? Is this a bug or maybe my mistake only? Have a good time Grzegorz
[14 Apr 2005 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[15 Apr 2005 14:40]
Sergei Golubchik
reopened
[19 Apr 2005 4:31]
MySQL Verification Team
miguel@light:~/dbs/4.1$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.12-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select
-> nr_kat_a as Rozmowy,
-> count(*) as ilosc,
-> sec_to_time(sum(cz_trwania)) as CzasTrwania,
-> format(sum(cena3),2) as Cena
-> from tar_records
-> where (data between '2004-11-01' and '2004-11-30')
-> and (przych=0)
-> and (nr_kat_a in (
-> select n_numer
-> from adm_numery, adm_num_grup, adm_grupy
-> where n_id=ng_idn and ng_idg=g_id and g_id=2)
-> )
-> group by Rozmowy with rollup
-> ;
+---------+-------+-------------+--------+
| Rozmowy | ilosc | CzasTrwania | Cena |
+---------+-------+-------------+--------+
| 111 | 265 | 06:12:20 | 137.77 |
| 112 | 42 | NULL | NULL |
| 114 | 72 | NULL | NULL |
| 115 | 68 | NULL | NULL |
| 120 | 6 | NULL | NULL |
| NULL | 453 | 12:19:16 | 252.55 |
+---------+-------+-------------+--------+
6 rows in set (0.07 sec)
mysql>
mysql> select
-> nr_kat_a as Rozmowy,
-> count(*) as ilosc,
-> sec_to_time(sum(cz_trwania)) as CzasTrwania,
-> format(sum(cena3),2) as Cena
-> from tar_records
-> where (data between '2004-11-01' and '2004-11-30')
-> and (przych=0)
-> and (nr_kat_a in (
-> '115', '114', '120', '111', '112')
-> )
-> group by Rozmowy with rollup;
+---------+-------+-------------+--------+
| Rozmowy | ilosc | CzasTrwania | Cena |
+---------+-------+-------------+--------+
| 111 | 265 | 06:12:20 | 137.77 |
| 112 | 42 | 00:37:37 | 11.62 |
| 114 | 72 | 02:22:02 | 49.95 |
| 115 | 68 | 02:59:03 | 51.44 |
| 120 | 6 | 00:08:14 | 1.78 |
| NULL | 453 | 12:19:16 | 252.55 |
+---------+-------+-------------+--------+
6 rows in set (0.11 sec)
[27 Jun 2005 8:00]
Oleksandr Byelkin
Thank you for bugreport, but I can't repeat it any more on current 4.1 bk source repository, it looks like fixed now (If you can feel free reopen bug, but please say how you make it in details): + select nr_kat_a as Rozmowy, count(*) as ilosc, sec_to_time(sum(cz_trwania)) + as CzasTrwania, format(sum(cena3),2) as Cena from tar_records where (data + between '2004-11-01' and '2004-11-30')and (przych=0) and (nr_kat_a in + (select n_numer from adm_numery, adm_num_grup, adm_grupy where n_id=ng_idn + and ng_idg=g_id and g_id=2)) group by Rozmowy with rollup; + Rozmowy ilosc CzasTrwania Cena + 111 265 06:12:20 137.77 + 112 42 00:37:37 11.62 + 114 72 02:22:02 49.95 + 115 68 02:59:03 51.44 + 120 6 00:08:14 1.78 + NULL 453 12:19:16 252.55
Description: I've found a bug in nested SELECT querries (grouping with aggregate functions doesn't work in connection with nested IN SELECT). The bug persist when using aggregate functions with other functions and in WHERE clouse there is nested SELECT and result have more then 1 row. My MySQL querries works good in MySQL 4.1.7, but not in 4.1.9 and 4.1.10 (standard). The bug is independent from client's side software (I can repeat it in mysql text client, mysqlcc and from PHP functions). It is possible to change querries and have the same result without nested SELECT but this is very inconvenient, so in my opinion severity is 3. How to repeat: Have a look at this querries and results: THIS IS BUGGY QUERRY: mysql> select -> nr_kat_a as Rozmowy, -> count(*) as ilosc, -> sec_to_time(sum(cz_trwania)) as CzasTrwania, -> format(sum(cena3),2) as Cena -> from tar_records -> where (data between '2004-11-01' and '2004-11-30') -> and (przych=0) -> and (nr_kat_a in ( -> select n_numer -> from adm_numery, adm_num_grup, adm_grupy -> where n_id=ng_idn and ng_idg=g_id and g_id=2) -> ) -> group by Rozmowy with rollup -> ; +---------+-------+-------------+--------+ | Rozmowy | ilosc | CzasTrwania | Cena | +---------+-------+-------------+--------+ | NULL | 265 | 06:12:20 | 137.77 | | NULL | 42 | NULL | NULL | | NULL | 72 | NULL | NULL | | NULL | 68 | NULL | NULL | | NULL | 6 | NULL | NULL | | NULL | 453 | 12:19:16 | 252.55 | +---------+-------+-------------+--------+ 6 rows in set (0.06 sec) THIS IS QUERRY FOR NESTED SELECT ONLY: mysql> select n_numer -> from adm_numery, adm_num_grup, adm_grupy -> where n_id=ng_idn and ng_idg=g_id and g_id=2 -> ; +---------+ | n_numer | +---------+ | 115 | | 114 | | 120 | | 111 | | 112 | +---------+ 5 rows in set (0.00 sec) AND THIS IS THE SAME QUERRY WITHOUT NESTED SELECT: mysql> select -> nr_kat_a as Rozmowy, -> count(*) as ilosc, -> sec_to_time(sum(cz_trwania)) as CzasTrwania, -> format(sum(cena3),2) as Cena -> from tar_records -> where (data between '2004-11-01' and '2004-11-30') -> and (przych=0) -> and (nr_kat_a in ( -> '115', '114', '120', '111', '112') -> ) -> group by Rozmowy with rollup -> ; +---------+-------+-------------+--------+ | Rozmowy | ilosc | CzasTrwania | Cena | +---------+-------+-------------+--------+ | 111 | 265 | 06:12:20 | 137.77 | | 112 | 42 | 00:37:37 | 11.62 | | 114 | 72 | 02:22:02 | 49.95 | | 115 | 68 | 02:59:03 | 51.44 | | 120 | 6 | 00:08:14 | 1.78 | | NULL | 453 | 12:19:16 | 252.55 | +---------+-------+-------------+--------+ 6 rows in set (0.04 sec) Suggested fix: I'd like not to change my querries... I have a lot of such type querries in my application :((