From: sasha
Date: July 25 2000 11:17pm
Subject: Re: Query with having does not work properly.
List-Archive: http://lists.mysql.com/mysql/45424
Message-Id: <397E200B.D4575DFB@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Peter Zaitsev wrote:
> 
> [pz@cesar pz]$ mysqladmin version
> mysqladmin  Ver 8.7Distrib 3.23.21beta, for pc-linux-gnu on i686
> TCX Datakonsult AB, by Monty
> 
> Server version          3.23.21beta
> Protocol version        10
> 
> Table:
> 
> CREATE TABLE ct00g00visitors (
>   visitor_id int(10) unsigned DEFAULT '0' NOT NULL,
>   group_id int(10) unsigned DEFAULT '0' NOT NULL,
>   hits int(10) unsigned DEFAULT '0' NOT NULL,
>   sessions int(10) unsigned DEFAULT '0' NOT NULL,
>   ts timestamp(14),
>   PRIMARY KEY (visitor_id,group_id)
> )/*! type=MyISAM */;
> 
> #
> # Dumping data for table 'ct00g00visitors'
> #
> 
> INSERT INTO ct00g00visitors VALUES (465931136,7,2,2,20000318160952);
> INSERT INTO ct00g00visitors VALUES (173865424,2,2,2,20000318233615);
> INSERT INTO ct00g00visitors VALUES (173865424,8,2,2,20000318233615);
> INSERT INTO ct00g00visitors VALUES (173865424,39,2,2,20000318233615);
> INSERT INTO ct00g00visitors VALUES (173865424,7,2,2,20000318233615);
> INSERT INTO ct00g00visitors VALUES (173865424,3,2,2,20000318233615);
> INSERT INTO ct00g00visitors VALUES (173865424,6,2,2,20000318233615);
> INSERT INTO ct00g00visitors VALUES (173865424,60,2,2,20000318233615);
> INSERT INTO ct00g00visitors VALUES (173865424,1502,2,2,20000318233615);
> INSERT INTO ct00g00visitors VALUES (48985536,2,2,2,20000319013932);
> INSERT INTO ct00g00visitors VALUES (48985536,8,2,2,20000319013932);
> INSERT INTO ct00g00visitors VALUES (48985536,39,2,2,20000319013932);
> INSERT INTO ct00g00visitors VALUES (48985536,7,2,2,20000319013932);
> INSERT INTO ct00g00visitors VALUES (465931136,3,2,2,20000318160951);
> INSERT INTO ct00g00visitors VALUES (465931136,119,1,1,20000318160953);
> INSERT INTO ct00g00visitors VALUES (465931136,2,1,1,20000318160950);
> INSERT INTO ct00g00visitors VALUES (465931136,8,1,1,20000318160950);
> INSERT INTO ct00g00visitors VALUES (465931136,39,1,1,20000318160950);
> INSERT INTO ct00g00visitors VALUES (1092858576,14,1,1,20000319013445);
> INSERT INTO ct00g00visitors VALUES (357917728,3,2,2,20000319145026);
> INSERT INTO ct00g00visitors VALUES (357917728,7,2,2,20000319145027);
> 
> 1) Query which returns empty set instead of expected result:
> select visitor_id,max(ts) as mts from ct00g00visitors group by visitor_id
> having mts < DATE_SUB(NOW(),INTERVAL 3 MONTH);
> 
> 2) Query which looks almost the same but works fine:
> select visitor_id,max(ts) as mts from ct00g00visitors group by visitor_id
> having DATE_ADD(mts,INTERVAL 3 MONTH) < NOW();
> 
> +------------+----------------+
> | visitor_id | mts            |
> +------------+----------------+
> |   48985536 | 20000319013932 |
> |  173865424 | 20000318233615 |
> |  357917728 | 20000319145027 |
> |  465931136 | 20000318160953 |
> | 1092858576 | 20000319013445 |
> +------------+----------------+
> 5 rows in set (1.49 sec)

Here is an unofficial patch for this:

diff -u -r1.3 item_timefunc.cc
--- item_timefunc.cc    2000/06/19 23:25:00     1.3
+++ item_timefunc.cc    2000/07/25 23:11:21
@@ -943,6 +943,24 @@
   return 0;
 }

+longlong Item_date_add_interval::val_int()
+{
+  char buf[128];
+  String tmp(buf, sizeof(buf)), *s;
+  s = val_str(&tmp);
+  longlong result = 0;
+  const char* end = s->ptr() + s->length();
+  const char*p;
+
+  for(p = s->ptr(); p < end;p++)
+    {
+      int digit = *p - '0';
+      if(digit <= 9 && digit >= 0)
+       result = result * 10 + digit;
+    }
+
+  return result;
+}

 String *Item_date_add_interval::val_str(String *str)
 {

diff -u -r1.2 item_timefunc.h
--- item_timefunc.h     2000/06/19 23:25:00     1.2
+++ item_timefunc.h     2000/07/25 23:11:22
@@ -349,6 +349,8 @@
   Item_date_add_interval(Item *a,Item *b,interval_type type_arg,bool neg_arg)
     :Item_str_func(a,b),int_type(type_arg), neg(neg_arg) {}
   String *val_str(String *);
+  double val() { return (double)val_int(); }
+  longlong val_int();
   const char *func_name() const { return "date_add_interval"; }
   void fix_length_and_dec() { maybe_null=1; max_length=19; value.alloc(32);}
 };

The problem is that the return value of DATE_ADD() and DATE_SUB() was not
properly getting converted to integer or double.

Monty, I know this is not the most efficient way to do this, but I did not want
to mess with your val_str() - the right way, of course, is to have a function
that will compute the result in time format, and then have val_int() convert it
to longlong, and val_str() convert it to a string.

I have commited the change to CVS anyway to keep the source in sync.

-- 
Sasha Pachev

+------------------------------------------------------------------+
|      ____  __     _____   _____  ___     http://www.mysql.com    |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sasha Pachev            |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     sasha@stripped         |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Provo, Utah, USA        |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                         |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                        |
|             /*/             \*\ Developers Team                  |
+------------------------------------------------------------------+