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: | |
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
[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>