[none] - Mailing list pgsql-bugs

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        : Akihito Kaneko
Your email address    : akaneko@mx2.nisiq.net


System Configuration
- ---------------------
  Architecture (example: Intel Pentium)      : UltraSparc10

  Operating System (example: Linux 2.0.26 ELF)     : Solaris 2.5.1

  PostgreSQL version (example: PostgreSQL-6.4)  : PostgreSQL-6.4-BETA5

  Compiler used (example:  gcc 2.8.0)        : 2.7.2.3


Please enter a FULL description of your problem:
- ------------------------------------------------
Let (QUERY) be a SQL query string.
Then the result of "SELECT (QUERY)" and that of "SELECT * FROM someview"
may differ, where someview is defined as follows:
CREATE VIEW someview
AS SELECT (QUERY)
.

This also occurs on:
FreeBSD/i386 3.0-RELEASE
FreeBSD 2.2.7-STABLE as of Aug. 12, 1998.

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
- ----------------------------------------------------------------------
Sample SQL code:

- -----(cut here)-----
DROP TABLE testtable
\g

CREATE TABLE testtable (
code   CHAR(2),
num    INT4,
seq    INT4,
flag   INT2,
PRIMARY KEY (code, num, seq)
)
\g

DROP VIEW testview
\g

CREATE VIEW testview
AS SELECT
          code,
          num,
          MAX(seq)
FROM testtable
WHERE flag = 0
  AND (code, num) NOT IN (SELECT code, num FROM testtable WHERE flag = 1)
GROUP BY
  code, num
\g

DROP VIEW testview2
\g

CREATE VIEW testview2
AS SELECT
          code,
          num,
          seq
FROM testtable
WHERE (code, num) NOT IN (SELECT code, num FROM testtable WHERE flag = 1)
\g

INSERT INTO testtable VALUES ('01', 1, 1, 0);
INSERT INTO testtable VALUES ('01', 1, 2, 0);
INSERT INTO testtable VALUES ('02', 1, 1, 0);
INSERT INTO testtable VALUES ('02', 1, 2, 1);
INSERT INTO testtable VALUES ('02', 1, 3, 0);

SELECT * FROM testview
\g

SELECT code, num, MAX(seq) FROM testtable
WHERE flag = 0
  AND (code, num) NOT IN (SELECT code, num FROM testtable WHERE flag = 1)
GROUP BY
  code, num
\g

SELECT * FROM testview2
\g

SELECT code, num, seq
FROM testtable
WHERE (code, num) NOT IN (SELECT code, num FROM testtable WHERE flag = 1)
\g
- -----(cut here)-----

If you know how this problem might be fixed, list the solution below:
- ---------------------------------------------------------------------