| Subject: | column_info does not return columns in proper order for DBD::mysql |
Originally reported by Hans Ginzel at http://bugs.mysql.com/bug.php?id=48734
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
Show quoted text
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)
Show quoted textmysql>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.