Bug #12963 Problem with DAYOFWEEK(CURDATE()) and View
Submitted: 3 Sep 2005 8:22 Modified: 14 Sep 2005 17:11
Reporter: Are Casilla Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:MySQL 5.0.12-beta-standard OS:Linux (Debian version 2.6.8-2-686)
Assigned to: Assigned Account CPU Architecture:Any

[3 Sep 2005 8:22] Are Casilla
Description:
select * from asv_test4;
Empty set, 2 warnings (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '732557' |
| Warning | 1292 | Truncated incorrect datetime value: '732557' |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

This query have to return one record and no warnings.

I can only reproduce this in relationship to a view.

How to repeat:
DROP TABLE IF EXISTS test3;
CREATE TABLE `test3` (
id int(10) NOT NULL auto_increment,
day tinyint(1) NOT NULL default 0,
comment varchar(255) NOT NULL default '',
PRIMARY KEY (id)
);

INSERT INTO test3 VALUES (1, 0, 'day 0');
INSERT INTO test3 VALUES (1, 1, 'day 1');
INSERT INTO test3 VALUES (1, 2, 'day 2');
INSERT INTO test3 VALUES (1, 3, 'day 3');
INSERT INTO test3 VALUES (1, 4, 'day 4');
INSERT INTO test3 VALUES (1, 5, 'day 5');
INSERT INTO test3 VALUES (1, 6, 'day 6');
INSERT INTO test3 VALUES (2, 7, 'day 7');

DROP VIEW IF EXISTS asv_test3;
create view asv_test3 as
select * from test3;

DROP VIEW IF EXISTS asv_test4;
create view asv_test4 as
select * from asv_test3 where day = DAYOFWEEK(CURDATE());

select * from asv_test4;

Suggested fix:
It may be related to the use of tinyint(1) as day in table test3. Have not had time to check that. DAYOFWEEK is tinyint(1) so clearly it need to work.

Keep up the good work.
[6 Sep 2005 8:48] Andrey Hristov
possibly related to #13000
[14 Sep 2005 17:11] Sergey Petrunya
The same issue as BUG#13000. Fix for BUG#13000 fixed fix bug