MySQL Bugs: #10124: in joins, substring() affects how MySQL converts string columns to numbers

Bug #10124	in joins, substring() affects how MySQL converts string columns to numbers
Submitted:	24 Apr 2005 10:39	Modified:	10 Jul 2005 16:39
Reporter:	Martin Waite	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.11-standard-log	OS:	Linux (Linux ia32)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:

Normally, I expect strings which cannot be converted to a number to be treated as zero.  However, in a join where a substring() is taken from the column to be converted, something goes wrong.

How to repeat:
create table first( str varchar(20) not null, primary key(str) );
create table second( num int not null, primary key(num) );

insert into first values( 'notanumber' );
insert into second values(0),(1);

mysql> select first.str, second.num from first, second where second.num = first.str;
+------------+-----+
| str        | num |
+------------+-----+
| notanumber |   0 |
+------------+-----+
1 row in set (0.00 sec)

mysql> select first.str, second.num from first, second where second.num = substring(first.str from 1 for 6 );
Empty set (0.00 sec)

mysql> select first.str, second.num from first, second where second.num = substring( 'notanumber' from 1 for 6 );
+------------+-----+
| str        | num |
+------------+-----+
| notanumber |   0 |
+------------+-----+
1 row in set (0.00 sec)

works as expected in 4.0.24 but fails with 4.1.11 and 5.0.4

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/internals/26133

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/internals/26366

ChangeSet
  1.2311 05/06/23 06:15:50 igor@rurik.mysql.com +6 -0
  func_str.result, func_str.test:
    Added a test case for bug #10124.
  sql_select.h, item_subselect.cc, sql_select.cc:
    Fixed bug #10124.
    The copy method of the store_key classes can return
    STORE_KEY_OK=0, STORE_KEY_FATAL=1, STORE_KEY_CONV=2 now.
  field.cc:
    Fixed bug #10124.
    When ussuing a warning the store methods return 2 instead of 1 now.

Fix will appear in 4.1.13 and 5.0.9

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in change history for 4.1.13 and 5.0.9; closed.