Hello,
I have come across a possible bug in MySQL that causes NULL values to
be erroneously returned from a join.
Problem: An inner join between two tables succeeds, but one column is
erroneously returned as all NULL if the join occurs after a delete in
table 1.
Platforms Tested: 3.23.43 (Solaris, Linux, Mac OS X), 3.23.47 (Linux)
Detail:
I have attached a stripped down version of the problem tables. Perform
the following actions after loading the tables:
select t1.*, t2.name from t1, t2 where t2.id=t2_id;
Note that the column 'item_id' returns the expected answers.
delete from t1 where t1_id=97;
select t1.*, t2.name from t1, t2 where t2.id=t2_id;
Note that the column 'item_id' is now unexpectedly all NULL.
Workaround:
The provided tables are pretty close to the edge of the bug. Deleting
columns causes it to go away. Deleting some rows causes it to go away.
Thanks for looking at this, and thanks for a cool product,
John Heitmann
CombineNet
# MySQL dump 8.16
#
# Host: localhost Database: bug
#--------------------------------------------------------
# Server version 3.23.47
#
# Table structure for table 't1'
#
CREATE TABLE t1 (
t1_id int(11) default NULL,
t2_id int(11) default NULL,
type enum('Cost','Percent') default NULL,
cost_unit enum('Cost','Unit') default NULL,
min_value double default NULL,
max_value double default NULL,
t3_id int(11) default NULL,
item_id int(11) default NULL
) TYPE=MyISAM;
#
# Dumping data for table 't1'
#
INSERT INTO t1 VALUES (12,5,'Percent','Cost',-1,0,-1,-1);
INSERT INTO t1 VALUES (14,4,'Percent','Cost',-1,0,-1,-1);
INSERT INTO t1 VALUES (18,5,'Percent','Cost',-1,0,-1,-1);
INSERT INTO t1 VALUES (19,4,'Percent','Cost',-1,0,-1,-1);
INSERT INTO t1 VALUES (20,5,'Percent','Cost',100,-1,22,291);
INSERT INTO t1 VALUES (21,5,'Percent','Cost',100,-1,18,291);
INSERT INTO t1 VALUES (22,1,'Percent','Cost',100,-1,6,291);
INSERT INTO t1 VALUES (23,1,'Percent','Cost',100,-1,21,291);
INSERT INTO t1 VALUES (24,1,'Percent','Cost',100,-1,9,291);
INSERT INTO t1 VALUES (25,1,'Percent','Cost',100,-1,4,291);
INSERT INTO t1 VALUES (26,1,'Percent','Cost',100,-1,20,291);
INSERT INTO t1 VALUES (27,4,'Percent','Cost',100,-1,7,202);
INSERT INTO t1 VALUES (28,1,'Percent','Cost',50,-1,-1,137);
INSERT INTO t1 VALUES (29,2,'Percent','Cost',100,-1,4,354);
INSERT INTO t1 VALUES (30,2,'Percent','Cost',100,-1,9,137);
INSERT INTO t1 VALUES (93,2,'Cost','Cost',-1,10000000,-1,-1);
INSERT INTO t1 VALUES (97,2,'Cost','Cost',-1,10000000,-1,-1);
#
# Table structure for table 't2'
#
CREATE TABLE t2 (
id int(10) unsigned NOT NULL auto_increment,
name varchar(255) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table 't2'
#
INSERT INTO t2 VALUES (1,'s1');
INSERT INTO t2 VALUES (2,'s2');
INSERT INTO t2 VALUES (3,'s3');
INSERT INTO t2 VALUES (4,'s4');
INSERT INTO t2 VALUES (5,'s5');
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
