|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (
|
id int(11) NOT NULL PRIMARY KEY,
|
country varchar(32),
|
code int(11) default NULL
|
);
|
INSERT INTO t1 VALUES (1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100);
|
DROP VIEW IF EXISTS v1;
|
CREATE VIEW v1 AS SELECT * FROM t1;
|
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 AS
|
SELECT code, COUNT(DISTINCT country), MAX(id) FROM t1 GROUP BY code ORDER BY MAX(id);
|
SHOW CREATE TABLE t2;
|
returns
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`code` int(11) DEFAULT NULL,
|
`COUNT(DISTINCT country)` bigint(21) NOT NULL,
|
`MAX(id)` int(11)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice, MAX(id) was correctly created as INT(11) when doing CREATE..SELECT..FROM t1.
Now if I do the same with v1 instead of t1:
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 AS
|
SELECT code, COUNT(DISTINCT country), MAX(id) FROM v1 GROUP BY code ORDER BY MAX(id);
|
SHOW CREATE TABLE t2;
|
it returns
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`code` int(11) DEFAULT NULL,
|
`COUNT(DISTINCT country)` bigint(21) NOT NULL,
|
`MAX(id)` bigint(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice, MAX(id) was erroneously created as BIGINT(11).
The problem happens because Item_sum::create_tmp_field() does not adjust the data type from length.
|