Bug incompréhensible avec datetime2fromparts()

**StringBuilder** · 02/09/2019, 18h05

Bonjour,

J'ai une table dans laquelle est stockée une date dans une colonne bigint, au format yyyyMMddHHmmssfff

C'est à dire que pour la valeur 20190902175825478 cela correspond au 2 septembre 2019 à 17h58 et 25 secondes 478 millièmes.

Je tente d'utiliser datetime2fromparts pour convertir cette colonne en datetime2.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select 
	MA.Datum_DT,
	datetime2fromparts(cast(MA.Datum_DT / 10000000000000 as bigint), cast(MA.Datum_DT / 100000000000 as bigint) % 100, cast(MA.Datum_DT / 1000000000 as bigint) % 100, cast(MA.Datum_DT / 10000000 as bigint) % 100, cast(MA.Datum_DT / 100000 as bigint) % 100, cast(MA.Datum_DT / 1000 as bigint) % 100, MA.Datum_DT % 1000, 3)
from matable

J'obtiens cette erreur :

Msg*289, Niveau*16, État*5, Ligne*153
Impossible de construire le type de données datetime2, car certains des arguments ont des valeurs qui ne sont pas valides.

Pourtant, si je décompose les colonnes sans tenter de convertir les données, je je trouve aucune valeur incohérente…

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
select
	MA.Datum_DT,
	cast(MA.Datum_DT / 10000000000000 as bigint) yyyy, 
	cast(MA.Datum_DT / 100000000000 as bigint) % 100 MM, 
	cast(MA.Datum_DT / 1000000000 as bigint) % 100 dd, 
	cast(MA.Datum_DT / 10000000 as bigint) % 100 HH, 
	cast(MA.Datum_DT / 100000 as bigint) % 100 mm, 
	cast(MA.Datum_DT / 1000 as bigint) % 100 ss, 
	MA.Datum_DT % 1000 fff
from matable

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
Datum_DT             yyyy                 MM                   dd                   HH                   mm                   ss                   fff
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
20190812160000000    2019                 8                    12                   16                   0                    0                    0
20190814080000000    2019                 8                    14                   8                    0                    0                    0
20190814140000000    2019                 8                    14                   14                   0                    0                    0
20190814150000000    2019                 8                    14                   15                   0                    0                    0
20190814160000000    2019                 8                    14                   16                   0                    0                    0
20190814160000000    2019                 8                    14                   16                   0                    0                    0
20190816100000000    2019                 8                    16                   10                   0                    0                    0
 
(7 lignes affectées)

Qu'est-ce que j'ai mal fait ???

**StringBuilder** · 03/09/2019, 10h15

Je n'y comprends vraiment rien…

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
with MA (Datum_DT)
as
(
	select cast(20190812160000000 as bigint)
	union all
	select cast(20190814080000000 as bigint)
	union all
	select cast(20190814140000000 as bigint)
	union all
	select cast(20190814150000000 as bigint)
	union all
	select cast(20190814160000000 as bigint)
	union all
	select cast(20190814160000000 as bigint)
	union all
	select cast(20190816100000000 as bigint)
)
select
	MA.Datum_DT,
	datetime2fromparts(cast(MA.Datum_DT / 10000000000000 as bigint), cast(MA.Datum_DT / 100000000000 as bigint) % 100, cast(MA.Datum_DT / 1000000000 as bigint) % 100, cast(MA.Datum_DT / 10000000 as bigint) % 100, cast(MA.Datum_DT / 100000 as bigint) % 100, cast(MA.Datum_DT / 1000 as bigint) % 100, MA.Datum_DT % 1000, 3),
	cast(MA.Datum_DT / 10000000000000 as bigint) yyyy, 
	cast(MA.Datum_DT / 100000000000 as bigint) % 100 MM, 
	cast(MA.Datum_DT / 1000000000 as bigint) % 100 dd, 
	cast(MA.Datum_DT / 10000000 as bigint) % 100 HH, 
	cast(MA.Datum_DT / 100000 as bigint) % 100 mm, 
	cast(MA.Datum_DT / 1000 as bigint) % 100 ss, 
	MA.Datum_DT % 1000 fff
from MA;

Je repars donc des mêmes données que celles dans ma table.
Et pourtant ça marche !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
20190812160000000	2019-08-12 16:00:00.000	2019	8	12	16	0	0	0
20190814080000000	2019-08-14 08:00:00.000	2019	8	14	8	0	0	0
20190814140000000	2019-08-14 14:00:00.000	2019	8	14	14	0	0	0
20190814150000000	2019-08-14 15:00:00.000	2019	8	14	15	0	0	0
20190814160000000	2019-08-14 16:00:00.000	2019	8	14	16	0	0	0
20190814160000000	2019-08-14 16:00:00.000	2019	8	14	16	0	0	0
20190816100000000	2019-08-16 10:00:00.000	2019	8	16	10	0	0	0

**StringBuilder** · 03/09/2019, 10h39

Ca y est, je viens de comprendre…

Ben l'optimiseur de SQL Server, il a encore du boulot hein…

Pour faire simple, voici ma requête :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
/* Activités liées */
with wr (id, monday, sunday, lastmonday, nextsunday)
as
(
	select
		MA.ID,
		CAST(FORMAT(DATEADD(week, DATEDIFF(week, 0, datetime2fromparts(MA.Datum_DT / 10000000000000, (MA.Datum_DT / 100000000000) % 100, (MA.Datum_DT / 1000000000) % 100, (MA.Datum_DT / 10000000) % 100, (MA.Datum_DT / 100000) % 100, (MA.Datum_DT / 1000) % 100, MA.Datum_DT % 1000, 3)), 0), 'yyyyMMddHHmmssfff') as bigint),
		CAST(FORMAT(DATEADD(second, -1, DATEADD(week, DATEDIFF(week, 0, datetime2fromparts(MA.Datum_DT / 10000000000000, (MA.Datum_DT / 100000000000) % 100, (MA.Datum_DT / 1000000000) % 100, (MA.Datum_DT / 10000000) % 100, (MA.Datum_DT / 100000) % 100, (MA.Datum_DT / 1000) % 100, MA.Datum_DT % 1000, 3)) + 1, 0)), 'yyyyMMddHHmmssfff') as bigint),
		CAST(FORMAT(DATEADD(week, DATEDIFF(week, 0, datetime2fromparts(MA.Datum_DT / 10000000000000, (MA.Datum_DT / 100000000000) % 100, (MA.Datum_DT / 1000000000) % 100, (MA.Datum_DT / 10000000) % 100, (MA.Datum_DT / 100000) % 100, (MA.Datum_DT / 1000) % 100, MA.Datum_DT % 1000, 3)) - 1, 0), 'yyyyMMddHHmmssfff') as bigint),
		CAST(FORMAT(DATEADD(second, -1, DATEADD(week, DATEDIFF(week, 0, datetime2fromparts(MA.Datum_DT / 10000000000000, (MA.Datum_DT / 100000000000) % 100, (MA.Datum_DT / 1000000000) % 100, (MA.Datum_DT / 10000000) % 100, (MA.Datum_DT / 100000) % 100, (MA.Datum_DT / 1000) % 100, MA.Datum_DT % 1000, 3)) + 2, 0)), 'yyyyMMddHHmmssfff') as bigint)
	from TE_ISI_MA MA
	where MA.ID = 0x00002329001d9c92
),
IDCP (ID, ID_FI, ID_PE)
as
(
	select ID, ID_FI, ID_PE
	from TE_ISI_CP cp
	where cp.LosKZ = 0
	and cp.ID = 4295017575
)
select
	case
		when ma.Datum_DT < wr.monday then 1
		when ma.Datum_DT between wr.monday and wr.sunday then 2
		else 3
	end,
	datetime2fromparts(cast(MA.Datum_DT / 10000000000000 as bigint), cast(MA.Datum_DT / 100000000000 as bigint) % 100, cast(MA.Datum_DT / 1000000000 as bigint) % 100, cast(MA.Datum_DT / 10000000 as bigint) % 100, cast(MA.Datum_DT / 100000 as bigint) % 100, cast(MA.Datum_DT / 1000 as bigint) % 100, MA.Datum_DT % 1000, 3),
	case ma.Kontakt
		when 0 then 'Téléphone'
		when 1 then 'Visite'
		when 210 then 'Visite technique'
		else ''
	end,
	fi.Firma,
	case ma.Zweck
		when 210 then '1 - Prospection'
		when 211 then '2 - Découverte'
		when 212 then '3 - Négocitation'
		when 213 then '4 - Développement'
		when 214 then '5 - Fidélisation'
		when 215 then '6 - Animation'
		when 216 then 'Sans but'
		else ''
	end,
	ma.F7003,
	ma.F7004,
	concat(ma.Dauer, case ma.Einheit
		when 1 then ' secondes'
		when 2 then ' minutes'
		when 3 then ' heures'
		when 4 then ' jours'
		else ''
	end)
