Bug #6284 Warnings on SELECT ... INSERT with join on partial index
Submitted: 27 Oct 2004 14:09 Modified: 3 Dec 2004 14:00
Reporter: Tom Cunningham Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0.18-standard and 4.1.2-alpha- OS:Any (any)
Assigned to: Sergei Golubchik CPU Architecture:Any

[27 Oct 2004 14:09] Tom Cunningham
Description:
Get a lot of "column truncated" warnings when doing certain SELECT ... INSERTS.

It only happens when I have a partial index on the email column which I'm doing the join on.

It's no actual partiular inconvenience, except a lot of confusing warnings.

How to repeat:
DROP TABLE IF EXISTS customers;
CREATE TABLE `customers` (
 `id` bigint(20) NOT NULL auto_increment,
 `email` varchar(255) NOT NULL default '',
 PRIMARY KEY  (`id`),
 UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

drop table if exists user;
CREATE TABLE `user` (
 `user_id` int(11) NOT NULL auto_increment,
 `email` varchar(255) NOT NULL default '',
 PRIMARY KEY  (`user_id`),
 KEY `user_id` (`user_id`),
 KEY `email` (`email`(15))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into user (user_id, email)
values
(    5122 , 'xxx@adomain.co.uk' ),
(    5143 , 'xxx@bdomain.co.uk'    );

INSERT INTO customers (id)
SELECT null
 FROM user
 LEFT JOIN user dup_user
   ON dup_user.user_id>user.user_id
  AND user.email=dup_user.email
LIMIT 1;

show warnings;
[27 Oct 2004 14:49] Victoria Reznichenko
Verified with 4.1.8-debug-log
[27 Oct 2004 18:32] Sinisa Milivojevic
This "bug" is not a showstopper.

It just has to skip column length if a field in select list is not Item_field.
[3 Dec 2004 14:00] Sergei Golubchik
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:

fixed in 4.1.8