Bug #16283 View returns wrong result
Submitted: 8 Jan 2006 13:24 Modified: 16 Jan 2006 20:12
Reporter: Peter Rieder Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.17/18-nt OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[8 Jan 2006 13:24] Peter Rieder
Description:
I have a view 'sommertage' on a table like:
DROP VIEW IF EXISTS `wetterdaten`.`sommertage`;
CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sommertage` AS select `w`.`Datum` AS `Datum`,avg(`w`.`ATemp`) AS `Tagesmittel`,min(`w`.`ATemp`) AS `MinTemp`,max(`w`.`ATemp`) AS `MaxTemp` from `wetterdaten` `w` where (year(`w`.`Datum`) = 2001) group by month(`w`.`Datum`),dayofmonth(`w`.`Datum`) having (max(`w`.`ATemp`) > 25);

The reusult is correct with:
mysql> select * from sommertage;
(7 rows fetched)

Changing the view to:
DROP VIEW IF EXISTS `wetterdaten`.`sommertage`;
CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sommertage` AS select `w`.`Datum` AS `Datum`,avg(`w`.`ATemp`) AS `Tagesmittel`,min(`w`.`ATemp`) AS `MinTemp`,max(`w`.`ATemp`) AS `MaxTemp` from `wetterdaten` `w`  group by month(`w`.`Datum`),dayofmonth(`w`.`Datum`) having (max(`w`.`ATemp`) > 25);

The result is incorrect with:
mysql> select * from sommertage where Year(Datum) = 2001;
(27 rows fetched) which is absolute wrong

How to repeat:
Just have a table with fields 'Datum' and 'ATemp'
CREATE TABLE `Wetterdaten` (
  `Datum` date NOT NULL default '0000-00-00',
  `ATemp` double(5,1) default NULL
)
and a lot of values.

Suggested fix:
No solution found.
The same problem like Bug#16232 ?
[8 Jan 2006 13:24] Peter Rieder
I hope I use the view in correct way? With postgreSQL it worked as I axpected.
[8 Jan 2006 15:25] Hartmut Holzgraefe
hi, could you provide us with a dump of the `wetterdaten` table?
[10 Jan 2006 15:23] Peter Rieder
Upload done.
[11 Jan 2006 10:34] Valeriy Kravchuk
I was not able to repeat the difference you described on your dump loaded into 5.0.19-BK (ChangeSet@1.2020.3.1, 2006-01-10 13:44:08+02:00) on Linux:

mysql> select * from sommertage;
ERROR 1146 (42S02): Table 'backup.sommertage' doesn't exist

When I got this, I looked at the dump, and views were created as:

CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `backup`.`sommertage` AS select `w`.`Datum` AS `Datum`,avg(`w`.`ATemp`)
 AS `Tagesmittel`,min(`w`.`ATemp`) AS `MinTemp`,max(`w`.`ATemp`) AS `MaxTemp` from `wetterdaten`.`wetterdaten` `w` where (year(`w`.`Datum`) = 2001) group by month(`w`.`Datum`),dayofmonth(`w`.`Datum`) having (max(`w`.`ATemp`) > 25);

That is, reffering to the 'wetterdaten.wetterdaten' table. I changed them to refer to `backup` database:

mysql> CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `backup`.`sommertage` AS select `w`.`Datum` AS `Datum`,avg(`w`.`ATemp`)
 AS `Tagesmittel`,min(`w`.`ATemp`) AS `MinTemp`,max(`w`.`ATemp`) AS `MaxTemp` from `backup`.`wetterdaten` `w` where (year(`w`.`Datum`) = 2001) group by month(`w`.`Datum`),dayofmonth(`w`.`Datum`) having (max(`w`.`ATemp`) > 25);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `backup`.`sommertage1` AS select `w`.`Datum` AS `Datum`,avg(`w`.`ATemp`) AS `Tagesmittel`,min(`w`.`ATemp`) AS `MinTemp`,max(`w`.`ATemp`) AS `MaxTemp`
