MySQL Bugs: #19522: SELECT using Join of 2 tables on BIGINT column fails with PreparedStatement

Bug #19522	SELECT using Join of 2 tables on BIGINT column fails with PreparedStatement
Submitted:	3 May 2006 19:01	Modified:	12 May 2006 17:05
Reporter:	Dave Cosgrove	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.12	OS:	Windows (Win XP)
Assigned to:		CPU Architecture:	Any

Description:
When issuing executeQuery() on a PreparedStatement that requires a join of 2 tables on a BIGINT(20) UNSIGNED column, no records are returned in the ResultSet event though the same SQL query yields expected records when using a Statement object.  This problem has been verified with Connector/J 3.1.10->3.1.12.

How to repeat:
At the mysql command line issue the following:

-- begin SQL
USE test;

DROP TABLE test_a, test_b;

CREATE TABLE test_a ( test_id BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY );
CREATE TABLE test_b ( test_id BIGINT(20) UNSIGNED NOT NULL );
INSERT INTO test_a VALUES (1);
INSERT INTO test_b VALUES (1);

-- end SQL

// begin Java Code

>>>Then, compile and execute the following simple test harness and notice that no records are returned. One record containing the value '1' should be returned:

import java.sql.*;

public class Test {

    public static void main( String[] args ) {

        try {

            String jdbcUrl = "jdbc:mysql://localhost/test";
            String username = "root";
            String password = "root";

            long id = 1;

            String query =

                "SELECT a.test_id "  +
                "FROM   test_a as a, " +
                "       test_b as b "  +
                "WHERE a.test_id = b.test_id " +
                "AND a.test_id = ?";

            Class.forName("com.mysql.jdbc.Driver").newInstance();
        
            Connection conn =
                DriverManager.getConnection( jdbcUrl, username, password );

            PreparedStatement ps = 
                conn.prepareStatement ( query );

            ps.clearParameters();
            ps.setLong( 1, id );

            ResultSet rs = ps.executeQuery();

            while ( rs.next() ) {

                System.out.println("val : " + rs.getLong(1) );
            }

            rs.close();
            rs = null;
        }
        catch (Exception e) {

            System.err.println(e);
        }

    
    }
}

// end Java Code

Suggested fix:
???

Java test harness

Attachment: Test.java (application/octet-stream, text), 1.17 KiB.

SQL code to setup test environment

Attachment: create_test_setup.sql (application/octet-stream, text), 245 bytes.

Hi Dave. Thanks for nice test case. Sorry but I was unable to repeat your problem.
Console output:
init:
deps-jar:
Compiling 1 source file to C:\Documents and Settings\Tonci\Test19522\build\classes
compile:
run:
val : 1
BUILD SUCCESSFUL (total time: 0 seconds)

Environment:
MySQL 5.0.7. beta nt max log, 5.1.7 nt max log and 4.1.18
JDK 1.6
MySQL-connector-java 3.1.12
MS XP SP2

My default storage engine is MYISAM... I should have specified MYISAM in the table DDL; Are you using MYISAM tables by default as well?

Hi, Dave.
I do use MyISAM by default but this doesn't seem to be storage engine issue.

I just ran my unit test in a 5.0.20-nt environment against MYISAM tables with Connector/J 3.1.12 and all worked well; The same test conducted in the 4.1.11-nt failed (with Connector/J 3.1.12 and MYISAM tables).

Any additional word on this issue?

Hi Dave. I have nothing more to add but try to upgrade 4.1.11-nt to newer version since all my tests passed.

i have query that is showing the record of those result