"internal error" triggered by EXISTS()

I tried the following to find out whether a table has any records
with field1 < X (for a constant X):

tgl=> SELECT EXISTS(SELECT * FROM table WHERE field1 < X);
ERROR: internal error: do not know how to transform targetlist

Is this a bug? (I'm using development sources from yesterday.)

Am I using EXISTS() incorrectly? The examples I've been able to find
only show it as a part of a WHERE clause.

If it did work, would it be any faster than a table scan? The code
I was hoping to replace is like this:
SELECT COUNT(field1) WHERE field1 < X;
// test whether result > 0
Since aggregates aren't optimized very well, this ends up reading
much or all of the table, even if there is an index for field1.
I was hoping EXISTS() might be smarter...

regards, tom lane

Should have given a syntax error probably. But you might try:

select 1 where exists (select...);

Should be faster if and only if we are doing the existential query
optimization trick (stop on the first qualifying row).

-dg

David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
- If simplicity worked, the world would be overrun with insects. -

David Gould wrote:

I tried the following to find out whether a table has any records
with field1 < X (for a constant X):

tgl=> SELECT EXISTS(SELECT * FROM table WHERE field1 < X);
ERROR: internal error: do not know how to transform targetlist

Should have given a syntax error probably. But you might try:

select 1 where exists (select...);

Should be faster if and only if we are doing the existential query
optimization trick (stop on the first qualifying row).

We do.

Vadim

We only support subqueries in the target list. May have that expaned
for 6.4.

I tried the following to find out whether a table has any records
with field1 < X (for a constant X):

tgl=> SELECT EXISTS(SELECT * FROM table WHERE field1 < X);
ERROR: internal error: do not know how to transform targetlist

Is this a bug? (I'm using development sources from yesterday.)

Am I using EXISTS() incorrectly? The examples I've been able to find
only show it as a part of a WHERE clause.

If it did work, would it be any faster than a table scan? The code
I was hoping to replace is like this:
SELECT COUNT(field1) WHERE field1 < X;
// test whether result > 0
Since aggregates aren't optimized very well, this ends up reading
much or all of the table, even if there is an index for field1.
I was hoping EXISTS() might be smarter...

regards, tom lane

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Bruce Momjian wrote:

We only support subqueries in the target list. May have that expaned
for 6.4.

Not sure that EXISTS is allowed in target list...

Vadim

Bruce Momjian wrote:

We only support subqueries in the target list. May have that expaned
for 6.4.

Not sure that EXISTS is allowed in target list...

Vadim

I meant to say we only support subqueries in the "WHERE" clause. We do
NOT support subqueries in the target list. That may change in 6.4.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

We only support subqueries in the target list. May have that expaned
for 6.4.

Not sure that EXISTS is allowed in target list...

The standard does not allow it, but it might be a nifty feature if it returned a boolean true or false.

Andreas

Andreas Zeugswetter wrote:

We only support subqueries in the target list. May have that expaned
for 6.4.

Not sure that EXISTS is allowed in target list...

The standard does not allow it, but it might be a nifty feature if
it returned a boolean true or false.

I don't foresee problems with this.
BTW, shouldn't we allow the same for IN, ANY and ALL?

select ..., x in (...), ...

Vadim