============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Thomas R. Dean
Your email address : dean@legasys.on.ca
Category : runtime: back-end: SQL
Severity : serious
Summary: Errors in Subselect (two related bugs)
System Configuration
--------------------
Operating System : linux 2.0.36 ELF
PostgreSQL version : 6.5b2
Compiler used : gcc 2.7.2.3
Hardware:
---------
Pentium II/768Mb Ram: RedHat 5.1 + updates
Postgres compiled locally and installed manually.
Versions of other tools:
------------------------
--------------------------------------------------------------------------
Problem Description:
--------------------
Bug#1 -> select distinct a from foo where a not in (select
distinct a from bar union select distinct b from bat) ->
gives a syntax error. No syntax error in 6.4.2, although
incorrect results from the query (see Bug#2). Looking at
src/backend/parser/gram.y shows that union has been removed
from SubSelect, but this change is not noted in the
HISTORY file.
Bug#2 -> when the size of foo bar and bat are more than
trivial size (i.e. > 1000 rows), the above query in 6.4.2
returns 0 results (incorrectly, the reason we tried 6.5b2 and
ran into the unoin syntax bug). a and b are both of type
text. If the subquery is directed into a table (i.e select
distinct a into abc from bar union select distinct b from
bat) and then rephrase the original query as select a from
foo where a not in (select a from abc), we still get 0
rows as an answer. This is an error in both 6.4.2 and
6.5b2. Omitting bat from the subquery, i.e. select a
from foo where a not in (select distinct a from bar) gives
the correct results, but not if bar is changed to abc, the
result of the union query. All columns are of type text.
Trivial examples (i.e. total of 10 values) work fine.
The bug seems to be exercised when the tables grow to some
point. Last point: select distinct a from foo except
(select a from bar union select b from bat) and select
a from foo except select a from abc do not work either.
--------------------------------------------------------------------------
Test Case:
----------
Unfortunately the data that excercises the bug contains
private client information.
--------------------------------------------------------------------------
Solution:
---------
--------------------------------------------------------------------------