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] johannes@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