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.