Bug #91486 Wrong column type , view , binary
Submitted: 29 Jun 2018 7:21 Modified: 1 Aug 2019 16:45
Reporter: Valentina Rattazzi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[29 Jun 2018 7:21] Valentina Rattazzi
Description:
I'm migrate database from mysql 5.7 to 8
I found on problem with view , i getting on error from my application : "Wrong column type in ..... Found: binary, expected: CHAR" in the view on database

How to repeat:
I have a this  view :

CREATE VIEW `view` AS
    SELECT 
        `columnA`.`code` AS `area_code`,
        'County' AS `type`,
        NULL AS `parent_code`
    FROM
        `tableA` 
    UNION 
 SELECT 
        `columnA_B`.`code` AS `area_code`,
        'Region' AS `type`,
        `columnB_A`.`county_code` AS `parent_area_code`
    FROM
        `tableB`;

 
 SHOW FIELDS
FROM view;

the field at "parent_code" return binary(0) instead char(3).

In the 5.7 version on the contrary return char(3).

Suggested fix:
Invert select on the union statment and work for me!
[29 Jun 2018 12:54] MySQL Verification Team
Hi,

Thank you for your bug report.

In order to proceed further, we do need some further info from you.

What are the exact releases of 5.7 and 8.0 that you are using ???

What are the full structures of the TableA and TableB ???

Give us the first 10 rows from each table.

Do you have the same results if you use UNION ALL instead of UNION ???

If we correctly understood you, you get different result type if first node is dealing with TableB instead of TableA ???  This is understandable, since the result types are always derived from the first table. Further tables simply have to fit their  column and data types into definitions from the first table. In some cases , it is possible to adjust result types to other tables, but we have to have all data that we asked for in order to conclude what is the matter.

It is not hard to understand that the problem comes from NULL, which does not have ANY type, so it seems that 8.0 might have the correct behaviour.  it depends on whether SQL standard has changed between 5.7 and 8.0.

Anyway, we do require your feedback.
[29 Jun 2018 13:44] Valentina Rattazzi
Hi,
thank for your response.

I'm Use MySQL Community Server 5.7.22 and MySQL Community Server 8.0.11 version.

this is my view:
CREATE VIEW `area` AS
    SELECT 
        `location`.`code` AS `area_code`,
        `location`.`name` AS `name`,
        'County' AS `type`,
        `location`.`country_code` AS `country_code`,
        NULL AS `parent_area_code`,
        NULL AS `parent_area_name`
    FROM
        `location` 
    UNION SELECT 
        `locality`.`locality_code` AS `area_code`,
        `locality`.`locality_description` AS `name`,
        'Region' AS `type`,
        `locality`.`country_code` AS `country_code`,
        `locality`.`county_code` AS `parent_area_code`,
        `locality`.`county_name` AS `parent_area_name`
    FROM
        `locality`;

this is data of my "table A":
mysql> select * from location limit 10;
+------+--------------------------+----------------+--------------+
| code | name                     | country_name   | country_code |
+------+--------------------------+----------------+--------------+
| ABE  | Aberdeen and Shire       | United Kingdom | GB           |
| ALE  | All areas - England      | United Kingdom | GB           |
| ALL  | All areas-Ireland        | Ireland        | IE           |
| ALS  | All areas-Scotland       | United Kingdom | GB           |
| ANG  | Angus and Dundee         | United Kingdom | GB           |
| ANT  | Antrim                   | Ireland        | IE           |
| ARG  | Argyll and Oban          | United Kingdom | GB           |
| ARM  | Armagh                   | Ireland        | IE           |
| AVI  | Avimore and Avimore Area | United Kingdom | GB           |
| AVO  | Avon                     | United Kingdom | GB           |
+------+--------------------------+----------------+--------------+