from `backup`.`wetterdaten` `w` group by month(`w`.`Datum`),dayofmonth(`w`.`Datum`) having (max(`w`.`ATemp`) > 25);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sommertage;  DEFINER=`root`@`loca
+---------------------+--------------+----------+----------+
| Datum               | Tagesmittel  | MinTemp  | MaxTemp  |
+---------------------+--------------+----------+----------+
| 2001-08-20 00:00:00 | 17.660416667 | 13.40000 | 26.40000 |
| 2001-08-23 00:00:00 | 18.237500000 | 13.10000 | 26.30000 |
| 2001-08-24 00:00:00 | 18.610416667 | 12.20000 | 28.70000 |
| 2001-08-25 00:00:00 | 18.412500000 | 11.00000 | 28.10000 |
| 2001-08-26 00:00:00 | 18.706250000 | 11.00000 | 28.80000 |
| 2001-08-27 00:00:00 | 19.902083333 | 12.00000 | 30.40000 |
| 2001-08-30 00:00:00 | 15.412500000 |  7.20000 | 25.10000 |
+---------------------+--------------+----------+----------+
7 rows in set (0.32 sec)

mysql> select * from sommertage1 where year(Datum) = 2001;
+---------------------+--------------+----------+----------+
| Datum               | Tagesmittel  | MinTemp  | MaxTemp  |
+---------------------+--------------+----------+----------+
| 2001-08-20 00:00:00 | 17.660416667 | 13.40000 | 26.40000 |
| 2001-08-23 00:00:00 | 18.237500000 | 13.10000 | 26.30000 |
| 2001-08-24 00:00:00 | 18.610416667 | 12.20000 | 28.70000 |
| 2001-08-25 00:00:00 | 18.412500000 | 11.00000 | 28.10000 |
| 2001-08-26 00:00:00 | 18.706250000 | 11.00000 | 28.80000 |
| 2001-08-27 00:00:00 | 19.902083333 | 12.00000 | 30.40000 |
| 2001-08-30 00:00:00 | 15.412500000 |  7.20000 | 25.10000 |
+---------------------+--------------+----------+----------+
7 rows in set (0.26 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

So, I've got exactly the same results. 

It may be the result of fix to bug #16232 already applied to the development tree. So, please, use the latest snapshot or wait for the version with Bug#16232 fixed to be released, then check with it and inform about the results.
[16 Jan 2006 19:40] Peter Rieder
Uploaded additional files (2002_1to6.zip an 2002_7to12.zip).
Also tried version 5.0.18
[16 Jan 2006 20:12] Peter Rieder
mysql> show create view sommertage;
+------------+----------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--------------+
| View       | Create View

              |
+------------+----------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--------------+
| sommertage | CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sommertage` AS select `w
`.`Datum` AS `Datum`,avg(`w`.`ATemp`) AS `Tagesmittel`,min(`w`.`ATemp`) AS `MinTemp`,max(`w`.`ATemp`) AS `MaxTemp` from
`wetterdaten` `w` where (year(`w`.`Datum`) = 2001) group by month(`w`.`Datum`),dayofmonth(`w`.`Datum`) having (max(`w`.`
ATemp`) > 25) |
+------------+----------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--------------+
1 row in set (0.01 sec)

mysql> select * from sommertage;
+---------------------+--------------+----------+----------+
| Datum               | Tagesmittel  | MinTemp  | MaxTemp  |
+---------------------+--------------+----------+----------+
| 2001-08-20 00:00:00 | 17.660416667 | 13.40000 | 26.40000 |
| 2001-08-23 00:00:00 | 18.237500000 | 13.10000 | 26.30000 |
| 2001-08-24 00:00:00 | 18.610416667 | 12.20000 | 28.70000 |
| 2001-08-25 00:00:00 | 18.412500000 | 11.00000 | 28.10000 |
| 2001-08-26 00:00:00 | 18.706250000 | 11.00000 | 28.80000 |
| 2001-08-27 00:00:00 | 19.902083333 | 12.00000 | 30.40000 |
| 2001-08-30 00:00:00 | 15.412500000 |  7.20000 | 25.10000 |
+---------------------+--------------+----------+----------+
7 rows in set (0.63 sec)

mysql> show create view sommertage1;
+-------------+---------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------+
| View        | Create View

                                                                                                       |
+-------------+---------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------+
| sommertage1 | CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sommertage1` AS select
`w`.`Datum` AS `Datum`,avg(`w`.`ATemp`) AS `Tagesmittel`,min(`w`.`ATemp`) AS `MinTemp`,max(`w`.`ATemp`) AS `MaxTemp` fro
m `wetterdaten` `w` group by month(`w`.`Datum`),dayofmonth(`w`.`Datum`) having (max(`w`.`ATemp`) > 25) |
+-------------+---------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from sommertage1 where year(Datum) = 2001;
+---------------------+--------------+----------+----------+
| Datum               | Tagesmittel  | MinTemp  | MaxTemp  |
+---------------------+--------------+----------+----------+
| 2001-08-19 00:00:00 | 17.798387097 | 10.40000 | 29.30000 |
| 2001-08-20 00:00:00 | 17.943750000 | 10.00000 | 28.80000 |
| 2001-08-23 00:00:00 | 17.221875000 | 13.10000 | 26.30000 |
| 2001-08-24 00:00:00 | 17.130208333 | 12.20000 | 28.70000 |
| 2001-08-25 00:00:00 | 17.969791667 | 11.00000 | 28.10000 |
| 2001-08-26 00:00:00 | 18.279166667 | 11.00000 | 28.80000 |
| 2001-08-27 00:00:00 | 19.146875000 | 12.00000 | 30.40000 |
| 2001-08-28 00:00:00 | 17.622916667 | 12.10000 | 26.30000 |
| 2001-08-29 00:00:00 | 16.394791667 |  8.60000 | 25.90000 |
| 2001-08-30 00:00:00 | 16.817708333 |  7.20000 | 27.00000 |
| 2001-08-31 00:00:00 | 15.686458333 | 10.20000 | 27.80000 |
| 2001-09-04 00:00:00 | 14.178125000 |  9.30000 | 25.70000 |
| 2001-09-08 00:00:00 | 12.956250000 |  7.50000 | 25.80000 |
| 2001-09-09 00:00:00 | 12.387500000 |  5.70000 | 26.50000 |
+---------------------+--------------+----------+----------+
14 rows in set (0.88 sec)

mysql>