| Bug #47713 | SELECT WITH ROLLUP returned wrong data when using CONCAT | ||
|---|---|---|---|
| Submitted: | 29 Sep 2009 12:51 | Modified: | 29 Sep 2009 13:08 |
| Reporter: | Richard Teubel | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.0.83, 5.1.39 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Sep 2009 12:53]
Richard Teubel
.
[29 Sep 2009 13:08]
Valeriy Kravchuk
Thank you for the bug report. Verified with 5.1.39 also:
mysql> CREATE TABLE IF NOT EXISTS `a_Product` (
-> `ID` int(11) unsigned NOT NULL auto_increment,
-> `Name` varchar(20) collate latin1_german2_ci default NULL,
-> PRIMARY KEY (`ID`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_IN
CREMENT=1;
Query OK, 0 rows affected (0.61 sec)
mysql>
mysql> INSERT INTO `a_Product` (`ID`, `Name`) VALUES
-> (1, 'Car'),
-> (2, 'Bus'),
-> (3, 'Plain');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> CREATE TABLE IF NOT EXISTS `a_Sold` (
-> `ID` int(11) NOT NULL auto_increment,
-> `F_ProductID` int(11) unsigned default NULL,
-> `Quantity` int(11) unsigned default NULL,
-> `Year` int(11) default NULL,
-> PRIMARY KEY (`ID`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_IN
CREMENT=1 ;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> INSERT INTO `a_Sold` (`ID`, `F_ProductID`, `Quantity`, `Year`) VALUES
-> (1, 1, 4, 2008),
-> (2, 1, 6, 2008),
-> (3, 3, 7, 2008),
-> (4, 3, 2, 2009),
-> (5, 1, 44, 2009),
-> (6, 2, 5, 2008),
-> (7, 2, 6, 2008),
-> (8, 3, 4, 2009);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT a_Product.Name,
-> a_Sold.Year,
-> CONCAT('Sold ', a_Product.Name, ' at Year ', a_Sold.Year) AS SoldT
ext,
-> SUM(a_Sold.Quantity) AS SumQuantity
-> FROM a_Product
-> LEFT JOIN a_Sold
-> ON a_Sold.F_ProductID = a_Product.ID
-> GROUP BY a_Product.ID, a_Sold.`Year` with rollup;
+-------+------+-------------------------+-------------+
| Name | Year | SoldText | SumQuantity |
+-------+------+-------------------------+-------------+
| Car | 2008 | Sold Plain at Year 2008 | 10 |
| Car | 2009 | Sold Plain at Year 2009 | 44 |
| Car | NULL | NULL | 54 |
| Bus | 2008 | Sold Plain at Year 2008 | 11 |
| Bus | NULL | NULL | 11 |
| Plain | 2008 | Sold Plain at Year 2008 | 7 |
| Plain | 2009 | Sold Plain at Year 2009 | 6 |
| Plain | NULL | NULL | 13 |
| Plain | NULL | NULL | 78 |
+-------+------+-------------------------+-------------+
9 rows in set (0.13 sec)
mysql> SELECT a_Product.Name,
-> a_Sold.Year,
-> CONCAT('Sold ', a_Product.Name, ' at Year ', a_Sold.Year) AS SoldT
ext,
-> SUM(a_Sold.Quantity) AS SumQuantity
-> FROM a_Product
-> LEFT JOIN a_Sold
-> ON a_Sold.F_ProductID = a_Product.ID
-> GROUP BY a_Product.ID, a_Sold.`Year`;
+-------+------+-------------------------+-------------+
| Name | Year | SoldText | SumQuantity |
+-------+------+-------------------------+-------------+
| Car | 2008 | Sold Car at Year 2008 | 10 |
| Car | 2009 | Sold Car at Year 2009 | 44 |
| Bus | 2008 | Sold Bus at Year 2008 | 11 |
| Plain | 2008 | Sold Plain at Year 2008 | 7 |
| Plain | 2009 | Sold Plain at Year 2009 | 6 |
+-------+------+-------------------------+-------------+
5 rows in set (0.00 sec)
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.39-community |
+------------------+
1 row in set (0.00 sec)
[9 Nov 2013 1:21]
Jan Steinman
I am having a similar problem in trying to CONCAT() to produce a URL link. Oddly, the link produced goes to the web page for the record immediately following the one clicked! I can remove WITH ROLLUP and the links are correct. Here is a demonstration page. You can mouse-over the links in the "Product" column to see the difference between WITH ROLLUP and not. http://www.ecoreality.org/wiki/WITH_ROLLUP_problem
[9 Nov 2013 1:29]
Jan Steinman
It appears that both my example and the original have a problem with numeric values. I have other examples where VARCHAR fields do not exhibit this problem.
Description: I have a Qry with CONCAT for an output text and GROUP BY WITH ROLLUP for counting quantities of my products. The Name of the product should be in the SoldText, but it is an other product name. How to repeat: CREATE TABLE IF NOT EXISTS `a_Product` ( `ID` int(11) unsigned NOT NULL auto_increment, `Name` varchar(20) collate latin1_german2_ci default NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1; INSERT INTO `a_Product` (`ID`, `Name`) VALUES (1, 'Car'), (2, 'Bus'), (3, 'Plain'); CREATE TABLE IF NOT EXISTS `a_Sold` ( `ID` int(11) NOT NULL auto_increment, `F_ProductID` int(11) unsigned default NULL, `Quantity` int(11) unsigned default NULL, `Year` int(11) default NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ; INSERT INTO `a_Sold` (`ID`, `F_ProductID`, `Quantity`, `Year`) VALUES (1, 1, 4, 2008), (2, 1, 6, 2008), (3, 3, 7, 2008), (4, 3, 2, 2009), (5, 1, 44, 2009), (6, 2, 5, 2008), (7, 2, 6, 2008), (8, 3, 4, 2009); SELECT a_Product.Name, a_Sold.Year, CONCAT('Sold ', a_Product.Name, ' at Year ', a_Sold.Year) AS SoldText, SUM(a_Sold.Quantity) AS SumQuantity FROM a_Product LEFT JOIN a_Sold ON a_Sold.F_ProductID = a_Product.ID GROUP BY a_Product.ID, a_Sold.`Year` with rollup; +-------+------+-------------------------+-------------+ | Name | Year | SoldText | SumQuantity | +-------+------+-------------------------+-------------+ | Car | 2008 | Sold Plain at Year 2008 | 10 | <--! | Car | 2009 | Sold Plain at Year 2009 | 44 | <--! | Car | NULL | NULL | 54 | | Bus | 2008 | Sold Plain at Year 2008 | 11 | <--! | Bus | NULL | NULL | 11 | | Plain | 2008 | Sold Plain at Year 2008 | 7 | | Plain | 2009 | Sold Plain at Year 2009 | 6 | | Plain | NULL | NULL | 13 | | Plain | NULL | NULL | 78 | +-------+------+-------------------------+-------------+ 9 rows in set (0.00 sec) It should be: +-------+------+-------------------------+-------------+ | Name | Year | SoldText | SumQuantity | +-------+------+-------------------------+-------------+ | Car | 2008 | Sold Car at Year 2008 | 10 | <--! | Car | 2009 | Sold Car at Year 2009 | 44 | <--! | Car | NULL | NULL | 54 | | Bus | 2008 | Sold Bus at Year 2008 | 11 | <--! | Bus | NULL | NULL | 11 | | Plain | 2008 | Sold Plain at Year 2008 | 7 | | Plain | 2009 | Sold Plain at Year 2009 | 6 | | Plain | NULL | NULL | 13 | | Plain | NULL | NULL | 78 | +-------+------+-------------------------+-------------+ 9 rows in set (0.00 sec)