MySQL Bugs: #17940: the parameters are not processed properly

Bug #17940	the parameters are not processed properly
Submitted:	5 Mar 2006 17:03	Modified:	8 May 2006 7:12
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.12	OS:	Linux (Mandriva Linux release 2006.0)
Assigned to:		CPU Architecture:	Any

Description:
The parameters are not processed properly in prepared sql statements.

uname -a:
Linux host.name 2.6.12-12mdksmp #1 SMP Fri Sep 9 17:43:23 CEST 2005 i686 Intel(R) Pentium(R) 4 CPU 2.80GHz unknown GNU/Linux

jdbc:
mysql-connector-java-3.1.12

database:
4.1.15

How to repeat:
package mytest;

import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.util.logging.*;

import com.mysql.jdbc.util.*;

public class mytest extends BaseBugReport {
    private static final Properties properties = new Properties();
    {
        properties.put("autoReconnect","true");
        properties.put("characterEncoding","UTF-8");
        properties.put("characterSetResults","UTF-8");
        properties.put("connectionCollation","utf8_slovenian_ci");
    }

    public mytest() {
    }

    /**
     * Override this method with code that demonstrates the bug.
     *
     * @throws Exception if an error occurs during your test run.
     */
    public void runTest() throws Exception {
        Logger.getLogger(this.getClass().toString()).entering("runTest","");
        Connection conn = getConnection(getUrl(), properties);
        
        Calendar calendar = Calendar.getInstance();
        calendar.clear();
        calendar.set(2006,2,3);
        Date date = new java.sql.Date(calendar.getTime().getTime());

        PreparedStatement pstmt = conn.prepareStatement("select * from testdata");
        ResultSet rs = pstmt.executeQuery();
        
        ArrayList r1=new ArrayList();
        rs.beforeFirst();
        while (rs.next() && !rs.isAfterLast()) {
            r1.add(rs.getObject(1));
        }
        rs.close();
        Logger.getLogger(this.getClass().toString()).log(Level.INFO, "select * from testdata returned "+Integer.toString(r1.size())+" rows");
        
        /**BUG HERE**/
        pstmt = conn.prepareStatement("select * from testdata where ? between a and b");
        pstmt.clearParameters();        
        pstmt.setObject(1, date);
        rs = pstmt.executeQuery();

        ArrayList r2=new ArrayList();
        rs.beforeFirst();
        while (rs.next() && !rs.isAfterLast()) {
            r2.add(rs.getObject(1));
        }
        rs.close();
        Logger.getLogger(this.getClass().toString()).log(Level.INFO, "select * from testdata where ? between a and b returned "+Integer.toString(r2.size())+" rows");

        pstmt = conn.prepareStatement("select * from testdata where '"+date.toString()+"' between a and b");
        rs = pstmt.executeQuery();

        ArrayList r3=new ArrayList();
        rs.beforeFirst();
        while (rs.next() && !rs.isAfterLast()) {
            r3.add(rs.getObject(1));
        }
        rs.close();
        Logger.getLogger(this.getClass().toString()).log(Level.INFO, "select * from testdata where '"+date.toString()+"' between a and b returned "+Integer.toString(r3.size())+" rows");

        if (r1.size()!=r2.size() && r2.size()!=r3.size()) {
            Logger.getLogger(this.getClass().toString()).log(Level.SEVERE, "FAILED");
        }

        conn.close();
        Logger.getLogger(this.getClass().toString()).exiting("runTest","");
    }

    /**
     * Override this method with code that sets up the testcase for
     * demonstrating your bug (creating tables, populating data, etc).
     *
     * @throws Exception if an error occurs during the 'setUp' phase.
     */
    public void setUp() throws Exception {
        Logger.getLogger(this.getClass().toString()).entering("setUp","");
        Connection conn = getConnection(getUrl(), properties);

        Statement stmnt = conn.createStatement();

        stmnt.execute("create table testdata ( id int not null auto_increment primary key, a date, b date ) ");
        stmnt.execute("insert into testdata values(null, '2006-01-01', '2006-05-05')");
        
        conn.close();
    }

    /**
     * Override this method with code that cleans up anything created in the
     * setUp() method.
     *
     * @throws Exception if an error occurs during the 'tearDown' phase.
     */
    public void tearDown() throws Exception {
        Logger.getLogger(this.getClass().toString()).entering("tearDown","");
        Connection conn = getConnection(getUrl(), properties);

        Statement stmnt = conn.createStatement();

        stmnt.execute("drop table testdata");

        conn.close();
    }

    public static void main(String[] args) {
        mytest mytest = new mytest();
        
        try {
            mytest.setUp();
            try {
                mytest.runTest();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            mytest.tearDown();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

 }

MySQL test

Attachment: mytest.java (text/x-java), 4.39 KiB.

Test log:

Mar 5, 2006 6:05:41 PM mytest.mytest runTest
INFO: select * from testdata returned 1 rows
Mar 5, 2006 6:05:41 PM mytest.mytest runTest
INFO: select * from testdata where ? between a and b returned 0 rows
Mar 5, 2006 6:05:41 PM mytest.mytest runTest
INFO: select * from testdata where '2006-03-03' between a and b returned 1 rows
Mar 5, 2006 6:05:41 PM mytest.mytest runTest
SEVERE: TEST FAILED

I tested it on WinXP with MySQL 4.0.18 and 4.1.18, JDK 1.5.0 and 1.4.0 and Connector-J 1.3.12; also on Solaris 10 with MySQL 4.0.15, JDK 1.5.0_01 and Connector-J 1.3.12. All work fine.

Also return value of pstmt.toString() (in place where PreparedStatement marked as "BUG") is correct SQL query in all these cases.

Is interesting which verion of Java uses name withheld.

Thank you for the detailed problem report. Please, specify the exact JRE version used, as Sveta asked already.

./java -version
java version "1.4.2_08"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_08-b03)
Java HotSpot(TM) Client VM (build 1.4.2_08-b03, mixed mode)

Hi Uros.
Thanks for complete problem report. I was unable to repeat the error you get:
run:
2006.05.08 09:03:52 test17948.mytest runTest
INFO: select * from testdata returned 1 rows
2006.05.08 09:03:52 test17948.mytest runTest
INFO: select * from testdata where ? between a and b returned 1 rows
2006.05.08 09:03:52 test17948.mytest runTest
INFO: select * from testdata where '2006-03-03' between a and b returned 1 rows
BUILD SUCCESSFUL (total time: 1 second)

Environment:
  mysql-connector-java-3-1-12
  JDK 1.6
  MySQL server 4.1.13 on Linux
I presume that upgrading JDK will solve the problem.