Derby
  1. Derby
  2. DERBY-37

detection of incorrect types comparison is done at ? parameters

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Cloudscape 10 beta

      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.

        Activity

        Gavin made changes -
        Workflow jira [ 37717 ] Default workflow, editable Closed status [ 12797313 ]
        Satheesh Bandaram made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Satheesh Bandaram made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Cannot Reproduce [ 5 ]
        Hide
        Satheesh Bandaram added a comment -

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

        Show
        Satheesh Bandaram added a comment - Looks like Mamta tried to reproduce the problem and wasn't successful. Erik (orignial reporter) said OK to close this for now.
        Hide
        Erik Bengtson added a comment -

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

        Show
        Erik Bengtson added a comment - please resolve it. if I get this again, I report it with complete test case
        Hide
        Daniel John Debrunner added a comment -

        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.

        Show
        Daniel John Debrunner added a comment - 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.
        Mike Matrigali made changes -
        Field Original Value New Value
        Component/s SQL [ 11408 ]
        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.
        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.
        Hide
        Mamta A. Satoor added a comment -

        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();

        Show
        Mamta A. Satoor added a comment - 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();
        Hide
        Erik Bengtson added a comment -

        "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.

        Show
        Erik Bengtson added a comment - "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.
        Hide
        Jan Hlavatý added a comment -

        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

        Show
        Jan Hlavatý added a comment - 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 created issue -

          People

          • Assignee:
            Unassigned
            Reporter:
            Erik Bengtson
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development