php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #46508 [PATCH]: getColumnMeta returns 'LONG','VAR_STRING','BLOB' as php native_type
Submitted: 2008-11-06 15:02 UTC Modified: 2014-01-01 12:51 UTC
Votes:7
Avg. Score:4.7 ± 0.7
Reproduced:7 of 7 (100.0%)
Same Version:2 (28.6%)
Same OS:4 (57.1%)
From: marques at displague dot com Assigned:
Status: Open Package: PDO MySQL
PHP Version: 5.2.9 OS: *
Private report: No CVE-ID:
Have you experienced this issue?
Rate the importance of this bug to you:

 [2008-11-06 15:02 UTC] marques at displague dot com
Description:
------------
Using the pdo_mysql driver, when I do a getColumnMeta on an int(11) NULL field, the native_type returned is 'LONG'.  I expect 'integer'.

Also, the pdo_type returned on this column is PDO::PARAM_STR, not PDO::PARAM_INT as I would expect.

http://php.net/manual/en/pdostatement.getcolumnmeta.php defines native_type as "The PHP native type used to represent the column value.".  The example on that page shows a return value of 'integer'.. This would seem to mesh with the PHP types defined here:
http://php.net/manual/en/function.gettype.php

If this is just a documentation bug due to recent changes, perhaps someone should consider leaving native_type intact as php native type, and using 'type' or something for these new values.

Reproduce code:
---------------
// MySQL: create table `table` (`i` int default null, `s` varchar(32), `b` text, `t` timestamp, `d` datetime);

$pdo= new PDO("mysql:host=localhost;dbname=somedb","user","pass");
$stmt=$pdo->query("SELECT * from `table` limit 0"); 
for($i=0;$i<5;$i++) print_r($stmt->getColumnMeta($i));


Expected result:
----------------
Array
(
    [native_type] => LONG
    [flags] => Array
        (
        )

    [table] => table
    [name] => i
    [len] => 11
    [precision] => 0
    [pdo_type] => 1
)
Array
(
    [native_type] => VAR_STRING
    [flags] => Array
        (
        )

    [table] => table
    [name] => s
    [len] => 32
    [precision] => 0
    [pdo_type] => 2
)
Array
(
    [native_type] => BLOB
    [flags] => Array
        (
            [0] => blob
        )

    [table] => table
    [name] => b
    [len] => 65535
    [precision] => 0
    [pdo_type] => 2
)
Array
(
    [native_type] => TIMESTAMP
    [flags] => Array
        (
            [0] => not_null
        )

    [table] => table
    [name] => t
    [len] => 19
    [precision] => 0
    [pdo_type] => 2
)
Array
(
    [native_type] => DATETIME
    [flags] => Array
        (
        )

    [table] => table
    [name] => d
    [len] => 19
    [precision] => 0
    [pdo_type] => 2
)


Actual result:
--------------
Array
(
    [native_type] => integer
    [flags] => Array
        (
        )

    [table] => table
    [name] => i
    [len] => 11
    [precision] => 0
    [pdo_type] => 1
)
Array
(
    [native_type] => string
    [flags] => Array
        (
        )

    [table] => table
    [name] => s
    [len] => 32
    [precision] => 0
    [pdo_type] => 2
)
Array
(
    [native_type] => string
    [flags] => Array
        (
            [0] => blob
        )

    [table] => table
    [name] => b
    [len] => 65535
    [precision] => 0
    [pdo_type] => 2
)
Array
(
    [native_type] => string
    [flags] => Array
        (
            [0] => not_null
        )

    [table] => table
    [name] => t
    [len] => 19
    [precision] => 0
    [pdo_type] => 2
)
Array
(
    [native_type] => string
    [flags] => Array
        (
        )

    [table] => table
    [name] => d
    [len] => 19
    [precision] => 0
    [pdo_type] => 2
)


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-11-06 15:05 UTC] marques at displague dot com
I put my expected and actual results in the wrong boxes.

The capitalized native_types are not what I would expect because they are not php native types per the PHP documentation on types and the getColumnMeta example.
 [2008-11-07 15:49 UTC] felipe@php.net
Patch: http://felipe.ath.cx/diff/bug46508.diff (5.3)
 [2008-11-07 16:02 UTC] marques at displague dot com
