[jira] [Commented] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16985402#comment-16985402 ] Bernard commented on DERBY-5728: Richard I appreciate your comments where you highlight the dilemma. Non-native JPQL does not have a CAST because it aims to be platform agnostic. Consequently, in pure JPQL, without using the criteria API, a programmer would need to write different queries depending on the value of a parameter. This leads to a high number of combinations of queries to write and maintain, depending on the number of parameters. To maintain such an incredibly costly approach clearly defeats the purpose of programming. Therefore, it is not practical to do. Because static JPQL as described works with other databases, e.g. with SQL Server, a competitor, I would think that it is worth our consideration to implement a solution, even while it could be in conflict with the pure SQL grammar definitions as you describe. I would not want Derby to lag behind. The solution would allow us to write a single static JPQL query that serves an extremely common use case. Consider a database search entry form with multiple optional text fields. I cannot remember many applications that do not have at least one of these. So if you have the opportunity to resolve this as requested, I am sure that you would do the programmer community, and Derby a great service. > Add Support for NULL IS NULL > > > Key: DERBY-5728 > URL: https://issues.apache.org/jira/browse/DERBY-5728 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.8.2.2 > Environment: Windows XP > java version "1.6.0_31" > Java(TM) SE Runtime Environment (build 1.6.0_31-b05) > Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) >Reporter: bernard >Priority: Critical > Labels: derby_triage10_10 > Attachments: Bug5728.java, NullParameterEclipseLinkDerbyMaven.zip, > NullParameterHibernateDerbyMaven.zip > > > The following query fails: > SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) > Why this is an issue? > At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues > with generating SQL for trivial JPQL queries such as: > select object(c) from Customer c where ((name: is null) or (c.name = name:)) > where name: is a parameter > For why this is a fundamental issue, please see a minimalistic JPQL query at > http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples > Part of this has already been resolved by issue "Add support for > setObject(, null)" > https://issues.apache.org/jira/browse/DERBY-1938 > Please see EclipseLink and Hibernate test cases for verification. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16985289#comment-16985289 ] Bernard commented on DERBY-5728: Please let me add some context. It might be helpful, or even encouraging. I am not an expert on the JDBC spec. However, during research, I found that from the very start, the JDBC setNull with sql type parameter is a fallback for legacy JDBC drivers that do not pass the JDBC Driver Test Suite. This was many years ago, but it is still relevant for the very few databases that are still in this fallback situation. Meanwhile we have moved on and high popularity of abstractions like JPA make it difficult to figure out where the origin of some of these the problem is. This issue is only one manifestation of it. If this issue gets fixed, then automatically some other legacy / maintenance issues will go away. It is a hard pill to swallow that a type parameter must be passed alongside with null. JPQL does not even have a concept for that. This is such a good example where we should follow the trend as technology has clearly moved on, leaving the legacy behind. > Add Support for NULL IS NULL > > > Key: DERBY-5728 > URL: https://issues.apache.org/jira/browse/DERBY-5728 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.8.2.2 > Environment: Windows XP > java version "1.6.0_31" > Java(TM) SE Runtime Environment (build 1.6.0_31-b05) > Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) >Reporter: bernard >Priority: Critical > Labels: derby_triage10_10 > Attachments: Bug5728.java, NullParameterEclipseLinkDerbyMaven.zip, > NullParameterHibernateDerbyMaven.zip > > > The following query fails: > SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) > Why this is an issue? > At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues > with generating SQL for trivial JPQL queries such as: > select object(c) from Customer c where ((name: is null) or (c.name = name:)) > where name: is a parameter > For why this is a fundamental issue, please see a minimalistic JPQL query at > http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples > Part of this has already been resolved by issue "Add support for > setObject(, null)" > https://issues.apache.org/jira/browse/DERBY-1938 > Please see EclipseLink and Hibernate test cases for verification. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16971260#comment-16971260 ] Bernard commented on DERBY-5728: Richard, Thanks for experimenting. Unfortunately, the suggested workaround is not a solution. It uses a native query. The JPQL language specification does not include casting. I have researched the subject sufficiently about seven years ago, and nothing has changed since then. ORM is such a complex issue to deal with on the application programming side. JPQL and other abstractions have been invented for a reason, to make life easier for application programmers. This bug is ugly in that context. It defeats the purpose of writing cross-platform JPQL because it is not possible in this basic, general case. > Add Support for NULL IS NULL > > > Key: DERBY-5728 > URL: https://issues.apache.org/jira/browse/DERBY-5728 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.8.2.2 > Environment: Windows XP > java version "1.6.0_31" > Java(TM) SE Runtime Environment (build 1.6.0_31-b05) > Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) >Reporter: bernard >Priority: Critical > Labels: derby_triage10_10 > Attachments: Bug5728.java, NullParameterEclipseLinkDerbyMaven.zip, > NullParameterHibernateDerbyMaven.zip > > > The following query fails: > SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) > Why this is an issue? > At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues > with generating SQL for trivial JPQL queries such as: > select object(c) from Customer c where ((name: is null) or (c.name = name:)) > where name: is a parameter > For why this is a fundamental issue, please see a minimalistic JPQL query at > http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples > Part of this has already been resolved by issue "Add support for > setObject(, null)" > https://issues.apache.org/jira/browse/DERBY-1938 > Please see EclipseLink and Hibernate test cases for verification. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16971024#comment-16971024 ] Bernard commented on DERBY-5728: Is there a chance to develop this? It's a feature parity issue, perhaps can even be considered a bug as well because most other database engines have the expected behavior. > Add Support for NULL IS NULL > > > Key: DERBY-5728 > URL: https://issues.apache.org/jira/browse/DERBY-5728 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.8.2.2 > Environment: Windows XP > java version "1.6.0_31" > Java(TM) SE Runtime Environment (build 1.6.0_31-b05) > Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) >Reporter: bernard >Priority: Critical > Labels: derby_triage10_10 > Attachments: NullParameterEclipseLinkDerbyMaven.zip, > NullParameterHibernateDerbyMaven.zip > > > The following query fails: > SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) > Why this is an issue? > At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues > with generating SQL for trivial JPQL queries such as: > select object(c) from Customer c where ((name: is null) or (c.name = name:)) > where name: is a parameter > For why this is a fundamental issue, please see a minimalistic JPQL query at > http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples > Part of this has already been resolved by issue "Add support for > setObject(, null)" > https://issues.apache.org/jira/browse/DERBY-1938 > Please see EclipseLink and Hibernate test cases for verification. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16890625#comment-16890625 ] Bernard edited comment on DERBY-5728 at 7/23/19 2:48 AM: - >From what I can remember, it goes like this: Imagine we have a client form with multiple field entries, each tied to a JPQL fragment for that field. Let's start with one field: SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName Then take two fields: SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName and :firstName IS NULL OR a.firstName = :firstName If we can do this, then effectively the database ensures that, if an entry is missing, that the corresponding fragment can be ignored because it returns true. This is how programmers want the computer to behave. If we can't do this, and this issue prevents this from working, then things get really complicated. It does not work because the DB cannot handle NULL IS NULL where the first NULL is the missing parameter from the form. was (Author: bht): >From what I can remember, it goes like this: Imagine we have a client form with multiple field entries, each tied to a JPQL fragment for that field. Let's start with one field: SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName Then take two fields: SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName and :firstName IS NULL OR a.firstName = :firstName If we can do this, then effectively the database ensures that, if an entry is missing, that the corresponding fragment can be ignored because it returns true. This is how programmers want the computer to behave. If we can't do this, and this issue prevents this from working, then things get really complicated. > Add Support for NULL IS NULL > > > Key: DERBY-5728 > URL: https://issues.apache.org/jira/browse/DERBY-5728 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.8.2.2 > Environment: Windows XP > java version "1.6.0_31" > Java(TM) SE Runtime Environment (build 1.6.0_31-b05) > Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) >Reporter: bernard >Priority: Critical > Labels: derby_triage10_10 > Attachments: NullParameterEclipseLinkDerbyMaven.zip, > NullParameterHibernateDerbyMaven.zip > > > The following query fails: > SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) > Why this is an issue? > At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues > with generating SQL for trivial JPQL queries such as: > select object(c) from Customer c where ((name: is null) or (c.name = name:)) > where name: is a parameter > For why this is a fundamental issue, please see a minimalistic JPQL query at > http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples > Part of this has already been resolved by issue "Add support for > setObject(, null)" > https://issues.apache.org/jira/browse/DERBY-1938 > Please see EclipseLink and Hibernate test cases for verification. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Comment Edited] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16890625#comment-16890625 ] Bernard edited comment on DERBY-5728 at 7/23/19 2:45 AM: - >From what I can remember, it goes like this: Imagine we have a client form with multiple field entries, each tied to a JPQL fragment for that field. Let's start with one field: SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName Then take two fields: SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName and :firstName IS NULL OR a.firstName = :firstName If we can do this, then effectively the database ensures that, if an entry is missing, that the corresponding fragment can be ignored because it returns true. This is how programmers want the computer to behave. If we can't do this, and this issue prevents this from working, then things get really complicated. was (Author: bht): >From what I can remember, it goes like this: Imagine we have a client form with multiple field entries, each tied to a JPQL fragment for that field. Let's start with one field: SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName Then take two fields: SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName and :firstName IS NULL OR a.firstName = :firstName If we can do this, then effectively the database ensures that, if an entry is missing, that the corresponding fragment can be ignored because it returns true. This is how programmers want the computer to behave. If we can't do this, and this issue prevents this from working, then things get really complicated in comparison. > Add Support for NULL IS NULL > > > Key: DERBY-5728 > URL: https://issues.apache.org/jira/browse/DERBY-5728 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.8.2.2 > Environment: Windows XP > java version "1.6.0_31" > Java(TM) SE Runtime Environment (build 1.6.0_31-b05) > Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) >Reporter: bernard >Priority: Critical > Labels: derby_triage10_10 > Attachments: NullParameterEclipseLinkDerbyMaven.zip, > NullParameterHibernateDerbyMaven.zip > > > The following query fails: > SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) > Why this is an issue? > At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues > with generating SQL for trivial JPQL queries such as: > select object(c) from Customer c where ((name: is null) or (c.name = name:)) > where name: is a parameter > For why this is a fundamental issue, please see a minimalistic JPQL query at > http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples > Part of this has already been resolved by issue "Add support for > setObject(, null)" > https://issues.apache.org/jira/browse/DERBY-1938 > Please see EclipseLink and Hibernate test cases for verification. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16890625#comment-16890625 ] Bernard commented on DERBY-5728: >From what I can remember, it goes like this: Imagine we have a client form with multiple field entries, each tied to a JPQL fragment for that field. Let's start with one field: SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName Then take two fields: SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName and :firstName IS NULL OR a.firstName = :firstName If we can do this, then effectively the database ensures that, if an entry is missing, that the corresponding fragment can be ignored because it returns true. This is how programmers want the computer to behave. If we can't do this, and this issue prevents this from working, then things get really complicated in comparison. > Add Support for NULL IS NULL > > > Key: DERBY-5728 > URL: https://issues.apache.org/jira/browse/DERBY-5728 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.8.2.2 > Environment: Windows XP > java version "1.6.0_31" > Java(TM) SE Runtime Environment (build 1.6.0_31-b05) > Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) >Reporter: bernard >Priority: Critical > Labels: derby_triage10_10 > Attachments: NullParameterEclipseLinkDerbyMaven.zip, > NullParameterHibernateDerbyMaven.zip > > > The following query fails: > SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) > Why this is an issue? > At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues > with generating SQL for trivial JPQL queries such as: > select object(c) from Customer c where ((name: is null) or (c.name = name:)) > where name: is a parameter > For why this is a fundamental issue, please see a minimalistic JPQL query at > http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples > Part of this has already been resolved by issue "Add support for > setObject(, null)" > https://issues.apache.org/jira/browse/DERBY-1938 > Please see EclipseLink and Hibernate test cases for verification. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16890532#comment-16890532 ] Bernard commented on DERBY-5728: Hi, I am the original requester. I do not want comment on the illegal statements. But it would be absolutely fantastic if the changes meeting the original request would be implemented. These changes would let us write incredibly powerful and simple JPQL without the cumbersome criteria API. Works with most other databases like SQL Server, Oracle, HSQL, DB2. This is so old but still relevant. > Add Support for NULL IS NULL > > > Key: DERBY-5728 > URL: https://issues.apache.org/jira/browse/DERBY-5728 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.8.2.2 > Environment: Windows XP > java version "1.6.0_31" > Java(TM) SE Runtime Environment (build 1.6.0_31-b05) > Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) >Reporter: bernard >Priority: Critical > Labels: derby_triage10_10 > Attachments: NullParameterEclipseLinkDerbyMaven.zip, > NullParameterHibernateDerbyMaven.zip > > > The following query fails: > SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) > Why this is an issue? > At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues > with generating SQL for trivial JPQL queries such as: > select object(c) from Customer c where ((name: is null) or (c.name = name:)) > where name: is a parameter > For why this is a fundamental issue, please see a minimalistic JPQL query at > http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples > Part of this has already been resolved by issue "Add support for > setObject(, null)" > https://issues.apache.org/jira/browse/DERBY-1938 > Please see EclipseLink and Hibernate test cases for verification. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (DERBY-5629) Queries with guarded null Parameter fail
[ https://issues.apache.org/jira/browse/DERBY-5629?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14133040#comment-14133040 ] Bernard commented on DERBY-5629: Any progress on this after more than 2 years? Queries with guarded null Parameter fail Key: DERBY-5629 URL: https://issues.apache.org/jira/browse/DERBY-5629 Project: Derby Issue Type: Bug Components: JDBC Affects Versions: 10.8.2.2 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) Reporter: Bernard Labels: derby_triage10_9 Attachments: NullParameterHibernateDerbyMaven.zip, NullParameterHibernateHsqlMaven.zip 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 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] bernard updated DERBY-5728: --- Attachment: NullParameterEclipseLinkDerbyMaven.zip EclipseLink Test Case Add Support for NULL IS NULL Key: DERBY-5728 URL: https://issues.apache.org/jira/browse/DERBY-5728 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.8.2.2 Environment: Windows XP java version 1.6.0_31 Java(TM) SE Runtime Environment (build 1.6.0_31-b05) Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) Reporter: bernard Priority: Critical Attachments: NullParameterEclipseLinkDerbyMaven.zip The following query fails: SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) Why this is an issue? At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues with generating SQL for trivial JPQL queries such as: select object(c) from Customer c where ((name: is null) or (c.name = name:)) where name: is a parameter For why this is a fundamental issue, please see a minimalistic JPQL query at http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples Part of this has already been resolved by issue Add support for setObject(arg, null) https://issues.apache.org/jira/browse/DERBY-1938 I am attaching an EclipseLink test case for verification. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (DERBY-5728) Add Support for NULL IS NULL
bernard created DERBY-5728: -- Summary: Add Support for NULL IS NULL Key: DERBY-5728 URL: https://issues.apache.org/jira/browse/DERBY-5728 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.8.2.2 Environment: Windows XP java version 1.6.0_31 Java(TM) SE Runtime Environment (build 1.6.0_31-b05) Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) Reporter: bernard Priority: Critical Attachments: NullParameterEclipseLinkDerbyMaven.zip The following query fails: SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) Why this is an issue? At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues with generating SQL for trivial JPQL queries such as: select object(c) from Customer c where ((name: is null) or (c.name = name:)) where name: is a parameter For why this is a fundamental issue, please see a minimalistic JPQL query at http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples Part of this has already been resolved by issue Add support for setObject(arg, null) https://issues.apache.org/jira/browse/DERBY-1938 I am attaching an EclipseLink test case for verification. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] bernard updated DERBY-5728: --- Attachment: NullParameterHibernateDerbyMaven.zip Hibernate Test Case Add Support for NULL IS NULL Key: DERBY-5728 URL: https://issues.apache.org/jira/browse/DERBY-5728 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.8.2.2 Environment: Windows XP java version 1.6.0_31 Java(TM) SE Runtime Environment (build 1.6.0_31-b05) Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) Reporter: bernard Priority: Critical Attachments: NullParameterEclipseLinkDerbyMaven.zip, NullParameterHibernateDerbyMaven.zip The following query fails: SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) Why this is an issue? At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues with generating SQL for trivial JPQL queries such as: select object(c) from Customer c where ((name: is null) or (c.name = name:)) where name: is a parameter For why this is a fundamental issue, please see a minimalistic JPQL query at http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples Part of this has already been resolved by issue Add support for setObject(arg, null) https://issues.apache.org/jira/browse/DERBY-1938 I am attaching an EclipseLink test case for verification. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] bernard updated DERBY-5728: --- Description: The following query fails: SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) Why this is an issue? At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues with generating SQL for trivial JPQL queries such as: select object(c) from Customer c where ((name: is null) or (c.name = name:)) where name: is a parameter For why this is a fundamental issue, please see a minimalistic JPQL query at http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples Part of this has already been resolved by issue Add support for setObject(arg, null) https://issues.apache.org/jira/browse/DERBY-1938 Please see EclipseLink and Hibernate test cases for verification. was: The following query fails: SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) Why this is an issue? At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues with generating SQL for trivial JPQL queries such as: select object(c) from Customer c where ((name: is null) or (c.name = name:)) where name: is a parameter For why this is a fundamental issue, please see a minimalistic JPQL query at http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples Part of this has already been resolved by issue Add support for setObject(arg, null) https://issues.apache.org/jira/browse/DERBY-1938 I am attaching an EclipseLink test case for verification. Add Support for NULL IS NULL Key: DERBY-5728 URL: https://issues.apache.org/jira/browse/DERBY-5728 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.8.2.2 Environment: Windows XP java version 1.6.0_31 Java(TM) SE Runtime Environment (build 1.6.0_31-b05) Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) Reporter: bernard Priority: Critical Attachments: NullParameterEclipseLinkDerbyMaven.zip, NullParameterHibernateDerbyMaven.zip The following query fails: SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) Why this is an issue? At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues with generating SQL for trivial JPQL queries such as: select object(c) from Customer c where ((name: is null) or (c.name = name:)) where name: is a parameter For why this is a fundamental issue, please see a minimalistic JPQL query at http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples Part of this has already been resolved by issue Add support for setObject(arg, null) https://issues.apache.org/jira/browse/DERBY-1938 Please see EclipseLink and Hibernate test cases for verification. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13262224#comment-13262224 ] bernard commented on DERBY-5728: I think it it is different because it is more narrow in scope, it is an enhancement not a bug and does not leave open the question whether it might be an ORM bug that needs to be resolved. I hope this issue is easier to resolve. Add Support for NULL IS NULL Key: DERBY-5728 URL: https://issues.apache.org/jira/browse/DERBY-5728 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.8.2.2 Environment: Windows XP java version 1.6.0_31 Java(TM) SE Runtime Environment (build 1.6.0_31-b05) Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing) Reporter: bernard Priority: Critical Attachments: NullParameterEclipseLinkDerbyMaven.zip, NullParameterHibernateDerbyMaven.zip The following query fails: SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL)) Why this is an issue? At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues with generating SQL for trivial JPQL queries such as: select object(c) from Customer c where ((name: is null) or (c.name = name:)) where name: is a parameter For why this is a fundamental issue, please see a minimalistic JPQL query at http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples Part of this has already been resolved by issue Add support for setObject(arg, null) https://issues.apache.org/jira/browse/DERBY-1938 Please see EclipseLink and Hibernate test cases for verification. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1624) use of direct column name rather than alias make aggregation fail (Hibernate depends on that)
[ http://issues.apache.org/jira/browse/DERBY-1624?page=comments#action_12428464 ] Emmanuel Bernard commented on DERBY-1624: - Yes it's a pretty big one :-) Currently people cannot have query with both having and group by. From our side, this is clearly not an easy fix. It will require to work quite hard on the AST translator phases, changing the way queries are generated leading to lot's of QA before releasing that. use of direct column name rather than alias make aggregation fail (Hibernate depends on that) - Key: DERBY-1624 URL: http://issues.apache.org/jira/browse/DERBY-1624 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.1.1.0, 10.1.3.1 Reporter: Emmanuel Bernard Error: org.apache.derby.client.am.SqlException: Column 'MODEL0_.COL_0_0_' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'MODEL0_.COL_0_0_' is not a column in the target table., SQL State: 42X04, Error Code: -1 for select model0_.balance as col_0_0_, count(*) as col_1_0_ from account model0_ group by model0_.balance having count(*) 1 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-1623) Consider ANSI TRIM implementation
Consider ANSI TRIM implementation - Key: DERBY-1623 URL: http://issues.apache.org/jira/browse/DERBY-1623 Project: Derby Issue Type: Improvement Components: SQL Reporter: Emmanuel Bernard JPA is requiring databases to support this ANSI feature esp the ability to chose the trimmed character TRIM([ [ LEADING | TRAILING | BOTH ] [ chars ] FROM ] str) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-1624) use of direct column name rather than alias make aggregation fail (Hibernate depends on that)
use of direct column name rather than alias make aggregation fail (Hibernate depends on that) - Key: DERBY-1624 URL: http://issues.apache.org/jira/browse/DERBY-1624 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.1.3.1, 10.1.1.0 Reporter: Emmanuel Bernard Error: org.apache.derby.client.am.SqlException: Column 'MODEL0_.COL_0_0_' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'MODEL0_.COL_0_0_' is not a column in the target table., SQL State: 42X04, Error Code: -1 for select model0_.balance as col_0_0_, count(*) as col_1_0_ from account model0_ group by model0_.balance having count(*) 1 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1624) use of direct column name rather than alias make aggregation fail (Hibernate depends on that)
[ http://issues.apache.org/jira/browse/DERBY-1624?page=comments#action_12424957 ] Emmanuel Bernard commented on DERBY-1624: - Somehow related to DERBY-127 use of direct column name rather than alias make aggregation fail (Hibernate depends on that) - Key: DERBY-1624 URL: http://issues.apache.org/jira/browse/DERBY-1624 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.1.1.0, 10.1.3.1 Reporter: Emmanuel Bernard Error: org.apache.derby.client.am.SqlException: Column 'MODEL0_.COL_0_0_' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'MODEL0_.COL_0_0_' is not a column in the target table., SQL State: 42X04, Error Code: -1 for select model0_.balance as col_0_0_, count(*) as col_1_0_ from account model0_ group by model0_.balance having count(*) 1 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1624) use of direct column name rather than alias make aggregation fail (Hibernate depends on that)
[ http://issues.apache.org/jira/browse/DERBY-1624?page=comments#action_12424959 ] Emmanuel Bernard commented on DERBY-1624: - All but Derby DB works fine with it original issue from the hibernate dev list. I am working on enhancing Derby support a little bit, but have run into an issue with their syntax that I am unable to figure out. I was hoping someone on this list was familiar enough with Derby to point me in the right direction. Specifically, I am trying to properly deal with the manner in which Derby (and also DB2 largely) expects columns to be referenced in certain clauses. For example, because Hibernate always aliases columns in the select clause, derby requires that those aliases be used in certain later clauses. The query I am trying to work through right now is as follows: select model0_.name as col_0_0_, count(*) as col_1_0_ from Model model0_ group by model0_.name having count(*) 1 However, I get errors from Derby when passing this to the DB: ERROR 42X04: Column 'MODEL0_.COL_0_0_' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'MODEL0_.COL_0_0_' is not a column in the target table. If the having clause is removed, the query parses fine; I have tried various incantations regarding how to define the having clause without avail. This query seems taken almost verbatim from their reference docs, yet I cannot get this to work... http://db.apache.org/derby/docs/10.1/ref/rrefselectexpression.html Any thoughts? use of direct column name rather than alias make aggregation fail (Hibernate depends on that) - Key: DERBY-1624 URL: http://issues.apache.org/jira/browse/DERBY-1624 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.1.1.0, 10.1.3.1 Reporter: Emmanuel Bernard Error: org.apache.derby.client.am.SqlException: Column 'MODEL0_.COL_0_0_' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'MODEL0_.COL_0_0_' is not a column in the target table., SQL State: 42X04, Error Code: -1 for select model0_.balance as col_0_0_, count(*) as col_1_0_ from account model0_ group by model0_.balance having count(*) 1 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
