Bug #75471 Views that use hex literals fails
Submitted: 9 Jan 2015 17:29 Modified: 10 Jan 2015 22:59
Reporter: Chris Herridge Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.1/5.5/5.6/5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: hex, literal, VIEW

[9 Jan 2015 17:29] Chris Herridge
Description:
A view that uses a hex literal does not work at all.

How to repeat:
First generate a simple testing database:

CREATE TABLE `items` (
  `iditems` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `Title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`iditems`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `props` (
  `idprops` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `proptypes_idproptypes` binary(16) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`idprops`),
  KEY `key_proptypes` (`proptypes_idproptypes`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `proptypes` (
  `idproptypes` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `Name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`idproptypes`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `items_has_props` (
  `items_iditems` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `props_idprops` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  PRIMARY KEY (`items_iditems`,`props_idprops`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `items` VALUES
(0xE52A6998981E11E48B8250465DAC27FE,'My Small Red Product'),
(0xE52A6CA8981E11E48B8250465DAC27FE,'My Large Red Product'),
(0xE52A6D50981E11E48B8250465DAC27FE,'My Medium Green Product'),
(0xE52A6DBF981E11E48B8250465DAC27FE,'My Large Green Product'),
(0xE52A6E28981E11E48B8250465DAC27FE,'My Small Blue Product');

INSERT INTO `proptypes` VALUES
(0x60E6AC38981E11E48B8250465DAC27FE,'Colour'),
(0x63C9FB50981E11E48B8250465DAC27FE,'Size');

INSERT INTO `props` VALUES
(0x91B35210981E11E48B8250465DAC27FE,0x60E6AC38981E11E48B8250465DAC27FE,'Red'),(0x91B354CF981E11E48B8250465DAC27FE,0x60E6AC38981E11E48B8250465DAC27FE,'Green'),(0x91B35595981E11E48B8250465DAC27FE,0x60E6AC38981E11E48B8250465DAC27FE,'Blue'),(0xA63F4711981E11E48B8250465DAC27FE,0x63C9FB50981E11E48B8250465DAC27FE,'Small'),(0xA63F48B2981E11E48B8250465DAC27FE,0x63C9FB50981E11E48B8250465DAC27FE,'Medium'),(0xA63F499A981E11E48B8250465DAC27FE,0x63C9FB50981E11E48B8250465DAC27FE,'Large');

INSERT INTO `items_has_props` VALUES
(0xE52A6998981E11E48B8250465DAC27FE,0x91B35210981E11E48B8250465DAC27FE),
(0xE52A6998981E11E48B8250465DAC27FE,0xA63F4711981E11E48B8250465DAC27FE),
(0xE52A6CA8981E11E48B8250465DAC27FE,0x91B35210981E11E48B8250465DAC27FE),
(0xE52A6CA8981E11E48B8250465DAC27FE,0xA63F499A981E11E48B8250465DAC27FE),
(0xE52A6D50981E11E48B8250465DAC27FE,0x91B354CF981E11E48B8250465DAC27FE),
(0xE52A6D50981E11E48B8250465DAC27FE,0xA63F48B2981E11E48B8250465DAC27FE),
(0xE52A6DBF981E11E48B8250465DAC27FE,0x91B354CF981E11E48B8250465DAC27FE),
(0xE52A6DBF981E11E48B8250465DAC27FE,0xA63F499A981E11E48B8250465DAC27FE),
(0xE52A6E28981E11E48B8250465DAC27FE,0x91B35595981E11E48B8250465DAC27FE),
(0xE52A6E28981E11E48B8250465DAC27FE,0xA63F4711981E11E48B8250465DAC27FE);

Then, attempt this simple statement:

SELECT Title,sizes.Name Size,colours.Name Colour FROM test.items
JOIN (items_has_props linkSizes, props sizes,proptypes sizePropType)
ON
(
linkSizes.items_iditems = iditems
AND
linkSizes.props_idprops = sizes.idprops
AND
sizes.proptypes_idproptypes = sizePropType.idproptypes
AND
sizePropType.Name = "Size"
)
JOIN (items_has_props linkColours, props colours,proptypes colourPropType)
ON
(
linkColours.items_iditems = iditems
AND
linkColours.props_idprops = colours.idprops
AND
colours.proptypes_idproptypes = colourPropType.idproptypes
AND
colourPropType.Name = "Colour"
)

The result is:
My Small Red Product	Small	Red
My Large Red Product	Large	Red
My Medium Green Product	Medium	Green
My Large Green Product	Large	Green
My Small Blue Product	Small	Blue

This is correct.

Create that statement as a view, and query it

create view myView1
AS
SELECT Title,sizes.Name Size,colours.Name Colour FROM test.items
JOIN (items_has_props linkSizes, props sizes,proptypes sizePropType)
ON
(
linkSizes.items_iditems = iditems
AND
linkSizes.props_idprops = sizes.idprops
AND
sizes.proptypes_idproptypes = sizePropType.idproptypes
AND
sizePropType.Name = "Size"
)
JOIN (items_has_props linkColours, props colours,proptypes colourPropType)
ON
(
linkColours.items_iditems = iditems
AND
linkColours.props_idprops = colours.idprops
AND
colours.proptypes_idproptypes = colourPropType.idproptypes
AND
colourPropType.Name = "Colour"
);
select * from myView1;

The result is:
My Small Red Product	Small	Red
My Large Red Product	Large	Red
My Medium Green Product	Medium	Green
My Large Green Product	Large	Green
My Small Blue Product	Small	Blue

This is correct.

Then attempt this statement (shortcutting the proptables by using a hex reference)
SELECT Title,sizes.Name Size,colours.Name Colour FROM test.items
JOIN (items_has_props linkSizes, props sizes)
ON
(
linkSizes.items_iditems = iditems
AND
linkSizes.props_idprops = sizes.idprops
AND
sizes.proptypes_idproptypes = 0x63C9FB50981E11E48B8250465DAC27FE
)
JOIN (items_has_props linkColours, props colours)
ON
(
linkColours.items_iditems = iditems
AND
linkColours.props_idprops = colours.idprops
AND
colours.proptypes_idproptypes = 0x60E6AC38981E11E48B8250465DAC27FE
)

The result is:
My Small Red Product	Small	Red
My Large Red Product	Large	Red
My Medium Green Product	Medium	Green
My Large Green Product	Large	Green
My Small Blue Product	Small	Blue

This is correct.

Create that new statement as a view, and query it
create view myView2 AS
SELECT Title,sizes.Name Size,colours.Name Colour FROM test.items
JOIN (items_has_props linkSizes, props sizes)
ON
(
linkSizes.items_iditems = iditems
AND
linkSizes.props_idprops = sizes.idprops
AND
sizes.proptypes_idproptypes = 0x63C9FB50981E11E48B8250465DAC27FE
)
JOIN (items_has_props linkColours, props colours)
ON
(
linkColours.items_iditems = iditems
AND
linkColours.props_idprops = colours.idprops
AND
colours.proptypes_idproptypes = 0x60E6AC38981E11E48B8250465DAC27FE
);
select * from myView2;

No Results are returned. This is incorrect. Somewhere in the view-storage the Hex literals are being lost (I assume)

Suggested fix:
...No idea
[10 Jan 2015 22:47] Miguel Solorzano
mysql 5.6 > SELECT Title,sizes.Name Size,colours.Name Colour FROM test.items
    -> JOIN (items_has_props linkSizes, props sizes)
    -> ON
    -> (
    -> linkSizes.items_iditems = iditems
    -> AND
    -> linkSizes.props_idprops = sizes.idprops
    -> AND
    -> sizes.proptypes_idproptypes = 0x63C9FB50981E11E48B8250465DAC27FE
    -> )
    -> JOIN (items_has_props linkColours, props colours)
    -> ON
    -> (
    -> linkColours.items_iditems = iditems
    -> AND
    -> linkColours.props_idprops = colours.idprops
    -> AND
    -> colours.proptypes_idproptypes = 0x60E6AC38981E11E48B8250465DAC27FE
    -> )
    -> ;
+-------------------------+--------+--------+
| Title                   | Size   | Colour |
+-------------------------+--------+--------+
| My Small Red Product    | Small  | Red    |
| My Large Red Product    | Large  | Red    |
| My Medium Green Product | Medium | Green  |
| My Large Green Product  | Large  | Green  |
| My Small Blue Product   | Small  | Blue   |
+-------------------------+--------+--------+
5 rows in set (0.00 sec)

mysql 5.6 > create view myView2 AS
    -> SELECT Title,sizes.Name Size,colours.Name Colour FROM test.items
    -> JOIN (items_has_props linkSizes, props sizes)
    -> ON
    -> (
    -> linkSizes.items_iditems = iditems
    -> AND
    -> linkSizes.props_idprops = sizes.idprops
    -> AND
    -> sizes.proptypes_idproptypes = 0x63C9FB50981E11E48B8250465DAC27FE
    -> )
    -> JOIN (items_has_props linkColours, props colours)
    -> ON
    -> (
    -> linkColours.items_iditems = iditems
    -> AND
    -> linkColours.props_idprops = colours.idprops
    -> AND
    -> colours.proptypes_idproptypes = 0x60E6AC38981E11E48B8250465DAC27FE
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql 5.6 > select * from myView2;
Empty set (0.00 sec)

mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| innodb_version          | 5.6.23                               |
| protocol_version        | 10                                   |
| slave_type_conversions  |                                      |
| version                 | 5.6.23                               |
| version_comment         | Source distribution Clone 2015-01-10 |
| version_compile_machine | x86_64                               |
| version_compile_os      | Win64                                |
+-------------------------+--------------------------------------+
7 rows in set (0.00 sec)
[10 Jan 2015 22:59] Miguel Solorzano
Thank you for the bug report. Repeatable since 5.1 server.