Thread: SELECT BUG
<tt>I think I found some bugs in SELECT...</tt><br /><tt>I have two tables MASTER1 and DETAIL1 both of them with only onefield CODE</tt><br /><tt>of data type VARCHAR but MASTER1.CODE is 11 char long and DETAIL1.CODE 16 char l</tt><tt></tt><p><tt>hygea=>\d master1</tt><br /><tt>Table = master1</tt><br /><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>| Field | Type | Length|</tt><br /><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>| code | varchar() | 11 |</tt><br /><tt>+----------------------------------+----------------------------------+-------+</tt><tt></tt><p><tt>hygea=>\d detail1</tt><br/><tt>Table = detail1</tt><br /><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>| Field | Type | Length|</tt><br /><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>| code | varchar() | 16 |</tt><br /><tt>+----------------------------------+----------------------------------+-------+</tt><tt></tt><p><tt>--Ihave the followingtest data into these tables:</tt><tt></tt><p><tt>hygea=> select * from master1;</tt><br /><tt>code</tt><br /><tt>-----------</tt><br/><tt>a</tt><br /><tt>a1</tt><br /><tt>a13</tt><br /><tt>(3 rows)</tt><tt></tt><p><tt>hygea=>select * from detail1;</tt><br /><tt>code</tt><br /><tt>----------------</tt><br /><tt>a13</tt><br/><tt>a13</tt><br /><tt>a1</tt><br /><tt>(3 rows)</tt><tt></tt><p><tt>--if I try to join these two tablesI have the following (nothing):</tt><tt></tt><p><tt>hygea=> select m.*, d.* from master1 m, detail1 d where m.code=d.code;</tt><br/><tt>code|code</tt><br /><tt>----+----</tt><br /><tt>(0 rows)</tt><br /><tt>--and now trying withTRIM function... it works!</tt><tt></tt><p><tt>hygea=> select m.*, d.* from master1 m, detail1 d where trim(m.code)=trim(d.code</tt><br/><tt>code |code</tt><br /><tt>-----------+----------------</tt><br /><tt>a13 |a13</tt><br /><tt>a13 |a13</tt><br /><tt>a1 |a1</tt><br /><tt>(3 rows)</tt><tt></tt><p><tt>--andlast another variation using aliases: (note that I forgot to change</tt><br /><tt>-- MASTER1with m and DETAIL1 with d:</tt><br /><tt>hygea=> select master1.*, detail1.* from master1 m, detail1 d where trim(m.code)</tt><br/><tt>code |code</tt><br /><tt>-----------+----------------</tt><br /><tt>a |a13</tt><br/><tt>a1 |a13</tt><br /><tt>a13 |a13</tt><br /><tt>a |a13</tt><br /><tt>a1 |a13</tt><br/><tt>a13 |a13</tt><br /><tt>a |a1</tt><br /><tt>a1 |a1</tt><br /><tt>a13 |a1</tt><br/><tt>a |a13</tt><br /><tt>a1 |a13</tt><br /><tt>a13 |a13</tt><br /><tt>a |a13</tt><br/><tt>a1 |a13</tt><br /><tt>a13 |a13</tt><br /><tt>a |a1</tt><br /><tt>a1 |a1</tt><br/><tt>a13 |a1</tt><br /><tt>a |a13</tt><br /><tt>a1 |a13</tt><br /><tt>a13 |a13</tt><br/><tt>a |a13</tt><br /><tt>a1 |a13</tt><br /><tt>a13 |a13</tt><br /><tt>a |a1</tt><br/><tt>a1 |a1</tt><br /><tt>a13 |a1</tt><br /><tt>(27 rows)</tt><tt></tt><p>Any ideas? <p>José <br/>
José Soares <jose@sferacarta.com> writes:
> --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)
Looks to me like you have differing numbers of trailing spaces in the
entries in each table. If so, this is not a bug.
regards, tom lane
<tt>You mean that "a1 " is not equal to "a1 " ?</tt><br /><tt>but PostgreSQL has a different behavior in the followingexample:</tt><tt></tt><p><tt>hygea=> select code,len(code) as len_of_code,code1, len(code1) as len_of_code1</tt><br/><tt>from master1 where code = code1;</tt><tt></tt><p><tt>code |len_of_code|code1 |len_of_code1</tt><br/><tt>----------+-----------+------------+------------</tt><br /><tt>a1 | 10|a1 | 15</tt><br /><tt>(1 row)</tt><br /><tt></tt> <tt></tt><p><tt>in this case the test code = code1is true even if these fields have</tt><br /><tt>different number of trailling spaces.</tt><tt></tt><p><tt>Thereforeif the above test is OK there's a bug on:</tt><tt></tt><p><tt> select m.*, d.*from master1 m, detail1 d where m.code=d.code;</tt><br /><tt></tt> <tt></tt><p><tt>José</tt><br /><tt></tt> <tt></tt><p><tt>TomLane ha scritto:</tt><blockquote type="CITE"><tt>José Soares <jose@sferacarta.com> writes:</tt><br/><tt>> --I have the following test data into these tables:</tt><tt></tt><p><tt>> hygea=> select* from master1;</tt><br /><tt>> code</tt><br /><tt>> -----------</tt><br /><tt>> a</tt><br /><tt>> a1</tt><br/><tt>> a13</tt><br /><tt>> (3 rows)</tt><tt></tt><p><tt>> hygea=> select * from detail1;</tt><br /><tt>>code</tt><br /><tt>> ----------------</tt><br /><tt>> a13</tt><br /><tt>> a13</tt><br /><tt>> a1</tt><br/><tt>> (3 rows)</tt><tt></tt><p><tt>> --if I try to join these two tables I have the following (nothing):</tt><tt></tt><p><tt>>hygea=> select m.*, d.* from master1 m, detail1 d where m.code=d.code;</tt><br /><tt>>code|code</tt><br /><tt>> ----+----</tt><br /><tt>> (0 rows)</tt><br /><tt>> --and now trying with TRIMfunction... it works!</tt><tt></tt><p><tt>> hygea=> select m.*, d.* from master1 m, detail1 d where</tt><br /><tt>>trim(m.code)=trim(d.code</tt><br /><tt>> code |code</tt><br /><tt>> -----------+----------------</tt><br/><tt>> a13 |a13</tt><br /><tt>> a13 |a13</tt><br /><tt>> a1 |a1</tt><br /><tt>> (3 rows)</tt><tt></tt><p><tt>Looks to me like you have differing numbers of trailing spacesin the</tt><br /><tt>entries in each table. If so, this is not a bug.</tt><tt></tt><p><tt> regards, tom lane</tt></blockquote>
José Soares <jose@sferacarta.com> writes:
> You mean that "a1 " is not equal to "a1 " ?
I don't think they're equal ... do you? That is what trim()
is for, after all.
> but PostgreSQL has a different behavior in the following example:
> hygea=> select code,len(code) as len_of_code,code1, len(code1) as
> len_of_code1
> from master1 where code = code1;
What is this "len" function? I don't find one in the standard
distribution. I suspect you have some locally developed function
that returns the attrmod of the column --- which is the maximum
length of a varchar, but is not the same as the *actual* length
of the value.
> in this case the test code = code1 is true even if these fields have
> different number of trailling spaces.
I see no such behavior:
regression=> create table z2 (code varchar(10), code1 varchar(15));
CREATE
regression=> select code,len(code) from z2;
ERROR: No such function 'len' with the specified attributes
regression=> insert into z2 values ('a1', 'a1');
INSERT 282452 1
regression=> insert into z2 values ('a1 ', 'a1 ');
INSERT 282453 1
regression=> select *,length(code),length(code1) from z2 ;
code|code1 |length|length
----+---------+------+------
a1 |a1 | 2| 2
a1 |a1 | 4| 9
(2 rows)
regression=> select *,length(code),length(code1) from z2 where code = code1;
code|code1|length|length
----+-----+------+------
a1 |a1 | 2| 2
(1 row)
Can you provide a reproducible example?
regards, tom lane
> You mean that "a1 " is not equal to "a1 " ?
> but PostgreSQL has a different behavior in the following example:
You will have to give more details on your schema and data entry for
us to see the problem; things look good to me too. Examples below...
- Thomas
postgres=> create table t1 (v3 varchar(3), v5 varchar(5), c3 char(3),
c5 char(5));
CREATE
postgres=> insert into t1 values ('a1 ', 'a1 ', 'a1', 'a1');
INSERT 150220 1
postgres=> select * from t1 where v3 = v5;
v3|v5|c3|c5
--+--+--+--
(0 rows)
postgres=> select * from t1 where c3 = c5;
v3 |v5 |c3 |c5
---+-----+---+-----
a1 |a1 |a1 |a1
(1 row)
postgres=> select * from t1 where trim(v3) = trim(v5);
v3 |v5 |c3 |c5
---+-----+---+-----
a1 |a1 |a1 |a1
(1 row)
postgres=> insert into t1 values ('a2', 'a2', 'a2', 'a2');
INSERT 150221 1
postgres=> select * from t1 where v3 = v5;
v3|v5|c3 |c5
--+--+---+-----
a2|a2|a2 |a2
(1 row)
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
<tt>Sorry for the confusion:</tt><tt></tt><p><tt>Here an example...</tt><tt></tt><p><tt>create table master(mcode
char(11),mcode1 char(16));</tt><br /><tt>create table detail(dcode char(16));</tt><br /><tt>insert into master values
('a','a');</tt><br/><tt>insert into master values ('a1','a1');</tt><br /><tt>insert into master values
('a13','a13');</tt><br/><tt>insert into detail values ('a13');</tt><br /><tt>insert into detail values ('a1');</tt><br
/><tt>insertinto detail values ('a13');</tt><tt></tt><p><tt>--in the following example mcode is long 11 and mcode1 is
long16</tt><br /><tt>--but mcode=mcode1 is true:</tt><tt></tt><p><tt>select * from master where mcode=mcode1;</tt><br
/><tt>mcode |mcode1</tt><br /><tt>-----------+----------------</tt><br /><tt>a |a</tt><br /><tt>a1
|a1</tt><br/><tt>a13 |a13</tt><br /><tt>(3 rows)</tt><tt></tt><p><tt>--in the following example mcode is long 11
anddcode1 is long 16</tt><br /><tt>--but mcode=dcode1 is false:</tt><tt></tt><p><tt>select mcode, dcode from master m,
detaild where mcode=dcode;</tt><br /><tt>mcode|dcode</tt><br /><tt>-----+-----</tt><br /><tt>(0 rows)</tt><br
/><tt></tt> <tt></tt><p><tt>thesame example in informix-SE gives me this:</tt><br
/><tt>----------------------------------------------</tt><br/><tt>code code</tt><tt></tt><p><tt>a1 a1</tt><br
/><tt>a13 a13</tt><br /><tt></tt> <tt></tt><p><tt>José</tt>
José Soares <jose@sferacarta.com> writes:
> Here an example...
> create table master(mcode char(11), mcode1 char(16));
> create table detail(dcode char(16));
> insert into master values ('a','a');
> insert into master values ('a1','a1');
> insert into master values ('a13','a13');
> insert into detail values ('a13');
> insert into detail values ('a1');
> insert into detail values ('a13');
> --in the following example mcode is long 11 and mcode1 is long 16
> --but mcode=mcode1 is true:
> select * from master where mcode=mcode1;
> mcode |mcode1
> -----------+----------------
> a |a
> a1 |a1
> a13 |a13
> (3 rows)
On looking at the bpchar (ie, fixed-length char) comparison functions,
I see that they *do* strip trailing blanks before comparing. varchar
and text do not do this --- they assume trailing blanks are real data.
This inconsistency bothers me: I've always thought that char(),
varchar(), and text() are functionally interchangeable, but it seems
that's not so. Is this behavior mandated by SQL92?
> --in the following example mcode is long 11 and dcode1 is long 16
> --but mcode=dcode1 is false:
> select mcode, dcode from master m, detail d where mcode=dcode;
> mcode|dcode
> -----+-----
> (0 rows)
Oh my, that's interesting. Executing your query with current sources
gives me:
regression=> select mcode, dcode from master m, detail d where mcode=dcode;
mcode |dcode
-----------+----------------
a1 |a1
a13 |a13
a13 |a13
(3 rows)
When I "explain" this, I see that I am getting a mergejoin plan.
Are you getting a hash join, perhaps?
bpchareq is marked hashjoinable in pg_operator, but if its behavior
includes blank-stripping then that is WRONG. Hashjoin is only safe
for operators that represent bitwise equality...
regards, tom lane
> On looking at the bpchar (ie, fixed-length char) comparison functions,
> I see that they *do* strip trailing blanks before comparing. varchar
> and text do not do this --- they assume trailing blanks are real data.
> This inconsistency bothers me: I've always thought that char(),
> varchar(), and text() are functionally interchangeable, but it seems
> that's not so. Is this behavior mandated by SQL92?
I was pretty sure it is (though of course "text" isn't an SQL92 type).
What I'm finding in Date and Darwen and my draft SQL92 document is
that whether the default character set uses SPACE PAD or NO PAD
collation attribute for a character set is implementation defined.
I haven't found any explicit reference to a distinction between CHAR
and VARCHAR in the docs nor a discussion of the SQL_TEXT character set
wrt this topic. So apparently SQL_TEXT properties are implementation
defined too. But we should look into it more before deciding to change
anything because afaik the current behavior has been the same in
Postgres forever...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> This inconsistency bothers me: I've always thought that char(),
>> varchar(), and text() are functionally interchangeable, but it seems
>> that's not so. Is this behavior mandated by SQL92?
> I haven't found any explicit reference to a distinction between CHAR
> and VARCHAR in the docs nor a discussion of the SQL_TEXT character set
> wrt this topic. So apparently SQL_TEXT properties are implementation
> defined too. But we should look into it more before deciding to change
> anything because afaik the current behavior has been the same in
> Postgres forever...
I'm not necessarily arguing for a change; if you're satisfied that
the existing comparison logic obeys the spec, it's OK with me.
(Ignoring trailing blanks in bpchar does seem reasonable when you
think about it.)
But if it is correct, then we need to turn off oprcanhash for bpchareq.
Odd that no one has noticed this before.
Some doc updates might be in order too...
regards, tom lane
Tom Lane ha scritto:
> José Soares <jose@sferacarta.com> writes:
> > Here an example...
> > create table master(mcode char(11), mcode1 char(16));
> > create table detail(dcode char(16));
> > insert into master values ('a','a');
> > insert into master values ('a1','a1');
> > insert into master values ('a13','a13');
> > insert into detail values ('a13');
> > insert into detail values ('a1');
> > insert into detail values ('a13');
>
> > --in the following example mcode is long 11 and mcode1 is long 16
> > --but mcode=mcode1 is true:
>
> > select * from master where mcode=mcode1;
> > mcode |mcode1
> > -----------+----------------
> > a |a
> > a1 |a1
> > a13 |a13
> > (3 rows)
>
> On looking at the bpchar (ie, fixed-length char) comparison functions,
> I see that they *do* strip trailing blanks before comparing. varchar
> and text do not do this --- they assume trailing blanks are real data.
>
> This inconsistency bothers me: I've always thought that char(),
> varchar(), and text() are functionally interchangeable, but it seems
> that's not so. Is this behavior mandated by SQL92?
>
> > --in the following example mcode is long 11 and dcode1 is long 16
> > --but mcode=dcode1 is false:
>
> > select mcode, dcode from master m, detail d where mcode=dcode;
> > mcode|dcode
> > -----+-----
> > (0 rows)
>
> Oh my, that's interesting. Executing your query with current sources
> gives me:
>
> regression=> select mcode, dcode from master m, detail d where mcode=dcode;
> mcode |dcode
> -----------+----------------
> a1 |a1
> a13 |a13
> a13 |a13
> (3 rows)
>
> When I "explain" this, I see that I am getting a mergejoin plan.
> Are you getting a hash join, perhaps?
Yes.
> prova=> explain select mcode, dcode from master m, detail d where
> mcode=dcode;
> NOTICE: QUERY PLAN:
>
> Hash Join (cost=156.00 rows=1001 width=24)
> -> Seq Scan on detail d (cost=43.00 rows=1000 width=12)
> -> Hash (cost=43.00 rows=1000 width=12)
> -> Seq Scan on master m (cost=43.00 rows=1000 width=12)
>
> EXPLAIN
>
José
>
>
> bpchareq is marked hashjoinable in pg_operator, but if its behavior
> includes blank-stripping then that is WRONG. Hashjoin is only safe
> for operators that represent bitwise equality...
>
> regards, tom lane
<tt>And now the other SELECT bug in the same data:</tt><tt></tt><p><tt>select master1.*, detail1.*</tt><br /><tt>from master1m, detail1 d</tt><br /><tt>where trim(m.code)=trim(d.code);</tt><tt></tt><p><tt>(I know there's an error in this syntax,but I don't know why PostgreSQL</tt><br /><tt>finds it good and execute a strange query)</tt><tt></tt><p><tt>code |code1 |code</tt><br /><tt>-----------+----------------+----------------</tt><br/><tt>a |a |a13</tt><br /><tt>a1 |a1 |a13</tt><br /><tt>a13 |a13 |a13</tt><br /><tt>a |a |a1</tt><br /><tt>a1 |a1 |a1</tt><br /><tt>a13 |a13 |a1</tt><br /><tt>a |a |a13</tt><br /><tt>a1 |a1 |a13</tt><br /><tt>a13 |a13 |a13</tt><br /><tt>a |a |a13</tt><br /><tt>a1 |a1 |a13</tt><br/><tt>a13 |a13 |a13</tt><br /><tt>a |a |a1</tt><br /><tt>a1 |a1 |a1</tt><br /><tt>a13 |a13 |a1</tt><br /><tt>a |a |a13</tt><br /><tt>a1 |a1 |a13</tt><br /><tt>a13 |a13 |a13</tt><br/><tt>a |a |a13</tt><br /><tt>a1 |a1 |a13</tt><br /><tt>a13 |a13 |a13</tt><br /><tt>a |a |a1</tt><br /><tt>a1 |a1 |a1</tt><br /><tt>a13 |a13 |a1</tt><br /><tt>a |a |a13</tt><br/><tt>a1 |a1 |a13</tt><br /><tt>a13 |a13 |a13</tt><br /><tt>(27 rows)</tt><p>Anyidea ? <p>José <br />
José Soares <jose@sferacarta.com> writes:
>> When I "explain" this, I see that I am getting a mergejoin plan.
>> Are you getting a hash join, perhaps?
> Yes.
> prova=> explain select mcode, dcode from master m, detail d where
> mcode=dcode;
> NOTICE: QUERY PLAN:
>
> Hash Join (cost=156.00 rows=1001 width=24)
> -> Seq Scan on detail d (cost=43.00 rows=1000 width=12)
> -> Hash (cost=43.00 rows=1000 width=12)
> -> Seq Scan on master m (cost=43.00 rows=1000 width=12)
>
> EXPLAIN
OK, do this:
update pg_operator set oprcanhash = 'f' where oid = 1054;
and I think you'll be OK. I will put that change into the sources.
regards, tom lane
José Soares <jose@sferacarta.com> writes:
> And now the other SELECT bug in the same data:
> select master1.*, detail1.*
> from master1 m, detail1 d
> where trim(m.code)=trim(d.code);
This one is definitely pilot error. Since you've renamed master1 and
detail1 in the FROM clause, your use of the original names in the SELECT
list is treated as adding more FROM items. Effectively your query is
select m2.*, d2.*
from master1 m, detail1 d, master1 m2, detail1 d2
where trim(m.code)=trim(d.code);
You're getting a four-way join with only one restriction clause...
There was a thread just the other day about whether we ought to allow
queries like this, because of someone else making exactly the same
error. I believe allowing tables to be referenced without FROM entries
is a holdover from the old Postquel language that's not found in SQL92.
Maybe we should get rid of it on the grounds that it creates confusion.
regards, tom lane
Tom Lane ha scritto: > José Soares <jose@sferacarta.com> writes: > > And now the other SELECT bug in the same data: > > select master1.*, detail1.* > > from master1 m, detail1 d > > where trim(m.code)=trim(d.code); > > This one is definitely pilot error. Since you've renamed master1 and > detail1 in the FROM clause, your use of the original names in the SELECT > list is treated as adding more FROM items. Effectively your query is > > select m2.*, d2.* > from master1 m, detail1 d, master1 m2, detail1 d2 > where trim(m.code)=trim(d.code); > > You're getting a four-way join with only one restriction clause... > > There was a thread just the other day about whether we ought to allow > queries like this, because of someone else making exactly the same > error. I believe allowing tables to be referenced without FROM entries > is a holdover from the old Postquel language that's not found in SQL92. > Maybe we should get rid of it on the grounds that it creates confusion. > > regards, tom lane > > PostgreSQL should raise a syntax error like Informix and Oracle do. > ************ > INFORMIX: > > select master1.*, detail1.* from master1 m, detail1 d where mcode=dcode; > # ^ > # 522: Table (master1) not selected in query. > # > ------------------------------------------------------------------------ > ORACLE: > > select master1.*, detail1.* from master1 m, detail1 d where mcode=dcode > * > ERROR at line1: > ORA-00942: table or view does not exist > > José
<p>Tom Lane ha scritto: <blockquote type="CITE">José Soares <jose@sferacarta.com> writes: <br />>> When I"explain" this, I see that I am getting a mergejoin plan. <br />>> Are you getting a hash join, perhaps? <p>> Yes.<p>> prova=> explain select mcode, dcode from master m, detail d where <br />> mcode=dcode; <br />> NOTICE: QUERY PLAN: <br />> <br />> Hash Join (cost=156.00 rows=1001 width=24) <br />> -> Seq Scan on detaild (cost=43.00 rows=1000 width=12) <br />> -> Hash (cost=43.00 rows=1000 width=12) <br />> ->Seq Scan on master m (cost=43.00 rows=1000 width=12) <br />> <br />> EXPLAIN <p>OK, do this: <p>update pg_operatorset oprcanhash = 'f' where oid = 1054; <p>and I think you'll be OK. I will put that change into the sources.<p> regards, tom lane <p><tt>************</tt></blockquote><tt>Yes, Tom, now it works, but...</tt><br/><tt>Informix gives me a different result. Who is right ?</tt><br /><tt></tt> <tt></tt><p><tt>prova=> select mcode, dcode from master m, detail d where mcode=dcode;</tt><br /><tt>mcode|dcode</tt><br /><tt>-----+-----</tt><br/><tt>(0 rows)</tt><tt></tt><p><tt>prova=> update pg_operator set oprcanhash = 'f' where oid= 1054;</tt><br /><tt>UPDATE 1</tt><br /><tt>prova=> select mcode, dcode from master m, detail d where mcode=dcode;</tt><br/><tt>mcode |dcode</tt><br /><tt>-----------+----------------</tt><br /><tt>a1 |a1</tt><br/><tt>a13 |a13</tt><br /><tt>a13 |a13</tt><br /><tt>(3 rows)</tt><br /><tt></tt> <tt></tt><p><tt>INFORMIX:</tt><br/><tt>SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit</tt><br /><tt>Run the current SQL statements.</tt><br /><tt>----------------------- hygea@hygea ------------ PressCTRL-W for Help --------</tt><br /><tt>mcode dcode</tt><br /><tt>a1 a1</tt><br /><tt>a13 a13</tt><br /> <p>José<br /> <br />
José Soares <jose@sferacarta.com> writes:
> Yes, Tom, now it works, but...
> Informix gives me a different result. Who is right ?
Hard to tell, since I don't know what your data is.
> prova=> update pg_operator set oprcanhash = 'f' where oid = 1054;
> UPDATE 1
> prova=> select mcode, dcode from master m, detail d where mcode=dcode;
> mcode |dcode
> -----------+----------------
> a1 |a1
> a13 |a13
> a13 |a13
> (3 rows)
... but all three of those sure look equal to me ...
regards, tom lane
[Charset iso-8859-1 unsupported, filtering to ASCII...] > > > Tom Lane ha scritto: > > > José Soares <jose@sferacarta.com> writes: > > > And now the other SELECT bug in the same data: > > > select master1.*, detail1.* > > > from master1 m, detail1 d > > > where trim(m.code)=trim(d.code); > > > > This one is definitely pilot error. Since you've renamed master1 and > > detail1 in the FROM clause, your use of the original names in the SELECT > > list is treated as adding more FROM items. Effectively your query is > > > > select m2.*, d2.* > > from master1 m, detail1 d, master1 m2, detail1 d2 > > where trim(m.code)=trim(d.code); > > > > You're getting a four-way join with only one restriction clause... > > > > There was a thread just the other day about whether we ought to allow > > queries like this, because of someone else making exactly the same > > error. I believe allowing tables to be referenced without FROM entries > > is a holdover from the old Postquel language that's not found in SQL92. > > Maybe we should get rid of it on the grounds that it creates confusion. > > > > regards, tom lane > > > > > > PostgreSQL should raise a syntax error like Informix and Oracle do. We sould at least give them an elog(NOTICE) to say we are doing something special, no? > > > ************ > > INFORMIX: > > > > select master1.*, detail1.* from master1 m, detail1 d where mcode=dcode; > > # ^ > > # 522: Table (master1) not selected in query. > > # > > ------------------------------------------------------------------------ > > ORACLE: > > > > select master1.*, detail1.* from master1 m, detail1 d where mcode=dcode > > * > > ERROR at line1: > > ORA-00942: table or view does not exist > > > > > > Jos_ > > > ************ > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026