Подводный камень в Django ORM. | Forum

Posts: 552
Location: savao@yandex.ru

1 Aug. 2013, 21:36

Posts: 552
Location: savao@yandex.ru

Добрый день.

Стояла задача - разбить множество всех записей в одной таблице БД на непересекающиеся подмножества, в сумме дающие полное мноджество, в соответствии с несколькими условиями. Иначе говоря - факторизовать. Я всегда считал что если написать:

set1 = Models.objects.filter(condition A)
set2 = Models.objects.exclude(condition A)

То это и будет факторизацией всего множества Models на два класса. Т.е. set1 не пересекается с set2, в их объединение дает все множество Models. Но оказалось что это не так.

Решил поделится своим наблюдением, может это кому-то пригодится. А может кто-то знает причину и сможет ее объяснить.

Теперь конкретнее. Есть модель Contragents, есть ссылающиеся на нее модели Contact и Staff.

Я создаю запросы:

con1 = Contragents.objects.filter(~Q(contact__ctype='E', contact__staff=None, contact__actual=True)|~Q(contact__ctype__in=['M','P'], contact__staff=None, contact__actual=True)|~Q(staff__main=True)|Q(contact__actual=False)).distinct()

con2 = Contragents.objects.exclude(~Q(contact__ctype='E', contact__staff=None, contact__actual=True)|~Q(contact__ctype__in=['M','P'], contact__staff=None, contact__actual=True)|~Q(staff__main=True)|Q(contact__actual=False)).distinct()

И у меня получается что одна запись у которой contact__actual = False попадает не только в con1 но и в con2. Решить эту проблему можно довольно легко, изменив запрос:

con2 = Contragents.objects.exclude(~Q(contact__ctype='E', contact__staff=None, contact__actual=True)|~Q(contact__ctype__in=['M','P'], contact__staff=None, contact__actual=True)|~Q(staff__main=True)).exclude(contact__actual=False).distinct()

Но почему не работает первый вариант, я так и не понял.

Answer | 0

1 Aug. 2013, 21:44

Posts: 5750
Location: Львов

1 Aug. 2013, 21:44

Posts: 5750
Location: Львов

Та ну запросы SQL показывайте. Хрен его знает что там ОРМ нагенерил с таким-то условием. У сложных запросов всегда нужно SQL проверять. Это же не на уровне Python делается все фильтруется.

Фиксю баги по трейсбеку.

Answer | 0

1 Aug. 2013, 22:50 sjavao Posts: 552 Location: savao@yandex.ru	1 Aug. 2013, 22:50 sjavao Posts: 552 Location: savao@yandex.ru Запросы там сложноватые.
	Answer \| 0

1 Aug. 2013, 22:52

Posts: 552
Location: savao@yandex.ru

1 Aug. 2013, 22:52

Posts: 552
Location: savao@yandex.ru

Ну вот к примеру exclude:

