1 Reply Latest reply on Jun 11, 2004 5:52 AM by Alexey Loubyansky

    problem with SQL92Compiler

    Joachim Van der Auwera Expert

      Alexey,

      I tried using the

      <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler>

      setting, but it seems to cause problems.

      For a query defined as :
      SELECT OBJECT(o) FROM Weight o
      WHERE ( o.sale.client.traceId = ?1 ) AND ( o.sale.date = ?2 ) AND ( o.unidModificationDate >= ?3 )
      ORDER BY o.unidModificationDate LIMIT ?4
      


      In this table "sale" is of type WeightGroup (CMR), in in WeightGroup, "client" is of type "Company" (CMR)

      It generates the following SQL code (field replaced by *) :

      SELECT * FROM T_WEIGHT t0_o
      LEFT OUTER JOIN T_WEIGHTGROUP t2_o_sale ON t0_o.f_Sale_UOID=t2_o_sale.UNID_UOID
      LEFT OUTER JOIN T_COMPANY t1_o_sale_client ON t0_o.f_Client_UOID=t1_o_sale_client.UNID_UOID
      WHERE (t1_o_sale_client.f_TraceId = ?) AND (t2_o_sale.f_Date = ?) AND (t0_o.UNID_MODIFIED >= ?) ORDER BY t0_o.UNID_MODIFIED ASC
      


      This is wrong in that the second "left outer join" "skips" a related table, by referencing "t0_o.f_Client_UOID" which should be "t2_o.f_Client_UOID" causing an SQLException because the f_client_uoid does not exist in the t_Weight table.

      Joachim