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

Reply via email to