Re: table_info('','','','%') cannot return any types

On Tue, Jun 23, 2015 at 09:12:16AM +0100, Martin J. Evans wrote:
> I was recently confirming table_info special cases and discovered the case for getting table_types cannot work.
> 
> table_info('','','','%')
> 
> should return a list of table types but it returns a list of empty strings instead:
> 
> my @types = $h->tables('', '', '', '%');
> print "all types:\n", join("xxx\n", @types), "\n";
> # should output something like:
> # "dbo"
> # "INFORMATION_SCHEMA"
> # "sys"
> # and actually outputs:
> xxx
> xxx
> 
> It seems to be down to the following in DBI.pm:
> 
>     sub tables {
> 	my ($dbh, @args) = @_;
> 	my $sth    = $dbh->table_info(@args[0,1,2,3,4]) or return;
> 	my $tables = $sth->fetchall_arrayref or return;
> 	my @tables;
> 	if ($dbh->get_info(29)) { # SQL_IDENTIFIER_QUOTE_CHAR
>             # problem is missing 3 in the slice below
> 	    @tables = map { $dbh->quote_identifier( @{$_}[0,1,2] ) } @$tables;
> 	}
> 
> My test case missed this because currently it is returning 3 values but they are all ''.
> 
> Adding 3 to the slice fixes the issue but unfortunately changes the data returned from the deprecated tables method which now returns values like this:
> 
> "master"."dbo"."DBD_ODBC_LOB_TEST"."TABLE"
> 
> instead of (before)
> 
> "master"."dbo"."DBD_ODBC_LOB_TEST"
> 
> table_info is ok because it returns a result set and not a set of values pushed through quote_identifier.

Thanks for the great analysis Martin.

> Any comments?

The tables('', '', '', '%') call is a special case so it seems
reasonable to handle it as a special case in the code.

Tim.