| Subject: | Columns aliased with double quotes are a fatal error |
The following statements produce parse errors in SQL::Parser 1.14:
select foo AS "alias", bar as "pseudonym" from mystery_table;
select foo "alias", bar "pseudonym" from mystery_table;
The attached patch fixes that, then fixes the things that the patch broke, then tests the thing that got fixed, and includes the modifications that were made to the tests to make it easier to tell what was breaking in the first place.
diff -ur SQL-Statement-1.14/lib/SQL/Parser.pm SQL-Statement-1.14-patched/lib/SQL/Parser.pm
--- SQL-Statement-1.14/lib/SQL/Parser.pm 2005-04-18 20:41:09.000000000 -0400
+++ SQL-Statement-1.14-patched/lib/SQL/Parser.pm 2005-11-09 22:08:17.000000000 -0500
@@ -1057,7 +1057,7 @@
# need better alias test here, since AS is a common
# keyword that might be used in a function
#
- my ($fld, $alias) = ($col=~/^(.+)\s+AS\s+([A-Z]\w*)$/i)
+ my ($fld, $alias) = ($col=~/^(.+?)\s+(?:AS\s+)?([A-Z]\w*|\?QI\d+\?)$/i)
? ($1, $2)
: ($col, undef);
$col = $fld;
@@ -1100,7 +1100,11 @@
return undef unless $newcol = $self->COLUMN_NAME($col);
}
}
- $newalias = $self->COLUMN_NAME($alias||$newcol);
+ if (!$alias and uc($col) eq $newcol) {
+ $newalias = $col;
+ } else {
+ $newalias = $self->COLUMN_NAME($alias||$newcol);
+ }
$self->{struct}->{ORG_NAME}->{$newcol} = $newalias;
$aliases{uc $newalias} = $newcol;
push @newcols, $newcol;
@@ -1111,7 +1115,7 @@
if (!$self->{struct}->{col_obj}->{$newcol}) {
$self->{struct}->{col_obj}->{uc $newcol}
= SQL::Statement::Util::Column->new(
- uc $newcol,[],$alias
+ uc $newcol,[],$newalias
);
}
@@ -2241,7 +2245,7 @@
my $orgcol = $col_name;
if ($col_name =~ /^\?QI(\d+)\?$/) {
- $col_name = $self->replace_quoted_ids($col_name);
+ $col_name = $self->{struct}{quoted_ids}[$1];
}
else {
$col_name = uc $col_name unless $self->{struct}->{command} eq 'CREATE'
diff -ur SQL-Statement-1.14/t/04names.t SQL-Statement-1.14-patched/t/04names.t
--- SQL-Statement-1.14/t/04names.t 2005-04-21 22:44:09.000000000 -0400
+++ SQL-Statement-1.14-patched/t/04names.t 2005-11-09 22:03:18.000000000 -0500
@@ -11,7 +11,7 @@
plan skip_all => "Tests require DBD::File => 0.33";
}
else {
- plan tests => 2;
+ plan tests => 3;
}
use SQL::Statement; printf "SQL::Statement v.%s\n", $SQL::Statement::VERSION;
use vars qw($dbh $sth $DEBUG);
@@ -20,10 +20,13 @@
$dbh->do($_) for <DATA>;
$sth = $dbh->prepare("SELECT * FROM Prof");
-ok( 'pid pname' eq (join' ',cols($sth)),'Column Names: select list = *');
+is( (join' ',cols($sth)),'pid pname','Column Names: select list = *');
$sth = $dbh->prepare("SELECT pname,pID FROM Prof");
-ok( 'pname pID' eq (join' ',cols($sth)),'Column Names: select list = named');
+is( (join' ',cols($sth)), 'pname pID' ,'Column Names: select list = named');
+
+$sth = $dbh->prepare('SELECT pname "ProfName", pId "Magic#" from prof');
+is( (join' ',cols($sth)), 'ProfName Magic#' ,'Column Names: select list = aliased');
sub cols {
my($sth)=@_;
diff -ur SQL-Statement-1.14/t/07case.t SQL-Statement-1.14-patched/t/07case.t
--- SQL-Statement-1.14/t/07case.t 2005-04-21 22:46:38.000000000 -0400
+++ SQL-Statement-1.14-patched/t/07case.t 2005-11-09 20:31:28.000000000 -0500
@@ -52,10 +52,10 @@
$sth->execute;
my $msg = sprintf "imported table : %s", $query_case;
my $col = $sth->{NAME}->[0];
- ok($col eq 'col',$msg) if $query_case eq 'lower';
- ok($col eq 'COL',$msg) if $query_case eq 'upper';
- ok($col eq 'cOl',$msg) if $query_case eq 'mixed';
- ok($col eq 'col',$msg) if $query_case eq 'asterisked';
+ is($col, 'col',$msg) if $query_case eq 'lower';
+ is($col, 'COL',$msg) if $query_case eq 'upper';
+ is($col, 'cOl',$msg) if $query_case eq 'mixed';
+ is($col, 'col',$msg) if $query_case eq 'asterisked';
$sth->finish;
$sth->{Active}=0;
}
@@ -72,13 +72,13 @@
$sth->execute;
my $msg = sprintf "%s/%s", $create_case, $query_case;
my $col = $sth->{NAME}->[0];
- ok($col eq 'col',$msg) if $query_case eq 'lower';
- ok($col eq 'COL',$msg) if $query_case eq 'upper';
- ok($col eq 'cOl',$msg) if $query_case eq 'mixed';
+ is($col, 'col',$msg) if $query_case eq 'lower';
+ is($col, 'COL',$msg) if $query_case eq 'upper';
+ is($col, 'cOl',$msg) if $query_case eq 'mixed';
if ($query_case eq 'asterisked') {
- ok($col eq 'col',$msg) if $create_case eq 'lower';
- ok($col eq 'COL',$msg) if $create_case eq 'upper';
- ok($col eq 'cOl',$msg) if $create_case eq 'mixed';
+ is($col, 'col',$msg) if $create_case eq 'lower';
+ is($col, 'COL',$msg) if $create_case eq 'upper';
+ is($col, 'cOl',$msg) if $create_case eq 'mixed';
}
$sth->finish;
$sth->{Active}=0;
@@ -120,7 +120,7 @@
(mu,ml,um,ul,lm,lu)
- ok($col eq 'COL',$msg) if $query_case eq 'asterisked';
+ is($col, 'COL',$msg) if $query_case eq 'asterisked';
exit;
my $tbl = 'case';
$dbh->do("CREATE TEMP TABLE $tbl (lower INT)");
@@ -147,18 +147,19 @@
$sth=$dbh->prepare("
SELECT SUM(sales), MAX(sales) FROM biz
");
-ok('2700~1000^' eq query2str($sth),'AGGREGATE FUNCTIONS WITHOUT GROUP BY');
+is( query2str($sth), '2700~1000^', 'AGGREGATE FUNCTIONS WITHOUT GROUP BY');
$sth=$dbh->prepare("
SELECT region,SUM(sales), MAX(sales) FROM biz GROUP BY region
");
-ok('West~2000~1000^East~700~700^' eq query2str($sth),'GROUP BY one column');
+is( query2str($sth), 'West~2000~1000^East~700~700^' ,'GROUP BY one column');
$sth=$dbh->prepare("
SELECT region,store,SUM(sales), MAX(sales) FROM biz GROUP BY region,store
");
-ok('West~Los Angeles~1500~1000^West~San Diego~500~500^East~Boston~700~700^'
- eq query2str($sth),'GROUP BY several columns');
+is(query2str($sth),
+ 'West~Los Angeles~1500~1000^West~San Diego~500~500^East~Boston~700~700^',
+ 'GROUP BY several columns');
sub query2str {