MySQL Bugs: #3364: Join-query error

Bug #3364	Join-query error
Submitted:	2 Apr 2004 3:55	Modified:	3 Apr 2004 9:17
Reporter:	Vadims Zemlanojs	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	Connector/J 3.1.1-alpha	OS:	Linux (Linux 2.4 RedHet9)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
In Connector/J 3.1.1-alpha:

select  curr_list.name,rates.date_,rates.rate from test.rates , test.curr_list where (curr_list.CURR_ID=rates.CURR_ID) and (curr_list.name like 'USD');

gets more rows than the same using mysql-connector-java-3.0.10-stable-bin.jar
MySql server version: 4.1.1-alpha-standard.

Script:

CREATE TABLE curr_list
(CURR_ID INT  NOT NULL,
 IS_MAIN CHAR(1) DEFAULT 'N',
 NAME VARCHAR(127) NOT NULL,
 NOTE VARCHAR(255),
 TELLER_ID INT,
 TYPE_OF_RECORD INT DEFAULT 0 NOT NULL ) ;
ALTER TABLE  curr_list ADD  PRIMARY KEY(CURR_ID);

insert into curr_list(CURR_ID,NAME,IS_MAIN,TYPE_OF_RECORD) values(1,'USD','N',0);
insert into curr_list(CURR_ID,NAME,IS_MAIN,TYPE_OF_RECORD) values(2,'EUR','Y',0);
insert into curr_list(CURR_ID,NAME,IS_MAIN,TYPE_OF_RECORD) values(3,'RUB','N',0);

CREATE TABLE rates
(RATE_ID INT  NOT NULL AUTO_INCREMENT,
 DATE_ DATE  NOT NULL,
 CURR_ID INT NOT NULL,
 RATE DOUBLE(16,2) DEFAULT 1 NOT NULL,
 NAME VARCHAR(127) NOT NULL,
 NOTE VARCHAR(255),
 TELLER_ID INT,
 TYPE_OF_RECORD INT DEFAULT 0 NOT NULL,
  PRIMARY KEY (RATE_ID) )  ;

ALTER TABLE  rates ADD CONSTRAINT rates_currlist_currid_fk FOREIGN KEY(CURR_ID) REFERENCES curr_list(CURR_ID) on delete cascade ;
insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(1,1,'USD',0.9,'2003-1-1');
insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(2,2,'EUR',0.9,'2003-3-3');
insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(3,3,'RUB',0.03,'2003-2-2');

insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(6,1,'USD',0.91,'2003-1-21');
insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(4,2,'EUR',1,'2003-3-23');
insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(5,3,'RUB',0.03,'2003-2-22');

insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(7,1,'USD',0.92,'2003-1-24');
insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(8,2,'EUR',1,'2003-3-24');
insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(9,3,'RUB',0.032,'2003-2-24');

insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(10,1,'USD',0.92,'2003-1-25');
insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(11,2,'EUR',1,'2003-3-25');
insert into  rates(RATE_ID,CURR_ID,NAME,RATE,DATE_) values(12,3,'RUB',0.032,'2003-2-25');

How to repeat:
I don't know

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

I can't repeat this with standard statements.

If you're using PreparedStatements, then you should either build MySQL-4.1 from BitKeeper, or alternatively wait for MySQL-4.1.2, as many bugs similar to this in the server-side prepared statement code are fixed in MySQL-4.1.2 (the optimizer was incorrectly removing conditions in some cases).

You can also provide a _full_ testcase which also contains the Java code to reproduce this error and set this bug back to 'open' state.

It could be my mistake. Pure ResultSet works right. I used my old program with old java components. Then, error is - with different drivers "select" gets different result.

I've tried a little test with PreparedStatement.
There is en error without my old code, which I have mentioned above.

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.25.1/test:3306","test","test");

PreparedStatement stmt0 = conn.prepareStatement("select  curr_list.name,rates.date_,rates.rate from test.rates , test.curr_list where (curr_list.CURR_ID=rates.CURR_ID) and(curr_list.name like 'RUB')");

ResultSet rs  = stmt0.executeQuery();

This ResultSet has 4 records with old driver and 12 with new one.
Changing 'RUB' to 'USD' will have 4 records with old driver and 0 with new one.