0

I have view questView (algorithm MERGE). I try to do query (for example):

SELECT id FROM questView WHERE id NOT IN (SELECT id FROM questView)

I must get empty result. But NOT IN condition ignored. Query worked right if I use TEMPTABLE algorithm (empty result).

Why it happens? Full code of my view:

SELECT `qt`.`officeId` AS `officeId`,`qt`.`id` AS `id`,`qt`.`onlyOnce` AS `onlyOnce`,`qt`.`title` AS `title`,`qc`.`title` AS `catTitle`,`qt`.`description` AS `description`,`qt`.`imgUrl` AS `imgUrl`, IFNULL(`qr`.`exp`,0) AS `exp`, IFNULL(`qr`.`expMinus`,0) AS `expMinus`, IFNULL(`qr`.`gold`,0) AS `gold`, IFNULL(`qr`.`karma`,0) AS `karma`, IFNULL(`qr`.`hp`,0) AS `hp`,`qt`.`type` AS `type`,`qt`.`autocomplete` AS `autocomplete`,`lm`.`text` AS `log`,`qt`.`status` AS `status`,`qt`.`questCategoryId` AS `questCategoryId`,`qt`.`popularity` AS `popularity`
FROM (((`questType` `qt`
LEFT JOIN `logMessage` `lm` ON((`lm`.`dopId` = `qt`.`id`)))
LEFT JOIN `questRarity` `qr` ON((`qr`.`id` = `qt`.`questRarityId`)))
LEFT JOIN `questCategory` `qc` ON((`qc`.`id` = `qt`.`questCategoryId`)))
WHERE (`qt`.`status` = 1)
ORDER BY `qt`.`questCategoryId` DESC,`qt`.`id` DESC

UPD:

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `a` (`id`) VALUES (1), (2),(3);

CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `b` (`id`, `aid`) VALUES (1, 1),(2, 2),(3, 3);

CREATE ALGORITHM=MERGE VIEW `v` AS select `a`.`id` AS `aid`,`b`.`id` AS `bid` from `a` left join `b` on `b`.`aid` = `a`.`id` order by `a`.`id`;

CREATE ALGORITHM=MERGE VIEW `v2` AS select `a`.`id` AS `aid`,`b`.`id` AS `bid` from `a` left join `b` on `b`.`aid` = `a`.`id`;

My queries:

SELECT aid FROM v WHERE aid NOT IN (SELECT aid FROM v);
SELECT aid FROM v2 WHERE aid NOT IN (SELECT aid FROM v2);

result1:

aid
1
2
3

result2:

aid
Cœur
  • 37,241
  • 25
  • 195
  • 267
Alexander Lapenko
  • 167
  • 5
  • What's with all the parentheses? – T.J. Crowder Aug 19 '16 at 10:38
  • 1
    You'll need to add a [mcve] to the question: Reduce the view to the minimum necessary to replicate the behavior you're seeing, then add `CREATE TABLE`, `INSERT INTO`, `CREATE VIEW`, and finally the query returning the wrong results to the question. – T.J. Crowder Aug 19 '16 at 10:45
  • 1
    @T.J.Crowder I think you're right. If a given record's ID matches the `WHERE` clause, then worst case scenario we'd have `TRUE OR NULL` which is `TRUE` because it doesn't matter what we don't know about `NULL`. – Tim Biegeleisen Aug 19 '16 at 10:49
  • @T.J. Crowder I update my post. – Alexander Lapenko Aug 19 '16 at 11:15
  • Nice one! But you have both a table and a view called `v`. The point of an [mcve] is to be something you've **really run** to make sure you have a replicable issue. – T.J. Crowder Aug 19 '16 at 11:18
  • If I fix that and remove the definer stuff so we're not so tied to your environment, your query at the end returns the empty set, as expected. – T.J. Crowder Aug 19 '16 at 11:20
  • Please create an [SQLFiddle](http://sqlfiddle.com) that actually shows the behaviour you're seeing. – Bohemian Aug 19 '16 at 11:30
  • Updated my post. In SQLFiddle I can't repeat this trouble. Two queries worked identically (use mysql 5.6). http://sqlfiddle.com/#!9/05f631/1 On my server I use Maria DB 10.1.13 – Alexander Lapenko Aug 19 '16 at 11:50

0 Answers0