MySQL Bugs: #56350: View of Union with literals compiles with wrong data types.

Bug #56350	View of Union with literals compiles with wrong data types.
Submitted:	29 Aug 2010 20:52	Modified:	30 Aug 2010 4:19
Reporter:	Chris Handsel	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Other (Win XP)
Assigned to:		CPU Architecture:	Any

Description:
Similar to bug report 19714, When creating a VIEW with a UNION query with literals in one of the SELECT lists, the view compiles with BIGINT data types even if only INTs are provided as the data type in the source tables.  The literals seem to take priority in typing the data even though they are less explicit than the variable definitions.

How to repeat:
CREATE TABLE t (i INT, j INT);

CREATE VIEW vt AS 
SELECT i, j FROM t
UNION ALL
SELECT 0 As i, 0 As j FROM t;

This compiles as BIGINT for both i and j even though the most explicit data type definition comes from the table definition. The order of the SELECT statements does not matter.

Suggested fix:
Use the most explicit data type possible on columns when compiling VIEWs.

Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.48-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t (i INT, j INT);
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> CREATE VIEW vt AS
    -> SELECT i, j FROM t
    -> UNION ALL
    -> SELECT 0 As i, 0 As j FROM t;
Query OK, 0 rows affected (0.11 sec)

mysql> desc vt;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| i     | bigint(20) | YES  |     | NULL    |       |
| j     | bigint(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

This appears valid. The result data type should
be broad enough to hold any possible values from
both sides of the UNION, but no broader. The
"minimal common supertype" in this case is INT
since in simpler cases (e.g. CREATE TABLE t AS
SELECT 0) we would take 0 to be an INT.