from wr
cross join IDCP CP
inner join TE_ISI_ID ID on ID.id_fi = CP.id_fi and ID.id_pe = CP.id_pe
inner join TE_ISI_MA MA on MA.Datum_DT between wr.lastmonday and wr.nextsunday and ma.loskz = 0 and ma.TeilnehmerIds like concat('%', format(id.id, '000000000'), ',%') and ma.Kontakt in (1, 3, 210, 211, 212) and ma.Privat = 0
left outer join TE_ISI_KA ka on ka.LosKZ = 0 and ka.KatNr = 1077 and ka.Code = ma.F7011 and ka.SpracheNr = 0 and ka.ExtKey in ('9001_1077_1', '9001_1077_3')
inner join TE_ISI_FI FI on FI.ID = MA.ID_FI
where (ka.id is not null or ma.Kontakt in (0, 210))
union all
select
	case
		when A3.Startdatum_DT < wr.monday then 1
		when A3.Startdatum_DT between wr.monday and wr.sunday then 2
		else 3
	end,
	datetime2fromparts(A3.Startdatum_DT / 10000000000000, (A3.Startdatum_DT / 100000000000) % 100, (A3.Startdatum_DT / 1000000000) % 100, (A3.Startdatum_DT / 10000000) % 100, (A3.Startdatum_DT / 100000) % 100, (A3.Startdatum_DT / 1000) % 100, A3.Startdatum_DT % 1000, 3),
	case A3.A3Grund
		when 0 then 'Congés'
		when 1 then 'Maladie'
		when 2 then 'Autre absence'
		else ''
	end,
	'',
	'',
	0,
	0,
	''
from wr
cross join IDCP CP
inner join TE_ISI_ID ID on ID.id_fi = CP.id_fi and ID.id_pe = CP.id_pe
inner join TE_ISI_A3 A3 on A3.BeaId = ID.ID and A3.Startdatum_DT between wr.lastmonday and wr.nextsunday
order by 1, 2;

Dans la première partie du UNION, sur la jointure avec TE_ISI_MA, j'ai cette condition :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

inner join TE_ISI_MA MA on MA.Datum_DT between wr.lastmonday and wr.nextsunday and ma.loskz = 0 and ma.TeilnehmerIds like concat('%', format(id.id, '000000000'), ',%') and ma.Kontakt in (1, 3, 210, 211, 212) and ma.Privat = 0

Je vérifie dont bien que Datum_DT est compris entre la valeur de lundi dernier, et de dimanche prochain (par rapport à la date du jour retrouvée dans la ligne MA.ID = 0x00002329001d9c92 de la CTE "wr").

En toute logique, une valeur pourrie dans ma table TE_ISI_MA (genre 0 ou une valeur ne respectant pas le format, avec plus ou moins de chiffres) devrait donc être écartée…

Et pourtant, la requête plantait car SQL Server essayait d'évaluer la valeur "220000000" qui correspond à une activité récurrente à 22h, donc sans la partie date… Cette valeur n'est évidement pas comprise entre 20190826000000000 et 20190915235959000

Pourtant, SQL Server a tenté d'évaluer le résultat de calcul de la partie SELECT avant d'avoir filtré les lignes avec la jointure…

Si je passe le flag LosKZ à 1 sur la ligne, ce flag étant lui aussi présents dans la même clause de jointure, alors je n'ai plus de plantage… et la requête est plus de 10 fois plus rapide !

