![Thu Jul 04 00:52:39 2013 (6.4k) by randolph [...] deepcloud.com.au](/Ticket/Attachment/1231866/650932/image.jpg){kind=link}
| Subject: | UDF parse bug |
| Date: | Thu, 04 Jul 2013 14:52:08 +1000 |
| To: | <bug-SQL-Statement [...] rt.cpan.org> |
| From: | Randolph Pullen <randolph [...] deepcloud.com.au> |
I use SQL::Statement to parse and then generate other code rather than
executing SQL.
My extended Parser adds some function stubs as shown below.
Since upgrading to 1.33 I am having some problems with UDF's these throwing
up errors in Parser.pm
These work:
select octet_length(a) from rpp3;
select charextract(caller, 3) from cdr
select lowercase(a) from rpp3;
select rpad(a,2) from rpp3;
select shift(b,2) from rpp3;
...but these don't:
select ltrim(a) from rpp3; select rtrim(a) from rpp3;
Parser.pm reports: Unknown function 'ltrim'
(same with rtrim)
select nvl(caller, '2222') from cdr;
becomes
select nvl (caller,?0?) FROM cdr;
locate(), replace(), nvl2(), ifnull() and others using constant parameters
exhibit the same problem.
ie: Only in these cases, the fullorg field appears wrong:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$VAR1 = {
'subname' => 'SQL::Statement::Functions::SQL_FUNCTION_NVL',
'value' => [
{
'value' => 'caller',
'type' => 'column',
'fullorg' => 'caller'
},
{
'value' => '2222',
'type' => 'string',
'fullorg' => '2222'
}
],
'name' => 'nvl',
'type' => 'function',
'fullorg' => 'nvl (caller,?0?)',
'alias' => 'nvl'
};
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Is this a bug or are users expected to reconstruct the field by other means?
My UDF package looks like this:
package MyFunctions;
use strict;
use warnings;
use vars qw(@ISA $VERSION);
require SQL::Statement::Term;
$VERSION = '1.0';
#string funcs
sub SQL_FUNCTION_SHIFT{ } ;
sub SQL_FUNCTION_RTRIM{ } ;
sub SQL_FUNCTION_LTRIM{ } ;
sub SQL_FUNCTION_RPAD{ } ;
sub SQL_FUNCTION_RIGHT{ } ;
sub SQL_FUNCTION_OCTET_LENGTH{ } ;
sub SQL_FUNCTION_IFNULL{ } ;
sub SQL_FUNCTION_NVL2{ } ;
sub SQL_FUNCTION_REPLACE{ } ;
etc...
My Parsers instantiation method looks like:
sub new {
my $class = shift;
my $dialect = shift || 'ANSI';
my $flags = shift;
my $parent = $class->can("SUPER::new");
my $self = $class->SUPER::new($dialect ,$flags);
$self->LOAD('LOAD MyFunctions');
return bless $self, $class;
}
Randolph Pullen
Architect & Co-Founder
DeepCloud
E: randolph@deepcloud.com.au
P: +61 42089 5221
www.DeepCloud.co