Bug #70091 tinyint(1) column type returned by jdbc connector becomes Integer with ORDER BY

Description:
When fetching a value from a resultset through resultSet.getObject("column_name"), a Boolean object is normally returned for a tinyint(1) column.

However in the case below adding an ORDER BY clause makes the mysql-connector/J return an Integer object for a tinyint(1) column.

This bug is a regression introduced in 5.6.13. It does not occur in 5.6.12.

How to repeat:
Assume the following three tables:

delimiter $$

CREATE TABLE `test_category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `important` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci$$

CREATE TABLE `test_entity` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_category` FOREIGN KEY (`category_id`) REFERENCES `test_category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci$$

CREATE TABLE `test_property` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `test_entity_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_test_entity_idx` (`test_entity_id`),
  CONSTRAINT `fk_test_entity` FOREIGN KEY (`test_entity_id`) REFERENCES `test_entity` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci$$

Also assume the following populated data:

INSERT INTO `test_category` (`id`,`important`)
  VALUES (1,0), (2,1), (3,0)$$

INSERT INTO `test_entity` (`id`,`category_id`)
  VALUES (1,1), (2,2), (3,2)$$

INSERT INTO `test_entity` (`id`,`category_id`)
  VALUES (1,1), (2,1), (3,1), (4,2), (5,2)$$

When running the following Java program (mysql-connector-java v1.5.25 must be on the classpath), the variable categoryImportantClass is java.lang.Integer (not as expected). However, when commenting out the ORDER BY clause, the categoryImportantClass is java.lang.Boolean (as expected).

//------------TestQuery.java----------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestQuery {

	public static void main(String[] args) throws Exception {
		final Class<?> jdbcClass = Class.forName("com.mysql.jdbc.Driver");
		System.out.println("jdbc class: " + jdbcClass);

		String url = "JDBC-URL-TO-MYSQL-DATABASE";

		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		try {
			
			connection = DriverManager.getConnection(url, "user", "password");
			
	
			statement = connection.createStatement();
			
			
			StringBuilder query = new StringBuilder();
			
			query.append("SELECT * FROM test_entity");
			query.append("\n LEFT JOIN test_category ON (test_category.id = test_entity.category_id)");
			query.append("\n LEFT JOIN test_property ON (test_property.test_entity_id = test_entity.id)");
			query.append("\n WHERE test_entity.id = 1");
			query.append("\n ORDER BY test_property.id");

			
			System.out.println("the test query:\n" + query.toString());
			
			statement.execute(query.toString());
			
			resultSet = statement.getResultSet();
			
			while (resultSet.next()) {
				Object entityId = resultSet.getObject("test_entity.id");
				Object categoryId = resultSet.getObject("test_category.id");
				Object categoryBoolean = resultSet.getObject("test_category.important");
				Class<?> categoryImportantClass = categoryBoolean.getClass(); // comment out this line to get expected result
				
				System.out.println("hit: entityId:" + entityId + " categoryId:" + categoryId + " categoryImportant?" + categoryBoolean + "(class:" + categoryImportantClass + ")");
			}
			System.out.println("-----------end of resultSet---------------");
			
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (Throwable t) {
					// silently ignore
				} 
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (Throwable t) {
					// silently ignore
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (Throwable t) {
					// silently ignore
				}
			}
		}
	}

}

The INSERT statements above are wrong and should be like this to be able to reproduce:

INSERT INTO `test_category` (`id`,`important`)
  VALUES (1,0), (2,1), (3,0)$$

INSERT INTO `test_entity` (`id`,`category_id`)
  VALUES (1,1), (2,2), (3,2)$$

INSERT INTO `test_property` (`id`,`test_entity_id`)
  VALUES (1,1), (2,1), (3,1), (4,2), (5,2)$$

And the error is not with the mysql-connector/J but with the MySQL server (using mysql-connector/J with MySQL 5.6.12 and also 5.5.29 works as expected).

Please provide the query/output using the mysql client. Thanks.

Found some typos in the sql script above, and improved the java program to get a better console output, so here is the complete info to be able to reproduce (including resulting stdout):

----------sql script--------------

delimiter $$

CREATE TABLE `test_category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `important` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci$$

CREATE TABLE `test_entity` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_category` FOREIGN KEY (`category_id`) REFERENCES `test_category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci$$

