- 5.1.77 bin/mysql -uroot -S /tmp/mysql_ushastry.sock test < ../mysql-5.7.27/bug_dump_mysql.sql [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.1.77: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.77 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT now() date_created, -> NULL date_undo, -> spf.created_by_id created_by_id, JOIN test.account_test f ON fu.account_id = f.i -> spf.date_created staff_preferred_facility_date_created, -> spf.date_modified date_modified, -> spf.facility_id facility_id, -> spf.id spf_id, -> spf.modified_by_id modified_by_id, -> spf.staff_preferences_id staff_preferences_id, -> spf.default_facility default_facility -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> AND spf.id NOT IN -> (SELECT spf.id spf_id -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> GROUP BY spf.facility_id, sp.id) -> ORDER BY spf_id; +---------------------+-----------+---------------+---------------------------------------+---------------------+-------------+--------+----------------+----------------------+------------------+ | date_created | date_undo | created_by_id | staff_preferred_facility_date_created | date_modified | facility_id | spf_id | modified_by_id | staff_preferences_id | default_facility | +---------------------+-----------+---------------+---------------------------------------+---------------------+-------------+--------+----------------+----------------------+------------------+ | 2019-08-19 07:22:49 | NULL | 7517 | 2019-01-09 21:57:22 | 2019-01-09 21:57:22 | 107 | 2956 | NULL | 429 | 0 | | 2019-08-19 07:22:49 | NULL | 7517 | 2019-01-09 21:57:22 | 2019-01-09 21:57:22 | 149 | 2957 | NULL | 429 | 0 | | 2019-08-19 07:22:49 | NULL | 7517 | 2019-01-09 21:57:22 | 2019-01-09 21:57:22 | 49 | 2958 | NULL | 429 | 0 | | 2019-08-19 07:22:49 | NULL | 7517 | 2019-01-15 20:25:14 | 2019-01-15 20:25:14 | 49 | 2969 | NULL | 240 | 0 | | 2019-08-19 07:22:49 | NULL | 7517 | 2019-01-15 20:25:14 | 2019-01-15 20:25:14 | 107 | 2970 | NULL | 240 | 0 | +---------------------+-----------+---------------+---------------------------------------+---------------------+-------------+--------+----------------+----------------------+------------------+ 5 rows in set (0.00 sec) mysql> explain SELECT now() date_created, -> NULL date_undo, -> spf.created_by_id created_by_id, -> spf.date_created staff_preferred_facility_date_created, -> spf.date_modified date_modified, -> spf.facility_id facility_id, -> spf.id spf_id, -> spf.modified_by_id modified_by_id, -> spf.staff_preferences_id staff_preferences_id, -> spf.default_facility default_facility -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> AND spf.id NOT IN -> (SELECT spf.id spf_id -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> GROUP BY spf.facility_id, sp.id) -> ORDER BY spf_id; +----+--------------------+-------+--------+---------------------------------------------+-------------------------------------+---------+------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+--------+---------------------------------------------+-------------------------------------+---------+------------+------+-----------------------------------------------------------+ | 1 | PRIMARY | f | ref | PRIMARY,name | name | 52 | const | 1 | Using where; Using index; Using temporary; Using filesort | | 1 | PRIMARY | fu | ref | PRIMARY,account_id | account_id | 4 | test.f.id | 6 | Using index | | 1 | PRIMARY | sp | eq_ref | PRIMARY,uniq_idx_staff_preferences_staff_id | uniq_idx_staff_preferences_staff_id | 4 | test.fu.id | 1 | Using index | | 1 | PRIMARY | spf | ref | staff_preferences_id | staff_preferences_id | 5 | test.sp.id | 1 | Using where | | 2 | DEPENDENT SUBQUERY | f | ref | PRIMARY,name | name | 52 | const | 1 | Using where; Using index; Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | fu | ref | PRIMARY,account_id | account_id | 4 | test.f.id | 6 | Using index | | 2 | DEPENDENT SUBQUERY | sp | eq_ref | PRIMARY,uniq_idx_staff_preferences_staff_id | uniq_idx_staff_preferences_staff_id | 4 | test.fu.id | 1 | Using index | | 2 | DEPENDENT SUBQUERY | spf | ref | staff_preferences_id | staff_preferences_id | 5 | test.sp.id | 1 | Using where | +----+--------------------+-------+--------+---------------------------------------------+-------------------------------------+---------+------------+------+-----------------------------------------------------------+ 8 rows in set (0.00 sec) - 5.5.62 bin/mysql -uroot -S /tmp/mysql_ushastry.sock test < ../mysql-5.7.27/bug_dump_mysql.sql [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.5.62-linux-glibc2.12-x86_64: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.62 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT now() date_created, -> NULL date_undo, -> spf.created_by_id created_by_id, -> spf.date_created staff_preferred_facility_date_created, -> spf.date_modified date_modified, -> spf.facility_id facility_id, -> spf.id spf_id, -> spf.modified_by_id modified_by_id, -> spf.staff_preferences_id staff_preferences_id, -> spf.default_facility default_facility -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> AND spf.id NOT IN -> (SELECT spf.id spf_id -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> GROUP BY spf.facility_id, sp.id) -> ORDER BY spf_id; +---------------------+-----------+---------------+---------------------------------------+---------------------+-------------+--------+----------------+----------------------+------------------+ | date_created | date_undo | created_by_id | staff_preferred_facility_date_created | date_modified | facility_id | spf_id | modified_by_id | staff_preferences_id | default_facility | +---------------------+-----------+---------------+---------------------------------------+---------------------+-------------+--------+----------------+----------------------+------------------+ | 2019-08-19 07:20:36 | NULL | 7517 | 2019-01-09 21:57:22 | 2019-01-09 21:57:22 | 107 | 2956 | NULL | 429 | 0 | | 2019-08-19 07:20:36 | NULL | 7517 | 2019-01-09 21:57:22 | 2019-01-09 21:57:22 | 149 | 2957 | NULL | 429 | 0 | | 2019-08-19 07:20:36 | NULL | 7517 | 2019-01-09 21:57:22 | 2019-01-09 21:57:22 | 49 | 2958 | NULL | 429 | 0 | | 2019-08-19 07:20:36 | NULL | 7517 | 2019-01-15 20:25:14 | 2019-01-15 20:25:14 | 49 | 2969 | NULL | 240 | 0 | | 2019-08-19 07:20:36 | NULL | 7517 | 2019-01-15 20:25:14 | 2019-01-15 20:25:14 | 107 | 2970 | NULL | 240 | 0 | +---------------------+-----------+---------------+---------------------------------------+---------------------+-------------+--------+----------------+----------------------+------------------+ 5 rows in set (0.00 sec) mysql> explain SELECT now() date_created, -> NULL date_undo, -> spf.created_by_id created_by_id, -> spf.date_created staff_preferred_facility_date_created, -> spf.date_modified date_modified, -> spf.facility_id facility_id, -> spf.id spf_id, -> spf.modified_by_id modified_by_id, -> spf.staff_preferences_id staff_preferences_id, -> spf.default_facility default_facility -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> AND spf.id NOT IN -> (SELECT spf.id spf_id -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> GROUP BY spf.facility_id, sp.id) -> ORDER BY spf_id; +----+--------------------+-------+--------+---------------------------------------------+-------------------------------------+---------+------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+--------+---------------------------------------------+-------------------------------------+---------+------------+------+-----------------------------------------------------------+ | 1 | PRIMARY | f | ref | PRIMARY,name | name | 52 | const | 1 | Using where; Using index; Using temporary; Using filesort | | 1 | PRIMARY | fu | ref | PRIMARY,account_id | account_id | 4 | test.f.id | 6 | Using index | | 1 | PRIMARY | sp | eq_ref | PRIMARY,uniq_idx_staff_preferences_staff_id | uniq_idx_staff_preferences_staff_id | 4 | test.fu.id | 1 | Using index | | 1 | PRIMARY | spf | ref | staff_preferences_id | staff_preferences_id | 5 | test.sp.id | 1 | Using where | | 2 | DEPENDENT SUBQUERY | f | ref | PRIMARY,name | name | 52 | const | 1 | Using where; Using index; Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | fu | ref | PRIMARY,account_id | account_id | 4 | test.f.id | 6 | Using index | | 2 | DEPENDENT SUBQUERY | sp | eq_ref | PRIMARY,uniq_idx_staff_preferences_staff_id | uniq_idx_staff_preferences_staff_id | 4 | test.fu.id | 1 | Using index | | 2 | DEPENDENT SUBQUERY | spf | ref | staff_preferences_id | staff_preferences_id | 5 | test.sp.id | 1 | Using where | +----+--------------------+-------+--------+---------------------------------------------+-------------------------------------+---------+------------+------+-----------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> system cat docs/INFO_SRC commit: f65b4435376c0f1f1f11481aefe05dbc4218d02d date: 2018-08-20 13:32:37 +0200 build-date: 2018-08-28 23:12:58 +0200 short: f65b443 branch: mysql-5.5.62-release MySQL source 5.5.62 - 5.6.45(lowest checked 5.6.10 - same behavior as current GA), 5.7.27 abd 8.0.17 - 5.6.45 bin/mysql -uroot -S /tmp/mysql_ushastry.sock test < ../mysql-5.7.27/bug_dump_mysql.sql [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.45: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.45 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT now() date_created, -> NULL date_undo, -> spf.created_by_id created_by_id, -> spf.date_created staff_preferred_facility_date_created, -> spf.date_modified date_modified, -> spf.facility_id facility_id, -> spf.id spf_id, -> spf.modified_by_id modified_by_id, -> spf.staff_preferences_id staff_preferences_id, -> spf.default_facility default_facility -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> AND spf.id NOT IN -> (SELECT spf.id spf_id -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> GROUP BY spf.facility_id, sp.id) -> ORDER BY spf_id; Empty set (0.00 sec) mysql> explain SELECT now() date_created, -> NULL date_undo, -> spf.created_by_id created_by_id, -> spf.date_created staff_preferred_facility_date_created, -> spf.date_modified date_modified, -> spf.facility_id facility_id, -> spf.id spf_id, -> spf.modified_by_id modified_by_id, -> spf.staff_preferences_id staff_preferences_id, -> spf.default_facility default_facility -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> AND spf.id NOT IN -> (SELECT spf.id spf_id -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> GROUP BY spf.facility_id, sp.id) -> ORDER BY spf_id; +----+--------------------+-------+--------+---------------------------------------------+-------------------------------------+---------+--------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+--------+---------------------------------------------+-------------------------------------+---------+--------------------+------+----------------------------------------------+ | 1 | PRIMARY | fu | index | PRIMARY,account_id | account_id | 4 | NULL | 1 | Using index; Using temporary; Using filesort | | 1 | PRIMARY | f | eq_ref | PRIMARY,name | PRIMARY | 4 | test.fu.account_id | 1 | Using where | | 1 | PRIMARY | sp | eq_ref | PRIMARY,uniq_idx_staff_preferences_staff_id | uniq_idx_staff_preferences_staff_id | 4 | test.fu.id | 1 | Using index | | 1 | PRIMARY | spf | ref | staff_preferences_id | staff_preferences_id | 5 | test.sp.id | 1 | Using where | | 2 | DEPENDENT SUBQUERY | fu | index | PRIMARY,account_id | account_id | 4 | NULL | 1 | Using index | | 2 | DEPENDENT SUBQUERY | f | eq_ref | PRIMARY,name | PRIMARY | 4 | test.fu.account_id | 1 | Using where | | 2 | DEPENDENT SUBQUERY | sp | eq_ref | PRIMARY,uniq_idx_staff_preferences_staff_id | uniq_idx_staff_preferences_staff_id | 4 | test.fu.id | 1 | Using index | | 2 | DEPENDENT SUBQUERY | spf | eq_ref | PRIMARY,staff_preferences_id | PRIMARY | 4 | func | 1 | Using where | +----+--------------------+-------+--------+---------------------------------------------+-------------------------------------+---------+--------------------+------+----------------------------------------------+ 8 rows in set (0.00 sec) - 5.7.27 bin/mysql -uroot -S /tmp/mysql_ushastry.sock test < ../mysql-5.7.27/bug_dump_mysql.sql [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.27: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.27 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT now() date_created, -> NULL date_undo, -> spf.created_by_id created_by_id, -> spf.date_created staff_preferred_facility_date_created, -> spf.date_modified date_modified, -> spf.facility_id facility_id, -> spf.id spf_id, -> spf.modified_by_id modified_by_id, -> spf.staff_preferences_id staff_preferences_id, -> spf.default_facility default_facility -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> AND spf.id NOT IN -> (SELECT spf.id spf_id -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> GROUP BY spf.facility_id, sp.id) -> ORDER BY spf_id; Empty set (0.01 sec) mysql> explain SELECT now() date_created, -> NULL date_undo, -> spf.created_by_id created_by_id, -> spf.date_created staff_preferred_facility_date_created, -> spf.date_modified date_modified, -> spf.facility_id facility_id, -> spf.id spf_id, -> spf.modified_by_id modified_by_id, -> spf.staff_preferences_id staff_preferences_id, -> spf.default_facility default_facility -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> AND spf.id NOT IN -> (SELECT spf.id spf_id -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> GROUP BY spf.facility_id, sp.id) -> ORDER BY spf_id; +----+--------------------+-------+------------+--------+---------------------------------------------+-------------------------------------+---------+--------------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+---------------------------------------------+-------------------------------------+---------+--------------------+------+----------+----------------------------------------------+ | 1 | PRIMARY | fu | NULL | index | PRIMARY,account_id | account_id | 4 | NULL | 1 | 100.00 | Using index; Using temporary; Using filesort | | 1 | PRIMARY | f | NULL | eq_ref | PRIMARY,name | PRIMARY | 4 | test.fu.account_id | 1 | 100.00 | Using where | | 1 | PRIMARY | sp | NULL | eq_ref | PRIMARY,uniq_idx_staff_preferences_staff_id | uniq_idx_staff_preferences_staff_id | 4 | test.fu.id | 1 | 100.00 | Using index | | 1 | PRIMARY | spf | NULL | ref | staff_preferences_id | staff_preferences_id | 5 | test.sp.id | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | fu | NULL | index | PRIMARY,account_id | account_id | 4 | NULL | 1 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | f | NULL | eq_ref | PRIMARY,name | PRIMARY | 4 | test.fu.account_id | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | sp | NULL | eq_ref | PRIMARY,uniq_idx_staff_preferences_staff_id | uniq_idx_staff_preferences_staff_id | 4 | test.fu.id | 1 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | spf | NULL | eq_ref | PRIMARY,staff_preferences_id | PRIMARY | 4 | func | 1 | 5.62 | Using where | +----+--------------------+-------+------------+--------+---------------------------------------------+-------------------------------------+---------+--------------------+------+----------+----------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select now() AS `date_created`,NULL AS `date_undo`,`test`.`spf`.`created_by_id` AS `created_by_id`,`test`.`spf`.`date_created` AS `staff_preferred_facility_date_created`,`test`.`spf`.`date_modified` AS `date_modified`,`test`.`spf`.`facility_id` AS `facility_id`,`test`.`spf`.`id` AS `spf_id`,`test`.`spf`.`modified_by_id` AS `modified_by_id`,`test`.`spf`.`staff_preferences_id` AS `staff_preferences_id`,`test`.`spf`.`default_facility` AS `default_facility` from `test`.`staff_preferred_facility_test` `spf` join `test`.`staff_preferences_test` `sp` join `test`.`account_user_test` `fu` join `test`.`account_test` `f` where ((`test`.`spf`.`staff_preferences_id` = `test`.`sp`.`id`) and (`test`.`sp`.`staff_id` = `test`.`fu`.`id`) and (`test`.`f`.`id` = `test`.`fu`.`account_id`) and (`test`.`f`.`account_name` = 'Test_Account') and (not((`test`.`spf`.`id`,(/* select#2 */ select 1 from `test`.`staff_preferred_facility_test` `spf` join `test`.`staff_preferences_test` `sp` join `test`.`account_user_test` `fu` join `test`.`account_test` `f` where ((`test`.`spf`.`staff_preferences_id` = `test`.`sp`.`id`) and (`test`.`sp`.`staff_id` = `test`.`fu`.`id`) and (`test`.`f`.`id` = `test`.`fu`.`account_id`) and (`test`.`f`.`account_name` = 'Test_Account') and ((`test`.`spf`.`id`) = `test`.`spf`.`id`))))))) order by `spf_id` | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) - 8.0.17 bin/mysql -uroot -S /tmp/mysql_ushastry.sock test < ../mysql-5.7.27/bug_dump_mysql.sql [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.17: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.17 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT now() date_created, -> NULL date_undo, -> spf.created_by_id created_by_id, -> spf.date_created staff_preferred_facility_date_created, -> spf.date_modified date_modified, -> spf.facility_id facility_id, -> spf.id spf_id, -> spf.modified_by_id modified_by_id, -> spf.staff_preferences_id staff_preferences_id, -> spf.default_facility default_facility -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> AND spf.id NOT IN -> (SELECT spf.id spf_id -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> GROUP BY spf.facility_id, sp.id) -> ORDER BY spf_id; Empty set (0.01 sec) mysql> explain SELECT now() date_created, -> NULL date_undo, -> spf.created_by_id created_by_id, -> spf.date_created staff_preferred_facility_date_created, -> spf.date_modified date_modified, -> spf.facility_id facility_id, -> spf.id spf_id, -> spf.modified_by_id modified_by_id, -> spf.staff_preferences_id staff_preferences_id, -> spf.default_facility default_facility -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> AND spf.id NOT IN -> (SELECT spf.id spf_id -> FROM test.staff_preferred_facility_test spf -> JOIN test.staff_preferences_test sp ON spf.staff_preferences_id = sp.id -> JOIN test.account_user_test fu ON fu.id = sp.staff_id -> JOIN test.account_test f ON fu.account_id = f.id -> WHERE f.account_name='Test_Account' -> GROUP BY spf.facility_id, sp.id) -> ORDER BY spf_id; +----+-------------+-------+------------+--------+---------------------------------------------+-------------------------------------+---------+--------------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------------------+-------------------------------------+---------+--------------------+------+----------+----------------------------------------------+ | 1 | SIMPLE | fu | NULL | index | PRIMARY,account_id | account_id | 4 | NULL | 1 | 100.00 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | f | NULL | eq_ref | PRIMARY,name | PRIMARY | 4 | test.fu.account_id | 1 | 100.00 | Using where | | 1 | SIMPLE | sp | NULL | eq_ref | PRIMARY,uniq_idx_staff_preferences_staff_id | uniq_idx_staff_preferences_staff_id | 4 | test.fu.id | 1 | 100.00 | Using index | | 1 | SIMPLE | spf | NULL | ref | staff_preferences_id | staff_preferences_id | 5 | test.sp.id | 1 | 100.00 | NULL | | 1 | SIMPLE | fu | NULL | index | PRIMARY,account_id | account_id | 4 | NULL | 1 | 100.00 | Using where; Not exists; Using index | | 1 | SIMPLE | f | NULL | eq_ref | PRIMARY,name | PRIMARY | 4 | test.fu.account_id | 1 | 100.00 | Using where | | 1 | SIMPLE | sp | NULL | eq_ref | PRIMARY,uniq_idx_staff_preferences_staff_id | uniq_idx_staff_preferences_staff_id | 4 | test.fu.id | 1 | 100.00 | Using index | | 1 | SIMPLE | spf | NULL | eq_ref | PRIMARY,staff_preferences_id | PRIMARY | 4 | test.spf.id | 1 | 100.00 | Using where | +----+-------------+-------+------------+--------+---------------------------------------------+-------------------------------------+---------+--------------------+------+----------+----------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select now() AS `date_created`,NULL AS `date_undo`,`test`.`spf`.`created_by_id` AS `created_by_id`,`test`.`spf`.`date_created` AS `staff_preferred_facility_date_created`,`test`.`spf`.`date_modified` AS `date_modified`,`test`.`spf`.`facility_id` AS `facility_id`,`test`.`spf`.`id` AS `spf_id`,`test`.`spf`.`modified_by_id` AS `modified_by_id`,`test`.`spf`.`staff_preferences_id` AS `staff_preferences_id`,`test`.`spf`.`default_facility` AS `default_facility` from `test`.`staff_preferred_facility_test` `spf` join `test`.`staff_preferences_test` `sp` join `test`.`account_user_test` `fu` join `test`.`account_test` `f` anti join (`test`.`staff_preferred_facility_test` `spf` join `test`.`staff_preferences_test` `sp` join `test`.`account_user_test` `fu` join `test`.`account_test` `f`) on(((`test`.`spf`.`staff_preferences_id` = `test`.`sp`.`id`) and (`test`.`sp`.`staff_id` = `test`.`fu`.`id`) and (`test`.`f`.`id` = `test`.`fu`.`account_id`) and (`test`.`spf`.`id` = `test`.`spf`.`id`) and (`test`.`f`.`account_name` = 'Test_Account'))) where ((`test`.`spf`.`staff_preferences_id` = `test`.`sp`.`id`) and (`test`.`sp`.`staff_id` = `test`.`fu`.`id`) and (`test`.`f`.`id` = `test`.`fu`.`account_id`) and (`test`.`f`.`account_name` = 'Test_Account')) order by `spf_id` 1 row in set (0.00 sec)