SELECT DISTINCT `kad_contragents`.`id`, `kad_contragents`.`inn`, `kad_contragents`.`name`, `kad_contragents`.`type_of_company_id`, `kad_contragents`.`reject`, `kad_contragents`.`reject_comment` FROM `kad_contragents` LEFT OUTER JOIN `kad_contact` ON (`kad_contragents`.`id` = `kad_contact`.`contragent_id`) WHERE NOT ((`kad_contact`.`actual` = False  OR NOT ((`kad_contragents`.`id` IN (SELECT U1.`contragent_id` FROM `kad_contact` U1 WHERE (U1.`actual` = True  AND U1.`contragent_id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL) AND (`kad_contragents`.`id` IN (SELECT U0.`id` FROM `kad_contragents` U0 LEFT OUTER JOIN `kad_contact` U1 ON (U0.`id` = U1.`contragent_id`) LEFT OUTER JOIN `kad_staff` U2 ON (U1.`staff_id` = U2.`id`) WHERE (U2.`id` IS NULL AND U0.`id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL) AND (`kad_contragents`.`id` IN (SELECT U1.`contragent_id` FROM `kad_contact` U1 WHERE (U1.`ctype` = E  AND U1.`contragent_id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL)) OR NOT ((`kad_contragents`.`id` IN (SELECT U1.`contragent_id` FROM `kad_contact` U1 WHERE (U1.`actual` = True  AND U1.`contragent_id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL) AND (`kad_contragents`.`id` IN (SELECT U1.`contragent_id` FROM `kad_contact` U1 WHERE (U1.`ctype` IN (M, P) AND U1.`contragent_id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL) AND (`kad_contragents`.`id` IN (SELECT U0.`id` FROM `kad_contragents` U0 LEFT OUTER JOIN `kad_contact` U1 ON (U0.`id` = U1.`contragent_id`) LEFT OUTER JOIN `kad_staff` U2 ON (U1.`staff_id` = U2.`id`) WHERE (U2.`id` IS NULL AND U0.`id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL)) OR NOT ((`kad_contragents`.`id` IN (SELECT U1.`contragent_id` FROM `kad_staff` U1 WHERE (U1.`main` = True  AND U1.`contragent_id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL))))

Answer | 0

1 Aug. 2013, 22:54

Posts: 552
Location: savao@yandex.ru

1 Aug. 2013, 22:54

Posts: 552
Location: savao@yandex.ru

А вот filter:

SELECT DISTINCT `kad_contragents`.`id`, `kad_contragents`.`inn`, `kad_contragents`.`name`, `kad_contragents`.`type_of_company_id`, `kad_contragents`.`reject`, `kad_contragents`.`reject_comment` FROM `kad_contragents` LEFT OUTER JOIN `kad_contact` ON (`kad_contragents`.`id` = `kad_contact`.`contragent_id`) WHERE (`kad_contact`.`actual` = False  OR NOT ((`kad_contragents`.`id` IN (SELECT U1.`contragent_id` FROM `kad_contact` U1 WHERE (U1.`actual` = True  AND U1.`contragent_id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL) AND (`kad_contragents`.`id` IN (SELECT U0.`id` FROM `kad_contragents` U0 LEFT OUTER JOIN `kad_contact` U1 ON (U0.`id` = U1.`contragent_id`) LEFT OUTER JOIN `kad_staff` U2 ON (U1.`staff_id` = U2.`id`) WHERE (U2.`id` IS NULL AND U0.`id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL) AND (`kad_contragents`.`id` IN (SELECT U1.`contragent_id` FROM `kad_contact` U1 WHERE (U1.`ctype` = E  AND U1.`contragent_id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL)) OR NOT ((`kad_contragents`.`id` IN (SELECT U1.`contragent_id` FROM `kad_contact` U1 WHERE (U1.`actual` = True  AND U1.`contragent_id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL) AND (`kad_contragents`.`id` IN (SELECT U1.`contragent_id` FROM `kad_contact` U1 WHERE (U1.`ctype` IN (M, P) AND U1.`contragent_id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL) AND (`kad_contragents`.`id` IN (SELECT U0.`id` FROM `kad_contragents` U0 LEFT OUTER JOIN `kad_contact` U1 ON (U0.`id` = U1.`contragent_id`) LEFT OUTER JOIN `kad_staff` U2 ON (U1.`staff_id` = U2.`id`) WHERE (U2.`id` IS NULL AND U0.`id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL)) OR NOT ((`kad_contragents`.`id` IN (SELECT U1.`contragent_id` FROM `kad_staff` U1 WHERE (U1.`main` = True  AND U1.`contragent_id` IS NOT NULL)) AND `kad_contragents`.`id` IS NOT NULL)))

Answer | 0

2 Aug. 2013, 11:46 wdstrm Posts: 834	2 Aug. 2013, 11:46 wdstrm Posts: 834 мой мосх расплавился 71% добра
	Answer \| 0

2 Aug. 2013, 22:25 RaD Posts: 2822 Location: Снегири, МО	2 Aug. 2013, 22:25 RaD Posts: 2822 Location: Снегири, МО Отформатируй, чтоли..., чтобы было видно где чего. Если ничего не помогает, прочтите документацию, наконец!
	Answer \| 0

4 Aug. 2013, 13:03 sjavao Posts: 552 Location: savao@yandex.ru	4 Aug. 2013, 13:03 sjavao Posts: 552 Location: savao@yandex.ru Тут проблема, я не умею сложный sql запрос форматировать... не знаю, как его вообще приводить в читабельный вид.
	Answer \| 0

5 Aug. 2013, 13:16

Posts: 3

5 Aug. 2013, 13:16

Posts: 3

Вообще для разбора SQL было бы неплохо выкладывать сначала модель. Или описание таблиц на SQL, нечто вроде этого:

CREATE TABLE contragents
(
  id serial NOT NULL,
  inn text,
  name text,
  type_of_company_id integer,
  reject text,
  reject_comment text,
  CONSTRAINT pk_contragents PRIMARY KEY (id )
);
CREATE TABLE staff
(
  id serial NOT NULL,
  main boolean,
  contragent_id integer,
  CONSTRAINT pk_staff PRIMARY KEY (id ),
  CONSTRAINT fk_staff_contragent_id FOREIGN KEY (contragent_id)
      REFERENCES contragents (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE TABLE contact
(
  id serial NOT NULL,
  actual boolean,
  contragent_id integer,
  ctype character(1),
  staff_id integer,
  CONSTRAINT pk_contact PRIMARY KEY (id ),
  CONSTRAINT fk_contact_contragent_id FOREIGN KEY (contragent_id)
      REFERENCES contragents (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

На основе данных можно и анализ уже проводить. Вот отредактированный текст exclude. Убрана часть излишеств синтаксиса и добавление схемы в название таблиц (плюс кавычки). Проверил в PostgreSQL:

SELECT DISTINCT contragents.id,
        contragents.inn,
        contragents.name,
        contragents.type_of_company_id,
        contragents.reject,
        contragents.reject_comment
FROM    contragents
        LEFT JOIN contact ON contragents.id = contact.contragent_id
WHERE   NOT ((
            contact.actual = False
            OR NOT (
                (contragents.id IN (
                    SELECT  U1.contragent_id
                    FROM    contact U1
                    WHERE   (U1.actual = True 
                            AND U1.contragent_id IS NOT NULL)
                    )
                    AND contragents.id IS NOT NULL
                    AND contragents.id IS NOT NULL
                )
                AND (
                    contragents.id IN (
                        SELECT  U0.id
                        FROM    contragents U0
                                LEFT JOIN contact U1 ON U0.id = U1.contragent_id
                                LEFT JOIN staff U2 ON U1.staff_id = U2.id
                        WHERE   (U2.id IS NULL
                                AND U0.id IS NOT NULL)
                    )
                    AND contragents.id IS NOT NULL
                    AND contragents.id IS NOT NULL
                )
                AND (
                    contragents.id IN (
                    SELECT  U1.contragent_id
                    FROM    contact U1
                    WHERE   (U1.ctype = 'E' 
                            AND U1.contragent_id IS NOT NULL)
                    )
                    AND contragents.id IS NOT NULL
                    AND contragents.id IS NOT NULL
                )
            )
            OR NOT (
                (contragents.id IN (
                    SELECT  U1.contragent_id
                    FROM    contact U1
                    WHERE   (U1.actual = True 
                            AND U1.contragent_id IS NOT NULL)
                    )
                AND contragents.id IS NOT NULL
                AND contragents.id IS NOT NULL
                )
                AND (contragents.id IN (
                    SELECT  U1.contragent_id
                    FROM    contact U1
                    WHERE   (U1.ctype IN ('M', 'P')
                            AND U1.contragent_id IS NOT NULL)
                    )
                AND contragents.id IS NOT NULL
                AND contragents.id IS NOT NULL
                )
                AND (contragents.id IN (
                    SELECT  U0.id
                    FROM    contragents U0
                            LEFT JOIN contact U1 ON (U0.id = U1.contragent_id)
                            LEFT JOIN staff U2 ON (U1.staff_id = U2.id)
                    WHERE   (U2.id IS NULL
                            AND U0.id IS NOT NULL)
                    )
                AND contragents.id IS NOT NULL
                AND contragents.id IS NOT NULL
                )
            )
            OR NOT (
                (contragents.id IN (
                    SELECT  U1.contragent_id
                    FROM    staff U1
                    WHERE   (U1.main = True 
                            AND U1.contragent_id IS NOT NULL)
                    )
                AND contragents.id IS NOT NULL
                )
            )
        ))

Сразу смущает перебор проверок, вроде такой:

`kad_contragents`.`id` IS NOT NULL AND `kad_contragents`.`id` IS NOT NULL

почти в каждом вложенном фильтре. Как разберу этот винегрет, отпишу подробнее.

Answer | 0

5 Aug. 2013, 14:21

Posts: 3

5 Aug. 2013, 14:21

Posts: 3

Вот текст запроса "фильтр":

SELECT  DISTINCT contragents.id,
        contragents.inn,
        contragents.name,
        contragents.type_of_company_id,
        contragents.reject,
        contragents.reject_comment
FROM    contragents
        LEFT JOIN contact ON contragents.id = contact.contragent_id
WHERE   (
            contact.actual = False
            OR NOT (
                (contragents.id IN (
                    SELECT  U1.contragent_id
                    FROM    contact U1
                    WHERE   (U1.actual = True
                            AND U1.contragent_id IS NOT NULL)
                    )
                    AND contragents.id IS NOT NULL
                )
                AND (
                    contragents.id IN (
                        SELECT  U0.id
                        FROM    contragents U0
                                LEFT JOIN contact U1 ON U0.id = U1.contragent_id
                                LEFT JOIN staff U2 ON U1.staff_id = U2.id
                        WHERE   (U2.id IS NULL
                                AND U0.id IS NOT NULL)
                    )
                    AND contragents.id IS NOT NULL
                )
                AND (
                    contragents.id IN (
                    SELECT  U1.contragent_id
                    FROM    contact U1
                    WHERE   (U1.ctype = 'E'
                            AND U1.contragent_id IS NOT NULL)
                    )
                    AND contragents.id IS NOT NULL
                )
            )
            OR NOT (
                (contragents.id IN (
                    SELECT  U1.contragent_id
                    FROM    contact U1
                    WHERE   (U1.actual = True
                            AND U1.contragent_id IS NOT NULL)
                    )
                AND contragents.id IS NOT NULL
                )
                AND (contragents.id IN (
                    SELECT  U1.contragent_id
                    FROM    contact U1
                    WHERE   (U1.ctype IN ('M', 'P')
                            AND U1.contragent_id IS NOT NULL)
                    )
                AND contragents.id IS NOT NULL
                )
                AND (contragents.id IN (
                    SELECT  U0.id
                    FROM    contragents U0
                            LEFT JOIN contact U1 ON U0.id = U1.contragent_id
                            LEFT JOIN staff U2 ON U1.staff_id = U2.id
                    WHERE   (U2.id IS NULL
                            AND U0.id IS NOT NULL)
                    )
                AND contragents.id IS NOT NULL
                )
            )
            OR NOT (
                (contragents.id IN (
                    SELECT  U1.contragent_id
                    FROM    staff U1
                    WHERE   (U1.main = True
                            AND U1.contragent_id IS NOT NULL)
                    )
                AND contragents.id IS NOT NULL
            )
        ))

После форматирования стало очевидно, что тексты запросов абсолютно идентичны за исключением директивы NOT() в условии WHERE. И если подумать, действительно эти два запроса должны возвращать две части одного целого. НО. Длинные цепочки фильтров, применяемых на основе связанных данных дают непредсказуемый результат. Я ужаснулся когда увидел этот запрос в планировщике. После оптимизации такого запроса останется четверть текста и скорость возрастёт на порядки. Могу дать простой совет: для выполнения факторизации, кластеризации, классификации или рубрикации одного набора данных, создайте дополнительное поле в той же таблице и обновите его полученными классификационными признаками. Это гарантирует чёткое разделение. Не забудьте только для этого поля указать NOT NULL.

Answer | 0

5 Aug. 2013, 14:38

Posts: 3

5 Aug. 2013, 14:38

Posts: 3

Ну и вдогонку ещё маленький совет: проверяйте данные. Например, у логических полей в БД зачастую хранится три разных значения (True, False и NULL) поэтому при наличии записей с NULL в проверяемом поле факторизация типа

WHERE  boolean_field1 = true AND NOT (boolean_field2 = true)

WHERE  NOT(boolean_field1 = true) AND boolean_field = true

Вернёт два набора данных. И ни один не вернёт записи где boolean_field1 IS NULL либо boolean_field2 IS NULL

Answer | 0

5 Aug. 2013, 15:03 wdstrm Posts: 834	5 Aug. 2013, 15:03 wdstrm Posts: 834 ну, для boolean с NULL есть отдельный тип полей в джанге. Насколько помню, если использовать простое поле BooleanField то там все ок должно быть 71% добра
	Answer \| 0

13 Aug. 2013, 19:25

Posts: 552
Location: savao@yandex.ru

13 Aug. 2013, 19:25

Posts: 552
Location: savao@yandex.ru

Для поля boolean я просто не указываю null=True так что NULL оно быть не может.

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

Модели я не выкладывал, потмоу что, как мне кажется, не в них суть. Все данные которые нужны, в запросах уже есть. Но если надо, могу выложить и модели.

Вот главное что я тут прочел, что длинные цепочки запросов могут выдавать непредсказуемый результат. Если это так, то проблема не в django ORM, а в самом движке БД. И это печально. Это говорит о том что они ненадежны. Хотелось бы знать, насколько эта информация указанная gds верна. Кто подскажет?

Спасибо что не пожалели времени и ответили. Отдельное спасибо gds что провел такую большую работу, разбирая запросы, которые ему абсолютно не нужны.

Answer | 0

13 Aug. 2013, 22:24

Posts: 5750
Location: Львов

13 Aug. 2013, 22:24