Это упрощенная версия выполняемого нами запроса, в котором нам нужно найти все строки в основной родительской таблице, которым соответствуют дочерние строки. Приведенный ниже запрос не возвращает результатов, если одна из дочерних таблиц пуста.
У основной таблицы есть две дочерние таблицы:
CREATE TABLE main (id INT PRIMARY KEY, name VARCHAR(8));
CREATE TABLE child1(id INT PRIMARY KEY, main_id int, name VARCHAR(8));
ALTER TABLE child1 add constraint fk_child1_main foreign key (main_id) references main (id);
CREATE TABLE child2(id INT PRIMARY KEY, main_id int, name VARCHAR(8));
ALTER TABLE child2 add constraint fk_child2_main foreign key (main_id) references main (id);
INSERT INTO main (id, name) VALUES (1, 'main');
INSERT INTO child1 (id, main_id, name) VALUES (2, 1, 'child1');
В child2 нет строк, и следующий запрос не возвращает строк, когда он пуст:
SELECT
main.*
FROM
main
INNER JOIN
child1
ON
main.id = child1.main_id
INNER JOIN
child2
ON
main.id = child2.main_id
WHERE
child1.name = 'child1' OR
child2.name = 'DOES NOT EXIST';
Если строка добавляется к child2, даже если она не соответствует предложению WHERE, тогда SELECT возвращает строку в основной таблице.
INSERT INTO child2 (id, main_id, name) VALUES (4, 1, 'child2');
Я тестировал это на Derby и SQLite, так что это похоже на что-то общее с базами данных.
Почему это так себя ведет?
Что я могу сделать, чтобы это исправить?
Я мог бы изменить на UNION отдельные SELECT, но это гораздо более многословно, и, кроме того, мы генерируем SQL динамически, и мне не нужно менять наш код.
Еще одно исправление — просто добавить в базу данных тупую строку, но это грязно.
PS Основная таблица — это таблица сеансов в системе управления активами, в которой записываются активы, которые ищут клиенты. Существуют различные типы поиска, и каждый вид получает отдельную дочернюю таблицу, а также есть дочерняя таблица атрибутов для пар ключ/значение для сеанса, в котором можно выполнять поиск.
