Hi!
On Nov 22, Santino wrote:
> At 15:23 +0100 22-11-2004, Sergei Golubchik wrote:
> >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 ?
>
> I have a query that works fine on 4.0.20 but doesn't work in 4.1.7.
Thanks for the test case.
I could repeat the bug using the 4.1.7 distribution, but not the latest
4.1.8 tree - it means that the bug was apparently fixed since 4.1.7
release.
> ==============================CUT
> 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
>
> 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.
I am not sure I understand :(
What do you mean "query works" ?
Regards,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
