Return-Path: Delivered-To: apmail-incubator-open-jpa-dev-archive@locus.apache.org Received: (qmail 65578 invoked from network); 19 Sep 2006 01:30:59 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 19 Sep 2006 01:30:59 -0000 Received: (qmail 33012 invoked by uid 500); 19 Sep 2006 01:30:59 -0000 Delivered-To: apmail-incubator-open-jpa-dev-archive@incubator.apache.org Received: (qmail 32983 invoked by uid 500); 19 Sep 2006 01:30:59 -0000 Mailing-List: contact open-jpa-dev-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: open-jpa-dev@incubator.apache.org Delivered-To: mailing list open-jpa-dev@incubator.apache.org Received: (qmail 32974 invoked by uid 99); 19 Sep 2006 01:30:59 -0000 Received: from idunn.apache.osuosl.org (HELO idunn.apache.osuosl.org) (140.211.166.84) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 18 Sep 2006 18:30:59 -0700 X-ASF-Spam-Status: No, hits=0.0 required=5.0 tests= X-Ecelerity-Macros: i=209.237.227.198 p=brutus.apache.org ir=198.227.237.209 Received: from ([209.237.227.198:55715] helo=brutus.apache.org) by idunn.apache.osuosl.org (ecelerity 2.1 r(10620)) with ESMTP id 9F/70-25246-0584F054 for ; Mon, 18 Sep 2006 18:30:57 -0700 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id 426B57142FB for ; Tue, 19 Sep 2006 01:27:22 +0000 (GMT) Message-ID: <25992689.1158629242254.JavaMail.jira@brutus> Date: Mon, 18 Sep 2006 18:27:22 -0700 (PDT) From: "George Hongell (JIRA)" To: open-jpa-dev@incubator.apache.org Subject: [jira] Created: (OPENJPA-54) bad sql pushdown, should use all syntax MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N bad sql pushdown, should use all syntax --------------------------------------- Key: OPENJPA-54 URL: http://issues.apache.org/jira/browse/OPENJPA-54 Project: OpenJPA Issue Type: Bug Components: query Environment: Windows xp, derby, db2 Reporter: George Hongell 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) ] -- 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