The values that are currently being reported may belong in the getmetacolumn array member 'driver:decl_type' (per the getColumnMeta documentation example).
 [2008-11-07 16:24 UTC] felipe@php.net
Hi Marques, good observation! I've updated the patch. ;)

Thanks.
 [2009-04-10 14:01 UTC] php at displague dot com
This should probably be the topic of another bug, but TINYINT doesn't return a native_type (I'm guessing because TINY is used everywhere, but not TINYINT - maybe another constant is needed).

mysql://user@host/db> show columns from table like 'disable' \G;
*************************** 1. row ***************************
  Field: disable
   Type: tinyint(4)
   Null: NO
    Key: 
Default: 0
  Extra: 
1 row in set (0.00 sec)

getColumnMeta (php 5.2.9) returns:
Array
(
    [flags] => Array
        (
            [0] => not_null
        )

    [table] => promo_item
    [name] => disable
    [len] => 4
    [precision] => 0
    [pdo_type] => 2
)

native_type is missing.

Is there any chance this correction will make it into 5.2.x?
 [2009-06-29 09:42 UTC] uwendel at mysql dot com
Why would I bother about a function that has no specification? Without a specification there is no definition of how things should go and there is no bug - by definition...


"Warning

This function is EXPERIMENTAL. The behaviour of this function, its name, and surrounding documentation may change without notice in a future release of PHP. This function should be used at your own risk. ",
http://de.php.net/manual/en/pdostatement.getcolumnmeta.php

There needs to be a proper PDO spec before one can decide about any bug report. IMHO the bug report should be closed as bogus.
 [2009-06-29 10:18 UTC] marques at displague dot com
I stated in the bug report that the return values do not match up with the documentation.  The docs state (pretty clearly):

http://php.net/manual/en/pdostatement.getcolumnmeta.php:

native_type  	The PHP native type used to represent the column value.

driver:decl_type 	The SQL type used to represent the column value in the database. If the column in the result set is the result of a function, this value is not returned by PDOStatement::getColumnMeta(). 

pdo_type  	The type of this column as represented by the PDO::PARAM_* constants.


The problems are that (per the docs) native_type is missed for some types (TINYINT) and that the native_type values currently returned should be in driver:decl_type, and PHP native types should be returned for native_type instead.
 [2010-05-11 13:13 UTC] uw@php.net
-Status: Assigned +Status: Open -Assigned To: mysql +Assigned To:
 [2010-05-11 13:13 UTC] uw@php.net
Whatever the docs say, what counts is "EXPERIMENTAL" = "TENTATIVE" = "UNDEFINED". It is irrelevant how meaningful and sensible your suggestion is. 

If you want any changes to PDO, please write an RFC/discuss on internal/do whatever the current procedure is to get the "EXPERIMENTAL" removed. Specification via bug reports does not make much sense to me. You fix one and break another causing a bug report stating just the opposite and, for example, claiming you break backwards compatibility. 

The underlying issue is the lack of a clear definition. The issue is the "EXPERIMENTAL". 

I do understand how annoying the answer is. But please respect that "specification via bug reports" is not a good approach and sometimes it is better to go a step back and do it right: fix PDO as such.

Whoever wants, may play the patch-and-work-without-specs game. But I won't do it. Its an endless game leading nowhere: leaving bug open, unassigning mysql (at least as long as there is no clear specs).
 [2013-06-12 04:20 UTC] ssufficool@php.net
-Assigned To: +Assigned To: mysql
 [2013-06-12 15:02 UTC] johannes@php.net
-Assigned To: mysql +Assigned To:
 [2013-06-12 15:02 UTC] johannes@php.net
The complete topic is more complex and needs a more specific specification. I agree to the fact that long is no good choice. For the MySQL case mind that, if PDO is using emulation of prepared statements, which it does by default all data is returned as string, as that's the way it comes over the wire. when using native prepared statements the type used might change within a column (use a column with an integer type larger than PHP_INT_MAX, then values in PHP's integer/long range might be returned as PHP int/long (depends a bit on other factors, i.e. mysqlnd vs libmysql) else as string.

So unless we define this for all drivers properly we (MySQL) will not change the behavior.
 [2014-01-01 12:51 UTC] felipe@php.net
-Package: PDO related +Package: PDO MySQL
 
PHP Copyright © 2001-2017 The PHP Group
All rights reserved.
Last updated: Tue Mar 28 19:01:48 2017 UTC