>Description: MAX fails to return the correct value in some MERGE table situations.
Based on experimentation I am guessing that the query fails to read all of the member tables when the query can be performed entirely by reading the index. I seem to be able to reproduce this every time on MySQL 3.23.51, .55, and one of the versions in between (but I forget which). I don't believe this is the same as any of the known bugs listed in "MERGE table problems" in the docs. But I could be wrong. >How-To-Repeat: Here is a sequence of commands that can be source-d: -- optional drop table if exists whole; drop table if exists part1; drop table if exists part2; -- create tables create table part1 (id int(10) not null, value int(10), key (id, value)); create table part2 (id int(10) not null, value int(10), key (id, value)); create table whole (id int(10) not null, value int(10), key (id, value)) type=merge union=(part1,part2); -- insert some values insert into part1 values (1, 100), (2, 200), (3, 300); insert into part2 values (1, 200), (5, 500); -- this correctly shows all 5 rows select * from whole; -- this correctly shows the 2 values where id = 1 select value from whole where id = 1; -- this ought to say 200 but says 100 instead select max(value) from whole where id = 1; -- this also ought to say 200 but says 100 instead select floor(max(value)) from whole where id = 1; -- this correctly says 200 select max(floor(value)) from whole where id = 1; -- this also correctly says 200 select id, max(value) from whole where id = 1 group by id; >Fix: Workaround: in the query, replace MAX(expr) with MAX(FUNC(expr)) where FUNC is a no-op. The original problem arose with a datetime column (I changed it to an int for the test case above), so e.g. FROM_UNIXTIME(MAX(UNIX_TIMESTAMP(tstamp))) works around the bug. >Submitter-Id: >Originator: S. Spencer Sun >Organization: >MySQL support: none >Synopsis: MAX fails to return the correct value in some MERGE table situations >Severity: serious >Priority: high >Category: mysql >Class: sw-bug >Release: mysql-3.23.55 (Source distribution) >Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for pc-linux 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 3.23.55-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 10 min 22 sec Threads: 1 Questions: 121 Slow queries: 0 Opens: 35 Flush tables: 1 Open tables: 6 Queries per second avg: 0.195 >Environment: System: Linux pratt 2.2.22-6.2.2smp #2 SMP Tue Oct 22 20:32:19 PDT 2002 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O3 -mpentiumpro' CXX='gcc' CXXFLAGS='-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Mar 13 2001 /lib/libc.so.6 -> libc-2.1.3.so -rwxr-xr-x 1 root root 4106572 Sep 9 09:58 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 20336836 Sep 9 09:58 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Sep 9 09:58 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local' '--localstatedir=/var/mysql' '--enable-assembler' '--with-mysqld-ldflags=-all-static' '--with-raid' '--enable-local-infile' 'CFLAGS=-O3 -mpentiumpro' 'CXXFLAGS=-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti' '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