Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UGA5j-0008Tg-9N for pgsql-sql@arkaria.postgresql.org; Thu, 14 Mar 2013 15:29:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UGA5i-0002dA-OQ for pgsql-sql@arkaria.postgresql.org; Thu, 14 Mar 2013 15:29:14 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UGA5h-0002cB-FT for pgsql-sql@postgresql.org; Thu, 14 Mar 2013 15:29:13 +0000 Received: from adsltrust.ath.forthnet.gr ([194.219.204.174] helo=smadev.internal.net) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UGA5d-00019d-BD for pgsql-sql@postgresql.org; Thu, 14 Mar 2013 15:29:12 +0000 X-Bogosity: No, tests=bogofilter Received: from smadev.internal.net (localhost [127.0.0.1]) by smadev.internal.net (8.14.5/8.14.5) with ESMTP id r2EFT73V091137 for ; Thu, 14 Mar 2013 17:29:07 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Received: (from achill@localhost) by smadev.internal.net (8.14.5/8.14.5/Submit) id r2EFT72E091136 for pgsql-sql@postgresql.org; Thu, 14 Mar 2013 17:29:07 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) X-Authentication-Warning: smadev.internal.net: achill set sender to achill@matrix.gatewaynet.com using -f From: Achilleas Mantzios To: pgsql-sql@postgresql.org Subject: bug in 9.2.2 ? subquery accepts wrong column name : upd Date: Thu, 14 Mar 2013 17:29:07 +0200 Message-ID: <1641558.Xf5bk256pC@smadev.internal.net> Organization: Dynacom Tankers Mgmt X-Face: "g.Z.Lx$T1ZMcQ%hC!e^E&tD,cT:"bTs45WM(,vUj@8QBz6}T'sn+EnZTzy`UVQ:&A=`_; f)V+K4z}rG5:(uu[b:WY'*`6F"ou-Or(q; u{#Gxx|MkO4E.vh@E}[#7Ytt"shtU>A&@CO` a|Wx]m_wRD,?4!'Ir1$4iis{/.WU<`#dhKI]g2w^!B[CvRJr+W|; -VS~QcL!s1"'??rct} ^=5Fa!W!{a}Jd:W%6,E[N\r-<)T'_N[~3fy9pF"b>-Yj^p}/2tPudP>I"$%w]"W4CIja6J Tajm}"8t`-hJlf2kRQ_V,eT_kN6KLG+~2mZ+cPX,p,xQN9QVR List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM (select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY sst.setid ORDER BY character_length(vsl.name))) as arragg FROM sissets sst, vessels vsl WHERE vsl.id=sst.vslid ORDER BY sst.setid) as qry WHERE array_length(qry.arragg,1)>1 AND qry.setid IN (SELECT setid from sis_oper_cons) ORDER BY qry.setid,array_length(qry.arragg,1); setid | arragg -------+---------------------------------- 54 | {EQUZZZ,SAMZZZ} 55 | {"ZZZR","ZZZTRAVEL"} 81 | {"ZZZ SISTER","ZZZ DUMMY II"} (3 rows) however, there is not column setid in sis_oper_cons, dynacom=# SELECT setid from sis_oper_cons; ERROR: column "setid" does not exist LINE 1: SELECT setid from sis_oper_cons; ^ 9.2.2 Postgresql treats qry.setid IN (SELECT setid from sis_oper_cons) as true. However, making subquery look like (SELECT soc.setid from sis_oper_cons soc), as in dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM (select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY sst.setid ORDER BY character_length(vsl.name))) as arragg FROM sissets sst, vessels vsl WHERE vsl.id=sst.vslid ORDER BY sst.setid) as qry WHERE array_length(qry.arragg,1)>1 AND qry.setid NOT IN (SELECT soc.setid from sis_oper_cons soc) ORDER BY qry.setid,array_length(qry.arragg,1); ERROR: column soc.setid does not exist LINE 1: ...gth(qry.arragg,1)>1 AND qry.setid NOT IN (SELECT soc.setid ... ^ dynacom=# postgresql corerctly identifies and throws the error. - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql