Possible bug in 4.1.7

[Santino]

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]#
Is it a bug?
Santino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]