MySQL Bugs: #58078: create view struggles with certain combinations of backticks and column names

Bug #58078	create view struggles with certain combinations of backticks and column names
Submitted:	9 Nov 2010 11:21	Modified:	10 Nov 2010 14:57
Reporter:	Martijn Korse	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1.36, 5.1.52	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	backticks, create view, parser, show create view

Description:
I discovered the problem when i was trying to restore a backup that mysqldump had made: it encountered an error trying to recreate a view. It looks like the problem is a combination of backticks and columnames.

btw, I put this as S2 (serious) because i thought replication could be impacted too. I haven't been able to test this myself though. If not, i guess S3 would be better.

How to repeat:
/* create the following tables */

CREATE TABLE `Clients_Products_Grouptotals` (
	`ClientProductID` INT(11) UNSIGNED NULL DEFAULT NULL,
	`ProductID` INT(10) UNSIGNED NULL DEFAULT NULL,
	`DiscountID` INT(11) UNSIGNED NULL DEFAULT NULL,
	`TotalProductsInGroup` BIGINT(21) NULL DEFAULT NULL
)
ENGINE=MyISAM;

CREATE TABLE `DiscountTypes` (
	`DiscountTypeID` INT(11) UNSIGNED NOT NULL,
	`DiscountID` INT(11) NULL DEFAULT NULL,
	`Quantity` VARCHAR(255) NOT NULL DEFAULT '0',
	`Fixed` FLOAT(7,2) UNSIGNED NULL DEFAULT NULL,
	`Percentage` FLOAT(5,2) UNSIGNED NULL DEFAULT NULL
)
ENGINE=MyISAM;

CREATE TABLE `Products` (
	`ProductID` INT(10) UNSIGNED NOT NULL,
	`PricePerInterval` DECIMAL(8,2) UNSIGNED NOT NULL
)
ENGINE=MyISAM;

/* in the following view definition, the DiscountTypes.Fixed column is used. the view will be successfully created and the SHOW CREATE VIEW statement will output a query that can be successfully executed */

DROP VIEW IF EXISTS cp_total;
CREATE VIEW `cp_total` AS
	SELECT
		`CPT`.`ClientProductID`,
		IFNULL(
			(
				SELECT
					ROUND(`P`.`PricePerInterval` - IFNULL((`DT`.`Fixed`), 0), 2)
				FROM `DiscountTypes` AS `DT`
				WHERE `DT`.`DiscountID`=`CPT`.`DiscountID`
				AND (`DT`.`Quantity`=0 OR `DT`.`Quantity` <= `CPT`.`TotalProductsInGroup`)
				LIMIT 0,1
			),
			`P`.`PricePerInterval`
		) AS `TotalPricePerInterval`
FROM `Clients_Products_Grouptotals` `CPT`
	LEFT JOIN `Products` `P` ON `P`.`ProductID`=`CPT`.`ProductID`;

SHOW CREATE VIEW cp_total;

