detection of incorrect types comparison is done at ? parameters

• Key: DERBY-37
• Type: Bug
• Status: Closed
• Resolution: Cannot Reproduce
• Priority: Major
• Assignee: Unassigned
• Reporter: Erik Bengtson
• Component/s: SQL

Description

java code:

PreparedStatement ps = conn.prepareStatement(statement);

This statement:

SELECT THIS.CODE_EID,THIS.COMPOSED_EID,'org.jpox.samples.applicationidentity.ChildComposedIntID' as JPOXMETADATA ,THIS.INTEGER_IDX AS JPOXORDER0,ELEMENT_1.CODE,ELEMENT_1.COMPOSED,ELEMENT_1.DESCRIPTION,ELEMENT_1."NAME" FROM NORMALLISTAPPLICATIONID_COMPOS THIS INNER JOIN CHILDCOMPOSEDINTID "ELEMENT" ON THIS.CODE_EID = "ELEMENT".CODE AND THIS.COMPOSED_EID = "ELEMENT".COMPOSED INNER JOIN COMPOSEDINTID ELEMENT_1 ON "ELEMENT".CODE = ELEMENT_1.CODE AND "ELEMENT".COMPOSED = ELEMENT_1.COMPOSED WHERE THIS.NORMALLISTAPPLICATIONID_ID_OID = ? AND THIS.INTEGER_IDX >= ? ORDER BY JPOXORDER0

results in:

SQL Exception: Comparisons between 'VARCHAR' and 'INTEGER' are not supported.

possible cause:

The INTEGER_IDX is an INTEGER column. While running the prepareStatement, JDBC/Cloudscape thinks I'm comparing INTEGER_IDX with ? (question mark) (INTEGER vs VARCHAR). This is not true, ? (question mark) is a parameter that will be later substitued in my code by an integer value.

Comments

[Jan Hlavatý]

If this is the whole error message then error reporting in Derby needs an overhaul ;) I hate error messages of the type "Some error occured, but you have to guess what it is".

Are you sure its not something else? It's hard for us to tell without all the tables we don't have. Did you try removing the part with ? params? If you could strip the statement down to something reproducible on our side, it would help ;)

[Erik Bengtson]

"SQL Exception: Comparisons between 'VARCHAR' and 'INTEGER' are not supported."

The above text between quotes is the Derby exception. I suggest that you try this to reproduce:

create table xxxx ( COLUMN_A INT, COLUMN_B INT )

conn.prepareStatement("SELECT * FROM xxxx where COLUMN_B >= ?");

-------------------

The same code runs fine with MSSQL, MySQL, Postgresql, Oracle and others. If you need more information, ask me.

 

[Mamta A. Satoor]

I tried following on Derby and didn't get any comparison errors.
    s.executeUpdate("create table xxxx (COLUMN_A INT, COLUMN_B INT )");
    PreparedStatement ps = con.prepareStatement("select * from xxxx where COLUMN_B >= ?");
    ps.setInt(1,1);
    ps.execute();

[Daniel John Debrunner]

Should this be resolved as 'cannot reproduce'? Unless the sql definitions for the tables involved in the original query are supplied I don't see any way to move forward on this.

[Erik Bengtson]

please resolve it. if I get this again, I report it with complete test case

[Satheesh Bandaram]

Looks like Mamta tried to reproduce the problem and wasn't successful. Erik (orignial reporter) said OK to close this for now.