Selected column becomes null

[cnliou]

CREATE TABLE x1 (c1 VARCHAR(20) PRIMARY KEY);

CREATE TABLE x2 (c1 VARCHAR(20),c2 VARCHAR(3), PRIMARY KEY (c1,c2));

CREATE TABLE x3 (c1 VARCHAR(20),c2 NUMERIC, PRIMARY KEY (c1,c2));

INSERT INTO x1 VALUES ('a');
INSERT INTO x2 VALUES ('a','b');
INSERT INTO x3 VALUES ('a',1.1);

SELECT x2.c2,x3.c2
FROM x1 JOIN x2 USING (c1)
JOIN x3 ON x2.c1=x3.c1

------------------
The SELECT SQL returns NULL for x3.c2, and this is wrong.

This one works fine though:

SELECT x2.c2,x3.c2
FROM x2 JOIN x3 USING (c1)

[Michael]

Hi Cheng Nan,

which database are you using ???

[cnliou]

One more finding. This version yields correct result:

SELECT x2.c2,x3.c2*1.0
FROM x1 JOIN x2 USING (c1)
JOIN x3 ON x2.c1=x3.c1

[cnliou]

Hello! Michael,

It's a zeoslibTESTING revision 370 bug for PostgreSQL.

[mdaems]

Hi,

I think the problem is NOT 100% Postgres limited. It may be the field to field definition resolution that's failing.
I did try the sample on mysql. The query shows the right result there, indeed. So there seems to be no problem. However : when updating the second field value (=1) to 2 this fails. Even worse... Have a look at the monitor log :

2008-07-10 13:01:01 cat: Connect, proto: mysqld-5, msg: CONNECT TO "mysql" AS USER ""
2008-07-10 13:01:19 cat: Execute, proto: mysqld-5, msg: SELECT x2.c2,x3.c2
FROM x1 JOIN x2 USING (c1)
JOIN x3 ON x2.c1=x3.c1


2008-07-10 13:01:19 cat: Execute, proto: mysqld-5, msg: SHOW TABLES FROM ff2008 LIKE 'x2'
2008-07-10 13:01:19 cat: Execute, proto: mysqld-5, msg: SHOW COLUMNS FROM ff2008.x2 LIKE '%'
2008-07-10 13:01:39 cat: Execute, proto: mysqld-5, msg: SHOW KEYS FROM ff2008.x2
2008-07-10 13:01:39 cat: Execute, proto: mysqld-5, msg: UPDATE ff2008.x2 SET c2='2' WHERE c2='b' AND c2='1'
2008-07-10 13:01:41 cat: Execute, proto: mysqld-5, msg: SELECT x2.c2,x3.c2
FROM x1 JOIN x2 USING (c1)
JOIN x3 ON x2.c1=x3.c1

Please, log a bug report. (here we'll forget about it)

Mark