[debian-mysql] Bug#439021: mysql-server-5.0: 0+string not cast as integer when comparing with indexed enum

[Frédéric Brière]

Package: mysql-server-5.0
Version: 5.0.45-1
Severity: normal

  CREATE TABLE foo (
  	flavor ENUM('vanilla', 'chocolate') NOT NULL,
	number INT UNSIGNED NOT NULL,
	INDEX (flavor, number)
  );

  EXPLAIN SELECT * FROM foo WHERE flavor = CAST('1' AS UNSIGNED)\G
  [...]
  	key: flavor
	ref: const

  EXPLAIN SELECT * FROM foo WHERE flavor = 0+'1'\G
  [...]
  	key: flavor
	ref: NULL


Shouldn't 0+'1' be cast as a constant int, thus matching the index?  We
can see this is not the case, after inserting 10,000 random rows:

  SELECT SQL_NO_CACHE COUNT(*) FROM foo t1 WHERE EXISTS
    (SELECT * FROM foo t2 WHERE t2.flavor = 1 and t2.number = t1.number);
  [...]
  1 row in set (0.17 sec)

  SELECT SQL_NO_CACHE COUNT(*) FROM foo t1 WHERE EXISTS
    (SELECT * FROM foo t2 WHERE t2.flavor = 0+'1' and t2.number = t1.number);
  [...]
  1 row in set (1 min 48.82 sec)


(Yes, I know this example sounds contrieved, but DBD::mysql stringifies
everything, and I need to work with enum indexes.  And yes, I'm
switching to CAST.  <g>)


-- System Information:
Debian Release: lenny/sid
  APT prefers unstable
  APT policy: (500, 'unstable')
Architecture: i386 (i686)

Kernel: Linux 2.6.21-2-k7 (SMP w/1 CPU core)
Locale: LANG=en_CA.UTF-8, LC_CTYPE=en_CA.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash

Versions of packages mysql-server-5.0 depends on:
ii  adduser                 3.104            add and remove users and groups
ii  debconf [debconf-2.0]   1.5.14           Debian configuration management sy
ii  libc6                   2.6.1-1          GNU C Library: Shared libraries
ii  libdbi-perl             1.58-1           Perl5 database interface by Tim Bu
ii  libgcc1                 1:4.2.1-4        GCC support library
ii  libmysqlclient15off     5.0.45-1         MySQL database client library
ii  libncurses5             5.6+20070812-1   Shared libraries for terminal hand
ii  libreadline5            5.2-3            GNU readline and history libraries
ii  libstdc++6              4.2.1-4          The GNU Standard C++ Library v3
ii  libwrap0                7.6.dbs-14       Wietse Venema's TCP wrappers libra
ii  lsb-base                3.1-24           Linux Standard Base 3.1 init scrip
ii  mysql-client-5.0        5.0.45-1         MySQL database client binaries
ii  mysql-common            5.0.45-1         MySQL database common files
ii  passwd                  1:4.0.18.1-11    change and administer password and
ii  perl                    5.8.8-7          Larry Wall's Practical Extraction 
ii  psmisc                  22.5-1           Utilities that use the proc filesy
ii  zlib1g                  1:1.2.3.3.dfsg-5 compression library - runtime

Versions of packages mysql-server-5.0 recommends:
ii  mailx            1:8.1.2-0.20070424cvs-1 A simple mail user agent

-- debconf information:
  mysql-server-5.0/really_downgrade: false
  mysql-server-5.0/need_sarge_compat: false
  mysql-server-5.0/start_on_boot: true
  mysql-server/error_setting_password:
  mysql-server-5.0/nis_warning:
  mysql-server-5.0/postrm_remove_databases: false
  mysql-server-5.0/need_sarge_compat_done: true