Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D82DD9FB260 for ; Fri, 3 Nov 2006 02:36:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 02482-01 for ; Fri, 3 Nov 2006 02:36:18 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 592AC9FA4AD for ; Fri, 3 Nov 2006 02:36:17 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kA36aEnc026900; Fri, 3 Nov 2006 01:36:14 -0500 (EST) To: Jesper Krogh cc: Aaron Bono , pgsql-sql@postgresql.org Subject: Re: The empty list? In-reply-to: <454ADB6F.9070602@krogh.cc> References: <454A654B.2000306@krogh.cc> <21991.1162506863@sss.pgh.pa.us> <454ADB6F.9070602@krogh.cc> Comments: In-reply-to Jesper Krogh message dated "Fri, 03 Nov 2006 07:02:23 +0100" Date: Fri, 03 Nov 2006 01:36:14 -0500 Message-ID: <26899.1162535774@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/33 X-Sequence-Number: 26685 Jesper Krogh writes: > ... the right-hand-side of on > in operation will allways be a list (if I havent forgotten something). IN (SELECT ...) for one thing. > ... but missing SQL for the empty list basically means that > we cant handle the empty set encoded in the empty array without > explicitly introducing code for this size of array. From a > programatically viewpoint, this seems quite odd. FWIW, as of 8.2 the best option will probably be to use "col = ANY (array)", which does support zero-length arrays if you use either an out-of-line parameter or an array literal. regression=# prepare foo(int[]) as select * from tenk1 where unique1 = any ($1); PREPARE regression=# explain analyze execute foo('{1,9,42}'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on tenk1 (cost=36.38..424.30 rows=489 width=244) (actual time=0.487..0.524 rows=3 loops=1) Recheck Cond: (unique1 = ANY ($1)) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..36.38 rows=489 width=0) (actual time=0.420..0.420 rows=3 loops=1) Index Cond: (unique1 = ANY ($1)) Total runtime: 1.155 ms (5 rows) regression=# explain analyze execute foo('{}'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on tenk1 (cost=36.38..424.30 rows=489 width=244) (actual time=0.044..0.044 rows=0 loops=1) Recheck Cond: (unique1 = ANY ($1)) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..36.38 rows=489 width=0) (actual time=0.027..0.027 rows=0 loops=1) Index Cond: (unique1 = ANY ($1)) Total runtime: 0.478 ms (5 rows) This is not SQL-standard syntax IIRC, but then "foo IN ()" would certainly not be either. regards, tom lane