3 Replies Latest reply on Aug 29, 2007 10:42 AM by rooz

    query, join and IS NULL

    rooz
      Hello, I have two entities so defined: @Entity @Inheritance(strategy=InheritanceType.JOINED) @Table(name="an_nodi") @DiscriminatorColumn(name="tipo", discriminatorType=DiscriminatorType.STRING, columnDefinition="VARCHAR(31) NOT NULL") public abstract class Nodo implements Serializable {     @Id     @GeneratedValue(strategy = GenerationType.IDENTITY)     private Long id;         @Column(nullable=false)     private String nome; .... @Entity @Table(name="ordini") public class Ordine implements Serializable {         @Id     @GeneratedValue(strategy = GenerationType.IDENTITY)     private Long id;             @ManyToOne(cascade=CascadeType.PERSIST)     private Nodo nodo; ..... In entity Ordine the object nodo can be null. I'm trying to write a query that returns all objects Ordine with nodo set to NULL OR nodo specified with some values. For example in the database (a DB2 database) we can have table "an_nodi" ID     NOME 1     abc 2     def table "ordini" ID     NODO_ID      5     null 6     1 QUESTION 1) I have the correct results if I call these queries:      a) select o from Ordine o where o.nodo.nome = 'abc'  -> Ordine[id=6]      b) select o from Ordine o where o.nodo is null       -> Ordine[id=5] If I try to combine the queries with an 'OR' I have a wrong result:      c) select o from Ordine o where o.nodo is null OR o.nodo.nome = 'abc'  -> Ordine[id=6]   (ONLY !!!) This is the generated SQL:      d) SELECT t0.ID, t0.NODO_ID, .... FROM ordini t0, an_nodi t1 WHERE (((t1.NOME = 'abc') OR (t0.NODO_ID IS NULL)) AND (t1.ID = t0.NODO_ID)) I understood the relationship @ManyToOne specified in Ordine is an INNER JOIN.... Is it always an INNER JOIN or I can set it as an OUTER JOIN? QUESTION 2) This is the new query, with the LEFT JOIN:      e) select o from Ordine o           left join o.nodo n           where n.nome = 'abc' OR n is null I have the exception listed below. If I replace '[b]=*[/b]' with '[b]IS[/b]' the generated SQL is OK!!! Where did I go wrong? Local Exception Stack: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2006.8 (Build 060830)): oracle.toplink.essentials.exceptions.DatabaseException Internal Exception: org.apache.derby.client.am.SqlException: Syntax error: Encountered "*" at line 1, column 199.Error Code: -1 Call:SELECT t0.ID, t0.NODO_ID, t0.ATTORE_ID, t0.STATOORDINE_ID, t0.CLASSIFICAZIONE_ID FROM ordini t0 LEFT OUTER JOIN an_nodi t1 ON (t1.ID = t0.NODO_ID) WHERE ((t1.NOME = 'abc') OR t0.NODO_ID =* NULL) Query:ReportQuery(it.test.app.entity.Ordine)         at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:303)         at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:551)         at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)         at oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:465)         at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)         at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)         at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:270)         at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:600)         at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2115)         at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2081)         at oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:774)         at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:609)         at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:677)         at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:731)         at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2218)         at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937)         at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:909)         at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:346)         at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:447) Thanks, rooz
        • 1. Re: query, join and IS NULL
          rooz
          Hi marina, everything is like in V1  :-( This is the exception for query "e": Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0 (Build b58c-fcs (08/22/2007))): oracle.toplink.essentials.exceptions.DatabaseException Internal Exception: java.sql.SQLException: Syntax error: Encountered "*" at line 1, column 199. Error Code: -1 Call: SELECT t0.ID, t0.NODO_ID ..... FROM ordini t0 LEFT OUTER JOIN an_nodi t1 ON (t1.ID = t0.NODO_ID) WHERE ((t1.NOME = 'abc') OR t0.NODO_ID =* NULL) Query: ReportQuery(it.test.app.entity.Ordine)         at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)         at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:566)         at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:452)         at oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:473)         at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)         at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:214)         at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:285)         at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:615)         at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2416)         at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2382)         at oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:802)         at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:628)         at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:692)         at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:746)         at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2233)         at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:952)         at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:924)         at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:367)         at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:478)
          • 2. Re: query, join and IS NULL
            mvatkina
            Hi, Does the other one (where o.nodo is null OR o.nodo.nome = 'abc' ) also fail? thanks, -marina
            • 3. Re: query, join and IS NULL
              rooz
              Hi marina, I have the same behavior also for "where o.nodo is null OR o.nodo.nome = 'abc'". rooz