ANSI sql для сравнения со строкой и не строкой
Скажем, у меня есть одно из следующих выражений:
SELECT
DATE '2014-01-01' < '2014-02-01',
DATE '2014-01-01' < '321',
9 < '10',
9 < 'a'
Есть ли в стандарте SQL рекомендация или требование о том, как их следует сравнивать? Я полагаю, что три уровня или «строгость» будут следующими:
- Поднимите ошибку [самая строгая] -- все 4 приведенных выше выражения не сработают.
- Попробуйте привести строку к типу, отличному от строки, если это не сработает, возникнет ошибка - выражения 2 и 4 выше не сработают.
- Попробуйте привести строку к нестроковому типу, если не удастся вернуться к преобразованию нестрокового типа в строку - все 4 выше работают.
- Приведите нестроковый операнд к строке - все 4 выше работают.
Кажется, BigQuery использует второй подход, postgres использует что-то вроде 2/3 (только последний терпит неудачу), mysql использует либо 3, либо 4 (без ошибок).
Дает ли стандарт здесь какие-либо рекомендации?
@Hogan - это вопрос о том, что говорит стандарт sql (если что-либо) для обработки несовместимых типов данных, а не о конкретном запросе sql. Насколько я могу судить, вам нужно заплатить за копию «стандарта sql», поэтому лично я никогда его не видел;)
Я предполагаю, что другая возможность - принуждение к типу левого операнда. Понятия не имею, что говорит стандарт. Интересный вопрос.
Я не знаю стандарта, а для SQL-сервера неявные преобразования не всегда надежны, особенно для даты и времени (например: «20230511» будет правильно конвертировать в дату и время неявно при сравнении с датой и временем, но «2023/05/11» или «2023 -05-11 'может и не быть - видел, как он терпит неудачу при определенном языке, настройках даты и версии SQL-сервера). Вероятно, лучше всего использовать явные преобразования. Я думаю, что postgreSQL использует 2, и только 1 и 3 успешны.
@CetinBasoz, еще один случай, когда неявное преобразование может вызвать путаницу, - это 9 < '10', которое даст разные результаты в зависимости от того, приводите ли вы к char или к числовому.
@jarlh, точно.
Ответы 3
Стандарт SQL для преобразования использует ключевое слово CAST.
CAST ( expression AS data_type )
Я бы предложил использовать это, если вы хотите быть «стандартным»
Верно, но без явного приведения возникает вопрос, как приводятся (или нет) разные типы данных. Например, вот как это делает SQLServer: learn.microsoft.com/en-us/sql/t-sql/data-types/… (прокрутите вниз, чтобы увидеть рисунок).
@ David542 - вопрос не имеет смысла. Вы говорите о поведении, которое не охвачено стандартами, а затем спрашиваете, что такое стандартный способ. Это по определению не то, что имеет стандарт — каждая платформа будет отличаться.
конечно, но откуда вы знаете, что это не покрывается стандартом?
@ David542 откуда я знаю, что ты не картошка?
«Вы говорите о поведении, которое не охвачено стандартами» — именно об этом здесь и спрашивают. Для любой функции данный стандарт может точно определить ее, оставить открытой для реализации, объявить неопределенное поведение или вообще исключить ее. Стандарты ANSI не находятся в свободном доступе (модель похожа на публикацию исследовательских работ), и даже если бы они были, объяснение на простом английском языке всегда полезно. Помните, что Stack Overflow стремится быть репозиторием вопросов и ответов, а не справочным форумом.
@ ÁlvaroGonzález -- Как еще вы хотите, чтобы я ответил на этот вопрос? Я сказал - стандартом является использование ключевого слова cast для преобразования типов. Я действительно не знаю другого способа сказать, что стандарт делает более четко.
Извините, тогда, возможно, я не понял ответа. Вы имеете в виду, что использование оператора «меньше» с операндами с разными типами данных является открытым нарушением стандарта SQL ANSI, а СУБД, которые реализуют любой вид неявного приведения (в отличие от полного отклонения запроса), не совместимы с ANSI?
@ Альваро Гонсалес - да. Но ни одна СУБД не отклонила бы запрос, пользователи не смирились бы с этим. Есть МНОГИЕ части SQL, которые не совместимы с ANSI - например, нет стандарта для DDL. Для СП. У этого списка нет конца. Стандартный SQL на самом деле довольно мал.
Стандарт SQL включает как DDL, так и хранимые процедуры (PSM).
@ David542 «неявное преобразование типов» встречается только один раз в разделе 4.12 окончательного (1999 г.) стандарта ANSI SQL. Он не определяет, как должны выполняться «неявные преобразования типов», только если они могут быть, но далее говорится, что если вы хотите явно преобразовать типы, используйте приведение. Implicit type conversion can occur in expressions, fetch operations, single row select operations, inserts, deletes, and updates. Explicit type conversions can be specified by the use of the CAST operator.
ISO/IEC 9075 SQL: 2016 последние новые функции (также обсуждаемые здесь или в этом PDF-файле) мало что касалось кастинга.
Этот стандарт не диктует каждую деталь того, как конкретная реализация должна обрабатывать все ситуации. Вместо этого он устанавливает общие рекомендации и требования, которых должны придерживаться реализации, предоставляя при этом некоторую гибкость отдельным поставщикам для адаптации своих продуктов.
Это означает, что любое решение, основанное на CAST, необходимо сверить с конкретной документацией вашего SQL-сервера.
SELECT
DATE '2014-01-01' < DATE '2014-02-01',
DATE '2014-01-01' < CAST('321' AS DATE),
CAST(9 AS VARCHAR) < '10',
CAST(9 AS VARCHAR) < 'a'
или
SELECT
DATE '2014-01-01' < CAST('2014-02-01' AS DATE),
9 < CAST('10' AS INTEGER)
Хотя CAST обычно поддерживается более широко, вам может потребоваться использовать другую функцию или синтаксис в зависимости от конкретной реализации SQL, с которой вы работаете.
Например, SQL Server использует функцию CONVERT, которая похожа на CAST, но также позволяет указать аргумент стиля для определенных преобразований.
Дает ли стандарт здесь какие-либо рекомендации?
Я полагаю, вы спрашиваете, существуют ли какие-либо официальные правила, касающиеся автоматического преобразования типов для сравнения? Ответ - нет*.
Для операции сравнения в спецификациях упоминается, что типы данных [...] должны быть сопоставимы. Сравнение разных типов данных не описывается, но говорится, что неявное преобразование типов может происходить в выражениях [...]. Таким образом, РСУБД разрешено преобразовывать тип данных одного или обоих операндов для их сравнения.
При этом правила автоматического преобразования типов зависят от реализации. Правила сильно различаются в разных СУБД. Обратитесь к документации вашей СУБД, чтобы понять их.
Например, SQL Server использует приоритет типов данных для преобразования типа данных одного из операндов в соответствие с другим:
- Для
DATE '2014-01-01' < '2014-02-01'значение varchar будет преобразовано вdateдля сравнения. - Для
9 < '10'значение varchar будет преобразовано вintдля сравнения.
MySQL имеет другой набор правил:
- Для
DATE '2014-01-01' < '2014-02-01'оба значения будут преобразованы в временные метки (не тип данных временной метки) для сравнения. - Для
9 < '10'оба значения будут преобразованы в числа с плавающей запятой для сравнения.
К сожалению, у неявных преобразований слишком много ошибок, и их следует избегать. Например, 2.0 = '2.01' верно в SQL Server, а 2 = '2foo' верно в MySQL.
Используйте функцию CAST, переменные правильного типа (DECLARE @userdate AS DATE = '20120201') или соответствующие функции (STR_TO_DATE('01-02-2012','%d-%m-%Y')) для строк.
* Ответ основан на стандарте SQL-92 — очень старом, но все еще актуальном для вопроса.
спасибо за это, это очень полезно. Есть ли упоминание о «супертипе» или классе типов или что-то в этом роде. Например, 2 (int) сопоставимо с 2.4 (с плавающей запятой) или DATE '2014-01-01' сравнимо с DATETIME '2014-01-02' (я еще не видел реализации, которая не поддерживает сравнение этих аналогичные классы типов данных).
Как я уже сказал, это зависит от реализации. Общее правило заключается в том, что тип данных с меньшим диапазоном преобразуется в больший, чтобы избежать неожиданного поведения, например. 2 = 2,4 возвращает истину. Таким образом, по крайней мере, на SQL-сервере дата будет повышена до даты и времени с ч:м:с, установленным на 0. Int будет повышен до плавающего. Два десятичных знака с разным масштабом и точностью... оба будут преобразованы с одинаковой точностью и масштабом (большее из обоих). И снова SQL-сервер... нельзя сравнивать два text типа данных, потому что спецификации не определяют точных правил.
Что вы здесь делаете? Не имеет смысла иметь эти сравнения в операторе select.