Bug #48537 difference of index selection between rpm binary and .tar.gz, windows vs linux..

• Submitted: 4 Nov 2009 17:20
• Modified: 14 Oct 2010 13:45
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.0.84sp1, 5.0.87, 5.1.40
• OS: Linux (32-bit)
• Assigned to: Alexey Kopytov
• CPU Architecture: Any

[4 Nov 2009 17:20] Shane Bester

Description:
When comparing the EXPLAIN output of a simple query against a freshly created myisam table, the mysqld from the rpm package differs in result to the one from the .tar.gz generic linux package.

e.g
result using:
mysql-enterprise-gpl-5.0.87-0.rhel5.i386.tar

explain (cutted)
-------
key_len ref     rows    Extra
15      const,const,const       3       Using where
-------

is different to the result from mysqld in:
mysql-enterprise-gpl-5.0.87-linux-i686-glibc23.tar.gz

explain (cutted)
-------
key_len ref     rows    Extra
11      const,const     3       Using where
-------

How to repeat:
install the rpm version. import the testcase.
install the .tar.gz version, import the testcase.

[5 Nov 2009 5:08] MySQL Verification Team

Here's a public testcase:
-------------------------

drop table if exists t1;
create table t1(
`c1` int,`c2` int,`c4` int,`c5` int
,key(`c2`,`c5`),key(`c2`,`c4`,`c5`)
)engine=myisam;

insert into t1 values(4,1,1,1);
insert into t1 values(3,1,1,1);
insert into t1 values(2,1,1,1);
insert into t1 values(1,1,1,1);

explain select c1 
from t1 where 
c2 = 1 and 
c4 = 1 and
c5 = 1;

select c1 
from t1 where 
c2 = 1 and 
c4 = 1 and
c5 = 1;

Output from mysql-advanced-gpl-5.1.40-linux-i686-glibc23.tar.gz:

+------+---------------+------+---------+-------------+------+
| type | possible_keys | key  | key_len | ref         | rows |
+------+---------------+------+---------+-------------+------+
| ref  | c2,c2_2       | c2   | 10      | const,const |    3 |
+------+---------------+------+---------+-------------+------+

Output from mysql-advanced-gpl-5.1.40-0.rhel5.i386.tar:

+------+---------------+------+---------+-------------------+------+
| type | possible_keys | key  | key_len | ref               | rows |
+------+---------------+------+---------+-------------------+------+
| ref  | c2,c2_2       | c2_2 | 15      | const,const,const |    3 |
+------+---------------+------+---------+-------------------+------+

[5 Nov 2009 5:33] Valeriy Kravchuk

With recent 5.1.41 from bzr, built using our BUILD/compile-pentium-debug-max script on Mac OS X, I've got shorter (c2) index used.

[5 Nov 2009 6:17] MySQL Verification Team

I made a comparison of debug vs non-debug binaries:

.tar.gz mysqld shows: 
| c2,c2_2       | c2   | 10      | const,const |    3 |

.tar.gz mysqld-debug shows:
| c2_2 | 15      | const,const,const |    3 |

rpm mysqld shows:
| c2_2 | 15      | const,const,const |    3 |

rpm mysqld-debug shows:
| c2_2 | 15      | const,const,const |    3 | Using where |

no-install 64-bit windows mysqld.exe shows:
| c2   | 10      | const,const |    3 | Using where |

no-install 64-bit windows mysqld-debug.exe shows:
| c2   | 10      | const,const |    3 |

So I'm guessing somewhere the code in the optimizer is doing something unsafe
to get different results based on compiler settings.

Note: when running the RPM and tar.gz mysqld under valgrind, they give the same outputs.

[5 Nov 2009 10:29] MySQL Verification Team

With server compiled with: compile-amd64-max. I got:

+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | c2,c2_2       | c2   | 10      | const,const |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.02 sec)

[6 Nov 2009 9:29] MySQL Verification Team

folks, let's get this repeatable first.  i used:
mysql-enterprise-gpl-5.0.87-0.rhel5.i386.tar
vs
mysql-enterprise-gpl-5.0.87-linux-i686-glibc23.tar.gz

downloaded from our enterprise.mysql.com.  in this tricky case, i suspect that own-compiled or different versions can impact the results :)

[8 Nov 2009 20:40] Valeriy Kravchuk

Verified just as described on RHEL 5.1. We need our optimizer to return predictable execution plans whatever binaries (of the same version), compilation options (or order of index creation) we have.

[8 Nov 2009 20:45] Valeriy Kravchuk

RPMs are built using:

gcc version 4.1.2 20070626 (Red Hat 4.1.2-14)

ompilation info (used): CC='gcc'  CFLAGS=' -DDBUG_OFF -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables  '  CXX='gcc'  CXXFLAGS=' -DDBUG_OFF -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables   -fno-implicit-templates -fno-exceptions -fno-rtti'  LDFLAGS=' -rdynamic '  ASFLAGS=''

while .tar.gz binaries arte built using:

gcc version 4.1.2 20070626 (Red Hat 4.1.2-14)
                                   
Compilation info (call): CC='ccache /usr/local/gcc-4.3.2/bin/gcc -static-libgcc'  CFLAGS='-g -O3 -march=i686'  CXX='ccache /usr/local/gcc-4.3.2/bin/gcc -static-libgcc'  CXXFLAGS='-g -O3 -march=i686'  LDFLAGS=''  ASFLAGS=''                  Compilation info (used): CC='ccache /usr/local/gcc-4.3.2/bin/gcc -static-libgcc'  CFLAGS=' -DDBUG_OFF -g -O3 -march=i686  '  CXX='ccache /usr/local/gcc-4.3.2/bin/gcc -static-libgcc'  CXXFLAGS=' -DDBUG_OFF -g -O3 -march=i686   -fno-implicit-templates -fno-exceptions -fno-rtti'  LDFLAGS=' -rdynamic '  ASFLAGS=''

according to mysqlbug utility. I see clear difference (-O2 vs -O3, for example). Looks like -O3 leads to wrong (OK, not optimal) plan.

[23 Jan 2010 8:07] MySQL Verification Team

After thinking about this, it's really I2.  It can affect anybody on linux and windows and possibly other platforms.  Different explain plans for no logical reason makes customers and support's life unpredictable.  Migrations, upgrades can become random adventures due to queries malfunctioning.

How can simple queries be troubleshooted when explain plans are random?
My comment of "[5 Nov 2009 7:17] Shane Bester" shows *one* example.  How many more  possibilities are there?  I think a root cause analysis by a developer should be done asap.

[29 May 2010 18:17] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109541

3394 Alexey Kopytov	2010-05-29
      Bug #48537: difference of index selection between rpm binary
                  and .tar.gz, windows vs linux..
      
      On Intel x86 machines index selection by the MySQL query
      optimizer could sometimes depend on the compiler version and
      optimization flags used to build the server binary.
      
      The problem was a result of a known issue with floating point
      calculations on x86: since internal FPU precision (80 bit)
      differs from precision used by programs (32-bit float or 64-bit
      double), the result of calculating a complex expression may
      depend on how FPU registers are allocated by the compiler and
      whether intermediate values are spilled from FPU to memory. In
      this particular case compiler versions and optimization flags
      had an effect on cost calculation when choosing the best index
      in best_access_path().
      
      A possible solution to this problem which has already been
      implemented in mysql-trunk is to limit FPU internal precision
      to 64 bits. So the fix is a backport of the relevant code to
      5.1 from mysql-trunk.
     @ configure.in
        Configure check for fpu_control.h
     @ mysql-test/r/explain.result
        Test case for bug #48537.
     @ mysql-test/t/explain.test
        Test case for bug #48537.
     @ sql/mysqld.cc
        Backport of the code to switch FPU on x86 to 64-bit precision.

[17 Jun 2010 6:12] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615145247-8bj0vmuqlotbqsn9) (version source revid:[e-mail]-20100602100126-6uenzzbjgr0h2c70) (merge vers: 5.5.5-m3) (pib:16)

[17 Jun 2010 6:16] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615150216-cubqoyn1fj9b6a2p) (version source revid:[e-mail]-20100607075151-xjus8wzgjv3g9ok0) (pib:16)

[15 Jul 2010 20:02] Paul DuBois

Noted in 5.5.5 changelog.

On Intel x86 machines, the optimizer could choose different execution
plans for a query depending on the compiler version and optimization
flags used to build the server binary. 

Setting report to Need Merge pending push to 5.1.x.

[11 Oct 2010 15:48] MySQL Verification Team

why is this bug in 'Need Merge' status for 3 months? is it going into 5.1 or not?

[13 Oct 2010 17:02] Alexey Kopytov

Hm, no idea about the status. I see that the changeset is present in mysql-5.1, and the fix should be released in mysql-5.1.48. 

Some glitch with push triggers?

[14 Oct 2010 8:33] Bugs System

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:[e-mail]-20101014082627-jrmy9xbfbtrebw3c) (version source revid:[e-mail]-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)

[14 Oct 2010 8:48] Bugs System

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:[e-mail]-20101014083757-5qo48b86d69zjvzj) (version source revid:[e-mail]-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)

[14 Oct 2010 9:03] Bugs System

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:[e-mail]-20101014084420-y54ecj85j5we27oa) (version source revid:[e-mail]-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)

[14 Oct 2010 13:45] Jon Stephens

Added 5.1.48 changelog entry per comment above; no additional changelog entries required. Closed.