ERROR 1062 (23000): Duplicate entry '1' for key 1 during SELECT

[mike@xxxxxxxxxxxxxxx]

>Description:
When running the statements below, MySQL errors out with:
ERROR 1062 (23000) at line 5: Duplicate entry '1' for key 1 during SELECT

>How-To-Repeat:

drop table if exists t;

create table t (f int not null);

insert into t values (1),(1);

select 1 from 
   t straight_join
   t as t2
   left join t as t3 on 0=1
group by
   t2.f
having
   0=count(*)

>Fix:
Workarounds include removing "not null" from the table definition, using a
join instead of a straight_join, removing the left join, having no rows in
the left joined table (three different tables can be used; the bug doesn't
depend on aliases), grouping on t.f instead of t2.f, or changing
"0=count(*)" to "0=0".

>Submitter-Id:	<submitter ID>
>Originator:	Michael Thorpe
>Organization:  GISTnet, Inc.
>MySQL support: none
>Synopsis:	Duplicate key error during select
>Severity:	non-critical
>Priority:	low
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-5.0.37 (Source distribution)
>Server: /usr/bin/mysqladmin  Ver 8.41 Distrib 5.0.37, for unknown-linux-gnu on x86_64
Copyright (C) 2000-2006 MySQL 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		5.0.37-log
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/usr/mysql/sock/mysql.sock
Uptime:			170 days 4 hours 18 min 34 sec

Threads: 12  Questions: 218282739  Slow queries: 379  Opens: 4014478  Flush tables: 1  Open tables: 64  Queries per second avg: 14.846
>C compiler:    gcc (GCC) 3.3.5 (Debian 1:3.3.5-13)
>C++ compiler:  g++ (GCC) 3.3.5 (Debian 1:3.3.5-13)
>Environment:
	
System: Linux gn5 2.6.14 #5 SMP Mon Mar 5 14:19:52 PST 2007 x86_64 GNU/Linux
Architecture: x86_64

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/x86_64-linux/3.3.5/specs
Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --enable-__cxa_atexit --with-system-zlib --enable-nls --without-included-gettext --enable-clocale=gnu --enable-debug --enable-java-gc=boehm --enable-java-awt=xlib --enable-objc-gc --disable-multilib x86_64-linux
Thread model: posix
gcc version 3.3.5 (Debian 1:3.3.5-13)
Compilation info: CC='gcc'  CFLAGS=''  CXX='g++'  CXXFLAGS=''  LDFLAGS=''  ASFLAGS=''
LIBC: 
lrwxrwxrwx  1 root root 13 Feb 21  2007 /lib/libc.so.6 -> libc-2.3.2.so
-rwxr-xr-x  1 root root 1295328 Feb 17  2007 /lib/libc-2.3.2.so
-rw-r--r--  1 root root 3861862 Feb 17  2007 /usr/lib/libc.a
-rwxr-xr-x  1 root root 206 Feb 17  2007 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/mysql' '--with-unix-socket-path=/usr/mysql/sock/mysql.sock' '--with-mysqld-user=mysql' '--without-readline'


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx