Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

tdcbc problem

32 views
Skip to first unread message

Gerhard Reithofer

unread,
Mar 24, 2017, 1:30:51 PM3/24/17
to
Hi,
I found a strange issue using tcbs::sqlite3, I have the following table:

create table testtab (
ival integer primary key,
stat number(2),
cval number(13),
tval text);

.. and inserted 2 rows:
insert into testtab values(NULL,NULL,'TEST','TEXT');
insert into testtab values(NULL,9,'TEST','TEXT');

sqlite> select * from testtab;
1||TEST|TEXT
9|2|TEST|TEXT

But when I try to read the table via tdbc the following appears:

package require tdbc::sqlite3
tdbc::sqlite3::connection create testconn testdb
set stmt [testconn prepare "select * from testtab"]
set rows [$stmt allrows]
$stmt close
for {set i 0} {$i<[llength $rows]} {incr i} {
set row [join [lindex $rows $i] ,]
puts "row $i: $row"
}

$ tclsh test.tcl
row 0: ival,1,cval,TEST,tval,TEXT
row 1: ival,2,stat,9,cval,TEST,tval,TEXT

The "NULL-Value" in record 0 is missing!
I would call this a bug.

Any other idea?

TIA,
Gerhard

--
Gerhard Reithofer - Techn. EDV Reithofer - http://www.tech-edv.co.at

Rich

unread,
Mar 24, 2017, 2:02:32 PM3/24/17
to
Gerhard Reithofer <gerhard....@tech-edv.co.at> wrote:
> $ tclsh test.tcl
> row 0: ival,1,cval,TEST,tval,TEXT
> row 1: ival,2,stat,9,cval,TEST,tval,TEXT
>
> The "NULL-Value" in record 0 is missing!
> I would call this a bug.
>
> Any other idea?

Possibly this is how the library allows you to distinguish between
"NULL" and "empty string". Empty string might have been:

ival,1,stat,,tval,TEXT

Otherwise, if empty string is returned for NULL, how do you know that
"" resulted from empty string instead of NULL.

If your code does not care, and empty string or NULL are both identical
to it, you can use the sql function coalesce to convert the NULL's into
empty strings:

select coalesce(stat, "") ...

And you'll likely get "stat,,tval" returning for this row.

Gerhard Reithofer

unread,
Mar 24, 2017, 4:54:23 PM3/24/17
to
Hi Rich,

On Fri, 24 Mar 2017, Rich wrote:

> Gerhard Reithofer <gerhard....@tech-edv.co.at> wrote:
> > $ tclsh test.tcl
> > row 0: ival,1,cval,TEST,tval,TEXT
> > row 1: ival,2,stat,9,cval,TEST,tval,TEXT
> >
> > The "NULL-Value" in record 0 is missing!
> > I would call this a bug.
> >
> > Any other idea?
>
> Possibly this is how the library allows you to distinguish between
> "NULL" and "empty string". Empty string might have been:
>
> ival,1,stat,,tval,TEXT
>
> Otherwise, if empty string is returned for NULL, how do you know that
> "" resulted from empty string instead of NULL.

in this case no "empty" string should be possible, it's a numeric field.

> If your code does not care, and empty string or NULL are both identical
> to it, you can use the sql function coalesce to convert the NULL's into
> empty strings:
>
> select coalesce(stat, "") ...
>
> And you'll likely get "stat,,tval" returning for this row.

IMHO the problem is that the number of return values does not report the
correct number of values.
I've extended my test program to use options "-as lists" in an addtional
call and I added another NULL value to the char field cval (which was
created incorrectly in my 1st example).
The default behavior is "-as dicts".

The new structure is:
create table testtab (
ival integer primary key,
stat numeric(2),
cval varchar(13),
tval text);

insert into testtab values(NULL,NULL,'TEST','TEXT');
insert into testtab values(NULL,9,NULL,'TEXT');

sqlite> select * from testtab;
1||TEST|TEXT
2|9||TEXT

The results from tcdb are:
$ tclsh test.tcl
sqlite3 1.0.4 loaded
as dicts: select * from testtab
row 0 len=4: ival,1,cval,TEST
row 1 len=6: ival,2,stat,9,tval,TEXT
as dicts: select ival,stat,cval,tval from testtab
row 0 len=4: ival,1,cval,TEST
row 1 len=6: ival,2,stat,9,tval,TEXT
as dicts: select stat from testtab
row 0 len=0:
row 1 len=2: stat,9
as dicts: select ival,coalesce(stat,''),cval,tval from testtab
row 0 len=6: ival,1,coalesce(stat,''),,cval,TEST
row 1 len=6: ival,2,coalesce(stat,''),9,tval,TEXT
as lists: select * from testtab
row 0 len=4: 1,,TEST,
row 1 len=4: 2,9,,TEXT
as lists: select ival,stat,cval,tval from testtab
row 0 len=4: 1,,TEST,
row 1 len=4: 2,9,,TEXT
as lists: select ival,stat,cval,tval from testtab
row 0 len=4: 1,,TEST,
row 1 len=4: 2,9,,TEXT
as lists: select stat from testtab
row 0 len=1:
row 1 len=1: 9
as lists: select ival,coalesce(stat,''),cval,tval from testtab
row 0 len=4: 1,,TEST,
row 1 len=4: 2,9,,TEXT

Only with the option "as lists" the output seems to be consistent.
Unfortunately an extra call is necessary to get the column names which
is a requirement.

