| Bug #2713 | SELECT on derived table with no outer GROUP BY causes sig11 | ||
|---|---|---|---|
| Submitted: | 11 Feb 2004 13:33 | Modified: | 23 Feb 2004 7:05 |
| Reporter: | Dossy Shiobara | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.1 | OS: | Solaris (Solaris 2.8) |
| Assigned to: | CPU Architecture: | Any | |
[21 Feb 2004 12:27]
MySQL Verification Team
I tried this with 4.1.2 and it worked like a charm: select broj, naziv from (select broj, naziv from nazivi union all select broj, naziv from nazivi union all select broj, naziv from nazivi) as indata; +------+----------------------+ | broj | naziv | +------+----------------------+ | 1 | jedan | | 2 | dva | | 3 | tri | | 4 | xxxxxxxxxx | | 5 | a | | 10 | | | 6 | Sinisa | | 7 | Satkica | | 8 | yyyyy | | 9 | yyyyy | | 11 | aa | | 15 | NULL | | 55 | "kkk" | | 66 | ";;;" | | 33 | erik@frontbridge.com | | 1 | jedan | | 2 | dva | | 3 | tri | | 4 | xxxxxxxxxx | | 5 | a | | 10 | | | 6 | Sinisa | | 7 | Satkica | | 8 | yyyyy | | 9 | yyyyy | | 11 | aa | | 15 | NULL | | 55 | "kkk" | | 66 | ";;;" | | 33 | erik@frontbridge.com | | 1 | jedan | | 2 | dva | | 3 | tri | | 4 | xxxxxxxxxx | | 5 | a | | 10 | | | 6 | Sinisa | | 7 | Satkica | | 8 | yyyyy | | 9 | yyyyy | | 11 | aa | | 15 | NULL | | 55 | "kkk" | | 66 | ";;;" | | 33 | erik@frontbridge.com | +------+----------------------+
[21 Feb 2004 12:49]
Dossy Shiobara
Our SQL had a SUM() column. Looking at your query and data, try: select SUM(broj), naziv from (select broj, naziv from nazivi union all select broj, naziv from nazivi union all select broj, naziv from nazivi) as indata; -- Dossy
[23 Feb 2004 7:05]
MySQL Verification Team
Works just fine for me: ./client/mysql telcent -e "select SUM(broj), naziv from (select broj, naziv from nazivi union all select broj, naziv from nazivi union all select broj, naziv from nazivi) as indata" ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause [/mnt/work/mysql-4.1]$ ./client/mysql telcent -e "select SUM(broj), naziv from (select broj, naziv from nazivi union all select broj, naziv from nazivi union all select broj, naziv from nazivi) as indata group by naziv" +-----------+----------------------+ | SUM(broj) | naziv | +-----------+----------------------+ | 45 | NULL | | 30 | | | 198 | ";;;" | | 165 | "kkk" | | 15 | a | | 33 | aa | | 6 | dva | | 99 | erik@frontbridge.com | | 3 | jedan | | 21 | Satkica | | 18 | Sinisa | | 9 | tri | | 12 | xxxxxxxxxx | | 51 | yyyyy | +-----------+----------------------+
Description: A SELECT on a derived table with no outer GROUP BY causes a sig11 on the -max build but *sometimes* can throw an error on the -debug build. The error thrown is: ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause How to repeat: The SQL statement in question looks something like: SELECT a, b FROM ( SELECT a, b FROM t0 UNION ALL SELECT a, b FROM t1 ... UNION ALL SELECT a, b FROM tN ) AS indata; This isn't exactly the SQL we're using but it's a close approximation. This is a reproducible bug against MySQL 4.1.1-alpha-max on SPARC Solaris 2.8.