Bug #66794 Column metadata inconsistent when used against a View
Submitted: 13 Sep 2012 2:39 Modified: 13 Sep 2012 14:52
Reporter: Jason Prodonovich Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.63/5.5.29 OS:Linux (Ubuntu 32bit)
Assigned to: CPU Architecture:Any
Tags: column-type-info, metadata, VIEW
Triage: Needs Triage: D2 (Serious)

[13 Sep 2012 2:39] Jason Prodonovich
Description:
When executing a SELECTstatement against a view and using an alias for the view, the 'Table' metadata reported back for a particular column in the SELECT shows the table alias of the underlying view definition, and not the alias for the view itself under certain situations.

I observed this bug when working in PHP using PHP's PDO MySQL driver for data access. I also replicated the issue using the mysql client and the column-type-info option.	

How to repeat:
Execute the following SQL script to build the necessary objects.

--
-- Stand-in structure for view `my_installs`
--
CREATE TABLE IF NOT EXISTS `my_installs` (
`id` char(36)
,`user_id` char(36)
,`script_id` char(36)
,`script_version_id` char(36)
,`script_version_package_id` char(36)
,`name` varchar(35)
,`version` varchar(25)
,`path` varchar(255)
,`url` varchar(255)
,`admin_url` varchar(128)
,`icon` varchar(128)
,`created` datetime
);
-- --------------------------------------------------------

--
-- Table structure for table `scripts`
--

