MySQL Bugs: #91486: Wrong column type , view , binary

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

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!

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.

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!

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.

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!

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.

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    |       |
+-------+------------+------+-----+---------+-------+

Sivert,

Thank you very much .....

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.

Thank you, Paul ......