INNER join: результат зависит от порядка перечисления источников во from-предложении. Why?

Таблоид
Дата: 17.05.2012 21:27:44
hi all

очередная бесовщина тут выползла. Судя по всему, проявляется, когда один из источников - derived table.
Проверьте у себя вот это:
recreate table t(id int,q int,i_shifted int);
commit;
-- variant 1:
insert into t
with
s as(
select 0 i from rdb$database union all
select 1 i from rdb$database union all
select 2 i from rdb$database union all
select 3 i from rdb$database union all
select 4 i from rdb$database union all
select 5 i from rdb$database union all
select 6 i from rdb$database union all
select 7 i from rdb$database union all
select 8 i from rdb$database union all
select 9 i from rdb$database
)
select i,q,i+x.q i_shifted
from (select count(*) q from t) x inner join s on 1=1;

select * from t;
rollback;

-- variant 2:
insert into t
with
s as(
select 0 i from rdb$database union all
select 1 i from rdb$database union all
select 2 i from rdb$database union all
select 3 i from rdb$database union all
select 4 i from rdb$database union all
select 5 i from rdb$database union all
select 6 i from rdb$database union all
select 7 i from rdb$database union all
select 8 i from rdb$database union all
select 9 i from rdb$database
)
select i,q,i+x.q i_shifted
from s inner join (select count(*) q from t) x on 1=1;

select * from t;
rollback;

Результат в isql:
         ID            Q    I_SHIFTED
=========== ============ ============
0 0 0
1 0 1
2 0 2
3 0 3
4 0 4
5 0 5
6 0 6
7 0 7
8 0 8
9 0 9


ID Q I_SHIFTED
=========== ============ ============
0 0 0
1 1 2
2 2 4
3 3 6
4 4 8
5 5 10
6 6 12
7 7 14
8 8 16
9 9 18

То есть, во втором варианте FB начинает обращаться к fixed-таблице t (в которую при этом идёт insert) на каждой обрабатываемой строке источника "s" (derived-таблицы).
Я сначала подумал, что так происходит из-за "почти одинакового" числа строк в источниках (типа, можно пренебречь различием в каких-то 10 строк). Но после того, как предварительно к добавил к таблице "t" 1000 строк (до вып-я вышеприведенного примера), ситуация осталась прежней:
+
          ID            Q    I_SHIFTED
============ ============ ============
0 1000 1000
1 1000 1001
2 1000 1002
3 1000 1003
4 1000 1004
5 1000 1005
6 1000 1006
7 1000 1007
8 1000 1008
9 1000 1009


ID Q I_SHIFTED
============ ============ ============
0 1000 1000
1 1001 1002
2 1002 1004
3 1003 1006
4 1004 1008
5 1005 1010
6 1006 1012
7 1007 1014
8 1008 1016
9 1009 1018
Чем это объяснить ?
Не могу найти, обсуждалось ли тут: нет ли у ФБ какой-нибудь эвристики по оценке числа строк, возвращаемых из derived-таблицы ?

ISQL Version: WI-V2.5.2.26482 Firebird 2.5
Server version:
Firebird/x86/Windows NT (access method), version "WI-V2.5.2.26482 Firebird 2.5"
Firebird/x86/Windows NT (remote server), version "WI-V2.5.2.26482 Firebird 2.5/XNet (CSPROG)/P12"
Firebird/x86/Windows NT (remote interface), version "WI-V2.5.2.26482 Firebird 2.5/XNet (CSPROG)/P12"
on disk structure version 11.2
Таблоид
Дата: 17.05.2012 21:48:55
Вижу, что первый пример фактически юзает не inner, а cross join: источники ведь совершенно не связаны друг с другом.
Вот теперь уже точно с inner соединением:
recreate table t(id int,q int,i_shifted int);
insert into t
with recursive
r as(select 0 i from rdb$database union all select r.i+1 from r where r.i<5)
select -i,-1,null
from r;
commit;
/* то есть, перед началом следующих запросов в таблице `t` будет:
ID Q I_SHIFTED
============ ============ ============
0 -1 <null>
-1 -1 <null>
-2 -1 <null>
-3 -1 <null>
-4 -1 <null>
-5 -1 <null>
*/

-- variant 1:
insert into t
with
s as(
select 0 i from rdb$database union all
select 1 i from rdb$database union all
select 2 i from rdb$database union all
select 3 i from rdb$database union all
select 4 i from rdb$database union all
select 5 i from rdb$database union all
select 6 i from rdb$database union all
select 7 i from rdb$database union all
select 8 i from rdb$database union all
select 9 i from rdb$database
)
select i,q,i+x.q i_shifted
from (select count(*) q from t) x inner join s on x.q>s.i;

select * from t where i_shifted is not null;
rollback;

--variant 2:
insert into t
with
s as(
select 0 i from rdb$database union all
select 1 i from rdb$database union all
select 2 i from rdb$database union all
select 3 i from rdb$database union all
select 4 i from rdb$database union all
select 5 i from rdb$database union all
select 6 i from rdb$database union all
select 7 i from rdb$database union all
select 8 i from rdb$database union all
select 9 i from rdb$database
)
select i,q,i+x.q i_shifted
from s inner join (select count(*) q from t) x on x.q>s.i;

select * from t where i_shifted is not null;
rollback;

Result:

ID Q I_SHIFTED
============ ============ ============
0 6 6
1 6 7
2 6 8
3 6 9
4 6 10
5 6 11


ID Q I_SHIFTED
============ ============ ============
0 6 6
1 7 8
2 8 10
3 9 12
4 10 14
5 11 16
6 12 18
7 13 20
8 14 22
9 15 24
Как видим, во втором варианте всё равно идёт пересчет записей `t` (подсчёт их числа) для каждой обработанной строки источника `s`.
dimitr
Дата: 17.05.2012 21:50:57
насколько я вижу, тут имеем любимый "нестабильный курсор", ибо ты читаешь из таблицы, в которую тут же вставляешь. Результат есс-но будет зависеть от плана выполнения, вплоть до порядка соединения таблиц.
hvlad
Дата: 17.05.2012 23:39:01
Таблоид
Проверьте у себя вот это:
+
recreate table t(id int,q int,i_shifted int);
commit;
-- variant 1:
insert into t
with
s as(
select 0 i from rdb$database union all
select 1 i from rdb$database union all
select 2 i from rdb$database union all
select 3 i from rdb$database union all
select 4 i from rdb$database union all
select 5 i from rdb$database union all
select 6 i from rdb$database union all
select 7 i from rdb$database union all
select 8 i from rdb$database union all
select 9 i from rdb$database
)
select i,q,i+x.q i_shifted
from (select count(*) q from t) x inner join s on 1=1;

select * from t;
rollback;

-- variant 2:
insert into t
with
s as(
select 0 i from rdb$database union all
select 1 i from rdb$database union all
select 2 i from rdb$database union all
select 3 i from rdb$database union all
select 4 i from rdb$database union all
select 5 i from rdb$database union all
select 6 i from rdb$database union all
select 7 i from rdb$database union all
select 8 i from rdb$database union all
select 9 i from rdb$database
)
select i,q,i+x.q i_shifted
from s inner join (select count(*) q from t) x on 1=1;

select * from t;
rollback;
Ничего, что я в ИБЕ ?
Оба раза получаю:
ID Q I_SHIFTED
0 0 0
1 0 1
2 0 2
3 0 3
4 0 4
5 0 5
6 0 6
7 0 7
8 0 8
9 0 9


PS FB3 ;)
miwaonline
Дата: 17.05.2012 23:56:00
hvlad
...
PS FB3 ;)


Читер :)
Таблоид
Дата: 18.05.2012 00:07:27
dimitr
насколько я вижу, тут имеем любимый "нестабильный курсор", ибо ты читаешь из таблицы, в которую тут же вставляешь. Результат есс-но будет зависеть от плана выполнения, вплоть до порядка соединения таблиц.
Тогда почему этот самый нестабильный курсор перестаёт проявлять себя вот в таком примере:
recreate table s(i int); -- fixed-таблица вместо СТЕ (или GTT, результат такой же)
commit;
insert into s
  select 0 i from rdb$database union all
  select 1 i from rdb$database union all
  select 2 i from rdb$database union all
  select 3 i from rdb$database union all
  select 4 i from rdb$database union all
  select 5 i from rdb$database union all
  select 6 i from rdb$database union all
  select 7 i from rdb$database union all
  select 8 i from rdb$database union all
  select 9 i from rdb$database;
commit;

recreate table t(id int,q int,i_shifted int);
insert into t
with recursive
r as(select 0 i from rdb$database union all select r.i+1 from r where r.i<5)
select -i,-1,null
from r;
commit;

-- variant 1:
insert into t
select i,q,i+x.q i_shifted
from (select count(*) q from t) x inner join s on x.q>s.i;

select * from t where i_shifted is not null;
rollback;

