Почему sql запрос возвращает NULL?

Question

[Николай]

Всем доброго дня!
В наследство достался сайт магазин с 80 этажным генератором отчетов через cron + shell script.
Сейчас заметили что по некоторым товарам генерятся некорректные данные, в частности для поля состав возвращается NULL, хотя по факту поле заполнено.
Начал смотреть и вижу что содержимое генерится запросом

SELECT IF(
    !(SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'),
     (SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'),
'NULL'
)

При этом запрос

SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'

Возвращает корректное значение - 100% полиэстер

Если убрать !

IF(
    (SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'),
    (SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'),
'NULL'
)

тоже возвращает корректное значение.

Всё бы ничего, но для некоторых товаров возвращается корректное значение...

SELECT IF(
    !(SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 24074 AND pa.attribute_id = '35'),
    (SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 24074 AND pa.attribute_id = '35'),
'NULL'
)

Возвращает - Текстиль

При этом упрощенный запрос

SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 24074 AND pa.attribute_id = '35'

ничего не возвращает...

Как это вообще понимать?

UPDATE
Кое что проясняется.
Запрос

SELECT IF(
    !(SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'),
    (SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'),
'NULL'
)

Возвращает NULL вместо 100% полиэстер (Шерсть в изначальном вопросе попала туда от другого товара).
Но, phpMyadmin пишет Warning: #1292 Truncated incorrect DOUBLE value: '100% полиэстер'

Структура таблицы oc_product_attribute - https://s.mail.ru/96sf/scGaYa4Af

Что-то я не понял при чем тут DOUBLE...

Comments

[Akina]

Возвращает корректное значение - Шерсть

Что вернёт SELECT HEX(pa.text) FROM ... ?

[Николай]

Akina,

SELECT HEX(pa.text) FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'

3130302520D0BFD0BED0BBD0B8D18DD181D182D0B5D180

SELECT HEX(pa.text) FROM oc_product_attribute pa WHERE pa.product_id = 24074 AND pa.attribute_id = '35'

D182D0B5D0BAD181D182D0B8D0BBD18C

[Akina]

Что-то у тебя всё плавает... запрос, который по твоему заверению возвращал "Шерсть", тут вернул "100% полиэстер", второй запрос вернул значение с маленькой буквы, хотя в вопросе написано с большой...

К самим значениям в хексах претензий нет - честная кириллица.

Структура таблицы oc_product_attribute

Это не структура, а, я извиняюсь, чушь собачья. Структура - это полный и не тронутый редактированием результат выполнения SHOW CREATE TABLE tablename;

Теперь проверяем второе подозрение. Выкладывайте структуру таблицы, а также результат выполнения SHOW VARIABLES LIKE 'char%'; и SHOW VARIABLES LIKE 'coll%';

[Николай]

Akina,

Что-то у тебя всё плавает... запрос, который по твоему заверению возвращал "Шерсть", тут вернул "100% полиэстер", второй запрос вернул значение с маленькой буквы, хотя в вопросе написано с большой...

Так я написал что это случайно от другого товара значение, я в этих товарах сейчас как в помидорах.

SHOW CREATE TABLE oc_product_attribute;

CREATE TABLE `oc_product_attribute` (
`product_id` int(11) NOT NULL,
`attribute_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`text` varchar(255) NOT NULL,
PRIMARY KEY (`product_id`,`attribute_id`,`language_id`),
KEY `1` (`product_id`,`attribute_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SHOW VARIABLES LIKE 'char%'
https://s.mail.ru/Eu6w/kR3Nn5A1X

SHOW VARIABLES LIKE 'coll%'

https://s.mail.ru/2f8D/QphbfsND5

[Akina]

Мне уже не нравится то, что данные в UTF8 (чарсет для отдельных полей не указан, значит, применяется дефолтный чарсет таблицы), тогда как и сервер, и соединение работают в UTF8MB4. Очень теоретически это может вызывать проблемы. А сверху ещё наслаиваются проблемы несовпадения COLLATION для данных и для литералов. MySQL в принципе считает эти чарсеты совместимыми, а collation вообще приводит неявно и хрен поймёшь по каким правилам...

Правда, всё это к именно описанной проблеме приводить не должно.

Так, следующий вопрос. Проблема - воспроизводима? т.е. если pa.product_id = 19442 косячит - то косячит абсолютно всегда? А если выгрузить десяток записей из таблицы, включая и запись для этого продукта, и положить в отдельную таблицу указанной структуры - всё равно проблема воспроизводится? А если этот же запрос выполнять не через PHP, а из клиента командной строки - проблема так же остаётся и воспроизводима?

PS. Charset / collation можно было бы дать текстом, как сделано для CREATE TABLE. А все три вывода - завернуть тегом кода, а не цитаты, для сохранения форматирования вывода.

[Николай]

Akina,

Проблема - воспроизводима? т.е. если pa.product_id = 19442 косячит - то косячит абсолютно всегда?

50 раз выполняю запрос - 50 раз ответ NULL

А если выгрузить десяток записей из таблицы, включая и запись для этого продукта, и положить в отдельную таблицу указанной структуры - всё равно проблема воспроизводится?

Скопировал таблицу oc_product_attribute в oc_product_attribute_test полностью.
Результат не изменился.

А если этот же запрос выполнять не через PHP, а из клиента командной строки - проблема так же остаётся и воспроизводима?

Как я писал изначально, отчет генерится через shell скрипт на сервере, но на всякий случай сделал запрос напрямую из консоли? всё тоже самое - https://s.mail.ru/GmkB/NvzavQKLD

[Akina]

Николай, Удалите из oc_product_attribute_test все записи, кроме 5-7 штук, проблемную тоже оставьте. Что-то типа DELETE FROM oc_product_attribute_test WHERE product_id NOT BETWEEN 19440 AND 19450; Проверьте, что проблема по-прежнему воспроизводится. Сделайте дамп и выложите - попробуем воспроизвести проблему и понять её источник.

А, ещё одно. Так, сбоку. А нафига нужен индекс KEY `1`? Он по идее вообще не должен использоваться ни в одном запросе, кроме тех, для которых он покрывающий.

[Николай]

Akina, таблицу почистил, выполнил запрос - изменений нет.
сделал дамп - https://drive.google.com/file/d/1wFqeluOqxCKbAW8AE...
Там и проблемная запись и правильная, другие по остаточному принципу.

[Akina]

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d3a8b3...

Любые `text`, которые при CAST к числу, дают не ноль, в исходном запросе дадут "неверный" результат.

В качестве паллиативного решения предлагаю добавить в начало значения поля для записей, где оно начинается числом, какой-нибудь нечисловой символ, не сильно влияющий на внешний вид вывода. Например, подчёркивание.

Answer 1

[Дмитрий]

Предположу что стоит это понимать как то что NULL это специфическое значение, и для SQL и попытка привести к булеан через !(NULL) не работает;
Если я правильно понял то что здесь хотят сделать то вам стоит использовать функции для работы с null, такие как IFNULL, COALESCE.

SELECT IFNULL((SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 24074 AND pa.attribute_id = '35'), 'NULL');

Comments

[Akina]

Предположу что стоит это понимать как то что NULL это специфическое значение, и для SQL и попытка привести к булеан через !(NULL) не работает;

Бред.

Напомню, что булево значение может иметь три разных варианта - TRUE, FALSE и UNKNOWN. Вот последнее в терминах SQL и есть NULL. Так что !(NULL) - нормальное выражение булева типа, имеющее значение NULL.

Логика работы IF() тупая и дубовая. Получили TRUE - выполняем первую альтернативу, иначе вторую, и неважно, FALSE это или NULL.

[Дмитрий]

Akina, ну в тексте ссылочка где можно проверить бред это или нет. Можно в MySQL запустить

select if(!(null), 1, 2);
select if((null), 1, 2);

и посмотреть на дубовость и тупость логики IF

[Akina]

Можно в MySQL запустить

Можно и не запускать. Ибо в полном соответствии с написанным выше оба запроса вернут двойку.

[Дмитрий]

Akina, здорово и отсюда вытекает что изначальные запросы автора

SELECT IF( !(SELECT bla-bla-bla), (SELECT bla-bla-bla), 'NULL');
или
SELECT IF((SELECT bla-bla-bla), (SELECT bla-bla-bla), 'NULL');

сразу лишаются смысла ибо при отсутствии записи будут давать один и тот же вариант. ибо
select if(!(null), 1,2)=if((null), 1,2 ) ибо кастинг null к boolean работать не будет, и для работы с null есть специальные функции. И надо использовать либо IFNULL, либо SELECT IF(exists(SELECT bla-bla-bla), (SELECT bla-bla-bla), 'NULL');. Ну как я и написал выше.

[Akina]

Дмитрий,

сразу лишаются смысла ибо при отсутствии записи будут давать один и тот же вариант.

Да, при отсутствии записи оба дадут 'NULL' (обращаю внимание - это не NULL, а 4-символьный строковый литерал с таким значением).

Насчёт смысла ничего не скажу - это к автору вопросы.

ибо кастинг null к boolean работать не будет

Да работает он, работает!

Правда, в MySQL ещё следует обычно учитывать неявное приведение типов (в нём вообще такого базового типа данных как BOOLEAN - нет), но не в данном случае. К слову, с точки зрения MySQL NULL при любых проверках действует как FALSE. Что обычно приводит к непониманию работы CHECK constraint, который, в отличие от IF(), проверяет, наоборот, на FALSE - но тут достаточно просто запомнить.

[Дмитрий]

Akina,

Да работает он, работает!

Да где же он работает если у вас !(null) == (null)?

[Akina]

(null) - это NULL. Надеюсь, это вопросов не вызывает?

!(null), как я сказал выше, "!(NULL) - нормальное выражение булева типа, имеющее значение NULL".

Так что да - и то, и другое значение есть NULL. Который интерпретируется как FALSE (это я тоже говорил выше). Что в соответствии с логикой работы функции IF() (также описанной выше) приводит к выбору альтернативного значения. В обоих запросах.

И что не так-то? что не работает?

[Дмитрий]

Akina,

Который интерпретируется как FALSE

Null не может никак интерпретироваться. cast от null не возможен - ибо для cast нужно знать тип переменной которую мы кастуем. А null это тип - не определен. По этому попытка привести null к чему либо обречена на провал. И следовательно попытка его запихнуть в IF тоже - ибо он ровно пытается это сделать ибо if работает с boolean.
Можете поиграться

select cast(null as boolean);
select cast(null as text);
select cast(null as int);

З.Ы. а теперь давайте усложним, представим что вложенный запрос возвращает string.

select if('шерсть', 1, 2);
select if('cast переменных вещь к которой надо подходить с осторожностью!', 1, 2);
select if('true', 1, 2);
select if(null, 1, 2);
select if(!(null), 1, 2);

Весело да? Оказывается приведение string к boolean тоже штука такая. И мы возвращаемся к моему сообщению - используем ifnull (ну или exists) и не несем бред.

[Akina]

Null не может никак интерпретироваться.

Между терминами "интерпретироваться как" и "приводиться к" - дистанция огромного размера.

а теперь давайте усложним, представим что вложенный запрос возвращает шерсть.
select if('шерсть', 1, 2);

Условие содержит выражение 'шерсть'. Это выражение, согласно логике работы IF(), приводится к типу BOOLEAN, который на самом деле алиас TINYINT(1). То есть к числовому типу. Согласно правилам приведения, которые использует MySQL, при приведении строки к числу берётся числовой префикс строки, а при его отсутствии (строка начинается с нецифрового символа) результатом приведения будет ноль. Ноль интерпретируется как FALSE (или, если строго - то FALSE является алиасом целочисленного значения ноль). И выбирается альтернативный вариант.

Что "весело"? Всё логично и однозначно.

[Дмитрий]

Akina,

Что "весело"? Всё логично и однозначно.

Отлично а теперь мотаем назад и смотрим на запрос который приводит автор

SELECT IF(
    !(SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'),
     (SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'),
'NULL'
)

И понимаем что если запись отсутствует или не содержит '1' ("100% полиэстер" работает да?) в первом символе возвращенного результата, вся эта конструкция лишена смысла. А использование отрицания ! еще и никак не влияет на тот вариант если записи нет. И мы опять приходим к тому что я говорил изначально - IFNULL или EXISTS; Л - логика

[Николай]

Дмитрий, прошу прощения что вмешиваюсь, но по моей оплошности в данном запросе должно было вернуться не Шерсть, а 100% полиэстер
Т.е. в поле первое значение единица и возвращает NULL
А там где в поле значение текстиль возвращает корректно.

[Дмитрий]

Николай, дык вмешивайтесь - это же ваш вопрос, за что тут прощения просить?

Т.е. в поле первое значение единица и возвращает NULL

Дык у вас там отрицание стоит в запросе

SELECT IF(
    ->!<- (SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'),
     (SELECT pa.text FROM oc_product_attribute pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'),
'NULL'
)
select if(!('100% полиэстер'), '100% полиэстер', 'NULL');

[Николай]

Дмитрий, запросы

SELECT IFNULL((SELECT pa.text FROM oc_product_attribute_test pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'), 'NULL');
SELECT IFNULL((SELECT pa.text FROM oc_product_attribute_test pa WHERE pa.product_id = 24074 AND pa.attribute_id = '35'), 'NULL');

отдают корректные данные.
Вопрос от чайников - а в чем конкретно задача запроса формата SELECT IFNULL((SELECT pa.text FROM...?
Почему не используется простой SELECT pa.text FROM... ?

[Дмитрий]

Николай, Для того что у вас правильно работало нужно что бы было "0% полиэстер"

[Дмитрий]

Николай, и еще раз NULL это неопределенность. Ее нельзя ни с чем сравнивать и ни к чему приводить. Попытки использовать с ней какие либо операторы + - * или булевы типа ! - обречены на провал. Это неопределенность. Есть вагон шуток на эту тему

По этому в sql введены специальные функции для работы с этим. IFNULL, IS NULL. Ибо (select NULL=NULL) не даст вам true или false;

[Николай]

Дмитрий, так я NULL ни с чем её не сравниваю и не привожу)))
Задача отчета создать таблицу со столбцами
id товара | артикул | свойство 1 | ... | свойство 99 |
Ну и с их значениями соответственно.
Я бы для полей такого типа этого использовал запрос типа

SELECT pa.text FROM oc_product_attribute_test pa WHERE pa.product_id = 19442 AND pa.attribute_id = '35'

Но я понимаю что разработчик не дурак и не спроста обернул простой запрос в конструкцию IF (в вашем варианте IFNULL)
Для чего?

[Дмитрий]

Николай,

так я NULL ни с чем её не сравниваю и не привожу)))

IF пытается привести null к boolean. А IF же в запросе есть?

Но я понимаю что разработчик не дурак и не спроста обернул простой запрос в конструкцию IF (в вашем варианте IFNULL)

Чем руководствовался разработчик, я сказать не могу. Если сайт на php то скорее всего это банально ошибка связанная с тем что в php приведение типов работает иначе. А может быть когда текст атрибута всегда начинался с 0

echo (null)?'yes':'no';
no
echo !(null)?'yes':'no';
yes
echo ('wtf')?'yes':'no';
yes
echo !('wtf')?'yes':'no';
no

Там эта конструкция отработает на ура.

[Николай]

Дмитрий, сайт на php, но отчет строится без php.
То что этот генератор мог писать php'шник со своим понимание приведения типов это да, может быть.

Просто единственный смысл который я вижу это возвращение NULL вместо пустого ответа, но чем его пустой ответ в этом случай не устраивал непонятно.
В этом отчете некоторые поля пустые некоторые NULL.
Есть ощущение что есть какой-то более прикладной смысл.

[Akina]

в данном запросе должно было вернуться не Шерсть, а 100% полиэстер

Тогда не вижу проблемы.

После подстановки результата вместо подзапроса имеем

SELECT IF( !('100% полиэстер'), '100% полиэстер', 'NULL' )

Выражение условия приводится к числовому типу. Поскольку существует числовой префикс, он и получается. То есть делаем очередную подстановку и получаем

SELECT IF( ! 100, '100% полиэстер', 'NULL' )

100 в булевом контексте интерпретируется как TRUE. NOT 100 соответственно интерпретируется как FALSE. Делаем очередную подстановку и получаем

SELECT IF( FALSE, '100% полиэстер', 'NULL' )

Теперь понятно, почему возвращается 'NULL'?

[Дмитрий]

Николай,

Просто единственный смысл который я вижу это возвращение NULL вместо пустого запроса, но чем его пустой ответ в этом случай не устраивал непонятно.
Есть ощущение что есть какой-то более прикладной смысл.

ХЗ. Эта конструкция по моему убеждению - не рабочая. Но все мы можем ошибаться и 100% гарантию дают небеса.

[Николай]

Akina,

Теперь понятно, почему возвращается 'NULL'?

Примерно да, непонятно для чего эта конструкция была придумана.
В отчете куча столбцов с атрибутами товаров, есть и числовые и текстовые.
Разработчик хотел отфильтровать значения начинающиеся с 1 ?
ИМХО, в этом нет смысла...

[Дмитрий]

Николай, если это летает как утка, крякает как утка, плавает как утка - значит это утка. меняйте на IFNULL и не парьтесь

[Николай]

Дмитрий, ))))
Спасибо огромное!!!

[Akina]

Николай,

непонятно для чего эта конструкция была придумана.

Скорее всего, программист не учёл, что значение может начинаться с числа.