Thread: The empty list?

Hi.

Is there someone who can elaborate on why the "empty list" is'nt
implemented in Postgresql?

This works:
# select 1 in (1,2,3);?column?
----------t
(1 row)

And this works:
jesper=# select * from test;id
----
(0 rows)

jesper=# select 1 in (select id from test);?column?
----------f
(1 row)

Whereas this gives a syntax error:

# select 1 in ();
ERROR:  syntax error at or near ")" at character 14
LINE 1: select 1 in ();                    ^
If everyone where writing their SQL by hand .. this would probably not
be a problem, but nowadays lots of people use ORM's for accessing the
database.. thus it would be nice if the SQL implementation was a bit
more generic like a "language".

Jesper
-- 
Jesper Krogh, jesper@krogh.cc

Jesper Krogh <jesper@krogh.cc> writes:
> Whereas this gives a syntax error:

> # select 1 in ();
> ERROR:  syntax error at or near ")" at character 14
> LINE 1: select 1 in ();
>                      ^

If we took that syntax it would probably mean a zero-element row,
not an empty IN list.  But I'm disclined to allow either, as it'd
seem entirely too likely to convert plain old typos into queries
the system will think are valid ...

I think the short answer why the spec disallows this (which it does)
is that it considers the right-hand side to be a table, and with
absolutely nothing there, there is no way to impute a rowtype to the
table.
        regards, tom lane

On 11/2/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
So if this syntax violates the specs then the ORM is the what needs to be change.  The ORM should not attempt to do this.

I have to use DB2 sometimes and it also does not allow empty ().

What ORM are you using that is trying to do this?

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

Aaron Bono wrote:
> On 11/2/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Jesper Krogh <jesper@krogh.cc> writes:
>> > Whereas this gives a syntax error:
>>
>> > # select 1 in ();
>> > ERROR:  syntax error at or near ")" at character 14
>> > LINE 1: select 1 in ();
>> >                      ^
>>
>> I think the short answer why the spec disallows this (which it does)
>> is that it considers the right-hand side to be a table, and with
>> absolutely nothing there, there is no way to impute a rowtype to the
>> table.
>>
>>
> So if this syntax violates the specs then the ORM is the what needs to be
> change.  The ORM should not attempt to do this.

The SQL-spec is not widely available.. (as far as I know), so I cannot
tell if it violates the spec. But Sqlite3 actually supports the empty
list syntax ().

We agree that another place to fix this would be inside the ORM, but it
seems odd that this is considered as a list (1,2,3) whereas this is an
empty row () and not the empty list () cause the right-hand-side of on
in operation will allways be a list (if I havent forgotten something).

(Trying to stuff a row into the right-hand-side of an in operation)

jesper=# insert into test(id,description) values(1,'teststring');
INSERT 0 1
jesper=# select 1 in (select * from test);ERROR:  subquery has too many
columns

And as the original example showed, if the empty list is returned from a
select statement, then it works flawless.


> I have to use DB2 sometimes and it also does not allow empty ().

I know that it is quite unimplemented, mysql doesn't do this either

> What ORM are you using that is trying to do this?

Perl DBIx::Class.. but it is as much about not having to encode all
SQL-strange-cases into a large piece of application logic (the ORM) it
seems quite natural to encode the "in list" in the application as a
list(or array), 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.

Jesper
-- 
Jesper Krogh, jesper@krogh.cc

Jesper Krogh <jesper@krogh.cc> 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
HeapScan 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.420rows=3 loops=1)        Index Cond: (unique1 = ANY ($1))Total runtime: 1.155 ms
 
(5 rows)

regression=# explain analyze execute foo('{}');                                                      QUERY PLAN
                                              
 

-------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan 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.027rows=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

Tom Lane wrote:
> Jesper Krogh <jesper@krogh.cc> writes:
>> ... the right-hand-side of on
>> in operation will allways be a list (if I havent forgotten something).
> 
> IN (SELECT ...) for one thing.

Isn't that "just" a list of rows(complex value) instead of an list of
types(simple values)?

>> ... 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.

That looks nice.. is ANY in the SQL-spec?

Jesper
-- 
Jesper Krogh, jesper@krogh.cc

Jesper Krogh <jesper@krogh.cc> writes:
> Tom Lane wrote:
>> 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.

> That looks nice.. is ANY in the SQL-spec?

ANY is, but I think only in the form "var = ANY (SELECT ...)".
        regards, tom lane

On 11/3/06, Jesper Krogh <jesper@krogh.cc> wrote:
Somebody, somewhere has to programitacally handle the empty array.  You are just suggesting that the database do it, not you.

Also, if you were selecting anything from an empty list and your program knows it is from an empty list, why even bother asking the database.  The additional checks in your code can help reduce database calls and increase performance.

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================