Bug #9777 Empty set returned by Prepared Statement when it should return a non empty one
Submitted: 8 Apr 2005 22:17 Modified: 9 May 2005 2:53
Reporter: Radu Chiriac Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.11 OS:Linux (Linux (FC3))
Assigned to: Konstantin Osipov CPU Architecture:Any

[8 Apr 2005 22:17] Radu Chiriac 
Description:
Something was broken from mysql 4.1.10 -> 4.1.11 on Linux

A prepared statement that returned a row on 4.1.10 returns an empty set on 4.1.11 an identical DB.

MySQL: mysql-standard-4.1.11-pc-linux-gnu-i686.tar.gz

How to repeat:
admin_user.client_id is FK to client.id

On MySQL 4.1.11:

prepare stmt1 from 'SELECT t0_u.id FROM admin_user t0_u, client t1_u_client WHERE (t1_u_client.id = ? AND t0_u.client_id=t1_u_client.id)'
--------------

Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a = 1;
--------------
set @a = 1
--------------

Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt1 using @a;
--------------
execute stmt1 using @a
--------------

Empty set (0.00 sec)

Same steps on 4.1.10 (on an identical DB) return:

--------------
execute stmt1 using @a
--------------

+----+
| id |
+----+
|  3 |
+----+
[9 Apr 2005 7:55] Geert Vanderkelen 
Hi Radu,

I could not reproduce. Double check your query without preparing it, so directly execute it.

Regards,

Geert
[10 Apr 2005 16:27] Radu Chiriac 
Hi, here's the script that reproduces the problem on my machine:

--  start of the script that reproduces bug9777 on mysql-4.1.11 on Linux FC3

create database bug9777 /*!40100 DEFAULT CHARACTER SET utf8 */;

use bug9777;

