Bug #76276 for SQL-Statement: support 0-column input tables?

Tue Apr 03 09:53:19 2012 reinpost [...] win.tue.nl - Ticket created

Subject:

support 0-column input tables?

This is not a bug report, but a suggestion. I have a script, csvsql, that implements a DBD::CSV-based equivalent of join, the standard Unix utility. Example use: csvsql -e 'SELECT * FROM t1 NATURAL JOIN t2' a.csv b.csv > ab.csv This will crash when either input file is empty. Arguably, an input file is a valid CSV file with 0 columns, and that is in fact how I would like to be able to treat it in many use cases for my script. So this is a suggestion to add the option to support the recognition of 0-column CSV files. When enabled, and the first record fails on end-of- file, that is no longer an error but instead completes successfully, leaving a 0-column table. Only do this when DBI in general can deal with 0-column tables. (Perhaps there exist some fundamental issues with it elsewhere that I'm not aware of.)

Wed Apr 04 12:55:13 2012 rehsack [...] googlemail.com - Correspondence added

Subject:	Re: [rt.cpan.org #76276] support 0-column input tables?
Date:	Wed, 4 Apr 2012 17:55:03 +0100
To:	bug-DBD-CSV [...] rt.cpan.org
From:	Jens Rehsack <rehsack [...] googlemail.com>

Am 3. April 2012 14:53 schrieb Reinier Post via RT <bug-DBD-CSV@rt.cpan.org>: Show quoted text

> Tue Apr 03 09:53:19 2012: Request 76276 was acted upon. > Transaction: Ticket created by rpost > Queue: DBD-CSV > Subject: support 0-column input tables? > Broken in: (no value) > Severity: (no value) > Owner: Nobody > Requestors: reinpost@win.tue.nl > Status: new > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=76276 > > > > This is not a bug report, but a suggestion. > > I have a script, csvsql, that implements a DBD::CSV-based equivalent of > join, the standard Unix utility. Example use: > > csvsql -e 'SELECT * FROM t1 NATURAL JOIN t2' a.csv b.csv > ab.csv > > This will crash when either input file is empty. > > Arguably, an input file is a valid CSV file with 0 columns, and that is > in fact how I would like to be able to treat it in many use cases for my > script. > > So this is a suggestion to add the option to support the recognition of > 0-column CSV files. When enabled, and the first record fails on end-of- > file, that is no longer an error but instead completes successfully, > leaving a 0-column table. > > Only do this when DBI in general can deal with 0-column tables. > (Perhaps there exist some fundamental issues with it elsewhere that I'm > not aware of.)

Merjin, can you throw it over into SQL::Statement Queue? It's reasonable to handle it there. Best regards, Jens

Wed Apr 04 12:55:15 2012 The RT System itself - Status changed from 'new' to 'open'

Thu Apr 05 03:20:28 2012 h.m.brand [...] xs4all.nl - Correspondence added

Subject:	Re: [rt.cpan.org #76276] support 0-column input tables?
Date:	Thu, 5 Apr 2012 09:20:08 +0200
To:	Jens Rehsack <rehsack [...] googlemail.com>, bug-DBD-CSV [...] rt.cpan.org, DBI Developers Mailing List <dbi-dev [...] perl.org>
From:	"H.Merijn Brand" <h.m.brand [...] xs4all.nl>

On Wed, 4 Apr 2012 12:55:14 -0400, "Jens Rehsack via RT" <bug-DBD-CSV@rt.cpan.org> wrote: Show quoted text

> Merjin,

By now, you'd know how not to switch the i and j, right? Show quoted text

> can you throw it over into SQL::Statement Queue? It's reasonable to > handle it there.

Sure. I thought it would be DBD::File, as the *file* is empty and thus would do the same for all DBD::File related DBD's. It might even warrant a new attribute to allow this as normally in DBI a table always has columns (but not always rows). If a file is empty, it doesn't even have columns. Not all databases allow the creation of "empty" tables, as in a table with no rows at all. Postgres does, but Unify and Oracle do not. I have included the devel list to see how others think Show quoted text

> Best regards, > Jens

-- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using perl5.00307 .. 5.14 porting perl5 on HP-UX, AIX, and openSUSE http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/

Thu Apr 05 04:03:02 2012 rehsack [...] googlemail.com - Correspondence added

CC:	dbi-dev [...] perl.org
Subject:	Re: [rt.cpan.org #76276] support 0-column input tables?
Date:	Thu, 05 Apr 2012 10:02:35 +0200
To:	bug-DBD-CSV [...] rt.cpan.org
From:	Jens Rehsack <rehsack [...] googlemail.com>

Am 05.04.2012 09:20, schrieb h.m.brand@xs4all.nl via RT: Show quoted text

> Queue: DBD-CSV > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=76276 > > > On Wed, 4 Apr 2012 12:55:14 -0400, "Jens Rehsack via RT" > <bug-DBD-CSV@rt.cpan.org> wrote: >

>> Merjin,

> > By now, you'd know how not to switch the i and j, right?

*whistl* Show quoted text

>> can you throw it over into SQL::Statement Queue? It's reasonable to >> handle it there.

> > Sure. I thought it would be DBD::File, as the *file* is empty and thus > would do the same for all DBD::File related DBD's.

I do not expect that any DBD::* code is involved in the bug - but probably it's an non-instantiated table. I try to create a small test case and if I can't figure out what#s going wrong, I ask the originator for more details ... Show quoted text

> It might even > warrant a new attribute to allow this as normally in DBI a table always > has columns (but not always rows). If a file is empty, it doesn't even > have columns. Not all databases allow the creation of "empty" tables, as > in a table with no rows at all. Postgres does, but Unify and Oracle do > not.

Well, if the table is created with columns specified, you're right. If the originator wants the columns guessed from an empty b.csv, you're wrong from my point of view. DBD::CSV is special (as some DBD::AnyData, too), because it "guesses" columns from data file. Probably an $dbh attribute "f_default_columns" can help out, but before we think further let me dig out the root cause ;) I'm a bit busy with communion of my younger dauther until end of April and then something to clarify with our house renter - I expect I can invest larger blocks of time in mid of May. I hope I can create a test case before ... Show quoted text

> I have included the devel list to see how others think

So did I - good suggestion. Best, Jens

Thu Apr 05 05:29:35 2012 HMBRAND [...] cpan.org - Queue changed from DBD-CSV to SQL-Statement

Thu Apr 05 09:54:29 2012 reinpost [...] win.tue.nl - Correspondence added

From:

reinpost [...] win.tue.nl

On Thu Apr 05 04:03:02 2012, rehsack@googlemail.com wrote: Show quoted text

> Well, if the table is created with columns specified, you're right. > If the originator wants the columns guessed from an empty b.csv, you're > wrong from my point of view. DBD::CSV is special (as some DBD::AnyData, > too), because it "guesses" columns from data file.

True - this hadn't occurred to me. By default, DBD::CSV will read its column names from the first input line by default - so even without any columns, it should expect an (empty) first line. And this actually works - see the test output below. But the empty file is still unexpected as input. Only when asked to *infer* its column names should we expect it to accept the empty file as a valid 0-column input file. It doesn't, and this is something that might be fixed. Here are my tests (the script is attached): /tmp % sh Show quoted text

sh> : > empty.csv; echo > emptyline.csv sh> csvsql -e 'SELECT * FROM t1' emptyline.csv

Show quoted text

sh> csvsql -e 'SELECT * FROM t1' empty.csv

DBD::CSV::st execute failed: Execution ERROR: Missing first row due to EOF - End of data in parsing input stream at /usr/lib/perl5/site_perl/5.10/i686-cygwin/DBD/File.pm line 730 . [for Statement "SELECT * FROM t1"] at /cygdrive/f/scripts/squimp/csvsql line 80. fatal error: SQL statement execution failed: Execution ERROR: Missing first row due to EOF - End of data in parsing input stream at /usr/lib/perl5/site_perl/5.10/i686-cygwin/DBD/File.pm line 730 . Show quoted text

sh> csvsql -ae 'SELECT * FROM t1' emptyline.csv

DBD::CSV::st execute failed: Execution ERROR: Can't call method "getline" on an undefined value at /usr/lib/perl5/site_perl/5.10/DBD/CSV.pm line 433. . [for Statement "SELECT * FROM t1"] at /cygdrive/f/scripts/squimp/csvsql line 80. fatal error: SQL statement execution failed: Execution ERROR: Can't call method "getline" on an undefined value at /usr/lib/perl5/site_perl/5.10/DBD/CSV.pm line 433. . Show quoted text

sh>

Subject:

csvsql

Download csvsql
application/octet-stream 2k

Message body not shown because it is not plain text.

Thu Apr 05 16:30:56 2012 darren [...] DarrenDuncan.net - Correspondence added

CC:	Jens Rehsack <rehsack [...] googlemail.com>, bug-DBD-CSV [...] rt.cpan.org
Subject:	Re: [rt.cpan.org #76276] support 0-column input tables?
Date:	Thu, 05 Apr 2012 13:30:35 -0700
To:	"H.Merijn Brand" <h.m.brand [...] xs4all.nl>, DBI Developers Mailing List <dbi-dev [...] perl.org>
From:	Darren Duncan <darren [...] darrenduncan.net>

H.Merijn Brand wrote: Show quoted text

> On Wed, 4 Apr 2012 12:55:14 -0400, "Jens Rehsack via RT"

>> can you throw it over into SQL::Statement Queue? It's reasonable to >> handle it there.

> > Sure. I thought it would be DBD::File, as the *file* is empty and thus > would do the same for all DBD::File related DBD's. It might even > warrant a new attribute to allow this as normally in DBI a table always > has columns (but not always rows). If a file is empty, it doesn't even > have columns. Not all databases allow the creation of "empty" tables, as > in a table with no rows at all. Postgres does, but Unify and Oracle do > not. > > I have included the devel list to see how others think

Damn right that tables/rows/etc with zero columns should be supported! All SQL or relational databases should support zero-column tables/rows. Those are very important to *the* relational model. For example, with (natural) join operations (or product or intersect), the zero-column table is analogous to the number 1 if it has one row and to the number 0 if it has none; the one-row version is the identity value for join. Or, if you define a unique/key constraint that ranges over zero columns, that is a simple way to restrict the table to having at most one row, which is useful say if you want to have a table storing singleton information. Practically speaking, supporting zero-column tables/rows just makes sense, it makes as much sense as supporting Perl array or hash variables that are allowed to be empty, or allowing empty sets, which are identity values for set unions or hash/array catenation. From DBI's perspective, as we use Array/Hash to represent rows, it is just the empty one of those to represent the zero-column one. Suffice it to say, if even one DBMS supports this, DBI should too, and this is an example others can follow. -- Darren Duncan

Mon Oct 29 09:49:17 2012 REHSACK [...] cpan.org - Severity Wishlist added

Mon Oct 29 09:49:17 2012 REHSACK [...] cpan.org - Status changed from 'open' to 'stalled'