CREATE TABLE IF NOT EXISTS `scripts` (
  `id` char(36) NOT NULL,
  `category_id` char(36) NOT NULL,
  `name` varchar(35) NOT NULL,
  `icon` varchar(128) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `scripts`
--

INSERT INTO `scripts` (`id`, `category_id`, `name`, `icon`, `created`) VALUES
('057de1e0-7b48-11e1-8397-60195b7d6275', '05aff694-7b48-11e1-8397-60195b7d6275', 
'WordPress', 'icon_WordPress.gif', '0000-00-00 00:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `script_installs`
--

CREATE TABLE IF NOT EXISTS `script_installs` (
  `id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `script_version_package_id` char(36) NOT NULL,
  `path` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `script_version_package_id` (`script_version_package_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `script_installs`
--

INSERT INTO `script_installs` (`id`, `user_id`, `script_version_package_id`, 
`path`, `url`, `created`) VALUES
('a6d1342a-7b4d-11e1-8397-60195b7d6275', 'dc038c9e-7b4b-11e1-8397-60195b7d6275', 
'0c14429c-7b48-11e1-8397-60195b7d6275', 'blog1', 'blog1.example.com', '2009-06-
15 12:43:30'),
('a6d134ac-7b4d-11e1-8397-60195b7d6275', 'dc038c9e-7b4b-11e1-8397-60195b7d6275', 
'0c16aa6e-7b48-11e1-8397-60195b7d6275', 'blog2', 'blog2.example.com', '2009-06-
15 12:15:10'),
('a880554e-7b4d-11e1-8397-60195b7d6275', 'dc038c9e-7b4b-11e1-8397-60195b7d6275', 
'0c16aa6e-7b48-11e1-8397-60195b7d6275', 'blog3', 'blog3.example.com', '2010-06-
28 22:27:48'),
('ad7aac3e-7b4d-11e1-8397-60195b7d6275', 'dc038c9e-7b4b-11e1-8397-60195b7d6275', 
'0c14429c-7b48-11e1-8397-60195b7d6275', 'blog4', 'blog4.example.com', '2010-06-
29 18:19:46'),
('ae92abf8-7b4d-11e1-8397-60195b7d6275', 'dc038c9e-7b4b-11e1-8397-60195b7d6275', 
'0c16aa6e-7b48-11e1-8397-60195b7d6275', 'blog5', 'blog5.example.com', '2010-10-
04 19:12:28'),
('c71a2368-7b4d-11e1-8397-60195b7d6275', 'dc038c9e-7b4b-11e1-8397-60195b7d6275', 
'0c144120-7b48-11e1-8397-60195b7d6275', 'blog6', 'blog6.example.com', '2011-11-
07 22:26:38'),
('c71c1c36-7b4d-11e1-8397-60195b7d6275', 'dc038c9e-7b4b-11e1-8397-60195b7d6275', 
'0c144120-7b48-11e1-8397-60195b7d6275', 'blog7', 'blog7.example.com', '2011-11-
08 09:26:58');

-- --------------------------------------------------------

--
-- Table structure for table `script_versions`
--

CREATE TABLE IF NOT EXISTS `script_versions` (
  `id` char(36) CHARACTER SET utf8 NOT NULL,
  `script_id` char(36) CHARACTER SET utf8 NOT NULL,
  `version` varchar(25) CHARACTER SET utf8 NOT NULL,
  `admin_url` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `script_id` (`script_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `script_versions`
--

INSERT INTO `script_versions` (`id`, `script_id`, `version`, `admin_url`, 
`created`) VALUES
('0c02e286-7b48-11e1-8397-60195b7d6275', '057de1e0-7b48-11e1-8397-60195b7d6275', 
'3.2.1', 'wp-admin', '2011-12-01 10:15:52'),
('0c050624-7b48-11e1-8397-60195b7d6275', '057de1e0-7b48-11e1-8397-60195b7d6275', 
'3.3.1', 'wp-admin', '2012-02-21 15:12:01');

-- --------------------------------------------------------

--
-- Table structure for table `script_version_packages`
--

CREATE TABLE IF NOT EXISTS `script_version_packages` (
  `id` char(36) NOT NULL,
  `script_version_id` char(36) NOT NULL,
  `process` enum('install','upgrade','import') CHARACTER SET utf8 NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `script_version_id` (`script_version_id`,`process`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `script_version_packages`
--

INSERT INTO `script_version_packages` (`id`, `script_version_id`, `process`, 
`created`) VALUES
('0c144120-7b48-11e1-8397-60195b7d6275', '0c02e286-7b48-11e1-8397-60195b7d6275', 
'install', '2011-12-01 10:15:52'),
('0c14429c-7b48-11e1-8397-60195b7d6275', '0c02e286-7b48-11e1-8397-60195b7d6275', 
'upgrade', '2011-12-01 10:15:53'),
('0c16aa6e-7b48-11e1-8397-60195b7d6275', '0c050624-7b48-11e1-8397-60195b7d6275', 
'upgrade', '2012-01-03 18:05:35');

-- --------------------------------------------------------

--
-- Structure for view `my_installs`
--
DROP TABLE IF EXISTS `my_installs`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW 
`my_installs` AS select `ScriptInstall`.`id` AS `id`,`ScriptInstall`.`user_id` 
AS `user_id`,`Script`.`id` AS `script_id`,`ScriptVersion`.`id` AS 
`script_version_id`,`ScriptVersionPackage`.`id` AS 
`script_version_package_id`,`Script`.`name` AS `name`,`ScriptVersion`.`version` 
AS `version`,`ScriptInstall`.`path` AS `path`,`ScriptInstall`.`url` AS 
`url`,`ScriptVersion`.`admin_url` AS `admin_url`,`Script`.`icon` AS 
`icon`,`ScriptInstall`.`created` AS `created` from (((`script_installs` 
`ScriptInstall` left join `script_version_packages` `ScriptVersionPackage` 
on((convert(`ScriptVersionPackage`.`id` using utf8) = 
`ScriptInstall`.`script_version_package_id`))) left join `script_versions` 
`ScriptVersion` on((`ScriptVersion`.`id` = 
convert(`ScriptVersionPackage`.`script_version_id` using utf8)))) left join 
`scripts` `Script` on((`Script`.`id` = `ScriptVersion`.`script_id`)));

Then, using the mysql client with the column-type-info option enabled, execute the following statements.

mysql> SELECT `MyInstall`.`id`, `MyInstall`.`user_id`, `MyInstall`.`script_id`, `MyInstall`.`path`, `MyInstall`.`url`, `MyInstall`.`created`, `MyInstall`.`version`, `MyInstall`.`admin_url`, `MyInstall`.`name`, `MyInstall`.`icon` FROM `my_installs` AS `MyInstall` where user_id = "dc038c9e-7b4b-11e1-8397-60195b7d6275"  ORDER BY `url` ASC;

I see MySQL report the following meta data for the 3rd column (offset 2 in PDO):

Field   3:  `script_id`
Catalog:    `def`
Database:   `testpdo`
Table:      `MyInstall`
Org_table:  `scripts`
Type:       STRING
Collation:  utf8_general_ci (33)
Length:     108
Max_length: 36
Decimals:   0
Flags:      NO_DEFAULT_VALUE 

Adding a second condition to the WHERE clause does in fact change meta data as you report it.

mysql> SELECT `MyInstall`.`id`, `MyInstall`.`user_id`, `MyInstall`.`script_id`, `MyInstall`.`path`, `MyInstall`.`url`, `MyInstall`.`created`, `MyInstall`.`version`, `MyInstall`.`admin_url`, `MyInstall`.`name`, `MyInstall`.`icon` FROM `my_installs` AS `MyInstall` where user_id = "dc038c9e-7b4b-11e1-8397-60195b7d6275" and script_id = "057de1e0-7b48-11e1-8397-60195b7d6275" ORDER BY `url` ASC;

Note the difference:

Field   3:  `script_id`
Catalog:    `def`
Database:   `testpdo`
Table:      `Script`
Org_table:  `scripts`
Type:       STRING
Collation:  utf8_general_ci (33)
Length:     108
Max_length: 36
Decimals:   0
Flags:      NOT_NULL PRI_KEY NO_DEFAULT_VALUE PART_KEY

In the second case, `Script` was reported as the table instead of `MyInstalls`. This creates a number of problems with PHP development frameworks that use the returned metadata in building result sets.
[13 Sep 2012 7:52] Peter Laursen
I have reported a few issues with metadata as well.

It seems that since MySQL 4.0 metadata usability/reliability/accuracy has detoriated.  Metadata accuracy is utterly important for many types of clients.

Peter
(not a MySQL/Oracle person)
[13 Sep 2012 14:52] Miguel Solorzano
Thank you for the bug report. Verified on 5.5.29 too.

d:\dbs>5.5\bin\mysql -uroot --column-type-info --port=3541
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29-debug Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use viewbug;
Database changed
mysql>  SELECT `MyInstall`.`id`, `MyInstall`.`user_id`, `MyInstall`.`script_id`, `MyInstall`.`path`, `MyInstall`.`url`, `MyInstall`.`created`, `MyInstall`.
`version`, `MyInstall`.`admin_url`, `MyInstall`.`name`, `MyInstall`.`icon` FROM `my_installs` AS `MyInstall` where user_id = "dc038c9e-7b4b-11e1-8397-60195
b7d6275"  ORDER BY `url` ASC;

<CUT>

Field   3:  `script_id`
Catalog:    `def`
Database:   `viewbug`
Table:      `MyInstall`
Org_table:  `scripts`
Type:       STRING
Collation:  cp850_general_ci (4)
Length:     36
Max_length: 36
Decimals:   0
Flags:      NO_DEFAULT_VALUE

<CUT>

mysql>  SELECT `MyInstall`.`id`, `MyInstall`.`user_id`, `MyInstall`.`script_id`, `MyInstall`.`path`, `MyInstall`.`url`, `MyInstall`.`created`, `MyInstall`.
`version`, `MyInstall`.`admin_url`, `MyInstall`.`name`, `MyInstall`.`icon` FROM `my_installs` AS `MyInstall` where user_id = "dc038c9e-7b4b-11e1-8397-60195
b7d6275" and script_id = "057de1e0-7b48-11e1-8397-60195b7d6275" ORDER BY `url` ASC;

<CUT>

Field   3:  `script_id`
Catalog:    `def`
Database:   `viewbug`
Table:      `script`
Org_table:  `scripts`
Type:       STRING
Collation:  cp850_general_ci (4)
Length:     36
Max_length: 36
Decimals:   0
Flags:      NOT_NULL PRI_KEY NO_DEFAULT_VALUE PART_KEY