Обсуждение: 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