Bug #19714 COALESCE() returns wrong datatype in VIEWs
Submitted: 11 May 2006 9:13 Modified: 24 Jul 2006 17:29
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.22-BK, 5.0.21 OS:Any (any)
Assigned to: Igor Babaev

[11 May 2006 9:13] Beat Vontobel
Description:
A COALESCE() in the SELECT list of a VIEW returns a BIGINT even if only INTs are provided as arguments to the function. This affects VIEWS of both ALGORITHM TEMPTABLE and MERGE. (Other datatypes or functions might also be affected, just didn't have the time to check all possible combinations.)

This is especially bad in combination with bug #19077 which will be triggered by this implicit conversion and further VIEWs on the COALESCE() VIEW will return wrong results.

How to repeat:
localhost-test [bvontob]> CREATE TABLE t (i INT, j INT);
Query OK, 0 rows affected (0.18 sec)

localhost-test [bvontob]> CREATE ALGORITHM=TEMPTABLE VIEW vt AS SELECT COALESCE(i, j) FROM t;
Query OK, 0 rows affected (0.11 sec)

localhost-test [bvontob]> CREATE ALGORITHM=MERGE VIEW vm AS SELECT COALESCE(i, j) FROM t;
Query OK, 0 rows affected (0.01 sec)

localhost-test [bvontob]> DESCRIBE vt;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| COALESCE(i, j) | bigint(11) | YES  |     | NULL    |       | 
+----------------+------------+------+-----+---------+-------+
1 row in set (0.17 sec)

localhost-test [bvontob]> DESCRIBE vm;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| COALESCE(i, j) | bigint(11) | YES  |     | NULL    |       | 
+----------------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)

Suggested fix:
Use the most specific datatype possible on computed columns in VIEWs.
[11 May 2006 9:16] Beat Vontobel
Just to show that this is actually only a VIEW problem. In other places COALESCE() returns the correct datatype:

localhost-test [bvontob]> CREATE TABLE t2 SELECT COALESCE(i, j) FROM t;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

localhost-test [bvontob]> DESCRIBE t2;
+----------------+---------+------+-----+---------+-------+
| Field          | Type    | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| COALESCE(i, j) | int(11) | YES  |     | NULL    |       | 
+----------------+---------+------+-----+---------+-------+
1 row in set (0.10 sec)
[11 May 2006 10:24] Valerii Kravchuk
Verified just as described with 5.0.22-BK build on Linux:

mysql> CREATE ALGORITHM=TEMPTABLE VIEW vt AS SELECT
    -> COALESCE(i, j) FROM t;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE ALGORITHM=MERGE VIEW vm AS SELECT COALESCE(i,
    -> j) FROM t;
Query OK, 0 rows affected (0.02 sec)

mysql> desc vt\G
*************************** 1. row ***************************
  Field: COALESCE(i, j)
   Type: bigint(11)
   Null: YES
    Key:
Default: NULL
  Extra:
1 row in set (0.00 sec)

mysql> desc vm\G
*************************** 1. row ***************************
  Field: COALESCE(i,
j)
   Type: bigint(11)
   Null: YES
    Key:
Default: NULL
  Extra:
1 row in set (0.01 sec)

mysql> CREATE TABLE tv AS SELECT COALESCE(i, j) FROM t;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tv\G
*************************** 1. row ***************************
  Field: COALESCE(i, j)
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
1 row in set (0.01 sec)

This difference among table and views column type is a bug.
[11 Jul 2006 1:49] 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/9022
[14 Jul 2006 3:47] 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/9159
[19 Jul 2006 0:11] Evgeny Potemkin
DESCRIBE returned the type BIGINT for a column of a view if the column
was specified by an expression over values of the type INT.
      
E.g. for the view defined as follows:
  CREATE VIEW v1 SELECT COALESCE(f1,f2) FROM t1
DESCRIBE returned type BIGINT for the only column of the view if f1,f2 are
columns of the INT type.
At the same time DESCRIBE returned type INT for the only column of the table
defined by the statement:
  CREATE TABLE t2 SELECT COALESCE(f1,f2) FROM t1.

Fixed in 5.0.25
[24 Jul 2006 17:29] Paul Dubois
Noted in 5.0.25 changelog.