Hi,
What follows is not necessarily a bug, but may be a misinterpretation of
the SQL standard. I don't actually have a copy of the SQL standard, but I
am working from the book "A Guide to The SQL Standard, Fourth Edition" by
C.J. Date and Hugh Darwen. If you have this book handy, please refer to
page 241 for the following queries:
test=> SELECT VERSION();
version
-----------------------------------------------------------------------
PostgreSQL 7.2.1 on powerpc-unknown-linux-gnu, compiled by GCC 2.95.4
(1 row)
test=> SELECT (1,2,NULL) = (3,NULL,4);
?column?
----------
f
(1 row)
test=> SELECT (1,2,NULL) < (3,NULL,4);
?column?
----------
(1 row)
test=> SELECT (1,2,NULL) = (1,NULL,4);
?column?
----------
(1 row)
test=> SELECT (1,2,NULL) > (NULL,2,4);
?column?
----------
f
(1 row)
According to Date, the results should be false, true, unknown, unknown,
respectively. As you can see above, postgresql gets the correct result
for the queries involving =, but differs for the queries involving < and >.
Just so you know, I'm the developer of an SQL engine called ModSQL
<http://modsql.sourceforge.net/>. I also had problems with these
particular queries. When doing the inequality comparisons, it seems that
the SQL standard specifies that the rows should be treated like strings
and compared from left to right. Postgresql appears to do the comparison
column by column in no particular order (as I first tried to do).
I'm not sure how to explain it better. I hope you understand the problem
here and I assume you have a copy of the relevant SQL standards. If the
standards differ from the book I'm going by, please let me know.
Otherwise, postgresql may need to be changed to better comply with the
standards (assume this is your goal). If I haven't been entirely clear, I
am willing to discuss the matter further.
Thanks for your attention and the great work you've done so far on
postgresql.
Chris.
