Bug #75471 Views that use hex literals fails

• Submitted: 9 Jan 2015 17:29
• Modified: 25 Nov 2019 22:22
• Reporter: Chris Herridge
• Status: Closed
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 5.1/5.5/5.6/5.7
• OS: Any
• 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] MySQL Verification Team

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] MySQL Verification Team

Thank you for the bug report. Repeatable since 5.1 server.

[25 Nov 2019 22:22] Roy Lyseng

Posted by developer:
 
Fixed in 5.7