Derby
  1. Derby
  2. DERBY-5629

Queries with guarded null Parameter fail

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.8.2.2
    • Fix Version/s: None
    • Component/s: JDBC
    • Environment:
      java version "1.6.0_30"
      Java(TM) SE Runtime Environment (build 1.6.0_30-b12)
      Java HotSpot(TM) Client VM (build 20.5-b03, mixed mode, sharing)
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached

      Description

      Some test cases in the attached Maven project fail where a null parameter is passed in or a null value is coded in the query.

      In the context of this issue, a recently closed issue appears to be relevant:

      "Add support for setObject(<arg>, null)"
      https://issues.apache.org/jira/browse/DERBY-1938

      Some test cases in the attached project are Hibernate JPQL cases where Hibernate takes care of generating the SQL queries.

      I thought it was appropriate to make a few cases not only one so that the issue gets a little more test coverage.

      I also assume that issue DERBY-1938 aims to fix what we can see in these cases.

      This has become a major issue because it causes failure of a minimalistic JPQL query as shown at

      http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples

      that shows a JPQL query:

      SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = :lastName

        Activity

        Gavin made changes -
        Workflow jira [ 12654666 ] Default workflow, editable Closed status [ 12802057 ]
        Hide
        bernard added a comment -

        I would not have raised this issue if ANSI/ISO Standard syntax compliance was my concern.

        My concern is that Derby is an exception when compared with all major databases in use today such as Oracle, Microsoft, Sybase, MySQL. I have tried them with this testcase.

        This issue is in line with the direction that Derby seems to follow, see "Add support for setObject(<arg>, null)" https://issues.apache.org/jira/browse/DERBY-1938.

        People DO code like this as can be seen at http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples and they are irritated by Derby's failure.

        One could argue that this is an enhancement not a bug except the inconsitencey with DERBY-1938 but would that make a diference?

        I think a Derby initiative would be most productive even while there might be a Hibernate aspect.

        Show
        bernard added a comment - I would not have raised this issue if ANSI/ISO Standard syntax compliance was my concern. My concern is that Derby is an exception when compared with all major databases in use today such as Oracle, Microsoft, Sybase, MySQL. I have tried them with this testcase. This issue is in line with the direction that Derby seems to follow, see "Add support for setObject(<arg>, null)" https://issues.apache.org/jira/browse/DERBY-1938 . People DO code like this as can be seen at http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples and they are irritated by Derby's failure. One could argue that this is an enhancement not a bug except the inconsitencey with DERBY-1938 but would that make a diference? I think a Derby initiative would be most productive even while there might be a Hibernate aspect.
        Rick Hillegas made changes -
        Urgency Urgent [ 10051 ] Normal [ 10052 ]
        Hide
        Rick Hillegas added a comment -

        Hi Bernard,

        Looking at Kristian, Knut, and Dag's comments, I get the impression that this is not legal syntax in the Standard dialect which Derby supports. The fix would be for Hibernate to generate ANSI/ISO Standard syntax when the datastore is Derby.

        I have lowered the urgency of this issue. The Urgency field is owned by the release manager. It reflects the release manager's judgment about the issues which gate the next release. You are welcome to adjust the Priority field to reflect how important this issue is to you.

        Thanks,
        -Rick

        Show
        Rick Hillegas added a comment - Hi Bernard, Looking at Kristian, Knut, and Dag's comments, I get the impression that this is not legal syntax in the Standard dialect which Derby supports. The fix would be for Hibernate to generate ANSI/ISO Standard syntax when the datastore is Derby. I have lowered the urgency of this issue. The Urgency field is owned by the release manager. It reflects the release manager's judgment about the issues which gate the next release. You are welcome to adjust the Priority field to reflect how important this issue is to you. Thanks, -Rick
        Hide
        bernard added a comment -

        This error occurs with EclipseLink as well. Would it be possible to make a plan for fixing this?

        Show
        bernard added a comment - This error occurs with EclipseLink as well. Would it be possible to make a plan for fixing this?
        Mamta A. Satoor made changes -
        Labels derby_triage10_9
        Issue & fix info Repro attached [ 10424 ]
        bernard made changes -
        Attachment NullParameterHibernateHsqlMaven.zip [ 12515952 ]
        Hide
        bernard added a comment -

        Please see the test cases configured for HSQL if this helps. I have already filed an issue with Hibernate in case it is a derby dialect issue: https://hibernate.onjira.com/browse/JPA-28

        Show
        bernard added a comment - Please see the test cases configured for HSQL if this helps. I have already filed an issue with Hibernate in case it is a derby dialect issue: https://hibernate.onjira.com/browse/JPA-28
        Hide
        Dag H. Wanvik added a comment -

        I think it is reasonable to suspect this is a bug in the Hibernate mapping for the Derby dialect.

        Show
        Dag H. Wanvik added a comment - I think it is reasonable to suspect this is a bug in the Hibernate mapping for the Derby dialect.
        Hide
        Dag H. Wanvik added a comment -

        I agree with Knut's interpretation of the standard, so that part of this issue would be an improvement request, not a bug.

        Show
        Dag H. Wanvik added a comment - I agree with Knut's interpretation of the standard, so that part of this issue would be an improvement request, not a bug.
        Hide
        Kristian Waagan added a comment -

        Thanks, Knut.

        I guess many database systems has found that extension useful then.
        For completeness, I think the reason why "null is null" comes up is because of a prepared statement with "where ? is null".

        I may have garbled this, but I see two issues here:
        a) JPQL doesn't support casts.
        b) Where does Hibernate get the column type VARBINARY from? (the column is of type VARCHAR)

        By using explicit casts you can get the query working when you have control of the SQL, but it's harder when going through another query language. I think HQL is okay, but not JPQL due to the lack of CAST.
        I don't know if (b) is a bug in Hibernate or in Derby. It would have been easier if I know where Hibernate got the column type from, as it could be a bug in the meta data obtained from Derby. I also tried with the latest version of Hibernate and Derby trunk, but got the same error.

        Show
        Kristian Waagan added a comment - Thanks, Knut. I guess many database systems has found that extension useful then. For completeness, I think the reason why "null is null" comes up is because of a prepared statement with "where ? is null". I may have garbled this, but I see two issues here: a) JPQL doesn't support casts. b) Where does Hibernate get the column type VARBINARY from? (the column is of type VARCHAR) By using explicit casts you can get the query working when you have control of the SQL, but it's harder when going through another query language. I think HQL is okay, but not JPQL due to the lack of CAST. I don't know if (b) is a bug in Hibernate or in Derby. It would have been easier if I know where Hibernate got the column type from, as it could be a bug in the meta data obtained from Derby. I also tried with the latest version of Hibernate and Derby trunk, but got the same error.
        Hide
        Knut Anders Hatlen added a comment -

        According to SQL:2003, part 2, section 8.7 <null predicate>, the null predicate takes a <row value predicand>. As far as I can see, <row value predicand> doesn't allow untyped NULL. (It has to be a <contextually typed row value expression> to allow that.)

        So it looks like accepting "values null is null" would be an extension of the standard. The portable way to write that query is "values cast(null as integer) is null" to explicitly state the type of the null value. Or just "values true"...

        Show
        Knut Anders Hatlen added a comment - According to SQL:2003, part 2, section 8.7 <null predicate>, the null predicate takes a <row value predicand>. As far as I can see, <row value predicand> doesn't allow untyped NULL. (It has to be a <contextually typed row value expression> to allow that.) So it looks like accepting "values null is null" would be an extension of the standard. The portable way to write that query is "values cast(null as integer) is null" to explicitly state the type of the null value. Or just "values true"...
        Hide
        Kristian Waagan added a comment -

        I see that "values NULL IS NULL" fails [1]. Isn't that supposed to be supported?
        It also fails to parse when put in other places, for instance in a WHERE clause [2].

        [1] ij> values NULL IS NULL;
        ERROR 42X01: Syntax error: Encountered "IS" at line 1, column 13.

        [2] ij> select 1 from sys.systables where NULL IS NULL;
        ERROR 42X01: Syntax error: Encountered "NULL" at line 1, column 35.

        Show
        Kristian Waagan added a comment - I see that "values NULL IS NULL" fails [1] . Isn't that supposed to be supported? It also fails to parse when put in other places, for instance in a WHERE clause [2] . [1] ij> values NULL IS NULL; ERROR 42X01: Syntax error: Encountered "IS" at line 1, column 13. [2] ij> select 1 from sys.systables where NULL IS NULL; ERROR 42X01: Syntax error: Encountered "NULL" at line 1, column 35.
        Bernard made changes -
        Attachment NullParameterHibernateDerbyMaven.zip [ 12515879 ]
        Bernard made changes -
        Attachment NullParameterHibernateDerbyMaven.zip [ 12515880 ]
        Bernard made changes -
        Attachment NullParameterHibernateDerbyMaven.zip [ 12515881 ]
        Hide
        Bernard added a comment -

        Test cases

        Show
        Bernard added a comment - Test cases
        Bernard made changes -
        Field Original Value New Value
        Attachment NullParameterHibernateDerbyMaven.zip [ 12515879 ]
        Attachment NullParameterHibernateDerbyMaven.zip [ 12515880 ]
        Bernard created issue -

          People

          • Assignee:
            Unassigned
            Reporter:
            Bernard
          • Votes:
            2 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development