Incorrect field from a view
Hi,
I have view that is from a a 4 table union.
CREATE VIEW V1 AS
SELECT f1,f2,f3, null as f4, null as f5, null as f6, null as f7 from S1.T1
JOIN ...
UNION ALL
SELECT f1,f2,f3, f4, null as f5, null as f6 from S2.T1
JOIN ...
UNION ALL
SELECT f1,f2,f3, f4, f5, null as f6, null as f7 from S3.T1
JOIN ...
UNION ALL
SELECT f1,f2,f3, f5, f5, f6, f7 from S4.T1;
JOIN ...
The 4 databases are all iterations of the same table. Each iteration is more dependent on FK and having more columns.
Until recently this was not a problem. Over the weekend we added a F8 to all of the SELECTS (null as F8) and added a F8 to the Schema4 with a related JOIN.
Now when I try to
SELECT * FROM V1; -- ALL WORKS, all columns shows correct values
CREATE TABLE TMP FROM
SELECT * FROM V1; -- FAILS WITH 1292
Error Code: 1292. Truncated incorrect DOUBLE value: '006D000000lCZUfIAO'
which is a content of a column/row in the query.
I have tried wrapping the particular column in CAST('' as CHAR(18)) as C1 in the original view creation where the field does not exist.
Looking at the view's structure in mysql workbench the column presents as varchar(18).
Any ideas?