Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
 Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Игорь-PicoMed
Member

Откуда:
Сообщений: 34
Прекрасно работающий запрос в 2.1 - 2.5:

with hst as (
select hh.code srv, hh.Amount, hh.dat, hh.contract
from history hh
left join services srv on hh.code <> 0 and srv.id = hh.code
where hh.id = 123
),
rules as (
select first 1 h.*
from hst h
left join DAYS_OFF df on df.DAT = h.dat
)
select first 1 h.Amount, h.srv
from rules h
left join contract_cache cn
left join contract_detail cd on
(h.srv <> 0 and cd.contract = cn.contract and cd.service = h.srv and cd.type >= 0)
on h.contract <> 0 and cn.id = h.contract
отказывается работать в 3.0.4 с ошибкой в выделенной строке - "Column unknown H.SRV at line 16 column 66"
если не использовать алиас - hh.code srv, а обращаться по имени колонки "code" ошибка также сохраниться
если написать
 select first 1 h.srv
или
 select first 1 h.code
вместо
 select first 1 h.*
ошибка также сохраняется.

По поводу "странности" запроса камни не бросайте - всю математику я из него выбросил, чтобы понять в чем косяк - вложенность необходима (также как и вложенный left join)
17 фев 19, 00:41    [21811918]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 47380

У тебя два алиаса h. Очевидно, подцепляется неправильный.

Posted via ActualForum NNTP Server 1.5

17 фев 19, 01:36    [21811927]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
hvlad
Member

Откуда:
Сообщений: 10267
Игорь-PicoMed,

в условии джойна cn и cd не может быть полей из других таблиц.
17 фев 19, 02:07    [21811933]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Игорь-PicoMed
Member

Откуда:
Сообщений: 34
hvlad
Игорь-PicoMed,

в условии джойна cn и cd не может быть полей из других таблиц.


Почему ???
По стандарту SQL это возможно
17 фев 19, 11:48    [21811994]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Игорь-PicoMed
Member

Откуда:
Сообщений: 34
Dimitry Sibiryakov
У тебя два алиаса h. Очевидно, подцепляется неправильный.

замена второго алиаса на H1 не помогает, тем более что по полям второй алиас включает в себя поля первого

Главный момент - в FB 2.** работает
17 фев 19, 11:53    [21811999]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
dimitr
Member

Откуда: PNZ
Сообщений: 6851
Игорь-PicoMed
По стандарту SQL это возможно

подтвердить можешь?

Игорь-PicoMed
Главный момент - в FB 2.** работает

там много какой херни типа работает :-)

Собственно, это побочный эффект от CORE-2812. Вроде бы кто-то (камрад Коваленко?) уже писал про неработающие вложенные джойны-этажерки с подобными ссылками. Вот только не помню, нашел ли я доказательства в стандарте или просто забил...
17 фев 19, 12:07    [21812005]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
dimitr
Member

Откуда: PNZ
Сообщений: 6851
К слову, если переписать по-человечески:

select first 1 h.Amount, h.srv
 from rules h
  left join contract_cache cn 
    on h.contract <> 0 and cn.id = h.contract
  left join contract_detail cd on 
    (h.srv <> 0 and cd.contract = cn.contract and cd.service = h.srv and cd.type >= 0) 


так работает или нет?
17 фев 19, 12:11    [21812007]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9468
Игорь-PicoMed,

по стандарту on должен быть сразу после join table, а не через один уровень.
И кстати в RN 3.0 про это говорится
17 фев 19, 12:23    [21812016]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
dimitr
Member

Откуда: PNZ
Сообщений: 6851
Симонов Денис
по стандарту on должен быть сразу после join table, а не через один уровень

тут ты не прав, стандарт такое тоже позволяет

Все дело в том, что если правильно раскрыть синтаксис JOIN ... ON, то

from rules h
  left join contract_cache cn 
    left join contract_detail cd
      on (h.srv <> 0 and cd.contract = cn.contract and cd.service = h.srv and cd.type >= 0) 
  on h.contract <> 0 and cn.id = h.contract


