Edit report at https://bugs.php.net/bug.php?id=61588&edit=1
ID: 61588 User updated by: cdburgess at gmail dot com Reported by: cdburgess at gmail dot com Summary: PDOStatement::getColumnMeta returns original table name from view -Status: Feedback +Status: Assigned Type: Bug Package: PDO related Operating System: Mac OSX PHP Version: 5.3.10 Assigned To: mysql Block user comment: N Private report: N New Comment: PHP v5.3.10 MySQL v5.5.22 Apache v2.2.21 Here is a script that contains all of the information you need to reproduce. The commented parts at the bottom contain all of the schema / data information. Just create your database, setup the PDO access, and run the script. It will provide the queries, descriptions, and getColumnMeta results to show you what I am seeing. Thanks! ---------- SCRIPT BELOW HERE ---------- <?php $connection = new PDO( 'mysql:host=localhost;dbname=testpdo', 'root', 'password' ); $query = "select * from my_installs WHERE user_id = 'dcc87a2c-7b4b-11e1-8397- 60195b7d6275' and script_id = '057de1e0-7b48-11e1-8397-60195b7d6275' LIMIT 1"; echo $query . '<br>' . "\n"; echo 'In this query, you will see the table is reported as expected. (my_installs)'; $result = $connection->query($query); var_dump($result->getColumnMeta(2)); $query = "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` = 'dcc87a2c-7b4b-11e1-8397-60195b7d6275' ORDER BY `url` ASC"; echo $query . '<br>' . "\n"; echo 'With the Alias format of the query and using only the user_id in the where clause, the table Alias is reported.'; $result = $connection->query($query); var_dump($result->getColumnMeta(2)); $query = "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` = 'dcc87a2c-7b4b-11e1-8397-60195b7d6275' AND `script_id` = '057de1e0- 7b48-11e1-8397-60195b7d6275' ORDER BY `url` ASC"; echo $query . '<br>' . "\n"; echo 'When adding the second where clause (using script_id), the Script table alias is reported, but MyInstall or my_installs is expected.'; $result = $connection->query($query); var_dump($result->getColumnMeta(2)); /* -- phpMyAdmin SQL Dump -- version 3.4.10.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Apr 03, 2012 at 07:46 AM -- Server version: 5.5.22 -- PHP Version: 5.3.10 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; -- -- Database: `testpdo` -- DELIMITER $$ -- -- Procedures -- CREATE DEFINER=`root`@`localhost` PROCEDURE `BuildNewInstallStatPartition`() BEGIN DECLARE maxpart_date date; SELECT SUBSTR(MAX(PARTITION_DESCRIPTION) , 2, 19) + INTERVAL 1 MONTH INTO maxpart_date FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'install_stats'; SET @sql := CONCAT('ALTER TABLE install_stats ADD PARTITION (PARTITION p_' , YEAR(maxpart_date), MONTH(maxpart_date) , ' values less than(''' , CAST(maxpart_date as DATETIME) , '''))'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ; -- -------------------------------------------------------- -- -- 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`))); DELIMITER $$ -- -- Events -- CREATE DEFINER=`root`@`localhost` EVENT `e_create_order_stats_partition` ON SCHEDULE EVERY 1 MONTH STARTS '2011-03-15 03:30:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL BuildNewInstallStatPartition$$ DELIMITER ; */ Previous Comments: ------------------------------------------------------------------------ [2012-04-02 09:17:33] johan...@php.net I can't reproduce this. Please provide a reproduce script including table and view definition and information about the MySQL server version you are using. ------------------------------------------------------------------------ [2012-04-01 03:58:58] cdburgess at gmail dot com Description: ------------ The data returned from PDOStatement::getColumnMeta is inconsistent when run on a MySQL view. It works fine against tables, but in some cases when run against a View in MySQL, it returns the original table name from the table that was used to create the view. Expected result: ---------------- I would expect the Table name being returned to be the name of the View, not the original table the view is created from. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=61588&edit=1