CREATE TABLE `client` (
   `id` bigint(20) NOT NULL auto_increment,
   `code` varchar(20) character set utf8 collate utf8_bin NOT NULL default '',
   `company_name` varchar(250) character set utf8 collate utf8_bin default NULL,
   `company_size` int(11) default NULL,
   `phone` varchar(20) character set utf8 collate utf8_bin default NULL,
   `referrer` varchar(250) character set utf8 collate utf8_bin default NULL,
   `address1` varchar(250) character set utf8 collate utf8_bin default NULL,
   `address2` varchar(250) character set utf8 collate utf8_bin default NULL,
   `address3` varchar(250) character set utf8 collate utf8_bin default NULL,
   `city` varchar(100) character set utf8 collate utf8_bin default NULL,
   `state` varchar(60) character set utf8 collate utf8_bin default NULL,
   `zip` varchar(60) character set utf8 collate utf8_bin default NULL,
   `country` varchar(250) character set utf8 collate utf8_bin default NULL,
   `service_level` varchar(250) character set utf8 collate utf8_bin default NULL,
   `setup_mode` tinyint(4) default NULL,
   `start_date` datetime default NULL,
   PRIMARY KEY  (`id`),
         UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `admin_user` (
   `id` bigint(20) NOT NULL auto_increment,
   `email` varchar(250) character set utf8 collate utf8_bin default NULL,
   `name` varchar(250) character set utf8 collate utf8_bin default NULL,
   `client_id` bigint(20) default NULL,
   `password` varchar(250) character set utf8 collate utf8_bin default NULL,
   `primary_contact` tinyint(4) NOT NULL default '0',
   `email_opt_in` tinyint(4) NOT NULL default '1',
   PRIMARY KEY  (`id`),
   UNIQUE KEY `email` (`email`),
   KEY `client_id` (`client_id`),
   CONSTRAINT `admin_user_fk1` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into client values (1, 'demo', 'Demo S', -1, 'na', '', 'na', 'na', NULL, 'na', 'na', 'na', 'UNITED STATES', NULL, 0, current_date());
insert into client(id, code, company_name, setup_mode, start_date) values (2, 'code2', 'Name 2', 0, current_date());
insert into client(id, code, company_name, setup_mode, start_date) values (3, 'code3', 'Name 3', 0, current_date());

insert into admin_user values (2, 'email1', 'name1', 3, 'password1', 0, 0);
insert into admin_user values (3, 'email2', 'name1', 1, 'password2', 1, 0);
insert into admin_user values (5, 'email3', 'name3', 2, 'password3', 0, 0);

prepare stmt1 from 'SELECT t0_u.id FROM admin_user t0_u, client t1_u_client WHERE (t1_u_client.id = ? AND t0_u.client_id=t1_u_client.id)';
set @a = 1;
execute stmt1 using @a;

SELECT t0_u.id FROM admin_user t0_u, client t1_u_client WHERE (t1_u_client.id = 1 AND t0_u.client_id=t1_u_client.id);

-- here's the last lines of the output that i got on my machine:
--
--
-- mysql> prepare stmt1 from 'SELECT t0_u.id FROM admin_user t0_u, client t1_u_client WHERE (t1_u_client.id = ? AND t0_u.client_id=t1_u_client.id)';
-- Query OK, 0 rows affected (0.00 sec)
-- Statement prepared
-- 
-- mysql> set @a = 1;
-- Query OK, 0 rows affected (0.00 sec)
-- 
-- mysql> execute stmt1 using @a;
-- Empty set (0.00 sec)
-- 
-- mysql>
-- mysql> SELECT t0_u.id FROM admin_user t0_u, client t1_u_client WHERE (t1_u_client.id = 1 AND t0_u.client_id=t1_u_client.id);
-- +----+
-- | id |
-- +----+
-- |  3 |
-- +----+
--
[10 Apr 2005 16:30] Radu Chiriac 
Hi, here's the script that reproduces the problem on my machine:

--  start of the script that reproduces bug9777 on mysql-4.1.11 on Linux FC3

create database bug9777 /*!40100 DEFAULT CHARACTER SET utf8 */;

use bug9777;

CREATE TABLE `client` (
   `id` bigint(20) NOT NULL auto_increment,
   `code` varchar(20) character set utf8 collate utf8_bin NOT NULL default '',
   `company_name` varchar(250) character set utf8 collate utf8_bin default NULL,
   `company_size` int(11) default NULL,
   `phone` varchar(20) character set utf8 collate utf8_bin default NULL,
   `referrer` varchar(250) character set utf8 collate utf8_bin default NULL,
   `address1` varchar(250) character set utf8 collate utf8_bin default NULL,
   `address2` varchar(250) character set utf8 collate utf8_bin default NULL,
   `address3` varchar(250) character set utf8 collate utf8_bin default NULL,
   `city` varchar(100) character set utf8 collate utf8_bin default NULL,
   `state` varchar(60) character set utf8 collate utf8_bin default NULL,
   `zip` varchar(60) character set utf8 collate utf8_bin default NULL,
   `country` varchar(250) character set utf8 collate utf8_bin default NULL,
   `service_level` varchar(250) character set utf8 collate utf8_bin default NULL,
   `setup_mode` tinyint(4) default NULL,
   `start_date` datetime default NULL,
   PRIMARY KEY  (`id`),
         UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `admin_user` (
   `id` bigint(20) NOT NULL auto_increment,
   `email` varchar(250) character set utf8 collate utf8_bin default NULL,
   `name` varchar(250) character set utf8 collate utf8_bin default NULL,
   `client_id` bigint(20) default NULL,
   `password` varchar(250) character set utf8 collate utf8_bin default NULL,
   `primary_contact` tinyint(4) NOT NULL default '0',
   `email_opt_in` tinyint(4) NOT NULL default '1',
   PRIMARY KEY  (`id`),
   UNIQUE KEY `email` (`email`),
   KEY `client_id` (`client_id`),
   CONSTRAINT `admin_user_fk1` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into client values (1, 'demo', 'Demo S', -1, 'na', '', 'na', 'na', NULL, 'na', 'na', 'na', 'UNITED STATES', NULL, 0, current_date());
insert into client(id, code, company_name, setup_mode, start_date) values (2, 'code2', 'Name 2', 0, current_date());
insert into client(id, code, company_name, setup_mode, start_date) values (3, 'code3', 'Name 3', 0, current_date());

insert into admin_user values (2, 'email1', 'name1', 3, 'password1', 0, 0);
insert into admin_user values (3, 'email2', 'name1', 1, 'password2', 1, 0);
insert into admin_user values (5, 'email3', 'name3', 2, 'password3', 0, 0);

prepare stmt1 from 'SELECT t0_u.id FROM admin_user t0_u, client t1_u_client WHERE (t1_u_client.id = ? AND t0_u.client_id=t1_u_client.id)';
set @a = 1;
execute stmt1 using @a;

SELECT t0_u.id FROM admin_user t0_u, client t1_u_client WHERE (t1_u_client.id = 1 AND t0_u.client_id=t1_u_client.id);

-- here's the last lines of the output that i got on my machine:
--
--
-- mysql> prepare stmt1 from 'SELECT t0_u.id FROM admin_user t0_u, client t1_u_client WHERE (t1_u_client.id = ? AND t0_u.client_id=t1_u_client.id)';
-- Query OK, 0 rows affected (0.00 sec)
-- Statement prepared
-- 
-- mysql> set @a = 1;
-- Query OK, 0 rows affected (0.00 sec)
-- 
-- mysql> execute stmt1 using @a;
-- Empty set (0.00 sec)
-- 
-- mysql>
-- mysql> SELECT t0_u.id FROM admin_user t0_u, client t1_u_client WHERE (t1_u_client.id = 1 AND t0_u.client_id=t1_u_client.id);
-- +----+
-- | id |
-- +----+
-- |  3 |
-- +----+
--
[10 Apr 2005 16:45] Radu Chiriac 
The sql script that reproduces bug #9777

Attachment: bug9777.sql (text/x-sql), 3.37 KiB.
[10 Apr 2005 16:55] Radu Chiriac 
Found out something else - the below class works fine (returns the row that i managed to obtains without using query parameters) if using the old mysql-connector/j 3.0.16, and returns no records if using the latest connector/j 3.1.7. But, since it returns no records using PREPARE stmt1 FROM... - i'd say there are more chances that something's wrong with mysql server and not the connector. Btw, the below class returns correct results if used with mysql 4.1.10 no matter which connector/j version is used.

import java.sql.*;
import java.util.Properties;

public class Bug9777 {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");

    Properties connectionProperties = new Properties();
    connectionProperties.put("user", "xxx");
    connectionProperties.put("password", "yyy");
    Connection connection = DriverManager.getConnection(
      "jdbc:mysql://xzn:3306/bug9777", connectionProperties
    );

    PreparedStatement statement = connection.prepareStatement(
      "SELECT t0_u.id FROM admin_user t0_u, client t1_u_client " +
      "WHERE (t1_u_client.id = ? AND t0_u.client_id=t1_u_client.id)"
    );

    statement.setObject(1, new Long(1), Types.BIGINT);
    ResultSet resultSet = statement.executeQuery();

    if (resultSet.next()) {
      long id = resultSet.getLong(1);
      System.out.println("id = " + id);
    } else {
      System.out.println("No record found");
    }

    resultSet.close();
    statement.close();
    connection.close();
  }
}
[12 Apr 2005 20:40] Matan Amir 
I'd like to add that I also have this problem.  Seems to be a java -> mysql issue because my OS is Windows XP SP2.
[14 Apr 2005 15:16] Florian Gnägi 
Same here: 
- OS: SuSE Linux 
- 4.1.10 (self compiled) / mysql-connector-java-3.1.7 works fine
- 4.1.11 (self compiled) / mysql-connector-java-3.1.7 doesn't work: queries return 0 values when executed as prepared statement via jdbc driver, same query returns a row when executed directly via the mysql command line tool.
[14 Apr 2005 17:31] Geert Vanderkelen 
Hi Radu, Matan, Florian,

Thank you for the report! This has been verified: I could reproduce it without the JDBC interface.

Forgot to post a message here. Thanks again!

Regards,

Geert
[20 Apr 2005 19:58] Bryan Hess 
We saw this bug as well and worked around it by turning off server-side prepared statements in the jdbc url. The option to set is

useServerPrepStmts=false

we were using mysql 4.1.11 and connector/J 3.1.6
[28 Apr 2005 21:59] John David Duncan 
This bug is also effecting Five9 -- it has disrupted their upgrade from 4.0 to 4.1
[29 Apr 2005 6:49] Daniel Schneller 
Self-contained Java example:

import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class Test {

	public static void main(String[] args) {
		try {
			Driver tempDriver = (Driver)Class.forName("com.mysql.jdbc.Driver").newInstance();
			Properties tempProperties = new Properties();
			tempProperties.setProperty("user", "xxx");
			tempProperties.setProperty("password", "yyy");
			Connection tempConnection = tempDriver.connect(
				"jdbc:mysql://localhost:3306/?user=xxx&pass=yyy&characterEncoding=utf-8",
				tempProperties);
			Statement tempStatement = tempConnection.createStatement();
			tempStatement.executeUpdate("DROP TABLE IF EXISTS mww.pstest1");
			tempStatement.executeUpdate("DROP TABLE IF EXISTS mww.pstest2");
			tempStatement.executeUpdate("CREATE TABLE mww.pstest1 (a BIGINT(20)) TYPE=InnoDB");
			tempStatement.executeUpdate("CREATE TABLE mww.pstest2 (a BIGINT(20)) TYPE=InnoDB");
			
			tempStatement.close();
			
			tempStatement = tempConnection.createStatement();
			tempStatement.executeUpdate("INSERT INTO mww.pstest1 SET a=1");
			tempStatement.executeUpdate("INSERT INTO mww.pstest2 SET a=1");
			
			tempStatement.close();
			
			// Jetzt kommt der wahre Test
			PreparedStatement tempPS = tempConnection.prepareStatement(
				"SELECT x.a FROM mww.pstest1 x INNER JOIN mww.pstest2 y ON x.a=y.a WHERE (x.a = ?)");
			tempPS.setLong(1, 1l);
			ResultSet tempResult = tempPS.executeQuery();
			if (tempResult.next()) {
				System.out.println("Data found, looks OK.");
			} else {
				System.out.println("No data found! MySQL-BUG!");
			}
			tempPS.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
[4 May 2005 14:37] Konstantin Osipov 
I was able to repeat the bug.
[5 May 2005 9:14] Konstantin Osipov 
Subject: bk commit - 4.1 tree (konstantin:1.2225) BUG#9777

Fixed in 4.1.12 and 5.0.6
[6 May 2005 13:33] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24574
[6 May 2005 14:53] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24581
[9 May 2005 2:53] Paul DuBois 
Noted in 4.1.12, 5.0.6 changelogs.
[19 May 2005 14:46] [ name withheld ] 
I am running on solaris and am seeing a similiar problem in 4.1.10 and above (including 4.1.12). I am seeing that using substitution parameters always returns zero rows. I have tried both the 3.1 and 3.2 JDBC drivers.

Doing it this way always returns zero rows

 PreparedStatement stmt = conn.prepareStatement ("select * from users where user_id=?");
stmt.setString(1, "admin");

Doing it this way returns one row

stmt = conn.prepareStatement ("select * from users where user_id='admin'");
resultSet = stmt.executeQuery ();
[20 Oct 2005 7:58] Andreas Schildbach 
I have this very similar problem, with MySQL 4.1.12. Downgrading to 4.1.10a helped. However, the bug seems not be fixed on 4.1.12!
[30 Jan 2007 5:34] Trenton Adams 
This problem exists in 4.1.22 with jdbc 3.1.10 driver.
[30 Jan 2007 5:39] Trenton Adams 
Setting useServerPrepStmts=false in the URL line makes this work.  I'm assuming that this is still a bug then, eh?
[30 Jan 2007 14:01] Konstantin Osipov 
Trenton,
please do not comment on a closed bug report.
If the problem is still present in your environment, please open a new bug report - our bug verification team will get notified and will try to verify your report.