MySQL Bugs: #81816: Invalid select query result on FIRST time

Bug #81816	Invalid select query result on FIRST time
Submitted:	11 Jun 2016 19:53	Modified:	19 Dec 2019 22:47
Reporter:	Kamil Michalak	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.6.31	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Wrong result in select query on FIRST TIME on debian machine with  mysql -V
mysql  Ver 14.14 Distrib 5.6.31, for Linux (x86_64) using  EditLine wrapper
Older mysql from branch 5.5 work fine.
Windows version have the same problem.

 mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 version();
+-----------+
| version() |
+-----------+
| 5.6.31    |
+-----------+
1 row in set (0.00 sec)

mysql> use fs806;
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

QUERY#1
mysql> Select * From (bug_fs806_01, LigaKategorieTbl) Where LigaKategorieTbl.KategorieId=HCategoryId And MeczId = '1519726';
Empty set (0.00 sec)

These result is wrong.

QUERY#2
mysql> explain Select * From (bug_fs806_01, LigaKategorieTbl) Where LigaKategorieTbl.KategorieId=HCategoryId And MeczId = '1519726';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

The second query (the same but these one use join) the result is ok.
Run these query#1 one time more and now result is OK! 

QUERY#1
mysql> Select * From bug_fs806_01 left join LigaKategorieTbl on (LigaKategorieTbl.KategorieId=HCategoryId) Where  MeczId = '1519726';
+---------+-------------+-------------+----------------+
| MeczId  | HCategoryId | KategorieId | KategorieNazwa |
+---------+-------------+-------------+----------------+
| 1519726 |         228 |         228 | United States  |
+---------+-------------+-------------+----------------+
1 row in set (0.00 sec)

mysql> explain Select * From bug_fs806_01 left join LigaKategorieTbl on (LigaKategorieTbl.KategorieId=HCategoryId) Where  MeczId = '1519726';
+----+-------------+------------------+-------+------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------+------+-------------+
| id | select_type | table            | type  | possible_keys                                                                                                                | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------------+-------+------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | m                | const | PRIMARY,_LigaId,_LigaId_x_DataMeczu,fk_MeczTbl_ZespolTbl_LigaTbl__LigaId_HostID,fk_MeczTbl_ZespolTbl_LigaTbl__LigaId_GuestID | PRIMARY | 4       | const |    1 | NULL        |
|  1 | SIMPLE      | LigaTbl          | const | PRIMARY                                                                                                                      | PRIMARY | 4       | const |    1 | Using index |
|  1 | SIMPLE      | z1               | const | PRIMARY                                                                                                                      | PRIMARY | 4       | const |    1 | NULL        |
|  1 | SIMPLE      | LigaKategorieTbl | const | PRIMARY                                                                                                                      | PRIMARY | 4       | const |    1 | NULL        |
+----+-------------+------------------+-------+------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------+------+-------------+
4 rows in set (0.00 sec)

mysql> Select * From (bug_fs806_01, LigaKategorieTbl) Where LigaKategorieTbl.KategorieId=HCategoryId And MeczId = '1519726';
+---------+-------------+-------------+----------------+
| MeczId  | HCategoryId | KategorieId | KategorieNazwa |
+---------+-------------+-------------+----------------+
| 1519726 |         228 |         228 | United States  |
+---------+-------------+-------------+----------------+
1 row in set (0.00 sec)

How to repeat:
Create sample database.

create database fs806;
use fs806;

CREATE TABLE IF NOT EXISTS `bug_fs806_01` (
`MeczId` int(10) unsigned
,`HCategoryId` int(10) unsigned
);

