update or insert и подзапрос в качестве значения в values

Запросы, планы, оптимизация запросов, ...

Модераторы: kdv, CyberMax

Ответить
mdfv
Сообщения: 119
Зарегистрирован: 23 май 2006, 15:53

update or insert и подзапрос в качестве значения в values

Сообщение mdfv » 02 июн 2008, 11:09

так вот данная конструкция не вставляет записи.
Причем логику я так и не понял. Один раз как-то удалось вставить, потом на других данных перестало работать. Только если вставлять непосредственное значение.
перечитал релноты в разделе UPDATE OR INSERT Statement
и не нашел ничего подозрительного. Не нашел случая чтоб небыло вставки или апдейта. В статистике только чтение происходит из подзапросов а инсерта нету.

вот так не работает(точнее вставило чудом один раз на одних данных, а на других нет):

Код: Выделить всё

update or insert into ULRELTXTREL
(ADRNAME,ADRSOCR,SUL)
values(
(select (ADRNAME) from KLADR where ADRID=111111111111),
(select (ADRSOCR) from KLADR where ADRID=111111111111),
'УЛ ЛЕНИНА'
)
MATCHING(ADRNAME,ADRSOCR,SUL)
а так работает:

Код: Выделить всё

update or insert into ULRELTXTREL
(ADRNAME,ADRSOCR,SUL)
values(
'ЛЕНИНА',
'УЛ',
'УЛ ЛЕНИНА'
)
MATCHING(ADRNAME,ADRSOCR,SUL)
Причем если вставить прямым текстом(как в последнем примере) то потом вставка с подзапросами начинает таки апдейтить.

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Сообщение hvlad » 02 июн 2008, 11:22

Простой воспроизводимый пример поможет найти причину проблемы.

mdfv
Сообщения: 119
Зарегистрирован: 23 май 2006, 15:53

Сообщение mdfv » 02 июн 2008, 12:10

Server Version: WI-V2.1.0.17798 Firebird 2.1
База:

Код: Выделить всё

SET SQL DIALECT 3;

SET NAMES WIN1251;

SET CLIENTLIB 'FBCLIENT.dll';

CREATE DATABASE 'testdb.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET WIN1251;


CREATE GENERATOR GEN_ULRELTXTREL_ID;
SET GENERATOR GEN_ULRELTXTREL_ID TO 3;


CREATE TABLE KLADR (
    ADRID    BIGINT NOT NULL,
    ADRNAME  VARCHAR(40),
    ADRSOCR  VARCHAR(10)
);

CREATE TABLE ULRELTXTREL (
    PKULT    INTEGER NOT NULL,
    ADRNAME  VARCHAR(40),
    ADRSOCR  VARCHAR(10),
    SUL      VARCHAR(40)
);


ALTER TABLE KLADR ADD CONSTRAINT PK_KLADR PRIMARY KEY (ADRID);
ALTER TABLE ULRELTXTREL ADD CONSTRAINT PK_ULRELTXTREL PRIMARY KEY (PKULT);



CREATE INDEX ULRELTXTREL_ALL ON ULRELTXTREL (ADRNAME, ADRSOCR, SUL);




SET TERM ^ ;


/* Trigger: ULRELTXTREL_BI */
CREATE TRIGGER ULRELTXTREL_BI FOR ULRELTXTREL
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.pkult is null) then
    new.pkult = gen_id(gen_ulreltxtrel_id,1);
end
^

SET TERM ; ^
Данные:

Код: Выделить всё

insert into KLADR
(ADRID,
ADRNAME,
ADRSOCR
)

values
(111111111111,
'ЛЕНИНА',
'УЛ'
)
Если выполнить такой запрос:

Код: Выделить всё

update or insert into ULRELTXTREL
(ADRNAME,ADRSOCR,SUL)
values(
(select (ADRNAME) from KLADR where ADRID=111111111111),
(select (ADRSOCR) from KLADR where ADRID=111111111111),
'УЛ ЛЕНИНА'
)
MATCHING(ADRNAME,ADRSOCR,SUL)
То вставки не происходит. И в статистике происходит только 2 индексированных чтения из KLADR.

А если вставить несуществующую в KLADR запись
к примеру:

Код: Выделить всё

