MySQL Bugs: #1950: Connector/J adds mystery record

Bug #1950	Connector/J adds mystery record
Submitted:	25 Nov 2003 10:52	Modified:	3 Dec 2003 20:45
Reporter:	Axel Muench	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	3.0.9	OS:	Linux (Linux and OSX)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
The query below returns the RIGHT results using the command line util mysql or other tools to 
connect to mysql version 4.012 or 4.0.16. It will display the WRONG result using the Connector/J 
driver version 3.0.9. It will add a mystery record at the top of the results.

SELECT b.id as id, b.name AS category, a.id as parent_id, a.name AS parent_cat from Category AS a 
LEFT JOIN Category AS b on a.id = b.parent_id
GROUP BY id
ORDER BY category  LIMIT 1, 15

WRONG result:
+------+-------------+-----------+------------+
| id   | category    | parent_id | parent_cat |
+------+-------------+-----------+------------+

|    0 |                   |         3 |         SUV       |   <- added record !!!!!!

|    1 | cars            |         2 |         All        |
|    4 | sedan          |         1 |       cars       |
|    7 | small boats |         6 |      vessels    |
|    5 | sports cars |         1 |        cars       |
|    3 | SUV            |         1 |       cars       |
|    6 | vessels       |         2 |         All        |
+------+-------------+-----------+------------+

CORRECT result:
+------+-------------+-----------+------------+
| id   | category    | parent_id | parent_cat |
+------+-------------+-----------+------------+
|    1 | cars        |         2 |         All        |
|    4 | sedan       |         1 |      cars       |
|    7 | small boats |         6 | vessels    |
|    5 | sports cars |         1 |    cars       |
|    3 | SUV         |         1 |       cars       |
|    6 | vessels     |         2 |         All        |
+------+-------------+-----------+------------+
6 rows in set (0.00 sec)

How to repeat:
Query through a prepared statement

SELECT b.id as id, b.name AS category, a.id as parent_id, a.name AS parent_cat from Category AS a 
LEFT JOIN Category AS b on a.id = b.parent_id
GROUP BY id
ORDER BY category  LIMIT 1, 15

CREATE TABLE `Category` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `parent_id` int(11) unsigned NOT NULL default '0',
  `featured` tinyint(1) unsigned NOT NULL default '0',
  `sort_order` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name_index` (`name`)
) TYPE=MyISAM

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.

Can you please add the table data to this bug report as SQL INSERTs?

Mark,

Please let me know if you need more details.

# Dump of table Category
# ------------------------------------------------------------

INSERT INTO Category (id,name,parent_id,featured,sort_order) VALUES ("1","cars","2","1","0");
INSERT INTO Category (id,name,parent_id,featured,sort_order) VALUES ("2","All","0","1","0");
INSERT INTO Category (id,name,parent_id,featured,sort_order) VALUES ("3","SUV","1","1","0");
INSERT INTO Category (id,name,parent_id,featured,sort_order) VALUES ("4","sedan","1","1","0");
INSERT INTO Category (id,name,parent_id,featured,sort_order) VALUES ("5","sports cars","1","1","0");
INSERT INTO Category (id,name,parent_id,featured,sort_order) VALUES ("6","vessels","2","1","0");
INSERT INTO Category (id,name,parent_id,featured,sort_order) VALUES ("7","small 
boats","6","1","0");

You might want to know how the prepared statement is used:    

private static final String SELECT_CATEGORIES =
            "SELECT b.id as id, b.name as category, a.id as parent_id, a.name as parent_cat " +
            "FROM Category as a " +
            "LEFT JOIN Category as b on a.id = b.parent_id " +
            "GROUP BY b.id " +
            "ORDER BY category LIMIT ?, ?";

<cut>

prepStmt = con.prepareStatement(SELECT_CATEGORIES);
prepStmt.setInt(1,index);
prepStmt.setInt(2,range);
rs = prepStmt.executeQuery();

</cut>

I can't repeat this with Connector/J 3.0.x or 3.1.x against MySQL-4.0.16, 4.1.1 or 4.0.17. What version of the server are you using?

Sorry...I missed that you specified what server version you were running :(

I can not repeat this behavior with the data you have given. To debug this further, we would need a _standalone_ testcase that demonstrates the behavior. This would include the schema and data (which you've already provided), as well as Java code that produces the error you have reported. The small snippet that you _have_ provided, unfortunately, does not. Do you use Statement.setMaxRows(), or set any connection properties before preparing these statements?