Il est fou !

**aieeeuuuuu** · 04/09/2019, 15h36

bonjour,

Envoyé par StringBuilder

Pourtant, SQL Server a tenté d'évaluer le résultat de calcul de la partie SELECT avant d'avoir filtré les lignes avec la jointure…

Je pense qu'il s'agit du SELECT de la sous requête (wr)...
Ces colonne entrent en jeu dans les condition de jointure, il est donc normal qu'elle soient calculée avant certains filtres ou autres conditions de jointure.

il faudrait regarder les indexes utilisés, mais en en faisant des indexes couvrants (ou au moins en ajoutant les colonnes qui permettent de filtrer les lignes contenant des "mauvaises" valeurs a convertir), cela pourrait résoudre le problème

**StringBuilder** · 04/09/2019, 21h23

Non, la sous-requête wr lit une seule ligne de la table MA, et pour cette ligne la date est correctement renseignée (si j'ose dire).

C'est bien dans le select final que SQL Server tente de calculer les dates pour des lignes qui pourtant sont exclues par la clause "on" le la jointure sur MA.

Et j'ai drastiquement amélioré les performances en ajoutant un MA.Datum_DT between 19000000000000000 and 29999999999999999.
Visiblement, le between sur des colonnes calculées retournées par wr est évalué... après le select, tandis qu'avec des valeurs littérales il est évalué normalement.

Du coup mon :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

inner join TE_ISI_MA MA on MA.Datum_DT between wr.lastmonday and wr.nextsunday and ma.loskz = 0 and ma.TeilnehmerIds like concat('%', format(id.id, '000000000'), ',%') and ma.Kontakt in (1, 3, 210, 211, 212) and ma.Privat = 0

S'est transformé en :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

inner join TE_ISI_MA MA on MA.Datum_DT between wr.lastmonday and wr.nextsunday and ma.loskz = 0 and ma.TeilnehmerIds like concat('%', format(id.id, '000000000'), ',%') and ma.Kontakt in (1, 3, 210, 211, 212) and ma.Privat = 0 and MA.Datum_DT between 19000000000000000 and 29999999999999999

Et je n'ai plus de problème... Sâchant que wr.lastmonday est largement plus grand que 19000000000000000 et wr.nextsunday largement plus petit que 29999999999999999 ça n'a aucun sens mais bon...

Et accessoirement, je suis passé de 4 minutes (quand ça plantait pas) à 7 secondes...

**aieeeuuuuu** · 05/09/2019, 10h20

Envoyé par StringBuilder

Non, la sous-requête wr lit une seule ligne de la table MA, et pour cette ligne la date est correctement renseignée (si j'ose dire).

A condition que le filtre where MA.ID = 0x00002329001d9c92 soit appliqué avant l'autojointure inner join TE_ISI_MA MA on MA.Datum_DT between wr.lastmonday and wr.nextsunday...

Il faudrait voir le plan d'exécution...

Qu'est-ce que ça donne si on enlève les colonnes qui font le datetime2fromparts dans le SELECT final ?

**StringBuilder** · 05/09/2019, 20h12

Ça fonctionne, c'est vraiment le datetime2fromparts() du select final qui pose problème

**SQLpro** · 06/09/2019, 09h46

Pourquoi ne pas créer une colonne calculée persistante dans votre table qui convertit cette horreur en véritable DATETIME2 ?

A +

**StringBuilder** · 06/09/2019, 20h34

Malheureusement car je ne maîtrise pas le modèle de données.
C'est une table d'un outil éditeur (Aurea.CRM pour ne pas le citer) qui modifie lui-même la structure des tables, index, etc.

Et visiblement ils n'ont pas compris comment fonctionnait le type date...

Si je crée une colonne calculée, à la prochaine modification de paramétrage ma colonne sera certainement détruite.

Reste la solution de la vue...

**SQLpro** · 07/09/2019, 12h51

Envoyé par StringBuilder

... Reste la solution de la vue...

Dans ce cas, vue indexée...

A +

Bug incompréhensible avec datetime2fromparts() [2016]

Développement SQL Server

Discussions similaires

Partager

Partager