update or insert into ULRELTXTREL
(ADRNAME,ADRSOCR,SUL)
values(
(select (ADRNAME) from KLADR where ADRID=111111111112),
(select (ADRSOCR) from KLADR where ADRID=111111111112),
'ПЕР ЛЕНИНА'
)
MATCHING(ADRNAME,ADRSOCR,SUL)
то чтений естественно нет, но вставка с нуллами уже происходит.

mdfv
Сообщения: 119
Зарегистрирован: 23 май 2006, 15:53

Сообщение mdfv » 02 июн 2008, 12:42

Хочется просто узнать - это баг сервера/железа или баг в голове и надо внимательнее читать документы и писать по другому.....

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Сообщение hvlad » 02 июн 2008, 12:49

UPDATE OR INSERT это эквивалент

Код: Выделить всё

UPDATE ...
IF (ROW_COUNT = 0)
THEN INSERT ...
Т.к. у тебя есть SELECT'ы, то ROW_COUNT никак не 0, посему в твоём случае UPDATE OR INSERT не подходит.

А подходит тебе MERGE :

Код: Выделить всё

MERGE INTO ULRELTXTREL U
  USING (SELECT * FROM KLADR WHERE ADRID=111111111111) K
     ON U.ADRNAME = K.ADRNAME AND U.ADRSOCR = K.ADRSOCR

  WHEN MATCHED THEN UPDATE SET SUL = 'УЛ ЛЕНИНА'

  WHEN NOT MATCHED THEN INSERT (ADRNAME, ADRSOCR, SUL)
            VALUES (K.ADRNAME, K.ADRSOCR, 'УЛ ЛЕНИНА')

mdfv
Сообщения: 119
Зарегистрирован: 23 май 2006, 15:53

Сообщение mdfv » 02 июн 2008, 13:07

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

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Сообщение hvlad » 02 июн 2008, 13:15

Правильнее всегда использовать MERGE при обновлении таблицы данными из других таблиц.

Детали реализации описаны, например, здесь

Tonal
Сообщения: 104
Зарегистрирован: 30 сен 2007, 13:42

Сообщение Tonal » 05 июн 2008, 09:39

hvlad писал(а):UPDATE OR INSERT это эквивалент

Код: Выделить всё

UPDATE ...
IF (ROW_COUNT = 0)
THEN INSERT ...
Т.к. у тебя есть SELECT'ы, то ROW_COUNT никак не 0, посему в твоём случае UPDATE OR INSERT не подходит.
Мне кажется, что в таком случае нужно запретить в использование вложенных SELECT-ов в конструкции UPDATE OR INSERT поскольку она в этом случае вырождается в UPDATE и смысла не имеет.
Либо изменять логику и не учитывать ROW_COUNT от вложенных SELECT-ов.

mdfv
Сообщения: 119
Зарегистрирован: 23 май 2006, 15:53

Сообщение mdfv » 05 июн 2008, 11:00

Кстати MERGE INTO в документации где-нибудь описано?
Или только в материалах конференций, выступлений?

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Сообщение hvlad » 05 июн 2008, 11:19

mdfv писал(а):Кстати MERGE INTO в документации где-нибудь описано?
Или только в материалах конференций, выступлений?
Нет конечно, только на ушкО рассказываем

Ты документацию-то открывал ? Где не нашёл ?

mdfv
Сообщения: 119
Зарегистрирован: 23 май 2006, 15:53

Сообщение mdfv » 05 июн 2008, 11:28

Здесь не нашел:
Firebird 2.1 Release Notes
14 April 2008 - Document v. 0210_53 - for Firebird 2.1
А где надо?
Несколько раз смотрел.
UPDATE OR INSERT есть
а MERGE нет.

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Сообщение WildSery » 05 июн 2008, 11:59


mdfv
Сообщения: 119
Зарегистрирован: 23 май 2006, 15:53

Сообщение mdfv » 05 июн 2008, 12:03

Я смотрел pdf который с сервером идет.
В нем нет.

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Сообщение hvlad » 05 июн 2008, 12:47

mdfv писал(а):Я смотрел pdf который с сервером идет.
В нем нет.
Да, действительно нет. Значит пропустили. И в доке бывают баги :(

Можешь скачать свежую версию, там есть

Ответить