pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedThe empty list?
8+ messages / 3 participants
[nested] [flat]
* The empty list?
@ 2006-11-02 21:38 Jesper Krogh <jesper@krogh.cc>
0 siblings, 1 reply; 8+ messages in thread
From: Jesper Krogh @ 2006-11-02 21:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: The empty list?
@ 2006-11-02 22:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jesper Krogh <jesper@krogh.cc>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2006-11-02 22:34 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: The empty list?
@ 2006-11-02 22:43 Aaron Bono <postgresql@aranya.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Aaron Bono @ 2006-11-02 22:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jesper Krogh <jesper@krogh.cc>; pgsql-sql
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.
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
==================================================================
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: The empty list?
@ 2006-11-03 06:02 Jesper Krogh <jesper@krogh.cc>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 2 replies; 8+ messages in thread
From: Jesper Krogh @ 2006-11-03 06:02 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: The empty list?
@ 2006-11-03 06:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jesper Krogh <jesper@krogh.cc>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2006-11-03 06:36 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: Aaron Bono <postgresql@aranya.com>; pgsql-sql
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 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: The empty list?
@ 2006-11-03 06:46 Jesper Krogh <jesper@krogh.cc>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Jesper Krogh @ 2006-11-03 06:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Aaron Bono <postgresql@aranya.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: The empty list?
@ 2006-11-03 07:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jesper Krogh <jesper@krogh.cc>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2006-11-03 07:02 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: Aaron Bono <postgresql@aranya.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: The empty list?
@ 2006-11-03 18:26 Aaron Bono <postgresql@aranya.com>
parent: Jesper Krogh <jesper@krogh.cc>
1 sibling, 0 replies; 8+ messages in thread
From: Aaron Bono @ 2006-11-03 18:26 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On 11/3/06, Jesper Krogh <jesper@krogh.cc> wrote:
>
> ... 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.
>
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
==================================================================
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-11-03 18:26 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-02 21:38 The empty list? Jesper Krogh <jesper@krogh.cc>
2006-11-02 22:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-02 22:43 ` Aaron Bono <postgresql@aranya.com>
2006-11-03 06:02 ` Jesper Krogh <jesper@krogh.cc>
2006-11-03 06:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-03 06:46 ` Jesper Krogh <jesper@krogh.cc>
2006-11-03 07:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-03 18:26 ` Aaron Bono <postgresql@aranya.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox