>Description:
mysql> describe DLSummary;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| DownloadID | int(10) unsigned | YES | MUL | NULL | |
| Year | int(10) unsigned | YES | MUL | NULL | |
| YDay | int(10) unsigned | YES | | NULL | |
| Count | int(10) unsigned | YES | | NULL | |
+------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show keys from DLSummary;
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
|Cardinality | Sub_part | Packed | Null | Index_type
| Comment |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| DLSummary | 1 | Date | 1 | Year | A |
| 1 | NULL | NULL | YES | BTREE
| |
| DLSummary | 1 | Date | 2 | YDay | A |
| 377 | NULL | NULL | YES | BTREE
| |
| DLSummary | 1 | DownloadID | 1 | DownloadID | A |
|15378 | NULL | NULL | YES | BTREE
| |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.01 sec)
**** BUG ****
mysql> select Year,YDay,sum(Count) from DLSummary group by Year,YDay;
ERROR 1062: Duplicate entry '2001-125' for key 1
**** BUG ****
mysql> select Year,YDay,sum(Count) from DLSummary group by concat(Year,YDay);
+------+------+------------+
| Year | YDay | sum(Count) |
+------+------+------------+
| 2001 | 124 | 2140 |
| 2001 | 125 | 10302 |
.....
This seems rather odd. GROUP BY Year,YDay is documented to be topologically
equivalent to GROUP BY CONCAT(Year,YDay) (well, more or less) but has
functional difference.
Bug tested in 4.0.2 @ BK 1.1272 (May 17th) and also on 4.0.2 as of around
March 2nd, so it's nothing recent.
Table REPAIRED successfully, so I'm convinced that the table is good.
Repeatable with cleanly created tables.
>How-To-Repeat:
create table test (One int unsigned, Two int unsigned, Three int unsigned, Four int
unsigned);
insert into test values (1,2,1,4);
insert into test values (1,2,2,4);
insert into test values (1,2,3,4);
insert into test values (1,2,4,4);
insert into test values (1,1,1,4);
insert into test values (1,1,2,4);
insert into test values (1,1,3,4);
insert into test values (1,1,4,4);
insert into test values (1,3,1,4);
insert into test values (1,3,2,4);
insert into test values (1,3,3,4);
insert into test values (1,3,4,4);
select One, Two, sum(Four) from test group by One,Two; -- fails
select One, Two, sum(Four) from test group by concat(One,Two); -- succeeds
>Fix:
Use concat() to create a single group by element, but this isn't
the right way to do it, surely!
>Submitter-Id: <submitter ID>
>Originator: Matt Johnson
>Organization: AVSIM Online <http://www.avsim.com>
>MySQL support: none
>Synopsis: GROUP BY on multiple fields apparently broken
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.2-alpha (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 8.32 Distrib 4.0.2-alpha, for pc-linux-gnu on
>i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.2-alpha
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 6 hours 37 min 58 sec
Threads: 2 Questions: 166 Slow queries: 11 Opens: 26 Flush tables: 1 Open tables:
5 Queries per second avg: 0.007
>Environment:
System: Linux guysfield 2.4.18-k7 #1 Sun Apr 14 13:19:11 EST 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc-3.0' CFLAGS='-Wimplicit -Wreturn-type -Wid-clash-51
-Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec
-Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=athlon -O3
-fno-omit-frame-pointer' CXX='g++-3.0' CXXFLAGS='-Wimplicit -Wreturn-type
-Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat
-Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings
-Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy
-Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=athlon -O3
-fno-omit-frame-pointer' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 May 18 12:12 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1153784 Apr 28 10:57 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2390922 Apr 28 10:58 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 28 10:58 /usr/lib/libc.so
-rw-r--r-- 1 root root 716080 Jan 13 20:06 /usr/lib/libc-client.so.2001
Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler
--with-extra-charsets=none --enable-thread-safe-client
--with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql
--without-debug --without-bench --with-charset=latin1 --with-extra-charsets=none
--without-berkeley-db --without-innodb --enable-local-infile
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
