Thread: General Bug Report: empty SELECT WHEREs return error
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Austin Schutz
Your email address : tex@habit.com
Category : runtime: back-end: SQL
Severity : serious
Summary: empty SELECT WHEREs return error
System Configuration
--------------------
Operating System : SunOS 5.6
PostgreSQL version : 6.0
Compiler used : gcc 2.8.1
Hardware:
---------
SunOS pita 5.6 Generic_105181-09 sun4u sparc SUNW,Ultra-1
Versions of other tools:
------------------------
--------------------------------------------------------------------------
Problem Description:
--------------------
select * from foo where bar = "baz"
produces "ERROR: attribute 'hna' not found"
if there is no row with bar == "baz".
Rather than errorring an empty set should be returned.
This is important for cross-db-portable applications
which need to know whether a legitimate query returns
no rows vs. a 'bad' query.
It would be possible to test the error response for
being the specific 'attribute not found' error, but this is
extremely un-portable behavior (code would only work on
postgres) and further would mask cases where column bar
doesn't exist and the 'attribute not found' error is
correct.
--------------------------------------------------------------------------
Test Case:
----------
create table foo (bar char(1));
select * from foo where ( bar = "baz" );
ERROR: attribute 'baz' not found
--------------------------------------------------------------------------
Solution:
---------
--------------------------------------------------------------------------
On Wed, 23 Jun 1999, Unprivileged user wrote: > Your name : Austin Schutz > Your email address : tex@habit.com > > Category : runtime: back-end: SQL > Severity : serious > > Summary: empty SELECT WHEREs return error > > System Configuration > -------------------- > Operating System : SunOS 5.6 > > PostgreSQL version : 6.0 > > Compiler used : gcc 2.8.1 > > Problem Description: > -------------------- > select * from foo where bar = "baz" > produces "ERROR: attribute 'hna' not found" > if there is no row with bar == "baz". > Rather than errorring an empty set should be returned. > This is important for cross-db-portable applications > which need to know whether a legitimate query returns > no rows vs. a 'bad' query. > It would be possible to test the error response for > being the specific 'attribute not found' error, but this is > extremely un-portable behavior (code would only work on > postgres) and further would mask cases where column bar > doesn't exist and the 'attribute not found' error is > correct. > > -------------------------------------------------------------------------- > > Test Case: > ---------- > create table foo (bar char(1)); > select * from foo where ( bar = "baz" ); > ERROR: attribute 'baz' not found > You should use a single quotes: create table foo (bar char(1)); select * from foo where bar='baz'; bar --- (0 rows) @------------------+-----------------------------------------------@ | Fomichev Michail | The Government of Kamchatka region. | |<fomichev@null.ru>| The Labour and Social Development Department. | `------------------+-----------------------------------------------'
>You should use a single quotes:
>create table foo (bar char(1));
>select * from foo where bar='baz';
>bar
>---
>(0 rows)
Ah, didn't realize double quotes were a non-portable feature of
MySQL. This will add several extra days to the effort of porting to
Postgres.
Sigh.
Thanks for your help,
Austin