From: sasha Date: July 28 2000 10:45pm Subject: Re: Query with having does not work properly. List-Archive: http://lists.mysql.com/mysql/45904 Message-Id: <39820D16.C5C034FB@mysql.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Peter Zaitsev wrote: > > > Your patch looks ok, but I have now split the date add/sub code > > to make it faster and shorter; This will be in 3.23.22 (I don't > > submit a patch now as I want to test this throughly before releasing it!) > > I'm not shure if this is allready a source of this problems, but currently > such thing is happening: > > mysql> select date_add(0, interval 7 day)<20000101; > +--------------------------------------+ > | date_add(0, interval 7 day)<20000101 | > +--------------------------------------+ > | NULL | > +--------------------------------------+ > 1 row in set (0.01 sec) > > mysql> select 0 +-------------------------------------+ > | 0 +-------------------------------------+ > | 1 | > +-------------------------------------+ > 1 row in set (0.00 sec) This is correct behaviour. The first argument to date_add()/date_sub() has to be a date. If it is not, MySQL will try to convert it to a date - 0 does not convert to a date so date_add(0, interval 7 day) evaluates to NULL. In the second case, 20000101 can be converted to a valid date: 2000-01-01 -- Sasha Pachev +------------------------------------------------------------------+ | ____ __ _____ _____ ___ http://www.mysql.com | | /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sasha Pachev | | /*/ /*/ /*/ \*\_ |*| |*||*| sasha@stripped | | /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Provo, Utah, USA | | /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ | | ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ | | /*/ \*\ Developers Team | +------------------------------------------------------------------+