Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Падает простейший запрос. Помогите найти причину!  [new]
uaggster
Member

Откуда:
Сообщений: 867
Коллеги, просветите в отношении поведения оптимизатора!

Имеется MSSQL2008R2. Имеется некая таблица, в которой дата хранится в текстовом виде, dd.mm.yyyy, причем некоторые даты - кривые (не даты, мусор, Null-ы и т.д.).

Не работает вот такой запрос:
Set Dateformat dmy;
Select 137, a.ID from (
			select p.id, p.dr, p.DDEAD from dbo.PERSON p 
			 Where IsDate(p.DR)=1 and IsDate(p.DDead)=1
		 ) a
		where not convert(date, a.DDead, 104) between convert(date, a.DR, 104) and GETDATE()

Сообщение 241, уровень 16, состояние 1, строка 2
Conversion failed when converting date and/or time from character string.

Причем внутренний запрос - нормально отрабатывает и возвращает пустой набор (ну, ничего не возвращает).

Смотрю как баран на новые ворота.
В чем проблема?
23 янв 13, 15:55    [13816332]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
Glory
Member

Откуда:
Сообщений: 104760
uaggster
В чем проблема?

В том, что вы считаете, что сначала обязательно выполнится вложенный запрос. А потом только конвертация.
А сервер может эти операции выполнять в разном порядке.
23 янв 13, 16:00    [13816397]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
Во внутреннем запросе сконвертируйте p.DDEAD в NVARCHAR и в WHERE обратно конвертируйте в Date
23 янв 13, 16:01    [13816422]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
uaggster,

проблема в том, что сервер выполняет CONVERT() ДО WHERE в подзапросе (ну, так ему захотелось!).
А вот, например, последовательность проверок в CASE он никогда не меняет!
Так что повторите проверку во внешнем WHERE с помощью CASE
23 янв 13, 16:02    [13816426]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
какое то значение p.DR или p.DDead не в 104 формате
23 янв 13, 16:02    [13816428]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
samoxod
Member

Откуда: Москва
Сообщений: 31
uaggster,
А если добавить условие :
where a.DDead is NOT NULL AND  a.DR IS NOT NULL AND (not convert(date, a.DDead, 104) between convert(date, a.DR, 104) and GETDATE())
23 янв 13, 16:03    [13816441]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Мистер Хенки
какое то значение p.DR или p.DDead не в 104 формате
Да там есть просто мусор, как автор говорит.
23 янв 13, 16:04    [13816449]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Мистер Хенки
какое то значение p.DR или p.DDead не в 104 формате

select convert(date,'2013-01-23 10:10:10',104)
но
select ISDATE('2013-01-23 10:10:10')
23 янв 13, 16:04    [13816451]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
samoxod
uaggster,
А если добавить условие :
where a.DDead is NOT NULL AND  a.DR IS NOT NULL AND (not convert(date, a.DDead, 104) between convert(date, a.DR, 104) and GETDATE())
NULL преобразуется в DATE просто замечательно (как и в любой другой тип).
Проблема не в NULL.

Кстати, порядок вычисления предикатов тоже выбирает сервер!
23 янв 13, 16:07    [13816482]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Мистер Хенки
Мистер Хенки
какое то значение p.DR или p.DDead не в 104 формате

select convert(date,'2013-01-23 10:10:10',104)
но
select ISDATE('2013-01-23 10:10:10')
А так?
SET DATEFORMAT YDM
select ISDATE('2013-01-23 10:10:10')
23 янв 13, 16:10    [13816512]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
uaggster
Member

Откуда:
Сообщений: 867
Glory
В том, что вы считаете, что сначала обязательно выполнится вложенный запрос. А потом только конвертация.
А сервер может эти операции выполнять в разном порядке.

Гм... вот в отношении порядка проверок во Where и Join я слышал (порядок такой, какой посчитает удобным оптимизатор). А вот в отношении выполнения внутренних подзапросов - это для меня откровение!

Тогда вопрос - а как тогда быть?
Очень не хочется делать вставку в промежуточную таблицу!
23 янв 13, 16:12    [13816525]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
А разве для таких задач with... не лучшее ли решение?
23 янв 13, 16:12    [13816526]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
uaggster
Glory
В том, что вы считаете, что сначала обязательно выполнится вложенный запрос. А потом только конвертация.
А сервер может эти операции выполнять в разном порядке.

Гм... вот в отношении порядка проверок во Where и Join я слышал (порядок такой, какой посчитает удобным оптимизатор). А вот в отношении выполнения внутренних подзапросов - это для меня откровение!

Тогда вопрос - а как тогда быть?
Очень не хочется делать вставку в промежуточную таблицу!
Я ж вроде пытался подсказать про CASE
23 янв 13, 16:13    [13816541]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
Glory
Member

Откуда:
Сообщений: 104760
uaggster
Гм... вот в отношении порядка проверок во Where и Join я слышал (порядок такой, какой посчитает удобным оптимизатор). А вот в отношении выполнения внутренних подзапросов - это для меня откровение!

Оптимизатор открывает все вложенные запросы до уровня базовых таблиц. Так было всегда.
23 янв 13, 16:14    [13816551]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Или почему бы не сконвертировать внутренним запросом данные к нужному типу и уже потом во внешнем запросе накладывать between без конверта?
23 янв 13, 16:15    [13816569]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
uaggster
Member

Откуда:
Сообщений: 867
iap
Я ж вроде пытался подсказать про CASE[/quot]
В итоге так и сделал :(
Set Dateformat dmy;
Select 137, p.id 
	from dbo.PERSON p 
			 Where Case When IsDate(p.DR)=1 and IsDate(p.DDead)=1 Then 
				Case When not convert(date, p.DDead, 104) between convert(date, p.DR, 104) and GETDATE() Then 1
					Else 0 End
				Else 0 End =1
23 янв 13, 16:17    [13816585]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
SET DATEFORMAT DMY;
SELECT 137, a.ID
FROM
(
 SELECT p.id, p.dr, p.DDEAD
 FROM dbo.PERSON p
 WHERE ISDATE(p.DR)=1 AND ISDATE(p.DDead)=1
) a
WHERE NOT CONVERT(DATE, CASE ISDATE(a.DDead) WHEN 1 THEN a.DDead END, 104)
BETWEEN CONVERT(DATE, CASE ISDATE(a.DR) WHEN 1 THEN a.DR END, 104) AND GETDATE();
23 янв 13, 16:18    [13816589]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
iap
Зачем тут подзапрос тогда?
23 янв 13, 16:19    [13816601]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
ambarka_max
iap
Зачем тут подзапрос тогда?
Ну, просто повторил исходный текст для очевидности изменений
23 янв 13, 16:21    [13816611]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
Гость333
Member

Откуда:
Сообщений: 3683
uaggster
IsDate(p.DR)=1 and IsDate(p.DDead)=1

Я бы ещё для надёжности добавил
and p.DR like '__.__.____' and p.DDead like '__.__.____'

Пример:
Set Dateformat dmy;
declare @s varchar(100) = '2013-01-23T00:00:00';
select isdate(@s); -- возвращается 1
select convert(datetime, @s, 104) -- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
23 янв 13, 16:24    [13816642]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
А вот так?
SET DATEFORMAT DMY;
SELECT 137, a.ID
FROM
(
 SELECT p.id, 
	CONVERT(DATE, CASE ISDATE(p.DR) WHEN 1 THEN p.DR END, 104) AS DR, 
	CONVERT(DATE, CASE ISDATE(p.DDead) WHEN 1 THEN a.DDead END, 104) AS DDead
 FROM dbo.PERSON p
 WHERE ISDATE(p.DR)=1 AND ISDATE(p.DDead)=1
) a
WHERE NOT a.DDead BETWEEN a.DR AND GETDATE();
23 янв 13, 16:25    [13816646]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
А я бы ещё для надёжности добавил
and p.DR like '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' and p.DDead like '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]'
23 янв 13, 16:33    [13816745]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
uaggster
Member

Откуда:
Сообщений: 867
Гость333
uaggster
IsDate(p.DR)=1 and IsDate(p.DDead)=1

Я бы ещё для надёжности добавил
and p.DR like '__.__.____' and p.DDead like '__.__.____'

Пример:
Set Dateformat dmy;
declare @s varchar(100) = '2013-01-23T00:00:00';
select isdate(@s); -- возвращается 1
select convert(datetime, @s, 104) -- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Гм...
Наверное, лучше всё же будет вот такой вариант:
Set Dateformat dmy;
Select 137, p.id 
	from dbo.PERSON p 
			 Where Case When IsDate(p.DR)=1 and IsDate(p.DDead)=1 Then 
				Case When not Cast(p.DDead as date) between cast(p.DR AS date) and GETDATE() Then 1
					Else 0 End
				Else 0 End =1

Т.е. без явного указания, из какого формата мы конвертируем дату. Ожидаем день-месяц-год, формат разделителей - по вкусу.
Так?
23 янв 13, 16:34    [13816753]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Ладно, последнее это наверное уже шутка.
23 янв 13, 16:34    [13816754]     Ответить | Цитировать Сообщить модератору
 Re: Падает простейший запрос. Помогите найти причину!  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
uaggster
Т.е. без явного указания, из какого формата мы конвертируем дату.

Будет искажение дат в случаях 10.12 или 12.10
23 янв 13, 16:36    [13816766]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить