Skip Menu |
 

This queue is for tickets about the SQL-Statement CPAN distribution.

Report information
The Basics
Id: 85174
Status: rejected
Priority: 0/
Queue: SQL-Statement

People
Owner: Nobody in particular
Requestors: randolph [...] deepcloud.com.au
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Subject: Complex Literals
Date: Fri, 10 May 2013 15:21:41 +1000
To: <bug-SQL-Statement [...] rt.cpan.org>
From: Randolph Pullen <randolph [...] deepcloud.com.au>
Download (untitled) / with headers
text/plain 451b
It appears that Parser.pm is not handling complex literals (eg: date/time and timestamp literals). Specifically: Select * from lineitem where l_shipdate <= date '1998-12-01' - interval '90' day (3) This is processed Parser.pm as ³Error: String Œ?0?¹ not allowed in numeric expression. This is confirmed in SQL::Statement1.33 ­ and I can see no references in the change log to the problem. This is medium to high importance. Thanks, Randolph
Download (untitled) / with headers
text/plain 656b
On Fri May 10 01:22:03 2013, randolph wrote: Show quoted text
> It appears that Parser.pm is not handling complex literals (eg: date/time > and timestamp literals). > > Specifically: > Select * from lineitem > where l_shipdate <= date '1998-12-01' - interval '90' day (3) > > This is processed Parser.pm as > ³Error: String Œ?0?¹ not allowed in numeric expression. > > This is confirmed in SQL::Statement1.33 ­ and I can see no references in the > change log to the problem.
Well - I cannot remember patches regarding that. Show quoted text
> This is medium to high importance.
Why? This is a feature request which is automatically "wishlist". Show quoted text
> Thanks, > Randolph
Cheers, Jens
Subject: Re: [rt.cpan.org #85174] Complex Literals
Date: Fri, 10 May 2013 15:56:42 +1000
To: <bug-SQL-Statement [...] rt.cpan.org>
From: Randolph Pullen <randolph [...] deepcloud.com.au>
Download (untitled) / with headers
text/plain 1.3k
Jens, thanks for your prompt response. The example query snippet comes from the TPC-H query #1. This missing feature of SQL 99, prohibits running the TPC-H benchmark. I am only using SQL::Statement as a meta code compiler, so the benchmark should be quite fast. I would be quite happy to share the results if we get it working. Am I to understand that this work is currently regarded as wishlist for v 2.x ? On 10/05/13 3:37 PM, "Jens Rehsack via RT" <bug-SQL-Statement@rt.cpan.org> wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=85174 > > > On Fri May 10 01:22:03 2013, randolph wrote:
>> It appears that Parser.pm is not handling complex literals (eg: date/time >> and timestamp literals). >> >> Specifically: >> Select * from lineitem >> where l_shipdate <= date '1998-12-01' - interval '90' day (3) >> >> This is processed Parser.pm as >> ³Error: String �?0?¹ not allowed in numeric expression. >> >> This is confirmed in SQL::Statement1.33 ­ and I can see no references in the >> change log to the problem.
> > Well - I cannot remember patches regarding that. >
>> This is medium to high importance.
> > Why? This is a feature request which is automatically "wishlist". >
>> Thanks, >> Randolph
> > Cheers, > Jens
Randolph Pullen Architect & Co-Founder DeepCloud E: randolph@deepcloud.com.au P: +61 42089 5221 www.DeepCloud.co
Download (untitled) / with headers
text/plain 462b
This is volunteer software - read: if something is business relevant, bring business to provide patches (there is more than one way to do it ^^). What does it mean? I want to create an SQL::Statement 2.0 - but to be true, I see this for Perl6 instead of Perl5. And I don't see v2 this year (I'm neither sure if I see it next year). For sure it would be great if SQL::Statement could run the TPC-H benchmark - but I fear this will not happen soon. Cheers, Jens
Subject: Re: [rt.cpan.org #85174] Complex Literals
Date: Fri, 10 May 2013 16:38:09 +1000
To: <bug-SQL-Statement [...] rt.cpan.org>
From: Randolph Pullen <randolph [...] deepcloud.com.au>
Download (untitled) / with headers
text/plain 902b
I might have a look at it myself then. My quick inspection suggested that the subs: LITERAL,LITERAL_LIST and ROW_VALUE are implicated.... Any tips? On 10/05/13 4:30 PM, "Jens Rehsack via RT" <bug-SQL-Statement@rt.cpan.org> wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=85174 > > > This is volunteer software - read: if something is business relevant, bring > business to provide patches (there is more than one way to do it ^^). > > What does it mean? I want to create an SQL::Statement 2.0 - but to be true, I > see this for Perl6 instead of Perl5. And I don't see v2 this year (I'm neither > sure if I see it next year). > > For sure it would be great if SQL::Statement could run the TPC-H benchmark - > but I fear this will not happen soon. > > Cheers, > Jens
Randolph Pullen Architect & Co-Founder DeepCloud E: randolph@deepcloud.com.au P: +61 42089 5221 www.DeepCloud.co
Download (untitled) / with headers
text/plain 1.1k
On Fri May 10 02:38:30 2013, randolph wrote: Show quoted text
> I might have a look at it myself then.
Good idea. Please ensure to have a test ready ... Show quoted text
> My quick inspection suggested that the subs: > LITERAL,LITERAL_LIST and ROW_VALUE are implicated.... > Any tips?
SQL::Parser has a lot of internal side effects. Be very careful - but good luck. Best tip: join irc://irc.perl.org/#dbi Cheers Show quoted text
> On 10/05/13 4:30 PM, "Jens Rehsack via RT" <bug-SQL- > Statement@rt.cpan.org> > wrote: >
> > <URL: https://rt.cpan.org/Ticket/Display.html?id=85174 > > > > > This is volunteer software - read: if something is business
> relevant, bring
> > business to provide patches (there is more than one way to do it
> ^^).
> > > > What does it mean? I want to create an SQL::Statement 2.0 - but to
> be true, I
> > see this for Perl6 instead of Perl5. And I don't see v2 this year
> (I'm neither
> > sure if I see it next year). > > > > For sure it would be great if SQL::Statement could run the TPC-H
> benchmark -
> > but I fear this will not happen soon. > > > > Cheers, > > Jens
> > > Randolph Pullen > Architect & Co-Founder > DeepCloud > E: randolph@deepcloud.com.au > P: +61 42089 5221 > > www.DeepCloud.co > > >
Subject: Bug #85174 for SQL-Statement: Complex Literals
Date: Thu, 04 Jul 2013 15:05:53 +1000
To: <bug-SQL-Statement [...] rt.cpan.org>
From: Randolph Pullen <randolph [...] deepcloud.com.au>
Download (untitled) / with headers
text/plain 3.4k
A simple solution for this bug is to implement a strict reporting switch in the parser instantiation method ie: $self->{strict} =1; This can be overridden by users wanting less stringent checking of columns like date and interval. All other users will see no difference. The minor changes required are only in subs new , ROW_VALUE and IDENTIFIER as shown below. ie: if (($self->{opts}->{reserved_words}->{$id}) and ($self->{strict}>0)) ############################# # PUBLIC METHODS ############################# sub new { my $class = shift; my $dialect = shift || 'ANSI'; $dialect = 'ANSI' if ( uc $dialect eq 'ANSI' ); $dialect = 'AnyData' if ( ( uc $dialect eq 'ANYDATA' ) or ( uc $dialect eq 'CSV' ) ); $dialect = 'AnyData' if ( $dialect eq 'SQL::Eval' ); my $flags = shift || {}; $flags->{dialect} = $dialect; $flags->{PrintError} = 1 unless ( defined( $flags->{PrintError} ) ); my $self = bless( $flags, $class ); $self->dialect( $self->{dialect} ); $self->set_feature_flags( $self->{select}, $self->{create} ); $self->LOAD('LOAD SQL::Statement::Functions'); $self->{strict} =1; return $self; } ################################################################### # ROW_VALUE ::= <literal> | <column_name> ################################################################### sub ROW_VALUE { my ( $self, $str ) = @_; ... my @newvalues; foreach my $val (@vals) { my $newval = $self->ROW_VALUE($val); if (( $newval && $newval->{type} !~ m/number|column|placeholder|function/ ) and ($self->{strict}>0)) { return $self->do_err(qq[String '$val' not allowed in Numeric expression!]); } push( @newvalues, $newval ); } return { type => 'function', name => 'numeric_exp', str => $str, value => \@newvalues, fullorg => $orgstr, }; } ################################################################### # IDENTIFIER ::= <alphabetic_char> { <alphanumeric_char> | _ }... # # and must not be a reserved word or over 128 chars in length ################################################################### sub IDENTIFIER { my ( $self, $id ) = @_; if ( $id =~ m/^\?QI(.+)\?$/ ) { return 1; } if ( $id =~ m/^(.+)\.([^\.]+)$/ ) { my $schema = $1; # ignored $id = $2; } return 1 if $id =~ m/^".+?"$/s; # QUOTED IDENTIFIER my $err = "Bad table or column name: '$id' "; # BAD CHARS if ( $id =~ /\W/ ) { $err .= "has chars not alphanumeric or underscore!"; return $self->do_err($err); } # CSV requires optional start with _ my $badStartRx = uc( $self->{dialect} ) eq 'ANYDATA' ? qr/^\d/ : qr/^[_\d]/; if ( $id =~ $badStartRx ) { # BAD START $err .= "starts with non-alphabetic character!"; return $self->do_err($err); } if ( length $id > 128 ) { # BAD LENGTH $err .= "contains more than 128 characters!"; return $self->do_err($err); } $id = uc $id; if (($self->{opts}->{reserved_words}->{$id}) and ($self->{strict}>0)) { # BAD RESERVED WORDS $err .= "is a SQL reserved word!"; return $self->do_err($err); } return 1; }
Download (untitled) / with headers
text/plain 287b
Hi Randolph, I'm a bit busy at the moment. If you want to act me quick, please provide: 1) a test case demonstrating the problem 2) a unified diff showing your proposed solution You can go to https://github.com/perl5-dbi and pick the SQL-Statement repository from there. Cheers, Jens
Are you still interested to get it in? Then please send patch with test.
Subject: Re: [rt.cpan.org #86693] Bug #85174 for SQL-Statement: Complex Literals
Date: Wed, 10 Dec 2014 22:50:44 +1100
To: <bug-SQL-Statement [...] rt.cpan.org>
From: Randolph Pullen <randolph [...] deepcloud.com.au>
Download (untitled) / with headers
text/plain 647b
I have a modified version of 1.33 that fixes this by ignoring the generated error. If you want I could forward this tiny change for incorporation in later releases otherwise I will just distribute my modified code as is. I am not currently in a position to make delta's or test scripts. Regards, Randolph On 10/12/14 8:32 PM, "Jens Rehsack via RT" <bug-SQL-Statement@rt.cpan.org> wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=86693 > > > Are you still interested to get it in? Then please send patch with test.
Randolph Pullen Architect & Founder DeepCloud E: randolph@deepcloud.com.au P: +61 42089 5221 www.DeepCloud.co


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.