| Bug #29630 | "group by" doesnt work with subselects | ||
|---|---|---|---|
| Submitted: | 8 Jul 2007 19:27 | Modified: | 9 Jul 2007 13:07 |
| Reporter: | Niklaus Flunser | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.41-community-nt | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | count, exists, GROUP BY, nested, subselect, subselects, sum | ||
[9 Jul 2007 13:07]
MySQL Verification Team
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
mysql> select tab1.name, tab1.value
-> from tab1
-> where tab1.value in (select tab2.value_ok from tab2 where tab2.value_ok = tab1.value);
+------+-------+
| name | value |
+------+-------+
| aaa | 5 |
| aaa | 10 |
| bbb | 100 |
| bbb | 1000 |
+------+-------+
4 rows in set (0.07 sec)
mysql> select tab1.name, sum(tab1.value)
-> from tab1
-> where tab1.value in (select tab2.value_ok from tab2 where tab2.value_ok = tab1.value)
-> group by tab1.name;
Empty set (0.06 sec)
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 5.0.41-community-nt |
+---------------------+
1 row in set (0.00 sec)
mysql>
*****************************************************************************************
Microsoft Windows [versão 6.0.6000]
Copyright (c) 2006 Microsoft Corporation. Todos os direitos reservados.
c:\dev>cd 5.0
c:\dev\5.0>bin\mysqladmin -uroot create db7
c:\dev\5.0>bin\mysql -uroot db7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.46-nt Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `tab1` (
-> `name` varchar(30) NOT NULL,
-> `value` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`name`,`value`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE TABLE `tab2` (
-> `value_ok` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`value_ok`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.18 sec)
mysql> INSERT INTO `tab1` VALUES ('aaa', '5');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `tab1` VALUES ('aaa', '10');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `tab1` VALUES ('bbb', '100');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `tab1` VALUES ('bbb', '1000');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `tab2` VALUES ('5');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `tab2` VALUES ('10');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `tab2` VALUES ('100');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `tab2` VALUES ('1000');
Query OK, 1 row affected (0.00 sec)
mysql> select tab1.name, tab1.value
-> from tab1
-> where tab1.value in (select tab2.value_ok from tab2 where tab2.value_ok = tab1.value);
+------+-------+
| name | value |
+------+-------+
| aaa | 5 |
| aaa | 10 |
| bbb | 100 |
| bbb | 1000 |
+------+-------+
4 rows in set (0.00 sec)
mysql> select tab1.name, sum(tab1.value)
-> from tab1
-> where tab1.value in (select tab2.value_ok from tab2 where tab2.value_ok = tab1.value)
-> group by tab1.name;
+------+-----------------+
| name | sum(tab1.value) |
+------+-----------------+
| aaa | 15 |
| bbb | 1100 |
+------+-----------------+
2 rows in set (0.00 sec)
mysql>
Description: any "group by" (count, sum, etc.) doesnt work with any subselects (exists, in, etc.): - without the "sum"/"group by", i receive some rows - thats ok. (query1) - if i try to group this rows, i receive 0 rows - thats a bug. (query2) extra-info (very strange): 1. replace the "5" with a "1" in both tables. 2. run first query2 - it will return 0 rows as we expected. 3. but now run first query1 and then query2 - it will return 1 row, but this is false too. How to repeat: -- ---------------------------- -- Table structure for tab1 -- ---------------------------- CREATE TABLE `tab1` ( `name` varchar(30) NOT NULL, `value` int(10) unsigned NOT NULL, PRIMARY KEY (`name`,`value`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- ---------------------------- -- Table structure for tab2 -- ---------------------------- CREATE TABLE `tab2` ( `value_ok` int(10) unsigned NOT NULL, PRIMARY KEY (`value_ok`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- ---------------------------- -- Records -- ---------------------------- INSERT INTO `tab1` VALUES ('aaa', '5'); INSERT INTO `tab1` VALUES ('aaa', '10'); INSERT INTO `tab1` VALUES ('bbb', '100'); INSERT INTO `tab1` VALUES ('bbb', '1000'); INSERT INTO `tab2` VALUES ('5'); INSERT INTO `tab2` VALUES ('10'); INSERT INTO `tab2` VALUES ('100'); INSERT INTO `tab2` VALUES ('1000'); -- ---------------------------- -- query1 -- ---------------------------- select tab1.name, tab1.value from tab1 where tab1.value in (select tab2.value_ok from tab2 where tab2.value_ok = tab1.value) -- ---------------------------- -- query2 -- ---------------------------- select tab1.name, sum(tab1.value) from tab1 where tab1.value in (select tab2.value_ok from tab2 where tab2.value_ok = tab1.value) group by tab1.name Suggested fix: for the mainproblem: i have no idea :D for the strange thing: it looks like there is missing a operator. the value probably is used as boolean!? but im sure its not that simple ;)