/* the output of the SHOW CREATE VIEW statement:
-----------------
*/
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cp_total` AS
SELECT `CPT`.`ClientProductID` AS `ClientProductID`, IFNULL((
SELECT ROUND((`P`.`PricePerInterval` - IFNULL(`DT`.`Fixed`,0)),2) AS `ROUND(``P``.``PricePerInterval`` - IFNULL((``DT``.``Fixed``), 0), 2)`
FROM `discounttypes` `DT`
WHERE ((`DT`.`DiscountID` = `CPT`.`DiscountID`) AND ((`DT`.`Quantity` = 0) OR (`DT`.`Quantity` <= `CPT`.`TotalProductsInGroup`)))
LIMIT 0,1),`P`.`PricePerInterval`) AS `TotalPricePerInterval`
FROM (`clients_products_grouptotals` `CPT`
LEFT JOIN `products` `P` ON((`P`.`ProductID` = `CPT`.`ProductID`)))
/*
-----------------
*/

/* the following view definition differs from the first one in only 1 aspect: the DiscountTypes.Percentage column is used instead of the DiscountTypes.Fixed column. The view will be successfully created, but when the output of the SHOW CREATE VIEW statement is executed, this fails.

DROP VIEW IF EXISTS cp_total;
CREATE VIEW `cp_total` AS
	SELECT
		`CPT`.`ClientProductID`,
		IFNULL(
			(
				SELECT
					ROUND(`P`.`PricePerInterval` - IFNULL((`DT`.`Percentage`), 0), 2)
				FROM `DiscountTypes` AS `DT`
				WHERE `DT`.`DiscountID`=`CPT`.`DiscountID`
				AND (`DT`.`Quantity`=0 OR `DT`.`Quantity` <= `CPT`.`TotalProductsInGroup`)
				LIMIT 0,1
			),
			`P`.`PricePerInterval`
		) AS `TotalPricePerInterval`
FROM `Clients_Products_Grouptotals` `CPT`
	LEFT JOIN `Products` `P` ON `P`.`ProductID`=`CPT`.`ProductID`;

SHOW CREATE VIEW cp_total;

/* the output of the SHOW CREATE VIEW statement:
 *
 * NOTE: THIS IS THE QUERY THAT FAILS!!
 *
-----------------
*/
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cp_total` AS
SELECT `CPT`.`ClientProductID` AS `ClientProductID`, IFNULL((
SELECT ROUND((`P`.`PricePerInterval` - IFNULL(`DT`.`Percentage`,0)),2) AS `ROUND(``P``.``PricePerInterval`` - IFNULL((``DT``.``Percentage``), 0), 2)`
FROM `discounttypes` `DT`
WHERE ((`DT`.`DiscountID` = `CPT`.`DiscountID`) AND ((`DT`.`Quantity` = 0) OR (`DT`.`Quantity` <= `CPT`.`TotalProductsInGroup`)))
LIMIT 0,1),`P`.`PricePerInterval`) AS `TotalPricePerInterval`
FROM (`clients_products_grouptotals` `CPT`
LEFT JOIN `products` `P` ON((`P`.`ProductID` = `CPT`.`ProductID`)))
/*-----------------
*/

