Hi!
On Nov 22, Dilipan Sebastiampillai wrote:I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query using LEFT JOIN gives me different result. The result from 4.0.18 does s real LEFT JOIN but 4.1.7 differs sometimes and I don't want that.
I think it comes from an 'optimization' .... how can I make a query without using the LEFT JOIN optimization of MySQL 4.1.7 ?
What is the query ? Can you provide a repeatable test case ?
Hello,
I have a query that works fine on 4.0.20 but doesn't work in 4.1.7.
I want to search all the rows of table AULE that don't have a record in table OCCUPAZIONI so the query is:
select AUL_ID, OCC_ID from
AULE
left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
where OCC_ID is null;
After some tests I find that an index changes the results.
This is a sql command file that reproduce the problem on my Linux Fedora core 1 with MySql 4.1.7 :
mysql> select version(); +----------------+ | version() | +----------------+ | 4.1.7-standard | +----------------+ 1 row in set (0.00 sec)
==============================CUT DROP DATABASE IF EXISTS bug; create database bug;
use bug;
CREATE TABLE AULE ( AUL_ID int(11) NOT NULL auto_increment, PRIMARY KEY (AUL_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE OCCUPAZIONI ( OCC_ID int(11) NOT NULL auto_increment, OCC_ID_AUL int(11) NOT NULL, OCC_DATA date, PRIMARY KEY (OCC_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO AULE VALUES (1); INSERT INTO AULE VALUES (2); INSERT INTO AULE VALUES (3);
INSERT INTO OCCUPAZIONI VALUES (1, 1, '2004-11-10');
select "Before index";
select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
where OCC_ID is null;
alter table OCCUPAZIONI
add KEY OCC_ID_AUL (OCC_ID_AUL);select "After Index";
select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
where OCC_ID is null;
==============================CUT
Results: [EMAIL PROTECTED] bugs]# mysql < v.sql Before index Before index AUL_ID OCC_ID 2 NULL 3 NULL After Index After Index [EMAIL PROTECTED] bugs]#
Bug #6307 I noticed that when I create a table with 2 index (primary + key) the Cardinality of the primary key is 0 but the Cardinality of the key is null. If I insert a record in the table the Cardinality of the primary key is 1 but the Cardinality of the key is null. If I do an analyze TABLE ... the Cardinality of both index is OK and the query works but with a truncate table ... the Cardinality of the key is null. If I create the table without the second index, add some records and add the second index the cardinality is null.
At the moment I found that if I define a composite primary key ( old PRIMARY+KEY) the query works without the analyze.
Santino