превращается в

from rules h
  left join (
    contract_cache cn 
    left join contract_detail cd
      on (h.srv <> 0 and cd.contract = cn.contract and cd.service = h.srv and cd.type >= 0) 
    )
    on h.contract <> 0 and cn.id = h.contract


где
contract_cache cn left join contract_detail cd
по сути неявная derived table, в которой само собой (по стандарту) запрещены ссылки наружу подзапроса, т.е. на h в данном случае
17 фев 19, 12:47    [21812032]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Игорь-PicoMed
Member

Откуда:
Сообщений: 34
dimitr
Игорь-PicoMed
По стандарту SQL это возможно

подтвердить можешь?

Спецификация SQL-92:
7.5  <joined table>
***
a) Each <column reference> directly contained in the <search
condition> shall unambiguously reference a column of T1 or T2
or be an outer reference.


dimitr
Игорь-PicoMed
Главный момент - в FB 2.** работает

там много какой херни типа работает :-)


В данном случае эта "хрень" также работает в Оракле и postgresql

dimitr
Вот только не помню, нашел ли я доказательства в стандарте или просто забил...

Симонов Денис
по стандарту on должен быть сразу после join table, а не через один уровень.
И кстати в RN 3.0 про это говорится


На прямую в стандарте действительно так написано:
7.5  <joined table>
***
<qualified join> ::=
<table reference> [ NATURAL ] [ <join type> ] JOIN
<table reference> [ <join specification> ]

<join specification> ::=
<join condition>
| <named columns join>

<join condition> ::= ON <search condition>
***

однако в той же спецификации
см:
  21.2.9  COLUMNS view 
***
FROM DEFINITION_SCHEMA.COLUMNS AS C
LEFT JOIN
DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D1
LEFT JOIN
DEFINITION_SCHEMA.COLLATIONS AS C1
ON
( ( C1.COLLATION_CATALOG, C1.COLLATION_SCHEMA, C1.COLLATION_NAME )
= ( D1.COLLATION_CATALOG, D1.COLLATION_SCHEMA, D1.COLLATION_NAME ) )
ON
( ( C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME )
= ( D1.TABLE_OR_DOMAIN_CATALOG, D1.TABLE_OR_DOMAIN_SCHEMA,
D1.TABLE_OR_DOMAIN_NAME, D1.COLUMN_NAME ) )
LEFT JOIN
DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D2
LEFT JOIN
DEFINITION_SCHEMA.COLLATIONS AS C2
ON
( ( C2.COLLATION_CATALOG, C2.COLLATION_SCHEMA, C2.COLLATION_NAME )
= ( D2.COLLATION_CATALOG, D2.COLLATION_SCHEMA, D2.COLLATION_NAME ) )
ON*** и т.д.
17 фев 19, 13:17    [21812045]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Игорь-PicoMed
Member

Откуда:
Сообщений: 34
dimitr
К слову, если переписать по-человечески:

select first 1 h.Amount, h.srv
 from rules h
  left join contract_cache cn 
    on h.contract <> 0 and cn.id = h.contract
  left join contract_detail cd on 
    (h.srv <> 0 and cd.contract = cn.contract and cd.service = h.srv and cd.type >= 0) 


так работает или нет?


в таком варианте работает, но будет ли это корректно с точки зрения оптимизатора?

dimitr
по сути неявная derived table, в которой само собой (по стандарту) запрещены ссылки наружу подзапроса, т.е. на h в данном случае

Так оно по сути и должно быть с точки зрения логики построения запроса - в данном случае перемножение h*cd гораздо больше чем cn*cd, и см. письмо выше, стандарт разрешает внешние ссылкы в данных запросах
17 фев 19, 13:23    [21812047]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9468
Игорь-PicoMed,