CREATE TABLE `test_property` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `test_entity_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_test_entity_idx` (`test_entity_id`),
  CONSTRAINT `fk_test_entity` FOREIGN KEY (`test_entity_id`) REFERENCES `test_entity` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci$$

INSERT INTO `test_category` (`id`,`important`)
  VALUES (1,0), (2,1), (3,0)$$

INSERT INTO `test_entity` (`id`,`category_id`)
  VALUES (1,1), (2,2), (3,2)$$

INSERT INTO `test_property` (`id`,`test_entity_id`)
  VALUES (1,1), (2,1), (3,1), (4,2), (5,2)$$

----------TestQuery.java--------------

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestQuery {

	public static void main(String[] args) throws Exception {
		final Class<?> jdbcClass = Class.forName("com.mysql.jdbc.Driver");
		System.out.println("jdbc class: " + jdbcClass);

		String jdbcUrl = "jdbc url";
		
		String mysqlUsername = "username";
		String mysqlPassword = "password";

		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			connection = DriverManager.getConnection(jdbcUrl, mysqlUsername, mysqlPassword);

			DatabaseMetaData dbMetaData = connection.getMetaData();
			System.out.println(" MySQL server version: " + dbMetaData.getDatabaseProductVersion());
			System.out.println(" Driver Name: " + dbMetaData.getDriverName());
			System.out.println(" Driver Version: " + dbMetaData.getDriverVersion());

			System.out.println("url: " + jdbcUrl);

			statement = connection.createStatement();

			StringBuilder query = new StringBuilder();

			query.append("SELECT * FROM test_entity");
			query.append("\n LEFT JOIN test_category ON (test_category.id = test_entity.category_id)");
			query.append("\n LEFT JOIN test_property ON (test_property.test_entity_id = test_entity.id)");
			query.append("\n WHERE test_entity.id = 1");
			query.append("\n ORDER BY test_property.id"); // comment this line to get expected result

			System.out.println("the test query:\n" + query.toString());

			statement.execute(query.toString());

			resultSet = statement.getResultSet();

			while (resultSet.next()) {
				Object entityId = resultSet.getObject("test_entity.id");
				Object categoryId = resultSet.getObject("test_category.id");
				Object categoryBoolean = resultSet.getObject("test_category.important");
				Class<?> categoryImportantClass = categoryBoolean.getClass();

				System.out.println("hit: entityId:" + entityId + " categoryId:" + categoryId + " categoryImportant?" + categoryBoolean + "(class:" + categoryImportantClass
						+ ")");
			}
			System.out.println("-----------end of resultSet---------------");

		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (Throwable t) {
					// silently ignore
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (Throwable t) {
					// silently ignore
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (Throwable t) {
					// silently ignore
				}
			}
		}
	}

}

----------Console output MySQL 5.6.12--------------

jdbc class: class com.mysql.jdbc.Driver
 MySQL server version: 5.6.12-log
 Driver Name: MySQL Connector Java
 Driver Version: mysql-connector-java-5.1.25 ( Revision: ${bzr.revision-id} )
url: <jdbc-url>
the test query:
SELECT * FROM test_entity
 LEFT JOIN test_category ON (test_category.id = test_entity.category_id)
 LEFT JOIN test_property ON (test_property.test_entity_id = test_entity.id)
 WHERE test_entity.id = 1
 ORDER BY test_property.id
hit: entityId:1 categoryId:1 categoryImportant?false(class:class java.lang.Boolean)
hit: entityId:1 categoryId:1 categoryImportant?false(class:class java.lang.Boolean)
hit: entityId:1 categoryId:1 categoryImportant?false(class:class java.lang.Boolean)
-----------end of resultSet---------------

----------Console output MySQL 5.6.13--------------

jdbc class: class com.mysql.jdbc.Driver
 MySQL server version: 5.6.13-log
 Driver Name: MySQL Connector Java
 Driver Version: mysql-connector-java-5.1.25 ( Revision: ${bzr.revision-id} )
url: <jdbc-url>
the test query:
SELECT * FROM test_entity
 LEFT JOIN test_category ON (test_category.id = test_entity.category_id)
 LEFT JOIN test_property ON (test_property.test_entity_id = test_entity.id)
 WHERE test_entity.id = 1
 ORDER BY test_property.id
