MySQL Bugs: #944: make test failure

Bug #944	make test failure - select_safe
Submitted:	29 Jul 2003 5:02	Modified:	21 Aug 2003 10:43
Reporter:	Neeraj Patel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Compiling	Severity:	S3 (Non-critical)
Version:	4.0.14	OS:	Linux (Linux / SuSE 8.2)
Assigned to:	Lenz Grimmer	CPU Architecture:	Any

Description:
Confugured and built MySQL 4.0.14 using same configure options used to build & install 4.0.13 successfully. Earlier attempts were built '--without-debug' and '--with-raid', but also failed. Same steps work under Redhat 9 and 8.

Server specs: Dual Xeon, 2Gb RAM.

'uname -a' output: Linux srvr1 2.4.20-64GB-SMP #1 SMP Thu Jul 10 17:28:06 UTC 2003 i686 unknown unknown GNU/Linux

RPMS (selected): 
k_smp-2.4.20-95
glibc-2.3.2-6
glibc-devel-2.3.2-6
libgcc-3.3-23
gcc-3.3-23
gcc-c++-3.3-23
libtool-1.4.3-43

I've included a diff of the result and reject files:

srvr1:/usr/local/src/mysql-4.0.14/mysql-test/r #  diff select_safe.reject select_safe.result 
72,73c72,73
< t2    ALL     b       NULL    NULL    NULL    21
< t1    ALL     b       NULL    NULL    NULL    16      Using where
---
> t1    ALL     b       NULL    NULL    NULL    21
> t2    ALL     b       NULL    NULL    NULL    16      Using where
77,78c77,78
< t2    ALL     b       NULL    NULL    NULL    21
< t1    ref     b       b       21      t2.b    6       Using where
---
> t1    ALL     b       NULL    NULL    NULL    21
> t2    ref     b       b       21      t1.b    6       Using where

How to repeat:
Run configure, make && make test - always fails on same test (select_safe)

CXX="gcc" CFLAGS="-O3" CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" ./configure  --prefix=/usr/local/mysql --with-mysqld-user=mysqladm --enable-local-infile --enable-thread-safe-client  --enable-assembler --without-berkeley-db --without-innodb --without-ssl --with-mysqld-ldflags=-all-static --disable-shared  --with-charset=latin1 --with-extra-charsets=all --with-debug

make
make test 

I also ran the test case manually:

srvr1:/usr/local/src/mysql-4.0.14/mysql-test # ./mysql-test-run --local --debug select_safe
Installing Test Databases
Removing Stale Files
Installing Master Databases
running  ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables     --basedir=. --datadir=./var/master-data --skip-innodb --skip-bdb --language=../sql/share/english/
030729 14:59:43  ../sql/mysqld: Shutdown Complete

Installing Slave Databases
running  ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables     --basedir=. --datadir=./var/slave-data --skip-innodb --skip-bdb --language=../sql/share/english/
030729 14:59:43  ../sql/mysqld: Shutdown Complete

Manager disabled, skipping manager start.
Loading Standard Test Databases
Starting Tests

 TEST                           RESULT
------------------------------------------
select_safe                    [ fail ]

Errors are (from /usr/local/src/mysql-4.0.14/mysql-test/var/log/mysqltest-time) :
/usr/local/src/mysql-4.0.14/client/mysqltest: At line 67: Result content mismatch
(the last line(s) may be the ones that caused the die() in mysqltest)
Below are the diffs between actual and expected results:
-------------------------------------------------------
*** r/select_safe.result        Fri Jul 18 18:14:38 2003
--- r/select_safe.reject        Tue Jul 29 14:59:45 2003
***************
*** 69,81 ****
  insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
  explain select * from t1,t1 as t2 where t1.b=t2.b;
  table type    possible_keys   key     key_len ref     rows    Extra
! t1    ALL     b       NULL    NULL    NULL    21
! t2    ALL     b       NULL    NULL    NULL    16      Using where
  set MAX_SEEKS_FOR_KEY=1;
  explain select * from t1,t1 as t2 where t1.b=t2.b;
  table type    possible_keys   key     key_len ref     rows    Extra
! t1    ALL     b       NULL    NULL    NULL    21
! t2    ref     b       b       21      t1.b    6       Using where
  SET MAX_SEEKS_FOR_KEY=DEFAULT;
  drop table t1;
  SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT;
--- 69,81 ----
  insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
  explain select * from t1,t1 as t2 where t1.b=t2.b;
  table type    possible_keys   key     key_len ref     rows    Extra
! t2    ALL     b       NULL    NULL    NULL    21
! t1    ALL     b       NULL    NULL    NULL    16      Using where
  set MAX_SEEKS_FOR_KEY=1;
  explain select * from t1,t1 as t2 where t1.b=t2.b;
  table type    possible_keys   key     key_len ref     rows    Extra
! t2    ALL     b       NULL    NULL    NULL    21
! t1    ref     b       b       21      t2.b    6       Using where
  SET MAX_SEEKS_FOR_KEY=DEFAULT;
  drop table t1;
  SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT;
-------------------------------------------------------
Please follow the instructions outlined at
http://www.mysql.com/doc/R/e/Reporting_mysqltest_bugs.html
to find the reason to this problem and how to report this.

Aborting. To continue, re-run with '--force'.

Ending Tests
Shutting-down MySQL daemon

Master shutdown finished
Slave shutdown finished

Suggested fix:
Use 4.0.13?

Yes, we have received other reports too of the select_safe test failing,
with exactly the same configuration - SuSE 8.2 and gcc 3.3.

We are investigating this issue as well, but could you try downgrading
to an older gcc (2.95.3 for example) to see if that solves the failure?

I built gcc 3.3 from source using:

export gcc_lang="c,c++,ada,f77,objc"
export CFLAGS="-O2" CXXFLAGS="-O2" GCJFLAGS="-O2" && ../configure --prefix=/usr/local/gcc33 --enable-shared --with-system-zlib --enable-threads=posix --disable-checking --enable-long-long --enable-cstudio=stdio --enable-clocale=generic --enable-version-specfic-runtime-libs --with-local-prefix=/usr/local/gcc33

and then built MySQL with my new gcc:
export PATH=/usr/local/gcc33/bin:$PATH
CC="gcc" CXXFLAGS="gcc" CFLAGS="-O3" CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" ./configure  --prefix=/usr/local/mysql4014 --with-mysqld-user=mysqladm --enable-local-infile --enable-thread-safe-client  --enable-assembler --without-berkeley-db --without-innodb --with-extra-charsets=complex  --with-mysqld-ldflags=-all-static  --without-raid && make

'make test' completes sucessfully

I was able to repeat this test failure here, using SuSE Linux with the gcc-3.3-43 RPMs (which is available as an update from ftp.suse.com).

It only seems to occur, if you compile with "-O3" optimization flags - using "-O2"  for compiling does not reveal this problem.

Another user reported that he compiled gcc-3.3 by himself and was not able to reproduce the problem (even when using -O3), so it might be a SuSE-specific problem with their gcc-3.3 packages.

The failed test in select_safe is a test where it's equaly good to use t1 before t2 as it is to use t2 before t1.  In other words, the "new failed" result is also correct.

I assume that this has something to how some internal floating point variables are stored in the join optimizer. If the compiler stores some of them in register and compared these to variables stored in memory. mysqld may get a difference for something that would be equal if both variables where stored in memory.

I would regard this as a bug in our test suite as we are working within the limits of the floating point precission.

I have now fixed this by forcing the join order, which should fix this problem.

The fix will be in 4.0.15