Description:
Probably it's variation of recent bug with null joins, but just in case I want to post
it here because result depends on values inserted into tables, even if these values
are not participating in join.
After data insert with nulls the same query will produce different result after add
index.
How-To-Repeat:
create table t1 (id int); #1
create table t2 (id int); #2
insert into t1 values (null), (1); #3
insert into t2 values (null), (1), (null), (2); #4
select * from t1; #5
select * from t2; #6
select * from t1, t2 where t1.id =3D t2.id; #7
alter table t2 add index iid (id); #8
select * from t1, t2 where t1.id =3D t2.id; #9
#here is output for the last 5 lines of the script
#remarkably it doesn't join null if there is no (2) value inserted in line 4
mysql select * from t1;
+--+
| id |
+--+
| NULL |
|1 |
+--+
2 rows in set (0.00 sec)
mysql select * from t2;
+--+
| id |
+--+
| NULL |
|1 |
| NULL |
|2 |
+--+
4 rows in set (0.00 sec)
mysql select * from t1, t2 where t1.id =3D t2.id;
+--+--+
| id | id |
+--+--+
|1 |1 |
+--+--+
1 row in set (0.00 sec)
mysql alter table t2 add index iid (id);
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql select * from t1, t2 where t1.id =3D t2.id;
+--+--+
| id | id |
+--+--+
| NULL | NULL |
| NULL | NULL |
|1 |1 |
+--+--+
3 rows in set (0.01 sec)
mysql
Fix:
add not null conditions in where clause on one of join values, for ex:
select * from t1, t2 where t1.id =3D t2.id and t1.id is not null;
Synopsis: synopsis of the problem (one line)
Submitter-Id: submitter ID
Originator: your name
Organization: your organization
MySQL support: none
Severity: non-critical
Priority: low
Category: mysqld, mysql client
Class: sw-bug
Release:Server version 4.0.4-beta-max-nt
Exectutable: mysqld-nt
Environment: P2 320MB RAM 6G HDD
System:Win200 SP3
Compiler: VC++ 6.0
Architecture: i
Attention:
Any views expressed in this message are those of the individual sender, except where
the message states otherwise and the sender is authorized to state them to be the
views of any such entity.
The information contained in this message and or attachments is intended only for the
person or entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or entities other
than the intended recipient is prohibited. If you received this in error, please
contact the sender and delete the material from any system and destroy any copies.
-
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
