Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-54

bad sql pushdown, should use all syntax

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 0.9.7
    • query
    • None
    • Windows xp, derby, db2

    Description

      454 bad sql pushdown, should use all syntax
      TEST454; select e from EmpBean e where e.empid not in (select e.no from DeptBean e)
      Scalar subquery is only allowed to return a single row.

      {prepstmnt 241176160 SELECT t0.empid, t0.bonus, t2.deptno, t2.budget, t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street, t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street, t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street = t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE (NOT (t0.empid = (SELECT t1.deptno FROM DeptBean t1)) AND t0.empid IS NOT NULL)} [code=30000, state=21000]
      s/b
      select t0.empid from EMPBean t0 where ( t0.empid <> ALL ( select t2.deptno from DEPTbean t2) )

      [ FAILED 454- bucket = fvtfull, query = select e from EmpBean e where e.empid not in (select e.no from DeptBean e) :
      DIFFERENCE-locations based on expected-(
      diff at line 2 position 2 EXPECTED [T] ACTUAL [ ]
      TEST454; 0 tuples
      e
      )
      EXPECTED(
      TEST454; select e from EmpBean e where e.empid not in (select e.no from DeptBean e)
      TEST454; 0 tuples )
      ACTUAL(
      TEST454; select e from EmpBean e where e.empid not in (select e.no from DeptBean e)
      e
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Scalar subquery is only allowed to return a single row. {prepstmnt 241176160 SELECT t0.empid, t0.bonus, t2.deptno, t2.budget, t2.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t3.street, t3.city, t3.state, t3.zip, t0.isManager, t0.name, t0.salary, t4.street, t4.city, t4.state, t4.zip FROM EmpBean t0 LEFT OUTER JOIN DeptBean t2 ON t0.dept_deptno = t2.deptno LEFT OUTER JOIN AddressBean t3 ON t0.home_street = t3.street LEFT OUTER JOIN AddressBean t4 ON t0.work_street = t4.street WHERE (NOT (t0.empid = (SELECT t1.deptno FROM DeptBean t1)) AND t0.empid IS NOT NULL)}

      [code=30000, state=21000]
      TEST454; 1 tuple) ]

      Attachments

        1. failureEntities.jar
          7 kB
          George Hongell

        Activity

          No work has yet been logged on this issue.

          People

            Unassigned Unassigned
            ghongell George Hongell
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: