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

Откуда: Львов
Сообщений: 504
Привет всем,
Есть простое репро. Имеем таблицу таймзон и заказов. Нужно скорегировать дату заказа к указанной таймзоне, принимая что в таблице у нас дата заказа храниться в таймзоне UTC (0:00).

if object_id('dbo.TimeZones','U') is not null drop table TimeZones
create table TimeZones
(
	TZid int,
	TZOffset int
)

insert into TimeZones (TZid, TZOffset)
values (1, -5), (2, -6)

if object_id('dbo.Orders','U') is not null drop table Orders
create table Orders
(
	Oid int,	
	ODate datetime,
	TZid int
)

insert into Orders (Oid, ODate, TZid)
values (1, '2012-05-28 16:00', 1), (2, '2012-05-28 16:00', 2)


Казалось, что все просто:
select switchoffset(todatetimeoffset(ODate, 0), 60*tz.TZOffset )
from Orders o
left join TimeZones tz on tz.TZid = o.TZid


Ответ:
2012-05-28 11:00:00.000 -05:00
2012-05-28 10:00:00.000 -06:00


Но! Как только меняю left join на inner join, огребаю странную ошибку
Msg 9812, Level 16, State 1, Line 1
The timezone provided to builtin function switchoffset is invalid.

Сравниваю планы, смотрю - CONVERT_IMPLICIT к smallint опустился ниже. Помогите разобраться. Мне нужно чтоб работало однаково как с left join так и с inner join.

План с left join:

StmtText                                                                                                                                                 StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                            DefinedValues                                                                                                              EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                    Warnings Type                                                             Parallel EstimateExecutions
-------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------- -------- ---------------------------------------------------------------- -------- ------------------
select switchoffset(todatetimeoffset(ODate, 0), 60*tz.TZOffset )
from Orders o
left join TimeZones tz on tz.TZid = o.TZid 1 1 0 NULL NULL 1 NULL 2 NULL NULL NULL 0,00666742 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE:([Expr1006]=switchoffset([Expr1007],CONVERT_IMPLICIT(smallint,(60)*[f].[dbo].[TimeZones].[TZOffset] as [tz].[TZOffset],0)))) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1006]=switchoffset([Expr1007],CONVERT_IMPLICIT(smallint,(60)*[f].[dbo].[TimeZones].[TZOffset] as [tz].[TZOffset],0))) [Expr1006]=switchoffset([Expr1007],CONVERT_IMPLICIT(smallint,(60)*[f].[dbo].[TimeZones].[TZOffset] as [tz].[TZOffset],0)) 2 0 2E-07 16 0,00666742 [Expr1006] NULL PLAN_ROW 0 1
|--Nested Loops(Left Outer Join, WHERE:([f].[dbo].[TimeZones].[TZid] as [tz].[TZid]=[f].[dbo].[Orders].[TZid] as [o].[TZid])) 1 3 2 Nested Loops Left Outer Join WHERE:([f].[dbo].[TimeZones].[TZid] as [tz].[TZid]=[f].[dbo].[Orders].[TZid] as [o].[TZid]) NULL 2 0 1,672E-05 20 0,00666722 [tz].[TZOffset], [Expr1007] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE:([Expr1007]=todatetimeoffset(CONVERT_IMPLICIT(datetime2(3),[f].[dbo].[Orders].[ODate] as [o].[ODate],0),(0)))) 1 4 3 Compute Scalar Compute Scalar DEFINE:([Expr1007]=todatetimeoffset(CONVERT_IMPLICIT(datetime2(3),[f].[dbo].[Orders].[ODate] as [o].[ODate],0),(0))) [Expr1007]=todatetimeoffset(CONVERT_IMPLICIT(datetime2(3),[f].[dbo].[Orders].[ODate] as [o].[ODate],0),(0)) 2 0 2E-07 20 0,0032844 [o].[TZid], [Expr1007] NULL PLAN_ROW 0 1
| |--Table Scan(OBJECT:([f].[dbo].[Orders] AS [o])) 1 5 4 Table Scan Table Scan OBJECT:([f].[dbo].[Orders] AS [o]) [o].[ODate], [o].[TZid] 2 0,003125 0,0001592 19 0,0032842 [o].[ODate], [o].[TZid] NULL PLAN_ROW 0 1
|--Table Scan(OBJECT:([f].[dbo].[TimeZones] AS [tz])) 1 9 3 Table Scan Table Scan OBJECT:([f].[dbo].[TimeZones] AS [tz]) [tz].[TZid], [tz].[TZOffset] 2 0,0032035 8,07E-05 15 0,0033649 [tz].[TZid], [tz].[TZOffset] NULL PLAN_ROW 0 2

План с inner join:

StmtText                                                                                                                                                           StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                         DefinedValues                                                                                                EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                    Warnings Type                                                             Parallel EstimateExecutions
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ----------------------------- -------- ---------------------------------------------------------------- -------- ------------------
select switchoffset(todatetimeoffset(ODate, 0), cast(tz.TZOffset as int))
from Orders o
inner join TimeZones tz on tz.TZid = o.TZid 1 1 0 NULL NULL 1 NULL 2 NULL NULL NULL 0,02436512 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE:([Expr1006]=switchoffset([Expr1007],[Expr1008]))) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1006]=switchoffset([Expr1007],[Expr1008])) [Expr1006]=switchoffset([Expr1007],[Expr1008]) 2 0 2E-07 16 0,02436512 [Expr1006] NULL PLAN_ROW 0 1
|--Hash Match(Inner Join, HASH:([tz].[TZid])=([o].[TZid]), RESIDUAL:([f].[dbo].[Orders].[TZid] as [o].[TZid]=[f].[dbo].[TimeZones].[TZid] as [tz].[TZid])) 1 3 2 Hash Match Inner Join HASH:([tz].[TZid])=([o].[TZid]), RESIDUAL:([f].[dbo].[Orders].[TZid] as [o].[TZid]=[f].[dbo].[TimeZones].[TZid] as [tz].[TZid]) NULL 2 0 0,01779312 18 0,02436492 [Expr1007], [Expr1008] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(smallint,[f].[dbo].[TimeZones].[TZOffset] as [tz].[TZOffset],0))) 1 4 3 Compute Scalar Compute Scalar DEFINE:([Expr1008]=CONVERT_IMPLICIT(smallint,[f].[dbo].[TimeZones].[TZOffset] as [tz].[TZOffset],0)) [Expr1008]=CONVERT_IMPLICIT(smallint,[f].[dbo].[TimeZones].[TZOffset] as [tz].[TZOffset],0) 2 0 2E-07 13 0,0032844 [tz].[TZid], [Expr1008] NULL PLAN_ROW 0 1
| |--Table Scan(OBJECT:([f].[dbo].[TimeZones] AS [tz])) 1 5 4 Table Scan Table Scan OBJECT:([f].[dbo].[TimeZones] AS [tz]) [tz].[TZid], [tz].[TZOffset] 2 0,003125 0,0001592 15 0,0032842 [tz].[TZid], [tz].[TZOffset] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE:([Expr1007]=todatetimeoffset(CONVERT_IMPLICIT(datetime2(3),[f].[dbo].[Orders].[ODate] as [o].[ODate],0),(0)))) 1 9 3 Compute Scalar Compute Scalar DEFINE:([Expr1007]=todatetimeoffset(CONVERT_IMPLICIT(datetime2(3),[f].[dbo].[Orders].[ODate] as [o].[ODate],0),(0))) [Expr1007]=todatetimeoffset(CONVERT_IMPLICIT(datetime2(3),[f].[dbo].[Orders].[ODate] as [o].[ODate],0),(0)) 2 0 2E-07 20 0,0032844 [o].[TZid], [Expr1007] NULL PLAN_ROW 0 1
|--Table Scan(OBJECT:([f].[dbo].[Orders] AS [o])) 1 10 9 Table Scan Table Scan OBJECT:([f].[dbo].[Orders] AS [o]) [o].[ODate], [o].[TZid] 2 0,003125 0,0001592 19 0,0032842 [o].[ODate], [o].[TZid] NULL PLAN_ROW 0 1
28 май 12, 12:32    [12625080]     Ответить | Цитировать Сообщить модератору
 Re: Избавиться от Compute Scalar (CONVERT_IMPLICIT)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Так в хелпе же написано, что
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/32a48e36-0aa4-4260-9fe9-cae9197d16c5.htm
time_zone
Is a character string in the format [+|-]TZH:TZM or a signed integer (of minutes) that represents the time zone offset, and is assumed to be daylight-saving aware and adjusted.

select switchoffset(todatetimeoffset(ODate, 0), case when tz.TZOffset >= 0 then '' else '-' end + right('0' + ( cast( abs ( tz.TZOffset) as varchar(30)) ), 2) + ':00' )
from Orders o
left join TimeZones tz on tz.TZid = o.TZid
go
select switchoffset(todatetimeoffset(ODate, 0), case when tz.TZOffset >= 0 then '' else '-' end + right('0' + ( cast( abs ( tz.TZOffset) as varchar(30)) ), 2) + ':00' )
from Orders o
inner join TimeZones tz on tz.TZid = o.TZid
go


Вот строку туда и передавайте.
28 май 12, 12:42    [12625163]     Ответить | Цитировать Сообщить модератору
 Re: Избавиться от Compute Scalar (CONVERT_IMPLICIT)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Хотя да, про integer там тоже написано.
28 май 12, 12:43    [12625168]     Ответить | Цитировать Сообщить модератору
 Re: Избавиться от Compute Scalar (CONVERT_IMPLICIT)  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
Гавриленко Сергей Алексеевич,

or a signed integer (of minutes)
28 май 12, 12:43    [12625171]     Ответить | Цитировать Сообщить модератору
 Re: Избавиться от Compute Scalar (CONVERT_IMPLICIT)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
В общем, похоже на багу.

Вот так работает:

select switchoffset(todatetimeoffset(ODate, 0), a.x )
from Orders o
left join TimeZones tz on tz.TZid = o.TZid
outer apply (
    select cast(60*tz.TZOffset as int) as x
) a
go
select switchoffset(todatetimeoffset(ODate, 0), a.x )
from Orders o
inner join TimeZones tz on tz.TZid = o.TZid
outer apply ( -- cross apply опять все ломает
    select cast(60*tz.TZOffset as int) as x
) a
go


Сообщение было отредактировано: 28 май 12, 12:53
28 май 12, 12:47    [12625204]     Ответить | Цитировать Сообщить модератору
 Re: Избавиться от Compute Scalar (CONVERT_IMPLICIT)  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
Гавриленко Сергей Алексеевич,
Спасибо, конечно, за оба рабочих варианта - на крайняк так и сделаю, но хотелось без извратов со стрингами
28 май 12, 12:53    [12625248]     Ответить | Цитировать Сообщить модератору
 Re: Избавиться от Compute Scalar (CONVERT_IMPLICIT)  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
Гавриленко Сергей Алексеевич,
Спасибо, Сергей
28 май 12, 12:54    [12625264]     Ответить | Цитировать Сообщить модератору
 Re: Избавиться от Compute Scalar (CONVERT_IMPLICIT)  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

у меня вылечилось указанием типа джойна. причем, тип любой (hash, loop, merge)
можно указывать.

select switchoffset(todatetimeoffset(ODate, 0), 60*tz.TZOffset )
from Orders o
inner hash join TimeZones tz on tz.TZid = o.TZid


да, бага, видимо.
там ведь даже актуальный план не выдается.

Posted via ActualForum NNTP Server 1.5

28 май 12, 13:06    [12625357]     Ответить | Цитировать Сообщить модератору
 Re: Избавиться от Compute Scalar (CONVERT_IMPLICIT)  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
daw
да, бага, видимо.
там ведь даже актуальный план не выдается.

daw, под 2012 работает без проблем и ошибок. На 2008R2 можно получить планы через SET SHOWPLAN_XML ON, но только планы.
* Это просто как информация. Что поведение - бага, полностью согласен.
28 май 12, 13:49    [12625602]     Ответить | Цитировать Сообщить модератору
 Re: Избавиться от Compute Scalar (CONVERT_IMPLICIT)  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> На 2008R2 можно получить планы через SET SHOWPLAN_XML ON, но только планы.

про план я чего-то протупил просто. :(

Posted via ActualForum NNTP Server 1.5

28 май 12, 14:13    [12625773]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить