ReSQL.ru
     
powered by simpleCommunicator - 2.0.30     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оператор <=> в триггере - что я упускаю?
25 сообщений из 25, страница 1 из 1
Оператор <=> в триггере - что я упускаю?
    #40128389
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Простейший демо-триггер, обеспечивающий уникальность с учётом NULL:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TRIGGER tr_bi_check_uniqueness_with_nulls
BEFORE INSERT
ON test
FOR EACH ROW
BEGIN
    IF EXISTS ( SELECT NULL
                FROM test
                WHERE test.id <=> NEW.id
                  AND test.ts <=> NEW.ts ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicated values not allowed.';
    END IF;
END


Однако по какой-то причине оператор <=> неверно отрабатывает в триггере.

DEMO fiddle

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

Что я упускаю?
...
Рейтинг: 0 / 0
21.01.2022, 13:20
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128523
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
какая то фича MySQL при работе с TIMESTAMP
в MariaDB поле типа TIMESTAMP не может быть без DEFAULT
как то так.

поле делается как
Код: sql
1.
ts TIMESTAMP NULL DEFAULT NULL);


и все работает
DEMO MariaDB
...
Рейтинг: 0 / 0
21.01.2022, 17:00
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128531
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
MariaDB при создании таблицы делает это поле
Код: sql
1.
2.
3.
4.
из 
ts TIMESTAMP 
в 
`ts` TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()



в MariaDB поле типа TIMESTAMP не может быть без DEFAULT
`ts` TIMESTAMP NULL тоже работает ! и в MySQL 5.6 и в 5.7
...
Рейтинг: 0 / 0
21.01.2022, 17:12
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128613
OlegROA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlegROA
Гость
В MySQL 5.5 - 5.7 работает и без NULL.
...
Рейтинг: 0 / 0
21.01.2022, 22:20
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128675
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
OlegROA,

перейди по ссылке в посте Akina
и посмотри что там вставляется.
И сделай еще один инсерт ручками
...
Рейтинг: 0 / 0
22.01.2022, 13:02
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128677
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
Akina,

в 8.0
@@SESSION.sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE ,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

в 5.6 5.7 MariaDB
@@SESSION.sql_mode
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
...
Рейтинг: 0 / 0
22.01.2022, 13:09
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128691
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Alex_Ustinov, https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2fe843a87601414daf5b2ef0ae9dc5fb
...
Рейтинг: 0 / 0
22.01.2022, 15:53
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128783
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
Akina,

ну глюк такой явно в 8.0
при явной проверке значений отрабатывает как надо
а в таблице хранится не NULL, нам просто показывает NULL
MariaDB (добавлено show create table test; )
`ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
)

если переключить на 8.0 - видим в show create table -
`ts` timestamp NULL DEFAULT NULL, хотя мы не указывали NULL


БАГ 8.0
...
Рейтинг: 0 / 0
23.01.2022, 13:04
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128795
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
сделать влоб, работает везде и всегда
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
    IF EXISTS ( SELECT NULL
                FROM test
                WHERE test.id <=> NEW.id
                  AND (
                  (test.ts = NEW.ts) OR (ISNULL(test.ts)*ISNULL(NEW.ts))
                  )
                  ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicated values not allowed.';
    END IF;

sql_mode в 8.0 надо менять иначе будет чехарда
и в CREATE TABLE явно указать что может быть NULL
Код: sql
1.
2.
test (id INT, 
                   ts TIMESTAMP NULL );
...
Рейтинг: 0 / 0
23.01.2022, 14:30
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128817
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
интересно,
как может оператор <=> работать, но иногда не работать
в триггере
условия работают
test.ts <=> NULL
NEW.ts <=> NULL
а
test.ts <=> NEW.ts
нет

на багтрекере ничего похожего нет
...
Рейтинг: 0 / 0
23.01.2022, 16:12
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128922
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Alex_Ustinov
в таблице хранится не NULL, нам просто показывает NULL

Этой фразы не понял от слова "совсем".
Структура хранения такова, что NULL-поле имеет дополнительный байт признака NULL. Если он установлен, то поле имеет значение NULL, и то, что хранится в содержательной части значения, игнорируется. Если он не установлен, значение берётся из содержательной части.

MariaDB тут вообще не рассматривается. Она многое обрабатывает по-другому. Например, MySQL, если поле определить как NOT NULL, в принципе не рассматривает набор значений как валидный - он перед/между/после любого этапа обработки выполняет контроль значений на ограничения, т.е. "поле не может быть NULL" будет выдано ещё до выполнения триггера. Тогда как MariaDB выполняет подобный контроль только при помещении значения в хранилище.

Что до SQL Mode, то NO_ZERO_IN_DATE,NO_ZERO_DATE ограничивают на нули и никак не реагируют на NULL.
...
Рейтинг: 0 / 0
24.01.2022, 08:11
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128961
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
Akina,
Этой фразы не понял от слова "совсем".никто не понял как продукт Оракла
"здесь читает, а здесь не читает"

я лишь накидываю мысли.
В вопросе - "Что я упускаю" ни капли мысли.

MariaDB при том, что это флагман, там правят баги а не добавляют их. Именно в MariaDB находится Видениус (Monty), основной разработчик MySQL.

Я явно указал глюки MySQL в отличие от MariaDB.
Спрашивать "что я упускаю?", а затем включать лектора не надо.

Вы эта... давайте не путайте тёплое с мягким.

Ваш путь в исходники или на BUGs.mysql.com
...
Рейтинг: 0 / 0
24.01.2022, 11:52
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128969
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
тут же покажу еще прикол MySQL

MySQL 8.0 - в триггер Добавлена инициализация переменных @tts @nts

-- SET @tts:=test.ts;
-- SET @nts:=new.ts;
если эти строки раскомментировать - то все заработает.

ПРЕДПОЛОЖУ (не надо писать что непонятно от слова СОВСЕМ)
значение NEW. в 8.0 не определено до первого использования или определено как фиг знает что (CURRENT_TIMESTAMP судя по MariaDB или 5.6 5.7) до первого использования.
Хотя при создании табл с полем ts TIMESTAMP (мы не говорим что там возможен NULL)
в show cteate table `ts` timestamp NULL DEFAULT NULL
Поэтому я и ПРЕДПОЛОЖИЛ, что ТАМ не NULL

Чтобы это "понять" - необходимо перещелкнуть версию ДБ на 5.7 и посмотреть что там на самом деле в show create table (выше)
Долой лекции, в MySQL нужны эксперименты Благо в fiddle это удобно.
И еще раз - чтобы найти БАГоГЛЮК в MariaDB - надо просто посмотреть как это работает в MariaDB.
...
Рейтинг: 0 / 0
24.01.2022, 12:21
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128973
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
**
И еще раз - чтобы найти БАГоГЛЮК в MariaDB MySQL - надо просто посмотреть как это работает в MariaDB.
...
Рейтинг: 0 / 0
24.01.2022, 12:43
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40128983
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
автор-- SET @tts:=test.ts;
-- SET @nts:=new.ts;
если эти строки раскомментировать - то все заработает. ёмоё, так ошибка в триггере,
SET @tts:=ts; ошибка
OLD нельзя в BEFORE
та это замкнутый круг аля MySQL
Но все равно все завязано на непонятном значении NEW в триггере (там не может содержаться NULL, и там не нулл)
...
Рейтинг: 0 / 0
24.01.2022, 13:01
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40129053
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Alex_Ustinov
-- SET @tts:=test.ts;
-- SET @nts:=new.ts;
если эти строки раскомментировать - то все заработает.

Да, копирование NEW.ts в UDV делает код работоспособным: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d63f5e37530de27f0fd463f16d7c452e
...
Рейтинг: 0 / 0
24.01.2022, 15:23
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40129104
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
вот это определение не является правильным
ts TIMESTAMP);
в этом вся беда

правильным будет явное указание
ts TIMESTAMP NULL DEFAULT NULL);
иначе боюсь после исправления этого бага работать опять не будет
...
Рейтинг: 0 / 0
24.01.2022, 17:07
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40129163
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Alex_Ustinov
вот это определение не является правильным
ts TIMESTAMP);
в этом вся беда

правильным будет явное указание
ts TIMESTAMP NULL DEFAULT NULL);
В MySQL при отсутствии явного определения будет сгенерировано именно такое.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=afd55907a878a79dce404590c61fc2e2
...
Рейтинг: 0 / 0
24.01.2022, 20:57
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40129173
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
Akina,

это и есть нонсенс.
ВСЕ остальные версии генерируют
`ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()

об этом я писал выше.

в исходниках
mysql-8.0.28\sql\sql_table.cc (строка 4158) И
mysql-5.7.36\sql\sql_table.cc (и в MariaDB) (строка 3307) видна разница именно в генерации поля(с NULL и без NULL)
это только одно "место" кода которое я посмотрел. Причем в 8-ке перешли на nullptr, а в 5.7 и MrDB NULL. в таком случае лишь одна директива компилятора С/С++ могла перевернуть логику с ног на голову.
Еще могли в одном месте поменять код, а в другом просто не знать.
В триггере значение NEW. это же не всегда то что передаем в Инсерте, но и значение по дефолту, типа insert test(id) values(3).
То есть там тоже много "если-то-иначе 55 раз".
"Умные изречения"
гипотетически возможно что в 8.0 и сравнивается ерунда в операторе типа
'NULL' <=> (nullptr *)0 указатель на 0.
Но после каких то операций подключается директива типа
#define NULL 0
или
#define NULL (void*)0
и сравнивается как надо.
...
Рейтинг: 0 / 0
24.01.2022, 21:58
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40129174
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
Далее,

смотрим в том месте кода, что я указал;
mysql-8.0.28\sql\sql_table.cc (строка 4158) И
mysql-5.7.36\sql\sql_table.cc/**
Modifies the first column definition whose SQL type is TIMESTAMP
Изменяем определение первого столбца с типом SQL TIMESTAMP.
 by adding the features DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

@param column_definitions The list of column definitions, in the physical
order in which they appear in the table.
*/почему только первого???
наверное прикол какой-то.....
Проверяем fiddle (пощелкать версии)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
CREATE TABLE test (id INT, 
                   ts TIMESTAMP,
                   ts2 TIMESTAMP);
-- 5.7 надо DEFAULT для ts2, таблица не создается!!!
Invalid default value for 'ts2'
-- MariaDB -- show create table
  `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)
-- 8.0 
  `ts` timestamp NULL DEFAULT NULL,
  `ts2` timestamp NULL DEFAULT NULL
-- два одинаковых поля
-- получаем что в 8.0 даже комменты в коде не соответствуют сделаному-исправленному.

можно посмотреть также и с NULL в определении каждого поля.
Наверное тоже весело
...
Рейтинг: 0 / 0
24.01.2022, 22:18
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40129175
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Alex_Ustinov
ВСЕ остальные версии генерируют
`ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()

Это - расширение, явно описанное в документации.

https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html TIMESTAMP and DATETIME columns have no automatic properties unless they are specified explicitly, with this exception: If the explicit_defaults_for_timestamp system variable is disabled, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

Соответственно различия, наблюдаемые для версий - всего лишь различия в конфигурации.

Alex_Ustinov
В триггере значение NEW. это же не всегда то что передаем в Инсерте, но и значение по дефолту, типа insert test(id) values(3).

В триггере значение NEW. должно быть значением, которое на текущий момент должно быть присвоено полю. Откуда оно взято - из текста запроса (если указано) или из дефолтного значения поля (если не указано), разницы быть не должно. Формирование значений для записи, которые должны быть использованы для вставки либо обновления, должно быть выполнено ещё до срабатывания первого триггера. Косвенно это подтверждается тем, что если явно присваиваемое значение не соответствует требованиям на значение (неверный тип данных, NULL для NOT NULL поля), то этот факт детектируется как ошибка ещё до вызова триггера.
...
Рейтинг: 0 / 0
24.01.2022, 22:53
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40129176
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
ниже в коде комментарий
~второе значение CURRENT_TIMESTAMP по умолчанию из-за неявного продвижения секунды удаляется
...
Рейтинг: 0 / 0
24.01.2022, 23:04
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40129179
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
авторявно описанное в документации.описано значит воспользуемся
в 8,0 поставим
set @@explicit_defaults_for_timestamp=0;
получаем смесь 5.7/MariaDB -

CREATE TABLE test (id INT,
ts TIMESTAMP ,
ts2 TIMESTAMP NULL); (Без NULL ошибка, для второго TIMESTAMP необходимо дефолтное значение)
-->
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ts2` timestamp NULL DEFAULT NULL

fiddle8.0+errorMessage

но это все лирика.

и так и сяк в 8.0
test.ts <=> NEW.ts не работает
...
Рейтинг: 0 / 0
25.01.2022, 00:35
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40129313
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
можно было просто обернуть
COALESCE(NEW.ts,NULL)
...
Рейтинг: 0 / 0
25.01.2022, 15:42
| Ответить | Цитировать | Написать  
Оператор <=> в триггере - что я упускаю?
    #40135960
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
это действительно баг,

https://bugs.mysql.com/bug.php?id=105773
...
Рейтинг: 0 / 0
22.02.2022, 18:45
| Ответить | Цитировать | Написать  
25 сообщений из 25, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оператор <=> в триггере - что я упускаю?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&gotonew=1&tid=1827769]:
 0ms
get settings:
 15ms
get forum list:
 5ms
check forum access:
 1ms
check topic access:
 1ms
track hit:
 62ms
get topic data:
 6ms
get first new msg:
 3ms
get forum data:
 1ms
get page messages:
 415ms
get tp. blocked users:
 2ms
others: 285ms
total:  796ms
созд. / загр.: 796ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]