--variant 2:
insert into t
select i,q,i+x.q i_shifted
from s inner join (select count(*) q from t) x on x.q>s.i;

select * from t where i_shifted is not null;
rollback;
Result:
          ID            Q    I_SHIFTED
============ ============ ============
0 6 6
1 6 7
2 6 8
3 6 9
4 6 10
5 6 11


ID Q I_SHIFTED
============ ============ ============
0 6 6
1 6 7
2 6 8
3 6 9
4 6 10
5 6 11
Таблоид
Дата: 18.05.2012 00:14:52
ЗЫ. Планы выполнения для второго примера отличаются, помимо порядка таблиц, еще и наличием запятой:
PLAN JOIN (X T NATURAL(S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL))
------------------------
PLAN JOIN ((S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL)
PLAN (S RDB$DATABASE NATURAL), X T NATURAL)
Не сочтите за буквоедство, но почему запятую в первом варианте "проглотило" ?
dimitr
Дата: 18.05.2012 08:34:32
пути оптимизатора неисповедимы :-) Я даже разбираться не хочу, почему оно именно так. Сказано уже, до FB3 работать оно может как угодно, случайным образом. И никто это править все равно не будет. Хочешь поупираться - допиши ORDER BY '' ко всем запросам и будет тебе одинаковый результат.
Таблоид
Дата: 18.05.2012 09:23:03
dimitr
допиши
ORDER BY ''
ко всем запросам и будет тебе одинаковый результат.
Не пропёрло:
+
recreate table t(id int,q int,i_shifted int);
insert into t
with recursive
r as(select 0 i from rdb$database union all select r.i+1 from r where r.i<5)
select -i,-1,null
from r;
commit;
/* то есть, перед началом следующих запросов в таблице `t` будет:
ID Q I_SHIFTED
============ ============ ============
0 -1 <null>
-1 -1 <null>
-2 -1 <null>
-3 -1 <null>
-4 -1 <null>
-5 -1 <null>
*/
-- variant 1:
insert into t
with
s as(
select i
from(
select 0 i from rdb$database union all
select 1 i from rdb$database union all
select 2 i from rdb$database union all
select 3 i from rdb$database union all
select 4 i from rdb$database union all
select 5 i from rdb$database union all
select 6 i from rdb$database union all
select 7 i from rdb$database union all
select 8 i from rdb$database union all
select 9 i from rdb$database
)u
order by ''
)
select i,q,i+x.q i_shifted
from (select count(*) q from t order by '') x inner join s on x.q>s.i;

select * from t where i_shifted is not null;
rollback;

--variant 2:
insert into t
with
s as(
select i
from(
select 0 i from rdb$database union all
select 1 i from rdb$database union all
select 2 i from rdb$database union all
select 3 i from rdb$database union all
select 4 i from rdb$database union all
select 5 i from rdb$database union all
select 6 i from rdb$database union all
select 7 i from rdb$database union all
select 8 i from rdb$database union all
select 9 i from rdb$database
)u
order by ''
)
select i,q,i+x.q i_shifted
from s inner join (select count(*) q from t order by '') x on x.q>s.i;

select * from t where i_shifted is not null;
rollback;

Результат тот же:

ID Q I_SHIFTED
============ ============ ============
0 6 6
1 6 7
2 6 8
3 6 9
4 6 10
5 6 11


ID Q I_SHIFTED
============ ============ ============
0 6 6
1 7 8
2 8 10
3 9 12
4 10 14
5 11 16
6 12 18
7 13 20
8 14 22
9 15 24
Мну настораживает не то, что тут курсор нестабилен, а какое-то странное влияние на всё это DT.
Впрочем, если это не вылечивается, то и ладно.

BTW, 2 dimitr: тот патч, который ты присылал мне около года назад (по материализации оптимизатором промежуточных результатов) - он будет применён к когда-нибудь ФБ или утонет ? я его тестировал, несколько ошибок тогда нашлось, но ты их пофиксил. Чего еще ждать, может - пора дать народу поиграться ? Жалко ведь, если пропадёт.
dimitr
Дата: 18.05.2012 20:07:23
Таблоид
Не пропёрло

а ты делай так, как сказано, а не так, как тебе хочется. Добавлять надо к внешнему запросу, а не внутрь CTE.

Таблоид
тот патч, который ты присылал мне около года назад (по материализации оптимизатором промежуточных результатов) - он будет применён к когда-нибудь ФБ или утонет ?

будет применен к FB3, как и обещалось