| Bug #14607 | count(substring) cannot return value if we use rollup in group statement | ||
|---|---|---|---|
| Submitted: | 3 Nov 2005 14:25 | Modified: | 2 Nov 2006 7:02 |
| Reporter: | Lu Tao | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.7 | OS: | Linux (Linux, Windows) |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[3 Nov 2005 14:25]
Lu Tao
[3 Nov 2005 14:42]
Valeriy Kravchuk
Thank you for a problem report. Please, provide the dump of the table used (j601) or, at least, the "SHOW CREATE TABLE j601" command results.
[4 Nov 2005 4:35]
Lu Tao
For table J601 has other information, so I create a new table named J1
mysql> create table J1 as select z01_04 from j601;
Query OK, 41346 rows affected (0.38 sec)
Records: 41346 Duplicates: 0 Warnings: 0
mysql> show create table j1;
+-------+-------------------------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------------------------
| j1 | CREATE TABLE `j1` (
`z01_04` varchar(12) default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------
1 row in set (0.02 sec)
mysql> select * from j1 limit 5;
+--------------+
| z01_04 |
+--------------+
| 140303005202 |
| 140482002206 |
| 140226109200 |
| 140206005205 |
| 140822101212 |
+--------------+
5 rows in set (0.00 sec)
mysql> select substring(z01_04,1,4) s,count(distinct substring(z01_04,1,6))
-> xian,count(distinct substring(z01_04,1,9)) xiang ,count(distinct z01_04)
-> cun,count(z01_04) qy from j1 group by s;
+------+------+-------+------+------+
| s | xian | xiang | cun | qy |
+------+------+-------+------+------+
| 1401 | 13 | 199 | 1800 | 7733 |
| 1402 | 11 | 147 | 1540 | 8262 |
| 1403 | 11 | 149 | 1216 | 5396 |
| 1404 | 7 | 75 | 920 | 4859 |
| 1405 | 5 | 66 | 643 | 2348 |
| 1406 | 6 | 106 | 1104 | 3876 |
| 1407 | 9 | 126 | 812 | 3288 |
| 1408 | 6 | 87 | 234 | 651 |
| 1409 | 4 | 34 | 161 | 450 |
| 1410 | 9 | 110 | 1168 | 3825 |
| 1411 | 9 | 86 | 279 | 658 |
+------+------+-------+------+------+
11 rows in set (0.36 sec)
mysql> select substring(z01_04,1,4) s,count(distinct substring(z01_04,1,6))
-> xian,count(distinct substring(z01_04,1,9)) xiang ,count(distinct z01_04)
-> cun,count(z01_04) qy from j1 group by s with rollup;
+------+------+-------+------+-------+
| s | xian | xiang | cun | qy |
+------+------+-------+------+-------+
| 1401 | 0 | 0 | 1800 | 7733 |
| 1402 | 0 | 0 | 1540 | 8262 |
| 1403 | 0 | 0 | 1216 | 5396 |
| 1404 | 0 | 0 | 920 | 4859 |
| 1405 | 0 | 0 | 643 | 2348 |
| 1406 | 0 | 0 | 1104 | 3876 |
| 1407 | 0 | 0 | 812 | 3288 |
| 1408 | 0 | 0 | 234 | 651 |
| 1409 | 0 | 0 | 161 | 450 |
| 1410 | 0 | 0 | 1168 | 3825 |
| 1411 | 0 | 0 | 279 | 658 |
| NULL | 90 | 1185 | 9877 | 41346 |
+------+------+-------+------+-------+
12 rows in set (0.62 sec)
[4 Nov 2005 11:57]
Lu Tao
Oracle supports
select substr(z01_04,1,4) s,count(distinct substr(z01_04,1,6))
xian,count(distinct substr(z01_04,1,9)) xiang ,count(distinct z01_04)
cun,count(z01_04) qy from j601 where rownum<=100 group by substr(z01_04,1,4);
but MySQL must
select substr(z01_04,1,4) s,count(distinct substr(z01_04,1,6))
xian,count(distinct substr(z01_04,1,9)) xiang ,count(distinct z01_04)
cun,count(z01_04) qy from j601 where rownum<=100 group by s;
which one obey theSQL standard?
[4 Nov 2005 13:04]
Valeriy Kravchuk
Thank you for a bug report. Verified on data similar to yours on Linux and latest 5.0.16-BK build (ChangeSet@1.1957.1.18, 2005-11-03 20:29:21+02:00, jani@ua141d10.elisa.omakaista.fi):
[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.16
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE `j1` (
-> `z01_04` varchar(12) default ''
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0,00 sec)
mysql> insert into j1 values ('140303005202'), ('140482002206'), ('140226109200'), ('140206005205'), ('140822101212');
Query OK, 5 rows affected (0,00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into j1 select * from j1;
Query OK, 5 rows affected (0,00 sec)
Records: 5 Duplicates: 0 Warnings: 0
...
mysql> insert into j1 select * from j1;
Query OK, 20480 rows affected (0,23 sec)
Records: 20480 Duplicates: 0 Warnings: 0
mysql> select count(*) from j1;
+----------+
| count(*) |
+----------+
| 40960 |
+----------+
1 row in set (0,00 sec)
mysql> select substring(z01_04,1,4) s, count(distinct substring(z01_04,1,6)) xian,
-> count(distinct substring(z01_04,1,9)) xiang,
-> ccount(distinct z01_04) cun, count(z01_04) qy
-> from j1 group by s;
+------+------+-------+-----+-------+
| s | xian | xiang | cun | qy |
+------+------+-------+-----+-------+
| 1402 | 2 | 2 | 2 | 16384 |
| 1403 | 1 | 1 | 1 | 8192 |
| 1404 | 1 | 1 | 1 | 8192 |
| 1408 | 1 | 1 | 1 | 8192 |
+------+------+-------+-----+-------+
4 rows in set (0,79 sec)
mysql> select substring(z01_04,1,4) s, count(distinct substring(z01_04,1,6)) xian, count(distinct substring(z01_04,1,9)) xiang, count(distinct z01_04) cun, count(z01_04) qy from j1 group by s with rollup;
+------+------+-------+-----+-------+
| s | xian | xiang | cun | qy |
+------+------+-------+-----+-------+
| 1402 | 0 | 0 | 2 | 16384 |
| 1403 | 0 | 0 | 1 | 8192 |
| 1404 | 0 | 0 | 1 | 8192 |
| 1408 | 0 | 0 | 1 | 8192 |
| NULL | 5 | 5 | 5 | 40960 |
+------+------+-------+-----+-------+
5 rows in set (1,23 sec)
It really looks like a bug. May be related to a fix made for http://bugs.mysql.com/bug.php?id=12887.
As for your last question (Oracle or MySQL, that requires alias in group by, is closer to standard) - I should check, but my personal oppinion is that Oracle is far from any standards but its own. And even in Oracle using column alias should work too.
[7 Mar 2006 5:03]
Lu Tao
The bug isn't fixed in ver 5.1.7 -------------- mysql> select substring(z01_04,1,4) s, count(distinct substring(z01_04,1,6)) xia n,count(distinct substring(z01_04,1,9)) xiang,count(distinct z01_04) cun, count( z01_04) qy from j1 group by s; +------+------+-------+-----+----+ | s | xian | xiang | cun | qy | +------+------+-------+-----+----+ | 1402 | 2 | 2 | 2 | 2 | | 1403 | 1 | 1 | 1 | 1 | | 1404 | 1 | 1 | 1 | 1 | | 1408 | 1 | 1 | 1 | 1 | +------+------+-------+-----+----+ 4 rows in set (0.00 sec) mysql> select substring(z01_04,1,4) s, count(distinct substring(z01_04,1,6)) xia n,count(distinct substring(z01_04,1,9)) xiang,count(distinct z01_04) cun, count( z01_04) qy from j1 group by s with rollup; +------+------+-------+-----+----+ | s | xian | xiang | cun | qy | +------+------+-------+-----+----+ | 1402 | 0 | 0 | 2 | 2 | | 1403 | 0 | 0 | 1 | 1 | | 1404 | 0 | 0 | 1 | 1 | | 1408 | 0 | 0 | 1 | 1 | | NULL | 5 | 5 | 5 | 5 | +------+------+-------+-----+----+ 5 rows in set (0.00 sec)
[7 Mar 2006 5:05]
Lu Tao
and if we don't use "distinct", the answer is right mysql> select substring(z01_04,1,4) s, count( substring(z01_04,1,6)) xian,count( substring(z01_04,1,9)) xiang,count( z01_04) cun, count(z01_04) qy from j1 group by s; +------+------+-------+-----+----+ | s | xian | xiang | cun | qy | +------+------+-------+-----+----+ | 1402 | 2 | 2 | 2 | 2 | | 1403 | 1 | 1 | 1 | 1 | | 1404 | 1 | 1 | 1 | 1 | | 1408 | 1 | 1 | 1 | 1 | +------+------+-------+-----+----+ 4 rows in set (0.00 sec) mysql> select substring(z01_04,1,4) s, count( substring(z01_04,1,6)) xian,count( substring(z01_04,1,9)) xiang,count( z01_04) cun, count(z01_04) qy from j1 group by s with rollup; +------+------+-------+-----+----+ | s | xian | xiang | cun | qy | +------+------+-------+-----+----+ | 1402 | 2 | 2 | 2 | 2 | | 1403 | 1 | 1 | 1 | 1 | | 1404 | 1 | 1 | 1 | 1 | | 1408 | 1 | 1 | 1 | 1 | | NULL | 5 | 5 | 5 | 5 | +------+------+-------+-----+----+ 5 rows in set (0.00 sec)
[31 Oct 2006 10:51]
Georgi Kodinov
I failed to repeat the problem with mysql version 5.0.27 and 5.1.13 :
CREATE TABLE j1 (z01_04 varchar(12));
insert into j1 values ('140303005202'), ('140482002206'),
('140226109200'), ('140206005205'), ('140822101212');
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
insert into j1 select * from j1;
select count(*) from j1;
count(*)
40960
select
substring(z01_04,1,4) s,
count(distinct substring(z01_04,1,6)) xian,
count(distinct substring(z01_04,1,9)) xiang,
count(distinct z01_04) cun,
count(z01_04) qy
from j1 group by s;
s xian xiang cun qy
1402 2 2 2 16384
1403 1 1 1 8192
1404 1 1 1 8192
1408 1 1 1 8192
select
substring(z01_04,1,4) s,
count(distinct substring(z01_04,1,6)) xian,
count(distinct substring(z01_04,1,9)) xiang,
count(distinct z01_04) cun,
count(z01_04) qy
from j1 group by s with rollup;
s xian xiang cun qy
1402 2 2 2 16384
1403 1 1 1 8192
1404 1 1 1 8192
1408 1 1 1 8192
NULL 5 5 5 40960
[2 Nov 2006 6:53]
Lu Tao
it's a bug which was found in 5.1.7 i also cannot repeat it in 5.0.22 and 5.1.11 maybe someone fixed it in earlier version
[2 Nov 2006 7:02]
Lu Tao
C:\Documents and Settings\Administrator>cd C:\LT_C\mysql51\bin
C:\LT_C\mysql51\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.7-beta-community
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Database changed
mysql> CREATE TABLE `j1` (
-> `z01_04` varchar(12) default ''
-> );
ERROR 1050 (42S01): Table 'j1' already exists
mysql> select * from j1;
+--------------+
| z01_04 |
+--------------+
| 140303005202 |
| 140482002206 |
| 140226109200 |
| 140206005205 |
| 140822101212 |
+--------------+
5 rows in set (0.00 sec)
mysql> select substring(z01_04,1,4) s, count(distinct substring(z01_04,1,6))
-> xian, count(distinct substring(z01_04,1,9)) xiang, count(distinct z01_04)
cun,
-> count(z01_04) qy from j1 group by s with rollup;
+------+------+-------+-----+----+
| s | xian | xiang | cun | qy |
+------+------+-------+-----+----+
| 1402 | 0 | 0 | 2 | 2 |
| 1403 | 0 | 0 | 1 | 1 |
| 1404 | 0 | 0 | 1 | 1 |
| 1408 | 0 | 0 | 1 | 1 |
| NULL | 5 | 5 | 5 | 5 |
+------+------+-------+-----+----+
5 rows in set (0.03 sec)
mysql> select substring(z01_04,1,4) s, count(distinct substring(z01_04,1,6)) xia
n,
-> count(distinct substring(z01_04,1,9)) xiang,count(distinct z01_04) cun, c
ount(z01_04) qy
-> from j1 group by s;
+------+------+-------+-----+----+
| s | xian | xiang | cun | qy |
+------+------+-------+-----+----+
| 1402 | 2 | 2 | 2 | 2 |
| 1403 | 1 | 1 | 1 | 1 |
| 1404 | 1 | 1 | 1 | 1 |
| 1408 | 1 | 1 | 1 | 1 |
+------+------+-------+-----+----+
4 rows in set (0.00 sec)
mysql>
----------------------------
by the way, where can download 5.1.13?
[2 Nov 2006 8:46]
Georgi Kodinov
> by the way, where can download 5.1.13? I was referring to the source trees from BitKeeper.