может оно и позволяется стандартом, но лично меня от таких запросов коробит и начинает дёргаться глаз ибо запрос становится не читаемым и трудно понимаемым.
Привык в on видеть ясные и понятные условия того что соединяется, а не вообще любые.
А то что вы написали практически превращает JOIN в LATERAL JOIN, которые всё равно пока не работают.

Да и левые джойны пока что не дают оптимизатору никакого пространства, нету выполнения разными алгоритмами (только NESTED LOOP разве что с индексами поиграться), порядок соединения всё равно задан жёстко.
17 фев 19, 13:47    [21812055]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Игорь-PicoMed
Member

Откуда:
Сообщений: 34
Симонов Денис
Игорь-PicoMed,
может оно и позволяется стандартом, но лично меня от таких запросов коробит и начинает дёргаться глаз ибо запрос становится не читаемым и трудно понимаемым.

Если бы вы видели запрос целиком, то не только бы глаз начал дергаться - меня он и бесит и коробит, но альтернатива - использование GTT и selectable процедур, что не есть гуд, так как заведомо не совместимо с другими SQL базами, да и работает медленнее.
Симонов Денис
А то что вы написали практически превращает JOIN в LATERAL JOIN, которые всё равно пока не работают.
Да и левые джойны пока что не дают оптимизатору никакого пространства... порядок соединения всё равно задан жёстко.

и тут согласен - приходится самому включать мозг определяя порядок соединения исходя из объемов объединяемых таблиц.
Но какая альтернатива?
17 фев 19, 13:54    [21812060]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 47380

Игорь-PicoMed
стандарт разрешает внешние ссылкы в данных запросах

Внешние ссылки требуют внешних соединений. Иначе при рекомбинации таблиц ссылка может
внезапно стать внутренней.

Posted via ActualForum NNTP Server 1.5

17 фев 19, 14:05    [21812066]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 27939
Симонов Денис
лично меня от таких запросов коробит и начинает дёргаться глаз ибо запрос

а сравнения столбцов с константами в условии объединения таблиц?
h.srv <> 0, cd.type >= 0 ...
Это уже почти глаз на ж. Это же не условия объединения, это "фильтрация", им место в where.
17 фев 19, 19:48    [21812181]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9468
kdv,

да не, как раз с left join это уже условие соединение, вынос в where изменит результат.
17 фев 19, 20:04    [21812188]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
Vlad F
Member

Откуда:
Сообщений: 778
kdv,

КМК, у тебя "глаз на ж" уже точно замылился, ведь в случае несимметрижных объединений это будет
кардинально другой запрос.))
17 фев 19, 20:07    [21812193]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
WildSery
Member

Откуда: да, оттуда.
Сообщений: 15353
kdv
Это уже почти глаз на ж. Это же не условия объединения, это "фильтрация", им место в where.
Симонов Денис
да не, как раз с left join это уже условие соединение, вынос в where изменит результат.

Мало того, что полно ситуаций, когда по-другому нельзя, соответственно, утверждать, что вот здесь надо в условие соединения, а вообще обязательно в "где" - это слегка раздвоение.
Даже если внутренним соединением соедините пару десятков таблиц, посмотрю я на ваш глаз, где условия фильтрации для присоединяемых будут во WHERE, а не аккуратно и очевидно в условии соединения.
18 фев 19, 09:09    [21812521]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или почему рабочий запрос с 2.** не работает в 3.**  [new]
dimitr
Member

Откуда: PNZ
Сообщений: 6851
Дошли тут руки покопаться немного.

Игорь-PicoMed
В данном случае эта "хрень" также работает в Оракле и postgresql

оракла под рукой нет, увы. Однако, в PG это не работает:

select *
from part
  join partsupp
    join supplier on p_size = 0 and ps_suppkey = s_suppkey
on p_partkey = ps_partkey


ERROR: столбец "p_size" не существует
HINT: Столбец "p_size" есть в таблице "part", но на него нельзя ссылаться из этой части запроса
10 мар 19, 11:21    [21828665]     Ответить | Цитировать Сообщить модератору
Все форумы / Firebird, InterBase Ответить