Bug #73537 Wrong result in query between 5.5.x and 5.6.x

• Submitted: 11 Aug 2014 17:39
• Modified: 11 Aug 2014 19:38
• Reporter: Kamil Michalak
• Status: Can't repeat
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.6.19
• OS: Linux (debian)
• CPU Architecture: Any
• Tags: query result

[11 Aug 2014 17:39] Kamil Michalak

Description:
Different result in select between 5.5.x and 5.6.19

How to repeat:
CREATE TABLE IF NOT EXISTS `table1` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `_Id` int(11) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `table1` (`Id`, `_Id`) VALUES
(1, 3),(2, 3),(3, 3),(4, 4),(5, 4),(6, 5),(7, 5),(8, 6),(9, 6);

CREATE TABLE IF NOT EXISTS `table2` (
  `_Id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`_Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `table2` (`_Id`) VALUES
(1),(2),(3),(4);

create or replace view view_test as
Select t1.Id, t2._Id, 1 as AlwaysOne
From ( table1 as t1, table2 as t2 )
Where t1._Id = t2._Id;

#################################
Query 1 (ok)
SELECT * FROM view_test Order by Id

field AlwaysOne has value 1
#################################
Query 2 (wrong! value of AlwaysOne)

SELECT * FROM view_test Order by _Id

field AlwaysOne has value 0 why?

Suggested fix:
Both query should give the same value of field AlwaysOn

[11 Aug 2014 17:43] Kamil Michalak

query 1 result:
mysql> SELECT * FROM view_test Order by Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         1 |
|  2 |   3 |         1 |
|  3 |   3 |         1 |
|  4 |   4 |         1 |
|  5 |   4 |         1 |
+----+-----+-----------+
5 rows in set (0.00 sec)

query 2 result:
mysql> SELECT * FROM view_test Order by _Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         0 |
|  2 |   3 |         0 |
|  3 |   3 |         0 |
|  4 |   4 |         0 |
|  5 |   4 |         0 |
+----+-----+-----------+
5 rows in set (0.00 sec)

[11 Aug 2014 18:19] MySQL Verification Team

C:\dbs>net start mysqld55
The MySQLD55 service is starting..
The MySQLD55 service was started successfully.

C:\dbs>55

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.40 Source distribution

Copyright (c) 2000, 2014, 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.5 > USE test
Database changed
mysql 5.5 > CREATE TABLE IF NOT EXISTS `table1` (
    ->   `Id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `_Id` int(11) NOT NULL,
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.09 sec)

mysql 5.5 > INSERT INTO `table1` (`Id`, `_Id`) VALUES
    -> (1, 3),(2, 3),(3, 3),(4, 4),(5, 4),(6, 5),(7, 5),(8, 6),(9, 6);
Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 > CREATE TABLE IF NOT EXISTS `table2` (
    ->   `_Id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`_Id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.16 sec)

mysql 5.5 > INSERT INTO `table2` (`_Id`) VALUES
    -> (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql 5.5 > create or replace view view_test as
    -> Select t1.Id, t2._Id, 1 as AlwaysOne
    -> From ( table1 as t1, table2 as t2 )
    -> Where t1._Id = t2._Id;
Query OK, 0 rows affected (0.08 sec)

mysql 5.5 > SELECT * FROM view_test Order by Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         1 |
|  2 |   3 |         1 |
|  3 |   3 |         1 |
|  4 |   4 |         1 |
|  5 |   4 |         1 |
+----+-----+-----------+
5 rows in set (0.01 sec)

mysql 5.5 > SELECT * FROM view_test Order by _Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         1 |
|  2 |   3 |         1 |
|  3 |   3 |         1 |
|  5 |   4 |         1 |
|  4 |   4 |         1 |
+----+-----+-----------+
5 rows in set (0.01 sec)

mysql 5.5 > exit
Bye

C:\dbs>net start mysqld56
The requested service has already been started.

More help is available by typing NET HELPMSG 2182.

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21 Source distribution

Copyright (c) 2000, 2014, 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 test
Database changed
mysql 5.6 > CREATE TABLE IF NOT EXISTS `table1` (
    ->   `Id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `_Id` int(11) NOT NULL,
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.27 sec)

mysql 5.6 > INSERT INTO `table1` (`Id`, `_Id`) VALUES
    -> (1, 3),(2, 3),(3, 3),(4, 4),(5, 4),(6, 5),(7, 5),(8, 6),(9, 6);
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql 5.6 > CREATE TABLE IF NOT EXISTS `table2` (
    ->   `_Id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`_Id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.28 sec)

mysql 5.6 > INSERT INTO `table2` (`_Id`) VALUES
    -> (1),(2),(3),(4);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql 5.6 >
mysql 5.6 > create or replace view view_test as
    -> Select t1.Id, t2._Id, 1 as AlwaysOne
    -> From ( table1 as t1, table2 as t2 )
    -> Where t1._Id = t2._Id;
Query OK, 0 rows affected (0.13 sec)

mysql 5.6 > SELECT * FROM view_test Order by Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         1 |
|  2 |   3 |         1 |
|  3 |   3 |         1 |
|  4 |   4 |         1 |
|  5 |   4 |         1 |
+----+-----+-----------+
5 rows in set (0.01 sec)

mysql 5.6 > SELECT * FROM view_test Order by _Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         1 |
|  2 |   3 |         1 |
|  3 |   3 |         1 |
|  4 |   4 |         1 |
|  5 |   4 |         1 |
+----+-----+-----------+
5 rows in set (0.00 sec)

mysql 5.6 >

[11 Aug 2014 18:22] MySQL Verification Team

Please try version 5.6.20. Thanks.

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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
Database changed
mysql> CREATE TABLE IF NOT EXISTS `table1` (
    ->   `Id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `_Id` int(11) NOT NULL,
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.23 sec)

mysql>
mysql> INSERT INTO `table1` (`Id`, `_Id`) VALUES
    -> (1, 3),(2, 3),(3, 3),(4, 4),(5, 4),(6, 5),(7, 5),(8, 6),(9, 6);
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE IF NOT EXISTS `table2` (
    ->   `_Id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`_Id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.28 sec)

mysql>
mysql> INSERT INTO `table2` (`_Id`) VALUES
    -> (1),(2),(3),(4);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> create or replace view view_test as
    -> Select t1.Id, t2._Id, 1 as AlwaysOne
    -> From ( table1 as t1, table2 as t2 )
    -> Where t1._Id = t2._Id;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM view_test Order by Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         1 |
|  2 |   3 |         1 |
|  3 |   3 |         1 |
|  4 |   4 |         1 |
|  5 |   4 |         1 |
+----+-----+-----------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM view_test Order by _Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         1 |
|  2 |   3 |         1 |
|  3 |   3 |         1 |
|  4 |   4 |         1 |
|  5 |   4 |         1 |
+----+-----+-----------+
5 rows in set (0.02 sec)

mysql>

[11 Aug 2014 19:17] Kamil Michalak

I need some time. Percona currently uses version percona-server-server-5.6_5.6.19-67.0-618.wheezy_amd64.deb

[11 Aug 2014 19:23] MySQL Verification Team

Repeatable on MacBook Pro with 5.6.19:

Last login: Mon Aug 11 10:46:44 on console
[16:19:37][pochita:]~ miguel$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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> CREATE TABLE IF NOT EXISTS `table1` (
    ->   `Id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `_Id` int(11) NOT NULL,
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> INSERT INTO `table1` (`Id`, `_Id`) VALUES
    -> (1, 3),(2, 3),(3, 3),(4, 4),(5, 4),(6, 5),(7, 5),(8, 6),(9, 6);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE IF NOT EXISTS `table2` (
    ->   `_Id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`_Id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.30 sec)

mysql>
mysql> INSERT INTO `table2` (`_Id`) VALUES
    -> (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> create or replace view view_test as
    -> Select t1.Id, t2._Id, 1 as AlwaysOne
    -> From ( table1 as t1, table2 as t2 )
    -> Where t1._Id = t2._Id;
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT * FROM view_test Order by Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         1 |
|  2 |   3 |         1 |
|  3 |   3 |         1 |
|  4 |   4 |         1 |
|  5 |   4 |         1 |
+----+-----+-----------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM view_test Order by _Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         0 |
|  2 |   3 |         0 |
|  3 |   3 |         0 |
|  4 |   4 |         0 |
|  5 |   4 |         0 |
+----+-----+-----------+
5 rows in set (0.00 sec)

[11 Aug 2014 19:38] MySQL Verification Team

Thank you for the feedback. Closing as can't repeat since not more repeatable
with current released version (checked on MAC OSX 5.6.19 and 5.6.20).

[16:25:56][pochita:]~ miguel$ mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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> CREATE TABLE IF NOT EXISTS `table1` (
    ->   `Id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `_Id` int(11) NOT NULL,
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> INSERT INTO `table1` (`Id`, `_Id`) VALUES
    -> (1, 3),(2, 3),(3, 3),(4, 4),(5, 4),(6, 5),(7, 5),(8, 6),(9, 6);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE IF NOT EXISTS `table2` (
    ->   `_Id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`_Id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.47 sec)

mysql>
mysql> INSERT INTO `table2` (`_Id`) VALUES
    -> (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> create or replace view view_test as
    -> Select t1.Id, t2._Id, 1 as AlwaysOne
    -> From ( table1 as t1, table2 as t2 )
    -> Where t1._Id = t2._Id;
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT * FROM view_test Order by Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         1 |
|  2 |   3 |         1 |
|  3 |   3 |         1 |
|  4 |   4 |         1 |
|  5 |   4 |         1 |
+----+-----+-----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM view_test Order by _Id;
+----+-----+-----------+
| Id | _Id | AlwaysOne |
+----+-----+-----------+
|  1 |   3 |         1 |
|  2 |   3 |         1 |
|  3 |   3 |         1 |
|  4 |   4 |         1 |
|  5 |   4 |         1 |
+----+-----+-----------+
5 rows in set (0.00 sec)