Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Где прочитать про то, как оптимизатор "помогает" (см. внутри)  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8644
Похоже, что оптимизатор при построении плана нивелирует огрехи синтаксиса. Но я очкую, есличестно
Потыкайте мордой где неправ...

Разгребал старый говнокод, который успешно (?) пашет уже наверное год на

select @@version

Microsoft SQL Server 2008 (SP2) - 10.0.4316.0 (X64)

и воткнулся в непонятки:

1. удачно тестится и выполняется запрос
+
select cod_artic as 'offer/@id',  case when rez_kolch>0 then 'true' else 'false' end as 'offer/@available'
   from scl_artc WITH (NOLOCK)       
   where scl_artc.id_sclad = 1
     and cast(scl_artc.rez_kolch/(case when isnull(scl_artc.EDN_V_UPAK,0)!=0 then scl_artc.EDN_V_UPAK else 1 end)  as bigint) >= 1
     and scl_artc.cod_artic in ( select cod_artic from (select cod_artic, max(rub_price) rub_price 
                          from scl_pric WITH (NOLOCK) 
                          where name_price = 'price' 
                            and id_sclad = 1
                            and rez_kolch >= 1 
                          group by cod_artic ) GoodsRuPrices )
   for xml path (''), root ('offers') ,  type


2. но внутри используется (внезапно!) ошибочный подзапрос:
select cod_artic from (select cod_artic, max(rub_price) rub_price 
                          from scl_pric WITH (NOLOCK) 
                          where name_price = 'price' 
                            and id_sclad = 1
                            and rez_kolch >= 1 
                          group by cod_artic ) GoodsRuPrices 

Сообщение 207, уровень 16, состояние 1, строка 5
Invalid column name 'rez_kolch'.

, и ущербность подзапроса вот в этой конструкции:
select cod_artic, max(rub_price) rub_price 
                          from scl_pric WITH (NOLOCK) 
                          where name_price = 'price' 
                            and id_sclad = 1
                            and rez_kolch >= 1 
                          group by cod_artic

ибо действительно
Сообщение 207, уровень 16, состояние 1, строка 5
Invalid column name 'rez_kolch'.

----------------------------
В таблице SCL_PRIC действительно нет поля rez_kolch.

+
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SCL_PRIC](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[COD_ARTIC] [varchar](20) NOT NULL,
	[NAME_PRICE] [varchar](30) NULL,
	[N_USER] [varchar](8) NULL,
	[COEF_PRICE] [float] NULL,
	[RUB_PRICE] [float] NULL,
	[VALT_PRICE] [float] NULL,
	[CONTRACT] [varchar](10) NULL,
	[DATE_PRICE] [datetime] NULL,
	[ID_SCLAD] [int] NOT NULL,
 CONSTRAINT [pk_SCL_PRIC_1] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


Это поле есть в scl_artc
+
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SCL_ARTC](
	[COD_ARTIC] [varchar](20) NOT NULL,
	[id_sclad] int not null,
	[NGROUP_TVR] [varchar](30) NULL,
	[NAME_ARTIC] [varchar](200) NULL,
	[CENA_ARTIC] [float] NULL,
	[NACH_KOLCH] [float] NOT NULL,
	[KON_KOLCH] [float] NOT NULL,
	[REZ_KOLCH] [float] NOT NULL,
	[EDN_V_UPAK] [float] NULL,
	[TIP_TOVR] [varchar](10) NULL,
 CONSTRAINT [pk_1] PRIMARY KEY NONCLUSTERED 
(
	[COD_ARTIC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



Получается, что оптимизатор (?) понимает что ему надо взять REZ_KOLCH из [dbo].[SCL_ARTC]?

Запрос вообще значимо больше. Непонятно откуда берётся поле в самом "глубоком" подзапросе. Связанный подзапрос?
В плане ни чего не смог увидеть. Попробовал с опубликованной структурой сделать на 2016 на tempdb - поведение запроса и подзапросов аналогично.

--------------------------
No ROM Basic...
15 мар 19, 15:02    [21833938]     Ответить | Цитировать Сообщить модератору
 Re: Где прочитать про то, как оптимизатор "помогает" (см. внутри)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36689
Кто не пишет алиасы, должен страдать.
15 мар 19, 15:04    [21833942]     Ответить | Цитировать Сообщить модератору
 Re: Где прочитать про то, как оптимизатор "помогает" (см. внутри)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
SIMPLicity_,

алиасы надо писать всегда, тогда не будет таких вопросов
15 мар 19, 15:04    [21833943]     Ответить | Цитировать Сообщить модератору
 Re: Где прочитать про то, как оптимизатор "помогает" (см. внутри)  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8644
поясните....
15 мар 19, 15:38    [21833982]     Ответить | Цитировать Сообщить модератору
 Re: Где прочитать про то, как оптимизатор "помогает" (см. внутри)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
SIMPLicity_
поясните....


медитируй
;WITH  a(c1) AS
(
	SELECT 1 
), b(c2) AS
(
	SELECT 2
)
SELECt 
*
FROM a as T1
WHERE c1 IN 
	(
		SELECT c2
		FROM b AS T2
		WHERE c1 = c2
		--WHERE T1.c1 = T2.c2
		--WHERE T2.c1 = T1.c2
	)

худшая практика в подзапросав явно не прописывать алиасы...

;WITH  
a(c1) AS
(
	SELECT 2222
),
b(c1) AS
(
	SELECT 1
)
SELECt 
*
FROM a 
WHERE EXISTS 
	(
		SELECT 1
		FROM b
		WHERE c1 = c1
	)
15 мар 19, 15:44    [21833989]     Ответить | Цитировать Сообщить модератору
 Re: Где прочитать про то, как оптимизатор "помогает" (см. внутри)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
матчасть судя по всему сие
автор
The general rule is that column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level. If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-2017#qualifying
15 мар 19, 16:23    [21834038]     Ответить | Цитировать Сообщить модератору
 Re: Где прочитать про то, как оптимизатор "помогает" (см. внутри)  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8644
TaPaK
SIMPLicity_
поясните....


медитируй
;WITH  a(c1) AS
(
	SELECT 1 
), b(c2) AS
(
	SELECT 2
)
SELECt 
*
FROM a as T1
WHERE c1 IN 
	(
		SELECT c2
		FROM b AS T2
		WHERE c1 = c2
		--WHERE T1.c1 = T2.c2
		--WHERE T2.c1 = T1.c2
	)

худшая практика в подзапросав явно не прописывать алиасы...

;WITH  
a(c1) AS
(
	SELECT 2222
),
b(c1) AS
(
	SELECT 1
)
SELECt 
*
FROM a 
WHERE EXISTS 
	(
		SELECT 1
		FROM b
		WHERE c1 = c1
	)


Умопомрачительно
15 мар 19, 18:11    [21834185]     Ответить | Цитировать Сообщить модератору
 Re: Где прочитать про то, как оптимизатор "помогает" (см. внутри)  [new]
uaggster
Member

Откуда:
Сообщений: 757
SIMPLicity_, всё строго по стандарту.
Не писать алиасы - небезопасно, и может вызвать катастрофу на ровном месте.
Например, у вас не было алиаса, и всё было хорошо. А потом в соседней таблице появилось поле с тем же наименованием, и запрос перестал работать. После 10 лет безупречной работы, в дебрях хранимой процедуры, о существовании которой все позабыли, и система стала раком.
Или еще хуже = запрос стал работать неправильно, как раз классический вариант говна Вы и продемонстрировали.
Раньше был классный плагин sqlcodeguard, который как раз искал такого рода ошибки в хранимках, а также "повисшие" зависимости.
Сейчас его купила https://www.red-gate.com/products/sql-development/sql-code-guard/
И это совсем не то.
20 мар 19, 14:10    [21838504]     Ответить | Цитировать Сообщить модератору
 Re: Где прочитать про то, как оптимизатор "помогает" (см. внутри)  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8644
К сведению принял. Теперь почти везде пишу псевдонимы.
Меня шокировало, что банальный подзапрос превратился в связанный.
Раньше руководствовался замечание Ицика Бен-Гана типа, псевдонимы не обязательны, но если сомневаетесь писать псевдоним или нет,- пишите,- дополнительные накладные расходы копеечные, а нервы сэкономит.

Но вот в подзапросах давно их не писал,- вроде как плоская таблица,- всё однозначно. Ан нет. Обжёгся. Хорошо хоть что не сильно.
Ну, теперь буду писать правильнее.

Всем спасибо за комментарии.
21 мар 19, 03:25    [21839045]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить