Thread: pgsql.tcl: Bug concerning joins

I'm writing an application using PostgreSQL-6.3.2, Tcl/Tk 8.0 using
pgsql.tcl as an interface. This approach allows to run code completely
unmodified on Unix, Windows and the Mac.

I found a bug processing a simple join like
   SELECT s1.b,s2.b FROM pgsqlbug1 s1, pgsqlbug2 s2 WHERE s1.a = s2.c;
This bug only occurs whern using pgsql.tcl.

Here is the full story:

====== create script ============
-- Trigger a bug in the pgsql Tcl interface
--
CREATE TABLE pgsqlbug1 (
    a int2,
    b char(12),
    primary key(a)
);
CREATE TABLE pgsqlbug2 (
       a int2,
       b char(12),
       c int2,
       primary key(a)
);

INSERT INTO pgsqlbug1 (a,b) VALUES (0,'from table 1');
INSERT INTO pgsqlbug2 (a,b,c) VALUES (0,'from table 2',0);
====== end of create script ============

Using psql I get the following (correct) output:

====== psql session ==========
meyer=> select * from pgsqlbug1;
a|b
-+------------
0|from table 1
(1 row)

meyer=> select * from pgsqlbug2;
a|b           |c
-+------------+-
0|from table 2|0
(1 row)

meyer=> SELECT s1.b,s2.b FROM pgsqlbug1 s1, pgsqlbug2 s2 WHERE s1.a = s2.c;
b           |b
------------+------------
from table 1|from table 2
(1 row)

====== pend of sql session ==========

Using pgsql.tcl from Tcl however I get:

meyer> SELECT s1.b,s2.b FROM pgsqlbug1 s1, pgsqlbug2 s2 WHERE s1.a = s2.c;

attributes: b b
lAttributes: {b 1042 -1} {b 1042 -1}
Tuple 0: {from table 2} {from table 2}

... what is wrong, obviously.

(Version info: timestamp of pgsql.tcl is "May 21 15:25" on
ftp://ftp.flex.ro/pub/pgaccess).

BTW: What's the address of the developer of pgsql.tcl. I couldn't find
    it in the code....

Thanks,
    sebastian
--
    __o     Sebastian Meyer  Tel: (0421)218-7702 | "A weird imagination
  _ \<,_    meyer@mevis.de   http://www.mevis.de | is most useful to gain
 (_)/ (_)   MeVis an der Universität Bremen      | full advantage of all the
~~~~~~~~~~~ Universitätsallee 29, D-28359 Bremen | features." (amd(8))

Sebastian Meyer wrote:
>
> I found a bug processing a simple join like
>    SELECT s1.b,s2.b FROM pgsqlbug1 s1, pgsqlbug2 s2 WHERE s1.a = s2.c;
> This bug only occurs whern using pgsql.tcl.
>
>
> Using pgsql.tcl from Tcl however I get:
>
> meyer> SELECT s1.b,s2.b FROM pgsqlbug1 s1, pgsqlbug2 s2 WHERE s1.a = s2.c;
>
> attributes: b b
> lAttributes: {b 1042 -1} {b 1042 -1}
> Tuple 0: {from table 2} {from table 2}
>
> ... what is wrong, obviously.
>

I can't figure out why this happens, but I did find a work around. Try:

SELECT s1.b AS b1, s2.b AS b2 FROM pgsqlbug1 s1, pgsqlbug2 s2 WHERE s1.a
= s2.c ;

This worked fine with pgsql.tcl .

By the way, I was able to duplicate your error using pgsql.tcl. I think
it has to do with how proc pg_exec or pg_result processes columns with
the same name ( .b ).

--
-----------------------------------------------------------------
|John Dzilvelis                                                 |
-----------------------------------------------------------------

From: JohnDz <John_dzilvelis@valpak.com>

John Dzilvelis wrote:
>
> Sebastian Meyer wrote:
> >
> > I found a bug processing a simple join like
> >    SELECT s1.b,s2.b FROM pgsqlbug1 s1, pgsqlbug2 s2 WHERE s1.a = s2.c;
> > This bug only occurs whern using pgsql.tcl.
> >
> >
> > Using pgsql.tcl from Tcl however I get:
> >
> > meyer> SELECT s1.b,s2.b FROM pgsqlbug1 s1, pgsqlbug2 s2 WHERE s1.a =
s2.c;
> >
> > attributes: b b
> > lAttributes: {b 1042 -1} {b 1042 -1}
> > Tuple 0: {from table 2} {from table 2}
> >
> > ... what is wrong, obviously.
> >
>
> I can't figure out why this happens, but I did find a work around. Try:
>
> SELECT s1.b AS b1, s2.b AS b2 FROM pgsqlbug1 s1, pgsqlbug2 s2 WHERE s1.a
> = s2.c ;
>
> This worked fine with pgsql.tcl .
>
> By the way, I was able to duplicate your error using pgsql.tcl. I think
> it has to do with how proc pg_exec or pg_result processes columns with
> the same name ( .b ).

The problem is that pg_exec uses the names of the columns to store
results.

When you make connection pgsql create global array with unique name that
holds parameters for this connection, e.g. pgp0, and returns the name of the
array as result (see pgsql.doc).

When you exec sql command with pq_exec that returns result, then pg_exec
creats new global array, e.g. pgp0_res, that holds information for results
(see pgsql.doc). pg_exec saves the name of the array in pgp0(resName). It also
creates global array, e.g. pgp0_tup, that holds tuples. The name of this
array is saved to pgp0_res(tuples).

pgp0_tup(3,b) will contain data from row 4 and column b, so you MUST use
different names for the columns.


-------------------------------------------------------
   Valentin Iliev     e-mail: vale@aero.vmei.acad.bg
      Dept. of Aeronautics, Tech. univ. of Sofia
-------------------------------------------------------