Bug #26207 inserts don't work with shortened index
Submitted: 9 Feb 2007 6:18 Modified: 13 Apr 2007 18:03
Reporter: mike sherwood Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.22, 5.1 BK, 4.1 BK OS:Microsoft Windows (windows)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: INDEX, inserts

[9 Feb 2007 6:18] mike sherwood
Description:
I have no idea what to call this, but it is easy to replicate with the steps below.

If I put an index of length shorter than the field it is based on, I can't insert a row into a new table if that row has a value of that field longer than the index but shorter than the field definition. 

How to repeat:
Here's the sequence: 

1.Create two tables. table 1 is the source. table2 will be getting the inserts. table1 has an index (ix_org) which is 10 long, while org, the field it is based on is 30 long. 

create table table1 ( 
name varchar(30), 
place smallint, 
org varchar(30), 
index ix_org (org(10)) 
) engine = MyISAM; 

create table table2 ( 
name varchar(30), 
place smallint, 
org varchar(30), 
place_total varchar(10) 
) engine = MyISAM; 

2. Insert some data into table1. Two rows: one row with a "org" field length shorter than 10 and another with a field length longer than 10. 

insert into table1 (name, place, org) 
values ('Joe','5','7_chars'), 
('Fred','10','13_characters'); 

3. now do the inserts 

test 1: 

insert into table2 select 
name as name, 
place as place, 
org as org, 
concat(place,'/', (select max(place) from table1 where org='7_chars')) as place_total 
from table1 t1; 

Result: all is good. in this case we are joining (in the subquery) on a value of "org" which is less than 10 characters > two rows inserted. 

test 2: 

(now join in the subquery with field length of org longer than 13) 

insert into table2 select 
name as name, 
place as place, 
org as org, 
concat(place,'/', (select max(place) from table1 where org='13_characters')) as place_total 
from table1 t1; 

Result: does not insert. error "data too long for column org at row1".   Should have inserted the rows.

Other tests: 
* if the length of the index is made the same length as the field, the error doesn't occur. 
* if the subquery is not in the insert command, the error doesn't occur.
[9 Feb 2007 10:52] Sveta Smirnova
Thank you for the report.

Please provide output of SELECT @@sql_mode;;
[10 Feb 2007 1:35] mike sherwood
here is the result of SELECT @@sql_mode;

STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[12 Feb 2007 7:57] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read carefully about STRICT_TRANS_TABLES at http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
[12 Feb 2007 9:39] Sveta Smirnova
Sorry: was wrong in previous comment.

Verified as described. All versions are affected.

If omit STRICT_TRANS_TABLES, we get warning "Data truncated for column 'org' at row 1"
[1 Mar 2007 12:56] 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/20896

ChangeSet@1.2425, 2007-03-01 14:56:24+02:00, gkodinov@magare.gmz +3 -0
  Bug #26207: When making the key image to use
   in index search MySQL was not explicitly
   suppressing warnings. And if the context 
   happens to enable warnings (e.g. INSERT ..
   SELECT) the warnings resulting from converting 
   the constant the key is compared to are 
   reported to the client.
   Fixed by suppressing warnings when converting
   the constants to the same type as the key parts.
[22 Mar 2007 15:22] 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/22650

ChangeSet@1.2425, 2007-03-22 17:22:30+02:00, gkodinov@magare.gmz +3 -0
  Bug #26207: When making the key image to use
   in index search MySQL was not explicitly
   suppressing warnings. And if the context 
   happens to enable warnings (e.g. INSERT ..
   SELECT) the warnings resulting from converting 
   the data the key is compared to are 
   reported to the client.
   Fixed by suppressing warnings when converting
   the data to the same type as the key parts.
[22 Mar 2007 16:44] 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/22664

ChangeSet@1.2425, 2007-03-22 18:44:16+02:00, gkodinov@magare.gmz +3 -0
  Bug #26207: When making the key image to use
   in index search MySQL was not explicitly
   suppressing warnings. And if the context 
   happens to enable warnings (e.g. INSERT ..
   SELECT) the warnings resulting from converting 
   the data the key is compared to are 
   reported to the client.
   Fixed by suppressing warnings when converting
   the data to the same type as the key parts.
[23 Mar 2007 13:54] Alexey Botchkov
Pushed in 5.0.40 and 5.1.18
[13 Apr 2007 18:03] Paul Dubois
Noted in 5.0.40, 5.1.18 changelogs.

For INSERT INTO ... SELECT where index searches used column prefixes,
insert errors could occur when key value type conversion was done.