Bug #13000 WEEKDAY function in views changed to WEEKDAY(TO_DATE(field))
Submitted: 6 Sep 2005 0:28 Modified: 19 Sep 2005 17:24
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.11/BK Source OS:Mac OS X (Mac OSX 10.4/Linux)
Assigned to: Alexander Ivanov CPU Architecture:Any

[6 Sep 2005 0:28] [ name withheld ]
Description:
When used in a view, weekday function is changed from weekday(field) to weekday(to_date(field)) and then null is returned when the view is used.

How to repeat:
CREATE TABLE tblDates (
  `Date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO tblDates
VALUES('2005-09-05'), ('2005-09-06');

CREATE VIEW vDates AS
SELECT `Date`, WEEKDAY(`Date`) AS `Weekday`
FROM tblDates;

SELECT * FROM vDates;

SHOW CREATE VIEW vDates;
[6 Sep 2005 1:06] Miguel Solorzano
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.13-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE tblDates (
    ->   `Date` date NOT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> INSERT INTO tblDates
    -> VALUES('2005-09-05'), ('2005-09-06');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> CREATE VIEW vDates AS
    -> SELECT `Date`, WEEKDAY(`Date`) AS `Weekday`
    -> FROM tblDates;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> SELECT * FROM vDates;
+------------+---------+
| Date       | Weekday |
+------------+---------+
| 2005-09-05 |    NULL |
| 2005-09-06 |    NULL |
+------------+---------+
2 rows in set, 2 warnings (0.00 sec)

mysql> 
mysql> SHOW CREATE VIEW vDates\G
*************************** 1. row ***************************
       View: vDates
Create View: CREATE ALGORITHM=UNDEFINED VIEW `test`.`vDates` AS select `test`.`tblDates`.`Date` AS `Date`,weekday(to_days(to_days(`test`.`tblDates`.`Date`))) AS `Weekday` from `test`.`tblDates`
1 row in set (0.00 sec)

mysql>
[6 Sep 2005 8:47] Andrey Hristov
possibly related to #12963
[13 Sep 2005 14:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29747
[14 Sep 2005 9:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29815
[14 Sep 2005 16:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29854
[14 Sep 2005 17:33] Alexander Ivanov
ChangeSet
  1.1955 05/09/14 20:25:00 aivanov@mysql.com +6 -0
  Fixed BUG#12963, BUG#13000: wrong VIEW creation with DAYNAME(),
   DAYOFWEEK(), and WEEKDAY().

  sql/item_timefunc.h
    1.59 05/09/14 20:24:30 aivanov@mysql.com +4 -1
    Fixed bugs #12963, 13000: wrong VIEW creation with DAYNAME(),
     DAYOFWEEK(), and WEEKDAY.
     Modified Item_func_weekday::func_name(). It returns now different
     names depending on the odbc_type attribute value.

  sql/item_timefunc.cc
    1.94 05/09/14 20:24:30 aivanov@mysql.com +7 -6
    Fixed bugs #12963, #13000: wrong VIEW creation with DAYNAME(),
     DAYOFWEEK(), and WEEKDAY().
     Modified Item_func_weekday::val_int(). The argument of weekday should
     not be considered now to be Item_func_to_days object.

  sql/item_create.cc
    1.58 05/09/14 20:24:30 aivanov@mysql.com +3 -3
    Fixed bugs #12963, #13000: wrong VIEW creation with DAYNAME(),
     DAYOFWEEK(), and WEEKDAY().
     Modified create_func_dayname(), create_func_dayofweek(), and
     create_func_weekday(). They donĀ“t insert Item_func_to_days
     object now.

 mysql-test/t/view.test
    1.107 05/09/14 20:24:30 aivanov@mysql.com +31 -0
    Added testcases for for bugs #12963, #13000.

  mysql-test/r/view.result
    1.113 05/09/14 20:24:30 aivanov@mysql.com +43 -0
    Fixed some testcases results (bugs #12963, #13000).

  mysql-test/r/func_time.result
    1.46 05/09/14 20:24:30 aivanov@mysql.com +1 -1
    Fixed new results for testcases containing EXPLAIN EXTENDED SELECT ...
     WEEKDAY ... DAYNAME. The new results are correct and correspond to
     the changes in create_func_weekday() and create_func_dayname().

  The fix will appear in 5.0.13
[19 Sep 2005 17:24] Paul Dubois
Noted in 5.0.13 changelog.