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

[5 Mar 2006 17:03] [ name withheld ]
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();
        }
    }

 }
[5 Mar 2006 17:04] [ name withheld ]
MySQL test

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

[5 Mar 2006 17:08] [ name withheld ]
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
[10 Mar 2006 10:45] Sveta Smirnova
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.
[10 Mar 2006 10:47] Sveta Smirnova
Is interesting which verion of Java uses name withheld.
[10 Mar 2006 10:54] Valeriy Kravchuk
Thank you for the detailed problem report. Please, specify the exact JRE version used, as Sveta asked already.
[10 Mar 2006 11:50] [ name withheld ]
./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)
[8 May 2006 7:12] Tonci Grgin
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.