List:General Discussion« Previous MessageNext Message »
From:John Cartwright Date:May 30 2001 2:40pm
Subject:problems with concat, group by on doubles
View as plain text  
I'm noticing some puzzling behavior involving the concat function and
group by operation when used on a double type column in Mysql. Either
one by themselves is fine, but together...

mysql> select datetime_1965_secs from sample where datetime_1965_secs <
1009843300;
+--------------------+
| datetime_1965_secs |
+--------------------+
|         1009843200 |
|         1009843260 |
+--------------------+
2 rows in set (0.01 sec)


// works OK
mysql> select round(datetime_1965_secs,0) T,count(*) from sample where
datetime_1965_secs < 1009843300 group by T;
+------------+----------+
| T          | count(*) |
+------------+----------+
| 1009843200 |        1 |
| 1009843260 |        1 |
+------------+----------+
2 rows in set (0.01 sec)


// does not work OK
mysql> select concat(round(datetime_1965_secs,0)) T,count(*) from sample
where
datetime_1965_secs < 1009843300 group by T;
+----------+----------+
| T        | count(*) |
+----------+----------+
| 10098432 |        2 |
+----------+----------+
1 row in set (0.00 sec)


// works OK
mysql> select concat(round(datetime_1965_secs,0)) T from sample where
datetime_1965_secs < 1009843300;
+------------+
| T          |
+------------+
| 1009843200 |
| 1009843260 |
+------------+
2 rows in set (0.01 sec)


(The concat() is there to allow me to compare floats - I understand that
in this case where the precision is 0 and round() returns an BIGINT that
the concat() is unnecessary.)

Can anyone help me to understand why I'm third query listed above
doesn't work?

Thanks!


-- john

-- 
===============================================================================
John Cartwright
Professional Research Assistant / Associate Scientist
CIRES, SEG/NGDC/NOAA
(303) 497-6284
jcartwright@stripped
===============================================================================
Thread
problems with concat, group by on doublesJohn Cartwright30 May