Bye,

Rich

unread,
Mar 24, 2017, 5:02:26 PM3/24/17
to
Gerhard Reithofer <gerhard....@tech-edv.co.at> wrote:
> Hi Rich,
>
> On Fri, 24 Mar 2017, Rich wrote:
>
>> Gerhard Reithofer <gerhard....@tech-edv.co.at> wrote:
>> > $ tclsh test.tcl
>> > row 0: ival,1,cval,TEST,tval,TEXT
>> > row 1: ival,2,stat,9,cval,TEST,tval,TEXT
>> >
>> > The "NULL-Value" in record 0 is missing!
>> > I would call this a bug.
>> >
>> > Any other idea?
>>
>> Possibly this is how the library allows you to distinguish between
>> "NULL" and "empty string". Empty string might have been:
>>
>> ival,1,stat,,tval,TEXT
>>
>> Otherwise, if empty string is returned for NULL, how do you know that
>> "" resulted from empty string instead of NULL.
>
> in this case no "empty" string should be possible, it's a numeric field.

I doubt the interface library digs into the schema definition
suffiently to determine this fact. It likely maps NULL to "do not
return a key/value pair" for all instances of NULL. Additionally,
sqlite will allow you to insert empty string into a numeric field, and
it will store it just fine, so with sqlite an empty string in a numeric
field (unless you use a constraint or trigger to prevent it) is
possible.

>> If your code does not care, and empty string or NULL are both
>> identical to it, you can use the sql function coalesce to convert
>> the NULL's into empty strings:
>>
>> select coalesce(stat, "") ...
>>
>> And you'll likely get "stat,,tval" returning for this row.
>
> IMHO the problem is that the number of return values does not report the
> correct number of values.
> I've extended my test program to use options "-as lists" in an addtional
> call and I added another NULL value to the char field cval (which was
> created incorrectly in my 1st example).
> The default behavior is "-as dicts".

Do you expect these nullable columns to actually ever make use of NULL
values? If not then you could make them "not null" columns at which
point there will never be NULL values in them in the first place.

Gerhard Reithofer

unread,
Mar 24, 2017, 5:19:52 PM3/24/17
to
I made the same tests with the postgres tdbc interface and the behavior
is the same as with sqlite3.

testdb=# select * from testtab;
ival | stat | cval | tval
------+------+------+------
1 | | TEST | TEXT
2 | 9 | | TEXT

$ tclsh test.tcl
interface postgres 1.0.4 loaded
as dicts: select * from testtab
row 0 len=6: ival,1,cval,TEST,tval,TEXT
row 1 len=6: ival,2,stat,9,tval,TEXT
as dicts: select ival,stat,cval,tval from testtab
row 0 len=6: ival,1,cval,TEST,tval,TEXT
row 1 len=6: ival,2,stat,9,tval,TEXT
as dicts: select stat from testtab
row 0 len=0:
row 1 len=2: stat,9
as lists: select * from testtab
row 0 len=4: 1,,TEST,TEXT
row 1 len=4: 2,9,,TEXT
as lists: select ival,stat,cval,tval from testtab
row 0 len=4: 1,,TEST,TEXT
row 1 len=4: 2,9,,TEXT
as lists: select stat from testtab
row 0 len=1:
row 1 len=1: 9

So, it seems to be a "consistent bug" in the the tdbc interface ;-)

Should I report a bug?

Gerhard Reithofer

unread,
Mar 24, 2017, 5:27:47 PM3/24/17
to
Hi Rich,

On Fri, 24 Mar 2017, Rich wrote:

> Gerhard Reithofer <gerhard....@tech-edv.co.at> wrote:
> > Hi Rich,
> >
> > On Fri, 24 Mar 2017, Rich wrote:
> >
> >> Gerhard Reithofer <gerhard....@tech-edv.co.at> wrote:
> >> > $ tclsh test.tcl

...

> Do you expect these nullable columns to actually ever make use of NULL
> values? If not then you could make them "not null" columns at which
> point there will never be NULL values in them in the first place.

I would expect, that the results using "-as list" are the same as with
"-as dict" (see my last example) but they deliver different results
(different number of list entries).

I've written another post using the "postgres" interface. The behavior
is exactly the same.

Rich

unread,
Mar 24, 2017, 5:31:53 PM3/24/17
to
Gerhard Reithofer <gerhard....@tech-edv.co.at> wrote:
> I made the same tests with the postgres tdbc interface and the behavior
> is the same as with sqlite3.
>
> So, it seems to be a "consistent bug" in the the tdbc interface ;-)
>
> Should I report a bug?

It appears to be documented behavior. From the tdbc::result-set man
page:

for "nextlist" items: "NULL values are replaced by empty strings"

for "nextdict items: "If a column's value in the row is NULL, its
key is omitted from the dictionary."

And the reason for the "key omission" aspect for dictionaries would be
exactly to differentiate between actual SQL NULL and any other value
that could be in the column.

Gerhard Reithofer

unread,
Mar 24, 2017, 5:47:44 PM3/24/17
to
Hi Rich,

On Fri, 24 Mar 2017, Rich wrote:

you are right.

The documentation is a little "cluttered" (my personal opinion) but
consistent.
/--> nextdict
allrows --> nextrow
\--> nextlist

This "feature" is documented.

Thank you,
Gerhard
0 new messages