Bruce,
The "SELECT on two tables where zero or one table in WHERE or target
clause returns no rows" appears to have been fixed in 6.1. I'll put
the script that I tested it with at the end of this message. Works
for me.
Should I put a test like this into the regression tests to make sure no
future coding breaks this?
Also, could you remove the item for me to "Improve optimizer plan choice"?
Vadim's fixes to the optimzers just before 6.1 release took care of my problems.
Thanks.
Darren darrenk@insightdist.com
- -- script for testing selects from multiple tables w/no join clause.
- -- create a couple of simple tables
create table foo (a int4);
create table bar (a int4, b int2);
- -- add some values to foo
insert into foo values (4);
insert into foo values (7);
- -- add a few to bar also
insert into bar values (4,1);
insert into bar values (3,2);
insert into bar values (4,5);
- -- select * from foo, bar;
- -- no join should return cross product of foo and bar
- --
- -- a|a|b
- -- -+-+-
- -- 4|4|1
- -- 7|4|1
- -- 4|3|2
- -- 7|3|2
- -- 4|4|5
- -- 7|4|5
- -- (6 rows)
select * from foo, bar;
- -- select * from foo, bar where foo.a = 4;
- -- now returns only half of the rows from foo
- --
- -- a|a|b
- -- -+-+-
- -- 4|4|1
- -- 4|3|2
- -- 4|4|5
- -- (3 rows)
select * from foo, bar where foo.a = 4;
- -- select * from foo, bar where foo.a = bar.a;
- -- should return 2 rows from bar
- --
- -- a|a|b
- -- -+-+-
- -- 4|4|5
- -- 4|4|1
- -- (2 rows)
select * from foo, bar where foo.a = bar.a;
- -- insert another value into foo to make things interesting
insert into foo values (4);
- -- select * from foo, bar;
- -- no join should return cross product of foo and bar - now 9 rows
- --
- -- a|a|b
- -- -+-+-
- -- 4|4|1
- -- 7|4|1
- -- 4|4|1
- -- 4|3|2
- -- 7|3|2
- -- 4|3|2
- -- 4|4|5
- -- 7|4|5
- -- 4|4|5
- -- (9 rows)
select * from foo, bar;
- -- select * from foo, bar where foo.a = 4;
- -- restrictive clause now return 6 rows...
- -- a|a|b
- -- -+-+-
- -- 4|4|1
- -- 4|4|1
- -- 4|3|2
- -- 4|3|2
- -- 4|4|5
- -- 4|4|5
- -- (6 rows)
select * from foo, bar where foo.a = 4;
- -- select * from foo, bar where foo.a = bar.a;
- -- should now have two from foo, two from bar
- -- a|a|b
- -- -+-+-
- -- 4|4|5
- -- 4|4|1
- -- 4|4|5
- -- 4|4|1
- -- (4 rows)
select * from foo, bar where foo.a = bar.a;
------------------------------
