MySQL Bugs: #2275: 4.0 to 4.1 -> Temp Table displaying REAL Values despite INTEGER Def

Bug #2275	4.0 to 4.1 -> Temp Table displaying REAL Values despite INTEGER Def
Submitted:	4 Jan 2004 14:24	Modified:	19 Jan 2004 15:13
Reporter:	Larry Boehm	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I was using 4.0 and previous versions.

Prior to 4.1, when defining a temporary table with a column of integer, using AVG query would result in a integer number as desired.
In 4.1, no matter what the definition, the query result will come back as a full REAL number 

This occurred immediately after upgrading to 4.1.

How to repeat:
Define a table with some integer data.
Create a temporary table and select data from the first table, put an AVG function on one of the pieces of integer data
Select data from the temporary table, data will come back in REAL format.

Play with the column type of the temporary table.
It will always comeback as a  REAL.

Suggested fix:
None...I assume this is a software bug.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

AVG() function must return floating type result and it always did.

There is no change in this respect between 4.0 and 4.1

Consider folowing sequence of queries:

create temporary table test (i int);
 
insert into test values (2), (4);
 
mysql> select avg(i) from test;
+--------+
| avg(i) |
+--------+
| 3.0000 |
+--------+
1 row in set (0.03 sec)
 
create temporary table test2 select avg(i) from test;
 
select * from test2;
+--------+
| avg(i) |
+--------+
| 3.0000 |
+--------+
1 row in set (0.01 sec)
 
show create table test2;
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| test2 | CREATE TEMPORARY TABLE `test2` (
  `avg(i)` double(17,4) default NULL
) TYPE=MyISAM |
+-------+-------------------------------------------------------------------------------------+
 

You will get same result with 3.23, 4.0, 4.1 and 5.0
The only difference in 4.1 and above will be DEFAULT CHARACTER SET for table, but this is irrelevant to current issue.

If you want to use result of AVG as Integer value you have to convert it to such. Since 4.0 you can use CAST function for dong this, but it is more appropriate to use some of rounding functions to control the conversion.

I was sent an email that this is not a bug. Maybe not, but the enclosed shows a difference in how "my" SQL outputs between 4.1 and earlier versions.

What rounding function are you referring to in the previous response? Please provide an example.

Output on 3.23 and 4.0 is
+---------+-----------+-----------+
| rsn     | daavgtemp | weavgtemp |
+---------+-----------+-----------+
| ABCDEF1 |        46 |        49 |
| ABCDEF2 |        46 |        49 |
+---------+-----------+-----------+

Output on 4.1 is:
+---------+----------------+----------------+
| rsn     | daavgtemp      | weavgtemp      |
+---------+----------------+----------------+
| ABCDEF1 | 46.00000000000 | 49.00000000000 |
| ABCDEF2 | 46.00000000000 | 49.00000000000 |
+---------+----------------+----------------+

SQL That ran the above is (ran from inside a shell script:

#!/bin/bash
mysqladmin -unetify -pnetifydb drop testavg
mysqladmin -unetify -pnetifydb create testavg
mysql -unetify -pnetifydb -e"use testavg;
CREATE TABLE temperature
  ( ndate DATETIME NOT NULL,
    rsn VARCHAR(20) NOT NULL,
    ssn VARCHAR(20) NOT NULL,
    temp VARCHAR(4) NOT NULL
  );
INSERT into  temperature (ndate,rsn,ssn,temp) VALUES ('2003-01-03 12:30:45','ABCDEF1','HIJKLMNO1','45');
INSERT into  temperature (ndate,rsn,ssn,temp) VALUES ('2003-01-03 12:30:46','ABCDEF2','HIJKLMNO2','46');
INSERT into  temperature (ndate,rsn,ssn,temp) VALUES ('2003-01-03 13:30:47','ABCDEF1','HIJKLMNO1','47');
INSERT into  temperature (ndate,rsn,ssn,temp) VALUES ('2003-01-04 12:30:48','ABCDEF2','HIJKLMNO2','48');
INSERT into  temperature (ndate,rsn,ssn,temp) VALUES ('2003-01-04 12:30:49','ABCDEF1','HIJKLMNO1','49');
INSERT into  temperature (ndate,rsn,ssn,temp) VALUES ('2003-01-04 13:30:50','ABCDEF2','HIJKLMNO2','50');

SELECT * FROM temperature;

CREATE TEMPORARY TABLE T1
  SELECT
  LEFT(ndate,13) as nday,
  temperature.rsn as rsn,
  temperature.ssn as ssn,
  AVG(temp) as avgtemp
  FROM temperature
  GROUP BY nday,rsn,ssn;

SELECT * FROM T1;

CREATE TEMPORARY TABLE T2
  SELECT LEFT(nday,10) as nday,rsn,ssn,AVG(avgtemp) as avgtemp
  FROM T1
  GROUP BY nday,rsn,ssn;

SELECT * FROM T2;

CREATE TEMPORARY TABLE DataFinal
SELECT rsn,ssn, 0 as daavgtemp, 0 as weavgtemp
FROM T2
GROUP BY rsn,ssn

UNION ALL

SELECT rsn,ssn, AVG(avgtemp) as daavgtemp,0 as weavgtemp
FROM T2
WHERE nday = '2003-01-03'
GROUP BY rsn,ssn

UNION ALL

SELECT rsn,ssn, 0 as daavgtemp,AVG(avgtemp) as weavgtemp
FROM T2
WHERE nday = '2003-01-04'
GROUP BY rsn,ssn;

SELECT * FROM DataFinal;

SELECT rsn,
SUM(daavgtemp) as daavgtemp,
SUM(weavgtemp) as weavgtemp
FROM DataFinal
GROUP BY rsn;
"

Mistake...Query doesn't work on 3.2.3, only on 4.0 and 4.1, but does have the different output between them.

You can see the difference in temporary table definition after it was created by CREATE ... SELECT statement

SHOW CREATE TABLE DataFinal;

4.0 

CREATE TEMPORARY TABLE `DataFinal` (
  `rsn` varchar(20) NOT NULL default '',
  `ssn` varchar(20) NOT NULL default '',
  `daavgtemp` bigint(1) NOT NULL default '0',
  `weavgtemp` bigint(1) NOT NULL default '0'
) TYPE=MyISAM;

4.1

CREATE TEMPORARY TABLE `DataFinal` (
  `rsn` varchar(20) NOT NULL default '',
  `ssn` varchar(20) NOT NULL default '',
  `daavgtemp` double(24,11) default NULL,
  `weavgtemp` double(24,11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

4.1 format is correct because AVG() returns floating type result. 

4.0 does some assumtions and changes the type depending on on result to bigint.
This is unwanted behaviour as it makes column types in resulting table unpredictable ans is non-standard.

4.1 is much more strict with regard of types in many areas.

For example you use:

LEFT(ndate,10) 

LEFT function takes one string parameter and one integer parameter and returns string. You should not expect LEFT() to return DATE. 

By the way there are at least two better ways to get only DATE part of DATETIME, but with very different result:

DATE_FORMAT() -> result is of string type
CAST (datetime AS DATE) -> result is of DATE type

Actually what you reported is a bug, but a different one.

It is a known deficiency of the UNION implementation in 4.0.
It is documented here: http://www.mysql.com/doc/en/UNION.html

The problem is that UNION determines column types and lengths using the first select only. Try, for example:

SELECT "A" UNION SELECT "BBBBB";
SELECT 1 UNION SELECT 2.345;

Same in your test case - first select does

SELECT rsn,ssn, 0 as daavgtemp, 0 as weavgtemp

and two last columns get the type bigint(1).

This deficiency was fixed in MySQL 4.1.1 - now the type of column is - correctly - defined by all selects in the union. Try my examples (as above) on 4.1.1 to see the difference.

And AVG() was always returning a double. Do SHOW CREATE TABLE T1 and SHOW CREATE TABLE T2 to see that columns created from AVG are double in any version (3.23 included).