hit: entityId:1 categoryId:1 categoryImportant?0(class:class java.lang.Integer)
hit: entityId:1 categoryId:1 categoryImportant?0(class:class java.lang.Integer)
hit: entityId:1 categoryId:1 categoryImportant?0(class:class java.lang.Integer)
-----------end of resultSet---------------

And for the reference, here is the link to the specification: Link to the specification: http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-type-conversions.html#idp1741...

Thank you for the feedback.

c:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot  --column-type-info -p d90
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

<CUT>

mysql> SELECT * FROM test_entity
    ->  LEFT JOIN test_category ON (test_category.id = test_entity.category_id)
    ->  LEFT JOIN test_property ON (test_property.test_entity_id = test_entity.id)
    ->  WHERE test_entity.id = 1
    ->  ORDER BY test_property.id;

<cut>

Field   4:  `important`
Catalog:    `def`
Database:   `d90`
Table:      `test_category`
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     4
Max_length: 1
Decimals:   0
Flags:      NUM

<cut>

+----+-------------+------+-----------+------+----------------+
| id | category_id | id   | important | id   | test_entity_id |
+----+-------------+------+-----------+------+----------------+
|  1 |           1 |    1 |         0 |    1 |              1 |
|  1 |           1 |    1 |         0 |    2 |              1 |
|  1 |           1 |    1 |         0 |    3 |              1 |
+----+-------------+------+-----------+------+----------------+
3 rows in set (0.02 sec)

mysql> SELECT * FROM test_entity
    ->  LEFT JOIN test_category ON (test_category.id = test_entity.category_id)
    ->  LEFT JOIN test_property ON (test_property.test_entity_id = test_entity.id)
    ->  WHERE test_entity.id = 1;

<CUT>

Field   4:  `important`
Catalog:    `def`
Database:   `d90`
Table:      `test_category`
Org_table:  `test_category`
Type:       TINY
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL NUM

<CUT>

+----+-------------+----+-----------+------+----------------+
| id | category_id | id | important | id   | test_entity_id |
+----+-------------+----+-----------+------+----------------+
|  1 |           1 |  1 |         0 |    1 |              1 |
|  1 |           1 |  1 |         0 |    2 |              1 |
|  1 |           1 |  1 |         0 |    3 |              1 |
+----+-------------+----+-----------+------+----------------+
3 rows in set (0.00 sec)

C:\dbs\5.5>bin\mysql -uroot --port=3550 --column-type-info d90
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.34 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM test_entity
    ->  LEFT JOIN test_category ON (test_category.id = test_entity.category_id)
    ->  LEFT JOIN test_property ON (test_property.test_entity_id = test_entity.id)
    ->  WHERE test_entity.id = 1
    ->  ORDER BY test_property.id;
Field   1:  `id`
Catalog:    `def`
Database:   `d90`
Table:      `test_entity`
Org_table:  `test_entity`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   2:  `category_id`
Catalog:    `def`
Database:   `d90`
Table:      `test_entity`
Org_table:  `test_entity`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL MULTIPLE_KEY UNSIGNED NO_DEFAULT_VALUE NUM PART_KEY

Field   3:  `id`
Catalog:    `def`
Database:   `d90`
Table:      `test_category`
Org_table:  `test_category`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   4:  `important`
Catalog:    `def`
Database:   `d90`
Table:      `test_category`
Org_table:  `test_category`
Type:       TINY
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL NUM

Field   5:  `id`
Catalog:    `def`
Database:   `d90`
Table:      `test_property`
Org_table:  `test_property`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      UNSIGNED NUM

Field   6:  `test_entity_id`
Catalog:    `def`
Database:   `d90`
Table:      `test_property`
Org_table:  `test_property`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      UNSIGNED NO_DEFAULT_VALUE NUM

+----+-------------+----+-----------+------+----------------+
| id | category_id | id | important | id   | test_entity_id |
+----+-------------+----+-----------+------+----------------+
|  1 |           1 |  1 |         0 |    1 |              1 |
|  1 |           1 |  1 |         0 |    2 |              1 |
|  1 |           1 |  1 |         0 |    3 |              1 |
+----+-------------+----+-----------+------+----------------+
3 rows in set (0.00 sec)