Re: The empty list?

Aaron Bono wrote:
> On 11/2/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> Jesper Krogh <jesper(at)krogh(dot)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(at)krogh(dot)cc