this is data of my "table B":
mysql> select * from locality limit 10;
+---------------+-------------+-------------+------------------------+--------------+
| locality_code | county_code | county_name | locality_description   | country_code |
+---------------+-------------+-------------+------------------------+--------------+
|            73 | ANT         | Antrim      | Antrim                 | IE           |
|            74 | ANT         | Antrim      | Belfast Area           | IE           |
|            75 | ANT         | Antrim      | Belfast City Centre    | IE           |
|            76 | ANT         | Antrim      | Giant's Causeway Area  | IE           |
|            77 | ANT         | Antrim      | Greater Belfast Region | IE           |
|            78 | ARM         | Armagh      | Armagh/Down            | IE           |
|            79 | CAR         | Carlow      | Carlow                 | IE           |
|            80 | CAV         | Cavan       | Cavan                  | IE           |
|            81 | CLA         | Clare       | Cliffs of Moher        | IE           |
|            82 | CLA         | Clare       | North Clare            | IE           |
+---------------+-------------+-------------+------------------------+--------------+

this is result of type field query:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>MYSQL 5.7
mysql> show fields from area;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| area_code        | varchar(11)  | NO   |     |         |       |
| name             | varchar(100) | NO   |     |         |       |
| type             | varchar(6)   | NO   |     |         |       |
| country_code     | varchar(3)   | YES  |     | NULL    |       |
| parent_area_code | varchar(3)   | YES  |     | NULL    |       |
| parent_area_name | varchar(100) | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>MYSQL 8
mysql> show fields from area;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| area_code        | varchar(11)  | NO   |     |         |       |
| name             | varchar(100) | NO   |     |         |       |
| type             | varchar(6)   | NO   |     |         |       |
| country_code     | varchar(3)   | YES  |     | NULL    |       |
| parent_area_code | binary(0)    | YES  |     | NULL    |       |
| parent_area_name | binary(0)    | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

I did not try to use "UNION ALL", I revert my database version, because of other application problems ....
however I do not discuss the order of the query, but the different behavior on the change mysql version..

Thankyou!
[29 Jun 2018 15:44] MySQL Verification Team
HI,

You did not provide us with structures for TableA and TableB. Meaning, the full CREATE TABLE statements.

Also, in order to reply to your question, we need to know the behaviour when table order is reversed, as change in result field types can be caused by anyone of the many changes between 5.7 and 8.0. For the same reason we need the behaviour of UNION ALL. That would change our focus from one area, like distinct filtering, to another, like column type matching.

That is all that we need from you right now.
[29 Jun 2018 23:27] Valentina Rattazzi
these are the statment of tables mentioned:

CREATE TABLE `location` (
  `code` varchar(3) NOT NULL,
  `name` varchar(100) NOT NULL,
  `country_name` varchar(100) DEFAULT NULL,
  `country_code` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `locality` (
  `locality_code` int(11) NOT NULL AUTO_INCREMENT,
  `county_code` varchar(3) NOT NULL,
  `county_name` varchar(100) NOT NULL,
  `locality_description` varchar(100) NOT NULL,
  `country_code` varchar(3) NOT NULL,
  PRIMARY KEY (`locality_code`),
  UNIQUE KEY `locality_description_UNIQUE` (`county_code`,`locality_description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm upgraded version to Mysql  8.. and tried used UNION ALL statment , and unfortunately not work...

i think the unique solution I get invert the query into the view,I have tested and it works!
[2 Jul 2018 12:07] MySQL Verification Team
Hi,

I have analysed deeply changes in our code and this seems to be an unintended, regression bug in our Optimiser.

Verified fully as reported.
[4 Jun 2019 7:33] Sivert Sørumgård
Posted by developer:
 
Simplified test case for repro:

5.7.27:
=================================
mysql> create view v as select null as 'c' union select 'a' as 'c';
mysql> show fields from v;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | varchar(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

mysql> create table test.t as select null as 'c' union select 'a' as 'c';
mysql> show fields from t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | varchar(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

8.0.18:
=================================
mysql> create view v as select null as 'c' union select 'a' as 'c';
mysql> show fields from v;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c     | binary(0) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+

mysql> create table t as select null as 'c' union select 'a' as 'c';
mysql> show fields from t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | varchar(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
[4 Jun 2019 12:34] MySQL Verification Team
Sivert,

Thank you very much .....
[1 Aug 2019 16:45] Paul DuBois
Posted by developer:
 
Fixed in 8.0.18.

INFORMATION_SCHEMA tables and SHOW COLUMNS could produce incorrect
view column types when a query on the view had multiple query blocks
combined with the UNION operator.
[2 Aug 2019 17:44] MySQL Verification Team
Thank you, Paul ......