Description:
Problem with timestamp(8) and GROUP BY
How-To-Repeat:
mysql> CREATE TABLE test (
-> id int auto_increment,
-> dd timestamp(8),
-> data int,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test (data) VALUES(1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+----+----------+------+
| id | dd | data |
+----+----------+------+
| 1 | 20020527 | 1 |
| 2 | 20020527 | 2 |
| 3 | 20020527 | 3 |
| 4 | 20020527 | 4 |
+----+----------+------+
4 rows in set (0.00 sec)
mysql> SELECT dd,count(*) FROM test GROUP BY dd;
+----------+----------+
| dd | count(*) |
+----------+----------+
| 20020527 | 4 |
+----------+----------+
1 row in set (0.00 sec)
mysql> UPDATE test SET dd='20020527000001' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE test SET dd='20020527000002' WHERE id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE test SET dd='20020527000003' WHERE id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE test SET dd='20020527000004' WHERE id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT dd,count(*) FROM test GROUP BY dd;
+----------+----------+
| dd | count(*) |
+----------+----------+
| 20020527 | 1 |
| 20020527 | 1 |
| 20020527 | 1 |
| 20020527 | 1 |
+----------+----------+
4 rows in set (0.00 sec)
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 3.23.49a-max-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 65 days 4 hours 9 min 10 sec
Threads: 6 Questions: 69973912 Slow queries: 688 Opens: 129933 Flush tables: 3
Open tables: 506 Queries per second avg: 12.427
>Environment:
System: Linux gap 2.4.18 #3 SMP Fri Mar 15 14:40:03 EET 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc' 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=pentiumpro
-O3 -fno-omit-frame-pointer' CXX='gcc' 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=pentiumpro -O3
-fno-omit-frame-pointer' LDFLAGS=''
LIBC:
-rwxr-xr-x 1 root root 1384040 Dec 18 18:24 /lib/libc.so.6
-rw-r--r-- 1 root root 25214756 Dec 18 18:07 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Dec 18 18:07 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler
--with-extra-charsets=complex --enable-thread-safe-client --with-innodb
--with-berkeley-db --enable-thread-safe-client
--with-other-libc=/usr/local/mysql-glibc '--with-comment=Official MySQL Binary'
--prefix=/usr/local/mysql --with-extra-charset=complex --enable-thread-safe-client
--enable-local-infile --with-server-suffix=-max '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=pentiumpro -O3 -fno-omit-frame-pointer' '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-r!
tti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX=gcc
---------------------------------------------------------------------
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
