MySQL Bugs: #73284: Wrong query result when search from 2 related views

Bug #73284	Wrong query result when search from 2 related views
Submitted:	13 Jul 2014 19:12	Modified:	14 Jul 2014 8:44
Reporter:	Alexey Demin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.5.35, 5.5.38	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	join buffer problem, query wrong result

Description:
Very strange bug with query result.
Instead of 

house_id | base |
---------+-------
HOUSE_1  | 10   |

i've  got

house_id | base |
---------+-------
HOUSE_1  |  0   |

Of course my query and database is much more bigger, and I extracted the minimum stuff for reproducing. 

So, just change on of the view ( remove one join to recomend_test from the first view or remove join to countries_test from the second one) and you'll get correct result.  

It reproduces on windows 5.5.20 and 5.5.35-0+wheezy1, I didn't test it on the last versions, sorry for that, but I think anybody can do it (it will take less then 3 minutes) and close the bug if it is already fixed.

How to repeat:
CREATE TABLE `hierarchy_test` (
  `hie_id` int(10) NOT NULL DEFAULT '0',
  `country_id` varchar(10) NOT NULL,
  `city_id` varchar(10) DEFAULT NULL,
  `street_id` varchar(128) DEFAULT NULL,
  `house_id` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `hierarchy_test` VALUES (900,'SPN','BARC','MAIN','HOUSE_1'),
                                    (901,'SPN','BARC','MAIN','HOUSE_2'),
                                    (902,'SPN','BARC','MAIN','HOUSE_3');

CREATE TABLE `recomend_test` (
  `rec_id` int(10) NOT NULL DEFAULT '0',
  `rec_name` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `recomend_test` VALUES (1,'Recommendation 1');

CREATE TABLE `applications_test` (
  `name` varchar(64) DEFAULT NULL,
  `app_id` int(11) NOT NULL DEFAULT '0',
  `rec_id1` int(11) DEFAULT NULL,
  `rec_id2` int(11) DEFAULT NULL,
  `rec_id3` int(11) DEFAULT NULL,
  `house_id` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `applications_test` VALUES ('John',2819,NULL,NULL,NULL,'HOUSE_1'),
                                       ('Paul',2820,NULL,NULL,NULL,'HOUSE_1'),
                                       ('Xavier',2821,NULL,NULL,NULL,'HOUSE_1'),
                                       ('Maricela',2822,NULL,NULL,NULL,'HOUSE_1'),
                                       ('Ann',2823,NULL,NULL,NULL,'HOUSE_1'),
                                       ('Julio',2824,NULL,NULL,NULL,'HOUSE_1'),
                                       ('Jose',2825,NULL,NULL,NULL,'HOUSE_1'),
                                       ('Mauricio',2826,NULL,NULL,NULL,'HOUSE_1'),
                                       ('Barbara',2827,NULL,NULL,NULL,'HOUSE_1'),
                                       ('Helena',2828,NULL,NULL,NULL,'HOUSE_1');

CREATE TABLE `countries_test` (
  `country_id` varchar(10) NOT NULL,
  `country_name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `countries_test` VALUES ('SPN','Spain'),('FRA','France');

CREATE TABLE `houses_test` (
  `house_id` varchar(128) NOT NULL COMMENT 'Code',
  `house_name` varchar(256) NOT NULL COMMENT 'Name'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `houses_test` VALUES ('HOUSE_1','The first house'),('HOUSE_2','Second one'),('HOUSE_3','Last one');

CREATE OR REPLACE VIEW app_test_vw AS 
SELECT a.app_id AS app_id,
a.house_id,
a.rec_id1 AS rec_id1,
a.rec_id2 AS rec_id2,
a.rec_id3 AS rec_id3,
h.city_id
FROM applications_test a
LEFT JOIN hierarchy_test h ON a.house_id = h.house_id
LEFT JOIN recomend_test r1 ON a.rec_id1 = r1.rec_id
LEFT JOIN recomend_test r2 ON a.rec_id2 = r2.rec_id
LEFT JOIN recomend_test r3 ON a.rec_id3 = r3.rec_id;

CREATE OR REPLACE view hie_test_vw AS
SELECT
p.house_id,
m.country_name,
p.house_name
FROM hierarchy_test h
INNER JOIN countries_test m ON m.country_id = h.country_id
INNER JOIN houses_test p ON p.house_id = h.house_id;

-- SELECT  z.house_id, ( SELECT count(*)  FROM app_test_vw a WHERE a.house_id = z.house_id ) AS base FROM   hie_test_vw z WHERE   z.house_id = 'HOUSE_1'

You can smoothly remove 
LEFT JOIN recomend_test r2 ON a.rec_id2 = r2.rec_id
LEFT JOIN recomend_test r3 ON a.rec_id3 = r3.rec_id
and corresponding select values.

It seems that the difference in EXPLAIN plans is "Using join buffer"

Hello Alexey,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

// 5.5.38 - affected

mysql>
mysql> CREATE TABLE `hierarchy_test` (
    ->   `hie_id` int(10) NOT NULL DEFAULT '0',
    ->   `country_id` varchar(10) NOT NULL,
    ->   `city_id` varchar(10) DEFAULT NULL,
    ->   `street_id` varchar(128) DEFAULT NULL,
    ->   `house_id` varchar(128) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO `hierarchy_test` VALUES (900,'SPN','BARC','MAIN','HOUSE_1'),
    ->                                     (901,'SPN','BARC','MAIN','HOUSE_2'),
    ->                                     (902,'SPN','BARC','MAIN','HOUSE_3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE `recomend_test` (
    ->   `rec_id` int(10) NOT NULL DEFAULT '0',
    ->   `rec_name` varchar(64) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `recomend_test` VALUES (1,'Recommendation 1');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> CREATE TABLE `applications_test` (
    ->   `name` varchar(64) DEFAULT NULL,
    ->   `app_id` int(11) NOT NULL DEFAULT '0',
    ->   `rec_id1` int(11) DEFAULT NULL,
    ->   `rec_id2` int(11) DEFAULT NULL,
    ->   `rec_id3` int(11) DEFAULT NULL,
    ->   `house_id` varchar(128) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO `applications_test` VALUES ('John',2819,NULL,NULL,NULL,'HOUSE_1'),
    ->                                        ('Paul',2820,NULL,NULL,NULL,'HOUSE_1'),
    ->                                        ('Xavier',2821,NULL,NULL,NULL,'HOUSE_1'),
    ->                                        ('Maricela',2822,NULL,NULL,NULL,'HOUSE_1'),
    ->                                        ('Ann',2823,NULL,NULL,NULL,'HOUSE_1'),
    ->                                        ('Julio',2824,NULL,NULL,NULL,'HOUSE_1'),
    ->                                        ('Jose',2825,NULL,NULL,NULL,'HOUSE_1'),
    ->                                        ('Mauricio',2826,NULL,NULL,NULL,'HOUSE_1'),
    ->                                        ('Barbara',2827,NULL,NULL,NULL,'HOUSE_1'),
    ->                                        ('Helena',2828,NULL,NULL,NULL,'HOUSE_1');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `countries_test` (
    ->   `country_id` varchar(10) NOT NULL,
    ->   `country_name` varchar(30) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO `countries_test` VALUES ('SPN','Spain'),('FRA','France');

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE `houses_test` (
    ->   `house_id` varchar(128) NOT NULL COMMENT 'Code',
    ->   `house_name` varchar(256) NOT NULL COMMENT 'Name'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO `houses_test` VALUES ('HOUSE_1','The first house'),('HOUSE_2','Second one'),('HOUSE_3','Last one');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE OR REPLACE VIEW app_test_vw AS
    -> SELECT a.app_id AS app_id,
    -> a.house_id,
    -> a.rec_id1 AS rec_id1,
    -> a.rec_id2 AS rec_id2,
    -> a.rec_id3 AS rec_id3,
    -> h.city_id
    -> FROM applications_test a
    -> LEFT JOIN hierarchy_test h ON a.house_id = h.house_id
    -> LEFT JOIN recomend_test r1 ON a.rec_id1 = r1.rec_id
    -> LEFT JOIN recomend_test r2 ON a.rec_id2 = r2.rec_id
    -> LEFT JOIN recomend_test r3 ON a.rec_id3 = r3.rec_id;

CREATE OR REPLACE view hie_test_vw AS
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> CREATE OR REPLACE view hie_test_vw AS
    -> SELECT
    -> p.house_id,
    -> m.country_name,
    -> p.house_name
    -> FROM hierarchy_test h
    -> INNER JOIN countries_test m ON m.country_id = h.country_id
    -> INNER JOIN houses_test p ON p.house_id = h.house_id;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT  z.house_id, ( SELECT count(*)  FROM app_test_vw a WHERE a.house_id = z.house_id ) AS base FROM   hie_test_vw z WHERE   z.house_id = 'HOUSE_1';
+----------+------+
| house_id | base |
+----------+------+
| HOUSE_1  |    0 |
+----------+------+
1 row in set (0.03 sec)

mysql>
mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.38-debug-log |
+------------------+
1 row in set (0.00 sec)

// 5.6.20/5.7.4 - not affected

mysql> SELECT  z.house_id, ( SELECT count(*)  FROM app_test_vw a WHERE a.house_id = z.house_id ) AS base FROM   hie_test_vw z WHERE   z.house_id = 'HOUSE_1';
+----------+------+
| house_id | base |
+----------+------+
| HOUSE_1  |   10 |
+----------+------+
1 row in set (0.01 sec)

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.7.4-m14-log |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT  z.house_id, ( SELECT count(*)  FROM app_test_vw a WHERE a.house_id = z.house_id ) AS base FROM   hie_test_vw z WHERE   z.house_id = 'HOUSE_1';
+----------+------+
| house_id | base |
+----------+------+
| HOUSE_1  |   10 |
+----------+------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.20-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)