Thread

SELECT BUG

Jose Soares <jose@sferacarta.com> — 1999-09-01T10:44:58Z

I think I found some bugs in SELECT...
I have two tables MASTER1 and DETAIL1 both of them with only one field
CODE
of data type VARCHAR but MASTER1.CODE is 11 char long and DETAIL1.CODE
16 char l

hygea=> \d master1
Table    = master1
+----------------------------------+----------------------------------+-------+

|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+

| code                             | varchar()
|    11 |
+----------------------------------+----------------------------------+-------+

hygea=> \d detail1
Table    = detail1
+----------------------------------+----------------------------------+-------+

|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+

| code                             | varchar()
|    16 |
+----------------------------------+----------------------------------+-------+

--I have the following test data into these tables:

hygea=> select * from master1;
code
-----------
a
a1
a13
(3 rows)

hygea=> select * from detail1;
code
----------------
a13
a13
a1
(3 rows)

--if I try to join these two tables I have the following (nothing):

hygea=> select m.*, d.* from master1 m, detail1 d where m.code=d.code;
code|code
----+----
(0 rows)
--and now trying with TRIM function... it works!

hygea=> select m.*, d.* from master1 m, detail1 d where
trim(m.code)=trim(d.code
code       |code
-----------+----------------
a13        |a13
a13        |a13
a1         |a1
(3 rows)

--and last another variation using aliases: (note that I forgot to
change
-- MASTER1 with m and DETAIL1 with d:
hygea=> select master1.*, detail1.* from master1 m, detail1 d where
trim(m.code)
code       |code
-----------+----------------
a          |a13
a1         |a13
a13        |a13
a          |a13
a1         |a13
a13        |a13
a          |a1
a1         |a1
a13        |a1
a          |a13
a1         |a13
a13        |a13
a          |a13
a1         |a13
a13        |a13
a          |a1
a1         |a1
a13        |a1
a          |a13
a1         |a13
a13        |a13
a          |a13
a1         |a13
a13        |a13
a          |a1
a1         |a1
a13        |a1
(27 rows)

Any ideas?

José