MySQL Bugs: #48734: column_info does not return columns in proper order for DBD::mysql

Bug #48734	column_info does not return columns in proper order for DBD::mysql
Submitted:	12 Nov 2009 15:22	Modified:	15 Feb 2013 17:21
Reporter:	Hans Ginzel	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S3 (Non-critical)
Version:	4.005, 4.020	OS:	Any (Windows XP, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	column_info, dbi, metadata, order, perl

Description:
Function DBI->column_info() does not return columns in ORDINAL_POSITION (create order) as described in http://perldoc.net/DBI.pm#column_info.

How to repeat:
create table temp.t1 (
	id	int,
	a	char(1),
	b	char(2),
	c	char(3)
);

#!/usr/bin/perl

my $user	= 'hginzel';
my $my_password	= '*******';

use DBI;
my $db = DBI->connect("dbi:mysql:temp", $user, $my_password);
warn DBI->VERSION;
warn DBD::mysql->VERSION;

my $t = 't1';
(my $ci = $db->column_info(undef, undef, $t, '%'))->bind_col(4, \my $c);
my @columns = ();
push @columns, $c while $ci->fetch;

warn join "\n", @columns;

It returns

1.607 at c.pl line 29.
4.005 at c.pl line 30.
c
a
b
id

But expected is 

id
a
b
c

C:\>perl --version

This is perl, v5.10.0 built for MSWin32-x86-multi-thread
(with 5 registered patches, see perl -V for more detail)

Copyright 1987-2007, Larry Wall

Binary build 1004 [287188] provided by ActiveState http://www.ActiveState.com
Built Sep  3 2008 13:16:37

mysql> show columns from temp.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| a     | char(1) | YES  |     | NULL    |       |
| b     | char(2) | YES  |     | NULL    |       |
| c     | char(3) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>select ORDINAL_POSITION, COLUMN_NAME
from  information_schema.columns
where table_schema='temp' AND table_name='t1'
order by ORDINAL_POSITION;
+------------------+-------------+
| ORDINAL_POSITION | COLUMN_NAME |
+------------------+-------------+
|                1 | id          |
|                2 | a           |
|                3 | b           |
|                4 | c           |
+------------------+-------------+
4 rows in set (0.01 sec)

Suggested fix:
Correct the order. Use order by ORDINAL_POSITION.

Please, try to repeat with a newer version of DBD::mysql, 4.013, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Same here with DBD::mysql version 4.020 and perl 5.14.1

Verified on Mac OS X:

macbook-pro:5.5 openxs$ perl5.12 bug48734.pl 
1.616 at bug48734.pl line 8.
4.020 at bug48734.pl line 9.
c
a
b
id at bug48734.pl line 16.
macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show columns from t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| a     | char(1) | YES  |     | NULL    |       |
| b     | char(2) | YES  |     | NULL    |       |
| c     | char(3) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)

We don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql

I re-submitted your report to https://rt.cpan.org/Public/Bug/Display.html?id=83358
Please subscribe to the new report on CPAN and work with DBD::mysql developers in case if they need additional details.