| Bug #33629 | last_day function can return null, but has 'not null' flag set for result | ||
|---|---|---|---|
| Submitted: | 2 Jan 2008 15:46 | Modified: | 12 Nov 2009 19:01 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0.52, 5.1.23, 6.0.3 | OS: | Any |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
| Tags: | Contribution, last_day | ||
[2 Jan 2008 15:57]
MySQL Verification Team
Thank you for the bug report.
c:\dbs>5.0\bin\mysql -uroot -T
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.56-nt Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from (select last_day('20070707')) a;
Field 1: `last_day('20070707')`
Catalog: `def`
Database: ``
Table: `a`
Org_table: ``
Type: DATE
Collation: binary (63)
Length: 10
Max_length: 10
Decimals: 0
Flags: NOT_NULL BINARY
+----------------------+
| last_day('20070707') |
+----------------------+
| 2007-07-31 |
+----------------------+
1 row in set (0.02 sec)
mysql> select 1 from (select (last_day('0')) `c`) `a`;
ERROR 1048 (23000): Column 'c' cannot be null
mysql>
[31 Jan 2009 14:14]
Bugs System
Patch for Bug #33629
Attachment: item_timefunc.h (text/x-chdr), 28.44 KiB.
[31 Jan 2009 14:18]
Bugs System
Jan. 31, 2009 Bug #33629 is caused by the fact that the inherited method (from the base class Item_date) fix_length_and_dec() of class Item_func_last_day is not setting maybe_null to 1(True, this Boolean variable indicates whether the column variable in question is allowed to hold a Null value.), the patch was created by making the method virtual in the base class and overriding it in the derived class Item_func_last_day (in-line) with assignment of maybe_null to 1. I discovered that the function MAKEDATE() suffers from the same problem, the patch to fix this problem is also included, this patch was created by setting maybe_null to 1 in fix_length_and_dec() method of class Item_func_makedate. File modified: item_timefunc.h Classes modified: Item_date, Item_func_last_day Item_func_makedate Methods modified: Item_date.fix_length_and_dec() (in-line) Item_func_makedate.fix_length_and_dec() (in-line) Method added: Item_func_last_day.fix_length_and_dec() (in-line) The patched item_timefunc.h has been uploaded. Hiromichi Watari
[4 Feb 2009 11:17]
Gleb Shchepa
Original Hiromichi's patch is:
=== modified file 'sql/item_timefunc.h'
--- old/sql/item_timefunc.h 2008-12-23 14:08:04 +0000
+++ new/sql/item_timefunc.h 2009-02-04 10:04:05 +0000
@@ -322,7 +322,12 @@ public:
longlong val_int();
double val_real() { return val_real_from_decimal(); }
const char *func_name() const { return "date"; }
- void fix_length_and_dec()
+
+/*
+ Patch for Bug #33629, this method is made virtual so that it can be overriden
+ in the derived class, Item_func_last_day.
+*/
+ virtual void fix_length_and_dec()
{
collation.set(&my_charset_bin);
decimals=0;
@@ -872,6 +877,9 @@ public:
{
decimals=0;
max_length=MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+
+/* Patch for a bug in MAKEDATE() similar to Bug #33629, this line was added. */
+ maybe_null= 1;
}
Field *tmp_table_field(TABLE *t_arg)
{
@@ -1044,4 +1052,16 @@ public:
Item_func_last_day(Item *a) :Item_date(a) {}
const char *func_name() const { return "last_day"; }
bool get_date(MYSQL_TIME *res, uint fuzzy_date);
+
+/*
+ Patch for Bug #33629, this method overrides the one in the base class
+ Item_date and sets maybe_null to 1.
+*/
+ void fix_length_and_dec()
+ {
+ collation.set(&my_charset_bin);
+ decimals=0;
+ max_length=MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+ maybe_null= 1;
+ }
};
It is generally ok and fixes the problem as expected.
Simplified patch for 5.0:
=== modified file 'sql/item_timefunc.h'
--- old/sql/item_timefunc.h 2008-12-23 14:08:04 +0000
+++ new/sql/item_timefunc.h 2009-02-04 11:02:28 +0000
@@ -872,6 +872,7 @@
{
decimals=0;
max_length=MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+ maybe_null= 1;
}
Field *tmp_table_field(TABLE *t_arg)
{
@@ -1044,4 +1045,9 @@
Item_func_last_day(Item *a) :Item_date(a) {}
const char *func_name() const { return "last_day"; }
bool get_date(MYSQL_TIME *res, uint fuzzy_date);
+ void fix_length_and_dec()
+ {
+ Item_date::fix_length_and_dec();
+ maybe_null= 1;
+ }
};
[14 May 2009 15:33]
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/commits/74079 3272 Gleb Shchepa 2009-05-14 Bug #33629: last_day function can return null, but has 'not null' flag set for result LAST_DAY and MAKEDATE functions are documented as returning NULL value, but actually they was implemented as returning NOT NULL typed values. That caused a confusing error "ERROR 1048 (23000): Column '...' cannot be null" on queries like: SELECT 1 FROM (SELECT LAST_DAY('0')) a; @ mysql-test/r/func_sapdb.result Updated test case for bug #33629. @ mysql-test/r/func_time.result Updated test case for bug #33629. @ mysql-test/r/type_date.result Added test case for bug #33629. @ mysql-test/t/type_date.test Added test case for bug #33629. @ sql/item_timefunc.h Bug #33629: last_day function can return null, but has 'not null' flag set for result 1. The Item_func_makedate::fix_length_and_dec method has been modified to declare MAKEDATE() as a function returning nullable value. 2. The Item_func_last_day::fix_length_and_dec method has been overloaded for the same purpose.
[18 May 2009 5: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/commits/74329 3291 Gleb Shchepa 2009-05-18 Bug #33629: last_day function can return null, but has 'not null' flag set for result LAST_DAY and MAKEDATE functions are documented as returning NULL value, but actually they was implemented as returning NOT NULL typed values. That caused a confusing error "ERROR 1048 (23000): Column '...' cannot be null" on queries like: SELECT 1 FROM (SELECT LAST_DAY('0')) a; @ mysql-test/r/func_sapdb.result Updated test case for bug #33629. @ mysql-test/r/func_time.result Updated test case for bug #33629. @ mysql-test/r/type_date.result Added test case for bug #33629. @ mysql-test/t/type_date.test Added test case for bug #33629. @ sql/item_timefunc.h Bug #33629: last_day function can return null, but has 'not null' flag set for result 1. The Item_func_makedate::fix_length_and_dec method has been modified to declare MAKEDATE() as a function returning nullable value. 2. The Item_func_last_day::fix_length_and_dec method has been overloaded for the same purpose.
[17 Jun 2009 19:27]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:gshchepa@mysql.com-20090518051437-5q55wda9fcqs3hg9) (merge vers: 6.0.12-alpha) (pib:11)
[29 Jun 2009 1:04]
Paul DuBois
Noted in 5.4.4 changelog. The LAST_DAY() and MAKEDATE() functions could return NULL, but the result metadata indicated NOT NULL. Thanks to Hiromichi Watari for the patch to fix this bug.
[12 Aug 2009 22:58]
Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:15]
Paul DuBois
Ignore previous comment about 5.4.2.
[13 Oct 2009 16:57]
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/commits/86709 2898 Gleb Shchepa 2009-10-13 Backport of fix to bug #33629 into mysql-next-mr-bugfixing. Bug #33629: last_day function can return null, but has 'not null' flag set for result LAST_DAY and MAKEDATE functions are documented as returning NULL value, but actually they was implemented as returning NOT NULL typed values. That caused a confusing error "ERROR 1048 (23000): Column '...' cannot be null" on queries like: SELECT 1 FROM (SELECT LAST_DAY('0')) a; @ mysql-test/r/func_sapdb.result Updated test case for bug #33629. @ mysql-test/r/func_time.result Updated test case for bug #33629. @ mysql-test/r/type_date.result Added test case for bug #33629. @ mysql-test/t/type_date.test Added test case for bug #33629. @ sql/item_timefunc.h Bug #33629: last_day function can return null, but has 'not null' flag set for result 1. The Item_func_makedate::fix_length_and_dec method has been modified to declare MAKEDATE() as a function returning nullable value. 2. The Item_func_last_day::fix_length_and_dec method has been overloaded for the same purpose.
[31 Oct 2009 8:18]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:gshchepa@mysql.com-20091013165330-kitckjkhvqe3j64r) (merge vers: 6.0.14-alpha) (pib:13)
[31 Oct 2009 17:30]
Paul DuBois
Noted in 6.0.14 changelog. Setting report to NDI pending push to 5.5.x
[12 Nov 2009 8:21]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091102100915-a2nbfxaqprpgptfw) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 19:01]
Paul DuBois
Noted in 5.5.0 changelog.
Description: I expected to get a resultset with 1 row of null, and a warning, but instead the second query fails: 1) normal date used mysql> select * from (select last_day('20070707')) a; Field 1: `last_day('20070707')` Catalog: `def` Database: `` Table: `a` Org_table: `` Type: DATE Collation: binary (63) Length: 10 Max_length: 10 Decimals: 0 Flags: NOT_NULL BINARY +----------------------+ | last_day('20070707') | +----------------------+ | 2007-07-31 | +----------------------+ 1 row in set (0.02 sec) 2) invalid date used mysql> select 1 from (select (last_day('0')) `c`) `a`; ERROR 1048 (23000): Column 'c' cannot be null mysql> How to repeat: select 1 from (select (last_day('0')) `c`) `a`; Suggested fix: why is the NOT NULL flag set, when last_day is meant to return null when it encounters bad data?