Bug #23974 DBI::Mysql::column_info() does not return an error when table does not exist
Submitted: 4 Nov 2006 15:00 Modified: 3 Mar 2007 1:37
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:Connectors: DBD::mysql ( Perl ) Severity:S3 (Non-critical)
Version:3.0008_1 OS:Linux (Linux)
Assigned to: Jim Winstead CPU Architecture:Any
Tags: column_info

[4 Nov 2006 15:00] Philip Stoev
Description:
Hello,

When calling my $sth = $dbh->column_info(undef, undef, $table_name, '%') returns an empty result set if the table does not exist. The reason for that is that the return status from issuing the DESCRIBE $table_name statement in column_info() is not checked at all.

Even though the DBI "spec" does not provide any standard on what the return value should be in such case, I assume that returning the statement handle produced by DESCRIBE is much better than returning just an artifically created result set. This enables the caller to call err() , errstr() and so on on the statement in order to determine what went wrong.

How to repeat:
$dbh->column_info(undef, undef, 'table-that-does-not-exist', '%');

Suggested fix:
diff -u -r DBD-mysql-3.0008_1/lib/DBD/mysql.pm dbd-mysql-mine-colinfo/lib/DBD/mysql.pm
--- DBD-mysql-3.0008_1/lib/DBD/mysql.pm 2006-10-16 16:11:04.000000000 +0300
+++ dbd-mysql-mine-colinfo/lib/DBD/mysql.pm     2006-11-04 16:40:30.000000000 +0200
@@ -320,6 +320,11 @@
     local $dbh->{FetchHashKeyName} = 'NAME_lc';
     my $desc_sth = $dbh->prepare("DESCRIBE $table_id");
     my $desc = $dbh->selectall_arrayref($desc_sth, { Columns=>{} });
+
+    if ($desc_sth->err()) {
+        return $desc_sth;
+    }
+
     my $ordinal_pos = 0;
     foreach my $row (@$desc) {
        my $type = $row->{type};
[15 Nov 2006 10:49] MySQL Verification Team
Thank you for the bug report.
[28 Dec 2006 22:51] Jim Winstead
I've applied the patch to the DBD::mysql repository, and it will be included in the next release (probably 4.01). Thanks for the bug report and patch.
[21 Feb 2007 23:07] Tim Bunce
column_info should not return an error if the table doesn't exist.
It should just return an empty resultset.
The DBI 1.54 spec will clarify this.
Please revert the patch.
[22 Feb 2007 0:35] Philip Stoev
With all due respect, I think the spec should be changed. It is better to return more information to the caller, rather than less -- if column_info returns a reference to an empty array, it would be difficult to know why the call failed, which can happen for various reasons.

Also, returning a reference to an empty array will make column_info different from all other calls that return a statement handle. Returning a [] is different from returning an empty result set, and I do not think any code that expects a $sth would behave properly if passed a [].
[22 Feb 2007 0:35] Philip Stoev
With all due respect, I think the spec should be changed. It is better to return more information to the caller, rather than less -- if column_info returns a reference to an empty array, it would be difficult to know why the call failed, which can happen for various reasons.

Also, returning a reference to an empty array will make column_info different from all other calls that return a statement handle. Returning a [] is different from returning an empty result set, and I do not think any code that expects a $sth would behave properly if passed a [].
[22 Feb 2007 0:55] Tim Bunce
The column_info method is, like the other schema metadata methods, based on ODBC:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlcolumns.a...
and should follow that behaviour closely.

Note that the result set includes columns for table_name, table_schema etc. That's because it is, in theory, able to return data for multiple tables in one go.

It is logically a SELECT statement on the INFORMATION_SCHEMA. The fact it isn't in the current driver is an implementation detail that's influencing your perspective.

The column_info method should return an empty result set and not an error for the same reasons that a SELECT that matches no rows should return an empty result set and not an error.

Of course if there's an actual _error_ (other than the non-existance of the table) then it should certainly return an error.

(I'm not sure why you mentioned returing []. It should never return []. Only undef or a statement handle.)
[22 Feb 2007 1:16] Philip Stoev
Yes sorry my fault I am getting back a bless( {}, 'DBI::st' ), and not a [].

Anyway, can we simply pass any error values from the DESCRIBE query to the DBI::Sponge statement handle that is being created? This way you will get both an empty result set and the original error values.
[22 Feb 2007 1:42] Tim Bunce
I'd just check for the specific table-not-found error from describe.
If you get that error then return an empty result set.
If you get any other error then return the error.
Otherwise return a resultset for the columns.
[25 Feb 2007 12:54] Patrick Galbraith
I'm working on a fix for this and plean to put out a release this coming week.
[1 Mar 2007 5:08] Patrick Galbraith
Will be releasing 4.002 March 1, 2007 to fix this bug.
[3 Mar 2007 1:37] Jim Winstead
Patrick fixed this in 4.002.