/* the following query is the same as the one that fails, except that the backticks have been removed. This one doesn't fail.
*/
DROP VIEW IF EXISTS cp_total;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cp_total` AS
SELECT `CPT`.`ClientProductID` AS `ClientProductID`, IFNULL((
SELECT ROUND((P.PricePerInterval - IFNULL(DT.Percentage,0)),2) AS `ROUND(P.PricePerInterval - IFNULL((DT.Percentage), 0), 2)`
FROM `discounttypes` `DT`
WHERE ((`DT`.`DiscountID` = `CPT`.`DiscountID`) AND ((`DT`.`Quantity` = 0) OR (`DT`.`Quantity` <= `CPT`.`TotalProductsInGroup`)))
LIMIT 0,1),`P`.`PricePerInterval`) AS `TotalPricePerInterval`
FROM (`clients_products_grouptotals` `CPT`
LEFT JOIN `products` `P` ON((`P`.`ProductID` = `CPT`.`ProductID`)))

Suggested fix:
Normally, you would never alias the columns as it happens when the view is created (stored)
To avoid the problems with the way the view is stored, give an alias to every column.

Could you please try with last version released 5.1.52 and comment the result here. Thanks in advance.

K, i downloaded and installed 5.1.52 and can report the following results:

- In my example i've shown the output of two SHOW CREATE VIEW statements: one where the view was created with that 'Fixed' column and one with the 'Percentage' column. If i copy paste that output, the one with the Fixed column still executes without problem and the one with Percentage column still causes an error. So, the parser still has problems with that one.
- When outputting SHOW CREATE VIEW however, it appears the alias is no longer included. So, this means that users are much less likely to experience the problem as it is not so likely someone would write that syntax him/herself.

> - When outputting SHOW CREATE VIEW however, it appears the alias is no longer included.

Yeah, it was causing too much trouble and was unnecessary in some cases. See Bug#40277 for more details.

Thank you for the feedback.

Have you tried to run SHOW CREATE VIEW and mysqldump in version 5.1.52?

> If i copy paste that output, the one with the Fixed column still executes
> without problem and the [..] So, the parser still has problems with [..]

Note that the problem was that the algorithm that printed views (SHOW CREATE VIEW) was generating syntactically invalid CREATE VIEW statements, hence the parser was (and still is) right to reject it. If possible, please upgrade and the problem should be gone.

> Note that the problem was that the algorithm that printed views (SHOW CREATE
> VIEW) was generating syntactically invalid CREATE VIEW statements, hence the
> parser was (and still is) right to reject it.

If the parser is right to reject it, then it's making a mistake not rejecting the first one.

Please consider these two views:

/* 1st view : cp_total_fixed */
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`cp_total_fixed` AS
SELECT `CPT`.`ClientProductID` AS `ClientProductID`, IFNULL((
SELECT ROUND((`P`.`PricePerInterval` - IFNULL(`DT`.`Fixed`,0)),2) AS
`ROUND(``P``.``PricePerInterval`` - IFNULL((``DT``.``Fixed``), 0), 2)`
FROM `discounttypes` `DT`
WHERE ((`DT`.`DiscountID` = `CPT`.`DiscountID`) AND ((`DT`.`Quantity` = 0) OR
(`DT`.`Quantity` <= `CPT`.`TotalProductsInGroup`)))
LIMIT 0,1),`P`.`PricePerInterval`) AS `TotalPricePerInterval`
FROM (`clients_products_grouptotals` `CPT`
LEFT JOIN `products` `P` ON((`P`.`ProductID` = `CPT`.`ProductID`)))

/* 2nd view : cp_total_percentage */
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`cp_total_percentage` AS
SELECT `CPT`.`ClientProductID` AS `ClientProductID`, IFNULL((
SELECT ROUND((`P`.`PricePerInterval` - IFNULL(`DT`.`Fixed`,0)),2) AS
`ROUND(``P``.``PricePerInterval`` - IFNULL((``DT``.``Fixed``), 0), 2)`
FROM `discounttypes` `DT`
WHERE ((`DT`.`DiscountID` = `CPT`.`DiscountID`) AND ((`DT`.`Quantity` = 0) OR
(`DT`.`Quantity` <= `CPT`.`TotalProductsInGroup`)))
LIMIT 0,1),`P`.`PricePerInterval`) AS `TotalPricePerInterval`
FROM (`clients_products_grouptotals` `CPT`
LEFT JOIN `products` `P` ON((`P`.`ProductID` = `CPT`.`ProductID`)))

The only difference between the two is that cp_total_fixed is selecting DT.Fixed in the subquery and the second (cp_total_percentage) is selecting DT.Percentage.
The first one executes fine, the second generates an error.

That means the parser is either wrong rejecting the second one or wrong accepting the first one.

Oops, i copy-pasted the wrong view in my previous comment. The first one is correct, the second one should be:

/* 2nd view : cp_total_percentage */
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`cp_total_percentage` AS
SELECT `CPT`.`ClientProductID` AS `ClientProductID`, IFNULL((
SELECT ROUND((`P`.`PricePerInterval` - IFNULL(`DT`.`Percentage`,0)),2) AS
`ROUND(``P``.``PricePerInterval`` - IFNULL((``DT``.``Percentage``), 0), 2)`
FROM `discounttypes` `DT`
WHERE ((`DT`.`DiscountID` = `CPT`.`DiscountID`) AND ((`DT`.`Quantity` = 0) OR
(`DT`.`Quantity` <= `CPT`.`TotalProductsInGroup`)))
LIMIT 0,1),`P`.`PricePerInterval`) AS `TotalPricePerInterval`
FROM (`clients_products_grouptotals` `CPT`
LEFT JOIN `products` `P` ON((`P`.`ProductID` = `CPT`.`ProductID`)))

Martijn,

In the 2nd case the column name length ends up being 65, whereas the limit is 64. This is one example where SHOW CREATE VIEW was generating invalid queries.

I think we can safely call this a duplicate of Bug#40277.

Just to perhaps clarify things further, when I say that SHOW CREATE VIEW generate invalid queries, it was specially invalid column names (alias), like this case. It didn't do any kind of check on those names and could end up creating column names with, for example, invalid characters or longer then the maximum allowed.

Ah i see. I completely overlooked the part about the maximum length!
It makes sense now. Thanks for the explanation.

Closed as a duplicate of Bug#40277.