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)
Selected column becomes null
Moderators: gto, cipto_kh, EgonHugeist, olehs
- mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
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 :
Mark
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 :
Please, log a bug report. (here we'll forget about it)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
Mark