Page 1 of 1
Selected column becomes null
Posted: 10.07.2008, 07:50
by 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)
Posted: 10.07.2008, 08:24
by Michael
Hi Cheng Nan,
which database are you using ???
Posted: 10.07.2008, 08:24
by 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
Posted: 10.07.2008, 08:31
by cnliou
Hello! Michael,
It's a zeoslibTESTING revision 370 bug for PostgreSQL.
Posted: 10.07.2008, 12:08
by 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