Почему функция ORACLE TO_DATE не выдает ошибку ORA-01841 в условии ИЛИ при прохождении пробелов?
SQL> select to_date(' ','YYYYMMDD') from dual;
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, not be 0
SQL> select * from dual where to_date(' ','YYYYMMDD') = '19960512' or 1 = 2;
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, not be 0
SQL> select * from dual where to_date(' ','YYYYMMDD') = '19960512' or 1 = 1;
Success.
В первых двух операторах мы передаем пробелы в функцию TO_DATE, которая выдает ожидаемую ошибку. Но
Теперь в третьем операторе мы передаем пробелы в функцию оракула TO_DATE, что недопустимо. Но все же он выполняется успешно.
Почему?
В реальном коде я могу передавать пробелы из переменной, и часть ИЛИ может быть верной, а может и нет.
Может ли кто-нибудь объяснить мне такое поведение и как справиться с ошибкой?
P.S - Я использую Oracle 12g EE
Ответы 3
Oracle использует здесь логику короткого замыкания. Поскольку известно, что 1=1 верен, Oracle оптимизирует оценку другого условия. Поскольку он никогда не оценивается, тот факт, что вы передаете ему недопустимые аргументы, несущественен.
Джастин Кейв здесь stackoverflow.com/questions/8900631/… утверждает, что оптимизатор ищет истину в любом порядке, чтобы он мог сократить набор предикатов, хотя никаких ссылок на документы оракула
Я добавил 1 = 1 ради условия ИЛИ, но эта часть зависит от потока, который может или не может быть правдой. Но как убедиться, что я получаю сообщение об ошибке, когда пробелы передаются в TO_DATE?
добавление 1=1 повлияло на результат. Если добавлено дополнительное условие вроде этого: select * from dual where to_date(' ','YYYYMMDD') = '19960512' or to_date('19960512','YYYYMMDD') = '19960512', то вы получите ожидаемую ошибку. Итак, решение: как справиться с ошибкой? - не использовать 1=1
Другой ответ не касался той части вашего вопроса, в которой вы все еще хотели получить ошибку.
Я рекомендую вам выполнять синтаксический анализ даты изолированно. Если это хранимая процедура, не помещайте запрос, как вы это сделали здесь, создайте переменную даты, используйте to_date, чтобы присвоить ей значение даты (он выйдет из строя, если пользователем указаны пробелы), затем используйте переменную даты в запросе. Таким образом, если переданы неверные данные, у Oracle все еще есть шанс подавиться ими.
В качестве альтернативы сделайте так, чтобы ваша процедура хранилища принимала параметр типа даты, а не строку, чтобы заставить вызывающую программу предоставить что-то разумное и последовательное, дату.
Эти запросы с условием или находятся на уровне данных и в строке из базы данных, полученной и переданной в TO_DATE в запросе.
Я вроде как не совсем понял, что вы имели в виду, но если вы чувствуете, что нет другого выхода, кроме как получить такой запрос, тогда вам придется сделать что-то еще, чтобы вызвать сбой; выберите to_date, а также сделайте его в предложении where или поместите в случай, когда это заставляет заказ. Я все еще думаю, что если вы говорите, что запрос - это все, что есть, вам следует правильно его параметризовать. В этой дате вообще не должно быть пробелов. Есть еще один аргумент в пользу того, что если вы ожидаете появления неверных данных определенного типа, вам следует обработать их, а не сбой, чтобы свести к минимуму объем работы, которую должна выполнять группа технической поддержки.
Вы можете повторить преобразование в ветви or, что явно немного беспорядочно:
select * from dual
where to_date(' ','YYYYMMDD') = '19960512'
or (1 = 1 and to_date(' ','YYYYMMDD') != '19960512');
Error report -
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Или, если единственный сценарий недопустимой даты, о котором вам нужно беспокоиться, - это пробелы, вы можете сделать что-то еще, чтобы в этом случае эта ветка оценивалась как false:
select * from dual
where to_date(' ','YYYYMMDD') = '19960512'
or (1 = 1 and trim(' ') is not null);
string from database getting fetched and passed to TO_DATE in the query
подразумевает, что вы храните даты как строки в своей базе данных, что не очень хорошая идея; так что, если у вас могут быть пробелы, у вас может быть что угодно, и вам также придется иметь дело с другими потенциальными ошибками, которые подход is not null не улавливает.
И ни один из этих запросов не приведет к ошибке с переданной пустой строкой (т.е. null), но тогда и ваши первые два исходных запроса не будут; они все просто не найдут данных.
Конечно, '19960512' - это не свидание, поэтому здесь вы выполняете дополнительные неявные преобразования; было бы безопаснее передать это через to_date() или использовать литерал даты, если это действительно фиксированное значение:
where to_date(' ','YYYYMMDD') = date '1996-05-12'
Проголосовали за, но было бы хорошо увидеть ссылку на документ для вашего ответа, потому что AFAIK в SQL, как правило, не имеет гарантированного порядка короткого замыкания (в отличие от языков приложений, таких как Java и C#, для которых делать имеют такие гарантированные правила).