CREATE TABLE IF NOT EXISTS `LigaKategorieTbl` (
  `KategorieId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `KategorieNazwa` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`KategorieId`),
  UNIQUE KEY `KategorieNazwa` (`KategorieNazwa`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=296 ;

INSERT INTO `LigaKategorieTbl` (`KategorieId`, `KategorieNazwa`) VALUES
(228, 'United States');

CREATE TABLE IF NOT EXISTS `LigaTbl` (
  `LigaId` int(10) unsigned NOT NULL COMMENT '{"alias":"LeagueId","AutoInc":true}',
  `LigaNazwa` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`LigaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `LigaTbl` (`LigaId`, `LigaNazwa`) VALUES
(43590, 'Conference');

CREATE TABLE IF NOT EXISTS `MeczTbl` (
  `MeczId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '{"alias":"MatchId"}',
  `_LigaId` int(10) unsigned NOT NULL DEFAULT '0',
  `DataMeczu` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `HostID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`MeczId`),
  KEY `_LigaId` (`_LigaId`),
  KEY `HostID` (`HostID`),
  KEY `DataMeczu` (`DataMeczu`),
  KEY `_LigaId_x_DataMeczu` (`_LigaId`,`DataMeczu`),
  KEY `fk_MeczTbl_ZespolTbl_LigaTbl__LigaId_HostID` (`_LigaId`,`HostID`),
  KEY `fk_MeczTbl_ZespolTbl_LigaTbl__LigaId_GuestID` (`_LigaId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1519727 ;

INSERT INTO `MeczTbl` (`MeczId`, `_LigaId`, `DataMeczu`, `HostID`) VALUES
(1519726, 43590, '2016-05-25 00:00:00', 18336);

CREATE TABLE IF NOT EXISTS `ZespolTbl` (
  `ZespolId` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '{"alias":"TeamId"}',
  `_KategorieId` int(10) unsigned NOT NULL,
  `ZespolNazwa` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ZespolId`),
  KEY `ZespolNazwa` (`ZespolNazwa`),
  KEY `_KategorieId` (`_KategorieId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=19386 ;

INSERT INTO `ZespolTbl` (`ZespolId`, `_KategorieId`, `ZespolNazwa`) VALUES
(18336, 228, 'Saint Louis U23');

DROP TABLE IF EXISTS `bug_fs806_01`;

create or replace view bug_fs806_01 as
	Select
		m.MeczId,
		z1._KategorieId as HCategoryId
	From
		(
			MeczTbl as m, LigaTbl
		)
	Left Join ZespolTbl as z1 on (m.HostID=z1.ZespolId)
	Where
		m._LigaId=LigaTbl.LigaId
;

Suggested fix:
Each query should return the same result

mysql> Select SQL_NO_CACHE * From (bug_fs806_01, LigaKategorieTbl) Where LigaKategorieTbl.KategorieId=HCategoryId And MeczId = '1519726';
Empty set (0.00 sec)

mysql> Select SQL_NO_CACHE * From (bug_fs806_01, LigaKategorieTbl) Where LigaKategorieTbl.KategorieId=HCategoryId And MeczId = '1519726';
Empty set (0.00 sec)

Fix query:
mysql> Select * From bug_fs806_01 left join LigaKategorieTbl on 
(LigaKategorieTbl.KategorieId=HCategoryId) Where  MeczId = '1519726';

And now requery with first query
mysql> Select SQL_NO_CACHE * From (bug_fs806_01, LigaKategorieTbl) Where LigaKategorieTbl.KategorieId=HCategoryId And MeczId = '1519726';
+---------+-------------+-------------+----------------+
| MeczId  | HCategoryId | KategorieId | KategorieNazwa |
+---------+-------------+-------------+----------------+
| 1519726 |         228 |         228 | United States  |
+---------+-------------+-------------+----------------+
1 row in set (0.00 sec)

And now when i restart mysql the result is wrong again until run "fix query"

reproducible for me like this

-- populate tablesand view as in test case posted

-- server restarted

-- q1
SELECT * FROM (bug_fs806_01, LigaKategorieTbl) WHERE LigaKategorieTbl.KategorieId=HCategoryId AND MeczId = '1519726';
-- empty set

-- q1
SELECT * FROM (bug_fs806_01, LigaKategorieTbl) WHERE LigaKategorieTbl.KategorieId=HCategoryId AND MeczId = '1519726';
-- still empty set

-- q2
SELECT * FROM bug_fs806_01 LEFT JOIN LigaKategorieTbl ON (LigaKategorieTbl.KategorieId=HCategoryId) WHERE  MeczId = '1519726';
- expected result

-- q1
SELECT * FROM (bug_fs806_01, LigaKategorieTbl) WHERE LigaKategorieTbl.KategorieId=HCategoryId AND MeczId = '1519726';
-- now result same as previous query with JOIN

-- Peter
-- not a MySQL Oracle person

@Hartmut.  

This report is not about a crash at a all. I don't see how the two are related. It is about inconsistent/non-deterministci SELECT query results. 

Was it the right one you linked to? If so please explain!

OK .. I also thought so!

Thank you for the bug report. Only 5.6 affected.

mysql 5.6 > create or replace view bug_fs806_01 as
    ->  Select
    ->          m.MeczId,
    ->          z1._KategorieId as HCategoryId
    ->  From
    ->          (
    ->                  MeczTbl as m, LigaTbl
    ->          )
    ->  Left Join ZespolTbl as z1 on (m.HostID=z1.ZespolId)
    ->  Where
    ->          m._LigaId=LigaTbl.LigaId
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql 5.6 > SELECT * FROM (bug_fs806_01, LigaKategorieTbl) WHERE LigaKategorieTbl.KategorieId=HCategoryId AND MeczId = '1519726';
+---------+-------------+-------------+----------------+
| MeczId  | HCategoryId | KategorieId | KategorieNazwa |
+---------+-------------+-------------+----------------+
| 1519726 |         228 |         228 | United States  |
+---------+-------------+-------------+----------------+
1 row in set (0.00 sec)

mysql 5.6 > exit
Bye

C:\dbs>net stop mysqld56
The MySQLD56 service is stopping.
The MySQLD56 service was stopped successfully.

C:\dbs>net start mysqld56
The MySQLD56 service is starting.
The MySQLD56 service was started successfully.

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.32 Source distribution PULL: 2016-MAY-09

Copyright (c) 2000, 2016, 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 5.6 > use fs806;
Database changed
mysql 5.6 > SELECT * FROM (bug_fs806_01, LigaKategorieTbl) WHERE LigaKategorieTbl.KategorieId=HCategoryId AND MeczId = '1519726';
Empty set (0.02 sec)

Posted by developer:
 
Fixed in 5.7.29