Проблема с full outer join

LeXa NalBat
Дата: 23.09.2004 14:52:49
select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
(1 row)

create table t1 ( i1 int );
create table t2 ( i2 int );
insert into t1 values ( 1 );
insert into t1 values ( 2 );
insert into t1 values ( 3 );
insert into t1 values ( 4 );
insert into t1 values ( 5 );
insert into t2 values ( 3 );
insert into t2 values ( 4 );
insert into t2 values ( 5 );
insert into t2 values ( 6 );
insert into t2 values ( 7 );
create index n1 on t1 ( i1 );
create index n2 on t2 ( i2 );
set enable_seqscan to off;

explain select i1,i2 from t1 join t2 on ( i1=i2 and i1 between 2 and 6 and i2 between 2 and 6 );
                            QUERY PLAN
-------------------------------------------------------------------
 Merge Join  (cost=0.00..6.71 rows=1 width=8)
   Merge Cond: ("outer".i1 = "inner".i2)
   ->  Index Scan using n1 on t1  (cost=0.00..3.35 rows=1 width=4)
         Index Cond: ((i1 >= 2) AND (i1 <= 6))
   ->  Index Scan using n2 on t2  (cost=0.00..3.35 rows=1 width=4)
         Index Cond: ((i2 >= 2) AND (i2 <= 6))
(6 rows)

explain select i1,i2 from t1 full join t2 on ( i1=i2 and i1 between 2 and 6 and i2 between 2 and 6 );
ERROR:  FULL JOIN is only supported with mergejoinable join conditions

Почему постгрес не может сделать full outer join? Ведь inner join с использованием megre join он может сделать.
4321
Дата: 23.09.2004 15:27:56
почему - не знаю (там, в "немаржибельных" фулл-джойнах с логикой все ли всегда будет в порядке?), но надо подшаманить с промежуточным представлением:

например вот так не работает:
SELECT * FROM test t FULL JOIN test t1 ON t.id=t1.id+1;
ERROR:  FULL JOIN is only supported with mergejoinable join conditions
(без FULL ес-нно работает)
а так:
SELECT * FROM test t FULL JOIN 
   (Select id+1 AS id1, * From test) AS t1 ON t.id=t1.id1;
работает.

А у вас
i1 between 2 and 6 and i2 between 2 and 6
надо попросту запихать в WHERE.
LeXa NalBat
Дата: 23.09.2004 15:44:22
4321
А у вас
i1 between 2 and 6 and i2 between 2 and 6
надо попросту запихать в WHERE.

Да, так работает:

explain select i1,i2 from t1 full join t2 on ( i1=i2 ) where i1 between 2 and 6 and i2 between 2 and 6;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.00..8.25 rows=5 width=8)
   Merge Cond: ("outer".i1 = "inner".i2)
   Filter: (("outer".i1 >= 2) AND ("outer".i1 <= 6) AND ("inner".i2 >= 2) AND ("inner".i2 <= 6))
   ->  Index Scan using n1 on t1  (cost=0.00..4.05 rows=5 width=4)
   ->  Index Scan using n2 on t2  (cost=0.00..4.05 rows=5 width=4)
(5 rows)

Но меня план этого запроса, по очевидной причине, не удовлетворяет. Потому что в реальных таблицах не по пять строк, а около ста тысяч. Нужно, чтобы условия between на i1 и i2 отрабатывались бы в Index Cond (как делается для inner join), а не во внешнем Filter.
LeXa NalBat
Дата: 23.09.2004 15:54:04
И вот еще что! :-) Если условия between убрать в where запроса, то результат такого outer join-а будет совпадать с результатом inner join-а. :-( Потому что строки с i1 is null или i2 is null не будут удовлетворять условию в where.

Если в where поставить конструкции типа ( i1 between 2 and 6 or i1 is null ), тогда мы еще столкнемся с проблемой неумения постгреса использовать index scan для ограничений is null. :-(
LeXa NalBat
Дата: 23.09.2004 16:03:09
Спасибо. Заработало.

explain select i1,i2 from ( select i1 from t1 where i1 between 2 and 6 ) as t1 full join ( select i2 from t2 where i2 between 2 and 6 ) as t2 on ( i1=i2 );
                            QUERY PLAN
-------------------------------------------------------------------
 Merge Join  (cost=0.00..6.71 rows=1 width=8)
   Merge Cond: ("outer".i1 = "inner".i2)
   ->  Index Scan using n1 on t1  (cost=0.00..3.35 rows=1 width=4)
         Index Cond: ((i1 >= 2) AND (i1 <= 6))
   ->  Index Scan using n2 on t2  (cost=0.00..3.35 rows=1 width=4)
         Index Cond: ((i2 >= 2) AND (i2 <= 6))
(6 rows)