Функция PostgreSQL TO_NUMBER не работает с дополнительными ограничениями WHERE?

У меня есть таблица в postgres, содержащая проанализированную адресную информацию. Когда я запускаю следующий запрос, я получаю правильные результаты:

select count(*) from address_table
  where (mod(to_number(to_address_left, '99999999'), 2) = 0 
         and to_address_left <> ' ')

Но когда я добавляю к нему ИЛИ ...

select count(*) from address_table
  where (mod(to_number(to_address_left, '99999999'), 2) = 0 
         and to_address_left <> ' ')
     or (from_address_left <> ' ')

Я получаю следующую ошибку ...

ERROR:  invalid input syntax for type numeric: " "
SQL state: 22P02

Я считаю, что это ошибка, связанная с функцией TO_NUMBER. Я знаю, что в некоторых из этих записей есть пробелы (намеренно), поэтому включены "and to_address_left <> ''". Но я не понимаю, почему добавление оператора OR, который ссылается на совершенно другое поле, заставляет автономную функцию TO_NUMBER генерировать ошибку.

Этот код отлично работает в Oracle SQL. Почему здесь не работает?

Каковы типы данных для столбцов to_address_left и from_address_left?

— 01.05.2018 19:53

Оба они являются текстовыми полями для размещения номеров адресов, которые могут включать буквенные символы. Если номер дома отсутствует, записывается пустое место.

— 01.05.2018 19:55

sql postgresql

01.05.2018 19:23

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

816

Ответы 1

SQL - это декларативный язык. Планировщик запросов может оценивать выражения в любом порядке, если он дает правильный результат. Итак, если вы добавите предложение OR, оно может сгенерировать план запроса, который сначала оценивает to_number, а затем - to_address_left <> ' '. Это вызовет исключение для строк с пробелом в to_address_left.

Последовательный способ решения этой проблемы - убедиться, что оценка to_number никогда не вызывает исключения. Похоже, что to_number преуспевает для любой строки, содержащей число, даже для 'b 1 d', поэтому вы можете проверить это на case:

to_number(case when str_col ~ '[0-9]' then str_col end, '99999999')

Таким образом, Postgres будет оценивать to_number, не вызывая исключения, даже если строка позже будет выброшена другими частями предложения where.

Альтернативный способ, основанный на том, что Postgres материализует выражение общей таблицы (которое пока поддерживается во всех версиях Postgres, но его нет в стандарте SQL):

with    CTE as
        (
        select  *
        from    YourTable
        where   str_col not like '% %'
        )
select  *
from    CTE
-- Line below below guaranteed not to run for rows with a space in str_col
where   to_number(str_col) > 42

Во-первых, я новичок в pgSQL. В настоящее время я использую существующий Oracle SQL, который отлично работает, и переношу данные и весь устаревший код в среду postgres. Поэтому я до сих пор не понимаю этот ответ как объяснение универсального SQL, когда он работает в одном варианте, но не работает в другом.

— 01.05.2018 20:28

Во-вторых, если я заменю оператор ИЛИ на что-то еще тривиальное, например: ИЛИ (1 = 1) ... он никогда не завершится с ошибкой. У меня создалось впечатление, что оценка в скобках отделена от любых других оценок. Также обратите внимание, что предложение OR полностью ссылается на другое поле ... from_address_left не to_address_left. Это как-то влияет на реакцию?

— 01.05.2018 20:33

@geospatial: Каждая база данных работает таким образом. См. этот вопрос Oracle и обратите внимание на /*+NO_MERGE*/ для обеспечения согласованности планировщика запросов. Согласно спецификации SQL, планировщик запросов может оценить часть 2=2 из 1=1 OR (1=0 AND 2=2), и иногда он это делает.

— 01.05.2018 20:59

После прочтения этого другого вопроса я думаю, что он имеет немного больше смысла, хотя интуитивно это немного шатко. Правильно ли я предполагаю, что причина, по которой он может работать в Oracle, а не в postgres, - это довольно произвольное решение оптимизатора в каждой из этих сред в порядке оценки всех операторов WHERE? Может ли он потерпеть неудачу в Oracle и работать в postgres при немного иных обстоятельствах? И спасибо за вашу помощь до сих пор. Код, который вы предоставили, похоже, работает.

— 01.05.2018 22:37

Да, он может дать сбой в Oracle и работать в Postgres. Это может даже зависеть от дополнительной версии базы данных, скорости дисков или объема оперативной памяти на сервере. Что угодно может изменить поведение планировщика запросов

— 01.05.2018 22:43

01.05.2018 19:54