| Bug #59192 | View not return data | ||
|---|---|---|---|
| Submitted: | 28 Dec 2010 12:38 | Modified: | 28 Dec 2010 14:40 |
| Reporter: | Carlos Garces | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0.81-community | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[28 Dec 2010 13:15]
Valeriy Kravchuk
Sorry, but SELECT itself does NOT return any data for me on 5.0.91:
mysql> CREATE TABLE `historical` (
-> `id` varchar(12) NOT NULL,
-> `date` date NOT NULL,
-> `price` decimal(9,3) NOT NULL,
-> PRIMARY KEY (`id`,`date`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.23 sec)
mysql> INSERT INTO `historical` VALUES('JAZ.MC', '2010-12-27', 3.550);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `historical` VALUES('JAZ.MC', '2010-12-23', 3.580);
Query OK, 1 row affected (0.00 sec)
...
mysql> INSERT INTO `historical` VALUES('ZJP7.BE', '2010-12-20', 6.590);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `historical` VALUES('MTF.MC', '2010-12-27', 7.300);
Query OK, 1 row affected (0.00 sec)
mysql> select id, date,price
-> from historical
-> group by id
-> having (min(date) = date);
Empty set (0.09 sec)
So, what do I miss?
I think the problem is that you do not group by date and price columns, while in the group with the same id their values are different. Our manual explains what can happen at http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html:
"MySQL extends the use of GROUP BY so that you can use nonaggregated columns or calculations in the select list that do not appear in the GROUP BY clause. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping.
...
When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.
A similar MySQL extension applies to the HAVING clause. The SQL standard does not permit the HAVING clause to name any column not found in the GROUP BY clause if it is not enclosed in an aggregate function. MySQL permits the use of such columns to simplify calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate."
[28 Dec 2010 13:55]
Carlos Garces
I have launched again the SQL SELECT id, date, price FROM historical GROUP BY id HAVING ( min( date ) = date ) And I confirm that return values ¿?, I'm not sure if made sense for you. I have changed the SQL adding group by date and now I think that works. SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; CREATE ALGORITHM=MERGE DEFINER=CURRENT_USER() SQL SECURITY DEFINER VIEW `START_PRICE` (id, date,price) AS SELECT id, date, price FROM historical GROUP BY id, date HAVING ( min( date ) = date )
[28 Dec 2010 14:33]
Valeriy Kravchuk
My point is that whatever results you get from your original query, it is OK. The result is unpredictable and may depend on many things, like indexes on columns and execution plan used by optimizer, or hardware platform and MySQL version (as in my case). This is explicitly documented and thus not a bug.
[28 Dec 2010 14:40]
Carlos Garces
I don't understand your position If I have a SQL that return n results and the same SQL inside a view return 0, is a bug, otherwise I can't used MySQL on production environments.
[29 Dec 2010 11:56]
Valeriy Kravchuk
My position is simple. The same SQL, even NOT in the view, returns zero rows (on a newer version in my case). So, the result set is just not deterministic. Moreover, as manual explains, it may return zero rows, or n rows, or another n rows, or whatever number of rows, as soon as data in the same group have different values in non-aggregated columns you use in SELECT ... or HAVING ... but do not use in GROUP BY.
Description: I'm trying to create this view drop view START_PRICE; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; CREATE ALGORITHM=MERGE DEFINER=`a6792578_finance`@`localhost` SQL SECURITY DEFINER VIEW `START_PRICE` (id, date,price) AS select id, date,price from historical group by id having (min(date) = date); The SELECT clause return data, but the view return 0 rows How to repeat: -- -- Table structure for table `historical` -- CREATE TABLE `historical` ( `id` varchar(12) NOT NULL, `date` date NOT NULL, `price` decimal(9,3) NOT NULL, PRIMARY KEY (`id`,`date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `historical` -- INSERT INTO `historical` VALUES('JAZ.MC', '2010-12-27', 3.550); INSERT INTO `historical` VALUES('JAZ.MC', '2010-12-23', 3.580); INSERT INTO `historical` VALUES('JAZ.MC', '2010-12-22', 3.600); INSERT INTO `historical` VALUES('JAZ.MC', '2010-12-21', 3.640); INSERT INTO `historical` VALUES('JAZ.MC', '2010-12-20', 3.560); INSERT INTO `historical` VALUES('SAN.MC', '2010-12-23', 8.130); INSERT INTO `historical` VALUES('SAN.MC', '2010-12-22', 8.170); INSERT INTO `historical` VALUES('SAN.MC', '2010-12-21', 8.280); INSERT INTO `historical` VALUES('SAN.MC', '2010-12-20', 8.050); INSERT INTO `historical` VALUES('SPS.MC', '2010-12-23', 0.410); INSERT INTO `historical` VALUES('SPS.MC', '2010-12-22', 0.430); INSERT INTO `historical` VALUES('SPS.MC', '2010-12-21', 0.430); INSERT INTO `historical` VALUES('SPS.MC', '2010-12-20', 0.430); INSERT INTO `historical` VALUES('BBVA.MC', '2010-12-23', 7.810); INSERT INTO `historical` VALUES('BBVA.MC', '2010-12-22', 7.910); INSERT INTO `historical` VALUES('BBVA.MC', '2010-12-21', 7.950); INSERT INTO `historical` VALUES('BBVA.MC', '2010-12-20', 7.710); INSERT INTO `historical` VALUES('BIO.MC', '2010-12-27', 0.630); INSERT INTO `historical` VALUES('BIO.MC', '2010-12-23', 0.640); INSERT INTO `historical` VALUES('BIO.MC', '2010-12-22', 0.610); INSERT INTO `historical` VALUES('BIO.MC', '2010-12-21', 0.640); INSERT INTO `historical` VALUES('BIO.MC', '2010-12-20', 0.640); INSERT INTO `historical` VALUES('MTF.MC', '2010-12-23', 7.300); INSERT INTO `historical` VALUES('MTF.MC', '2010-12-22', 7.300); INSERT INTO `historical` VALUES('MTF.MC', '2010-12-21', 7.300); INSERT INTO `historical` VALUES('MTF.MC', '2010-12-20', 7.300); INSERT INTO `historical` VALUES('BKT.MC', '2010-12-23', 4.400); INSERT INTO `historical` VALUES('BKT.MC', '2010-12-22', 4.410); INSERT INTO `historical` VALUES('BKT.MC', '2010-12-21', 4.390); INSERT INTO `historical` VALUES('BKT.MC', '2010-12-20', 4.200); INSERT INTO `historical` VALUES('ZEL.MC', '2010-12-23', 2.890); INSERT INTO `historical` VALUES('ZEL.MC', '2010-12-22', 2.930); INSERT INTO `historical` VALUES('ZEL.MC', '2010-12-21', 2.810); INSERT INTO `historical` VALUES('ZEL.MC', '2010-12-20', 2.750); INSERT INTO `historical` VALUES('SOS.MC', '2010-12-23', 1.000); INSERT INTO `historical` VALUES('SOS.MC', '2010-12-22', 0.850); INSERT INTO `historical` VALUES('SOS.MC', '2010-12-21', 0.820); INSERT INTO `historical` VALUES('SOS.MC', '2010-12-20', 0.790); INSERT INTO `historical` VALUES('BTO.MC', '2010-12-23', 6.430); INSERT INTO `historical` VALUES('BTO.MC', '2010-12-22', 6.480); INSERT INTO `historical` VALUES('BTO.MC', '2010-12-21', 6.430); INSERT INTO `historical` VALUES('BTO.MC', '2010-12-20', 6.330); INSERT INTO `historical` VALUES('IBR.MC', '2010-12-23', 2.670); INSERT INTO `historical` VALUES('IBR.MC', '2010-12-22', 2.700); INSERT INTO `historical` VALUES('IBR.MC', '2010-12-21', 2.670); INSERT INTO `historical` VALUES('IBR.MC', '2010-12-20', 2.630); INSERT INTO `historical` VALUES('BME.MC', '2010-12-23', 18.670); INSERT INTO `historical` VALUES('BME.MC', '2010-12-22', 18.750); INSERT INTO `historical` VALUES('BME.MC', '2010-12-21', 19.490); INSERT INTO `historical` VALUES('BME.MC', '2010-12-20', 19.020); INSERT INTO `historical` VALUES('FCC.MC', '2010-12-23', 19.100); INSERT INTO `historical` VALUES('FCC.MC', '2010-12-22', 19.000); INSERT INTO `historical` VALUES('FCC.MC', '2010-12-21', 19.080); INSERT INTO `historical` VALUES('FCC.MC', '2010-12-20', 18.680); INSERT INTO `historical` VALUES('REE.MC', '2010-12-23', 36.300); INSERT INTO `historical` VALUES('REE.MC', '2010-12-22', 36.350); INSERT INTO `historical` VALUES('REE.MC', '2010-12-21', 36.380); INSERT INTO `historical` VALUES('REE.MC', '2010-12-20', 36.110); INSERT INTO `historical` VALUES('POP.MC', '2010-12-23', 3.970); INSERT INTO `historical` VALUES('POP.MC', '2010-12-22', 4.020); INSERT INTO `historical` VALUES('POP.MC', '2010-12-21', 4.070); INSERT INTO `historical` VALUES('POP.MC', '2010-12-20', 4.020); INSERT INTO `historical` VALUES('ITX.MC', '2010-12-23', 58.010); INSERT INTO `historical` VALUES('ITX.MC', '2010-12-22', 59.000); INSERT INTO `historical` VALUES('ITX.MC', '2010-12-21', 58.760); INSERT INTO `historical` VALUES('ITX.MC', '2010-12-20', 56.600); INSERT INTO `historical` VALUES('EBRO.MC', '2010-12-23', 15.930); INSERT INTO `historical` VALUES('EBRO.MC', '2010-12-22', 16.840); INSERT INTO `historical` VALUES('EBRO.MC', '2010-12-21', 16.670); INSERT INTO `historical` VALUES('EBRO.MC', '2010-12-20', 16.340); INSERT INTO `historical` VALUES('ACN', '2010-12-27', 48.250); INSERT INTO `historical` VALUES('ACN', '2010-12-23', 48.340); INSERT INTO `historical` VALUES('ACN', '2010-12-22', 48.640); INSERT INTO `historical` VALUES('ACN', '2010-12-21', 48.730); INSERT INTO `historical` VALUES('ACN', '2010-12-20', 48.690); INSERT INTO `historical` VALUES('^IBEX', '2010-12-27', 999.990); INSERT INTO `historical` VALUES('^IBEX', '2010-12-23', 999.990); INSERT INTO `historical` VALUES('^IBEX', '2010-12-22', 999.990); INSERT INTO `historical` VALUES('^IBEX', '2010-12-21', 999.990); INSERT INTO `historical` VALUES('^IBEX', '2010-12-20', 999.990); INSERT INTO `historical` VALUES('358446.SG', '2010-12-23', 52.520); INSERT INTO `historical` VALUES('358446.SG', '2010-12-22', 52.200); INSERT INTO `historical` VALUES('358446.SG', '2010-12-21', 51.750); INSERT INTO `historical` VALUES('358446.SG', '2010-12-20', 51.030); INSERT INTO `historical` VALUES('JPJP.SG', '2010-12-23', 37.360); INSERT INTO `historical` VALUES('JPJP.SG', '2010-12-22', 37.540); INSERT INTO `historical` VALUES('JPJP.SG', '2010-12-21', 37.520); INSERT INTO `historical` VALUES('JPJP.SG', '2010-12-20', 37.170); INSERT INTO `historical` VALUES('ZJP7.BE', '2010-12-23', 6.610); INSERT INTO `historical` VALUES('ZJP7.BE', '2010-12-22', 6.590); INSERT INTO `historical` VALUES('ZJP7.BE', '2010-12-21', 6.590); INSERT INTO `historical` VALUES('ZJP7.BE', '2010-12-20', 6.590); INSERT INTO `historical` VALUES('MTF.MC', '2010-12-27', 7.300); SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; CREATE ALGORITHM=MERGE DEFINER=`CURRENT_USER() SQL SECURITY DEFINER VIEW `START_PRICE` (id, date,price) AS select id, date,price from historical group by id having (min(date) = date);