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ï¿½



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é
Wed, 01 Sep 1999 12:44:58 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
SELECT BUG
=?iso-8859-1?Q?Jos=E9?= 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

Wed, 01 Sep 1999 09:30:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SELECT BUG 
You mean that "a1 " is not equal to "a1      " ?
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;

code      |len_of_code|code1       |len_of_code1
----------+-----------+------------+------------
a1        |         10|a1          |          15
(1 row)


in this case the test code = code1 is true even if these fields have
different number of trailling spaces.

Therefore if the above test is OK there's a bug on:

        select m.*, d.* from master1 m, detail1 d where m.code=d.code;


Josï¿½


Tom Lane ha scritto:

> =?iso-8859-1?Q?Jos=E9?= 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


You mean that "a1 " is not equal to "a1     
" ?
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;
code      |len_of_code|code1      
|len_of_code1
----------+-----------+------------+------------
a1        |        
10|a1          |         
15
(1 row)
 
in this case the test code = code1 is true even if these fields
have
different number of trailling spaces.
Therefore if the above test is OK there's a bug on:
        select m.*, d.* from
master1 m, detail1 d where m.code=d.code;
 
José
 
Tom Lane ha scritto:
=?iso-8859-1?Q?Jos=E9?= 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
Wed, 01 Sep 1999 18:22:27 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
Re: [HACKERS] SELECT BUG
=?iso-8859-1?Q?Jos=E9?= 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

Thu, 02 Sep 1999 09:19:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SELECT BUG 
> 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

Thu, 02 Sep 1999 13:59:00 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] SELECT BUG
Sorry for the confusion:

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)

--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)


the same example in informix-SE gives me this:
----------------------------------------------
code code

a1   a1
a13  a13


Josï¿½


Sorry for the confusion:
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)
--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)
 
the same example in informix-SE gives me this:
----------------------------------------------
code code
a1   a1
a13  a13
 
José
Thu, 02 Sep 1999 18:47:41 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
Re: [HACKERS] SELECT BUG
=?iso-8859-1?Q?Jos=E9?= 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

Thu, 02 Sep 1999 13:33:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SELECT BUG 
> 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

Fri, 03 Sep 1999 02:18:41 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] SELECT BUG
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

Fri, 03 Sep 1999 01:59:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SELECT BUG 


Tom Lane ha scritto:

> =?iso-8859-1?Q?Jos=E9?= 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


Fri, 03 Sep 1999 12:57:00 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
Re: [HACKERS] SELECT BUG
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);

(I know there's an error in this syntax, but I don't know why PostgreSQL
finds it good and execute a strange query)

code       |code1           |code
-----------+----------------+----------------
a          |a               |a13
a1         |a1              |a13
a13        |a13             |a13
a          |a               |a1
a1         |a1              |a1
a13        |a13             |a1
a          |a               |a13
a1         |a1              |a13
a13        |a13             |a13
a          |a               |a13
a1         |a1              |a13
a13        |a13             |a13
a          |a               |a1
a1         |a1              |a1
a13        |a13             |a1
a          |a               |a13
a1         |a1              |a13
a13        |a13             |a13
a          |a               |a13
a1         |a1              |a13
a13        |a13             |a13
a          |a               |a1
a1         |a1              |a1
a13        |a13             |a1
a          |a               |a13
a1         |a1              |a13
a13        |a13             |a13
(27 rows)

Any idea ?

Josï¿½



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);
(I know there's an error in this syntax, but I don't know why PostgreSQL
finds it good and execute a strange query)
code       |code1          
|code
-----------+----------------+----------------
a          |a              
|a13
a1         |a1             
|a13
a13        |a13            
|a13
a          |a              
|a1
a1         |a1             
|a1
a13        |a13            
|a1
a          |a              
|a13
a1         |a1             
|a13
a13        |a13            
|a13
a          |a              
|a13
a1         |a1             
|a13
a13        |a13            
|a13
a          |a              
|a1
a1         |a1             
|a1
a13        |a13            
|a1
a          |a              
|a13
a1         |a1             
|a13
a13        |a13            
|a13
a          |a              
|a13
a1         |a1             
|a13
a13        |a13            
|a13
a          |a              
|a1
a1         |a1             
|a1
a13        |a13            
|a1
a          |a              
|a13
a1         |a1             
|a13
a13        |a13            
|a13
(27 rows)
Any idea ?
José
Fri, 03 Sep 1999 14:28:54 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
Re: [HACKERS] SELECT BUG
=?iso-8859-1?Q?Jos=E9?= 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

Fri, 03 Sep 1999 11:04:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SELECT BUG 
=?iso-8859-1?Q?Jos=E9?= 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

Fri, 03 Sep 1999 11:13:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SELECT BUG 


Tom Lane ha scritto:

> =?iso-8859-1?Q?Jos=E9?= 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ï¿½


Tue, 07 Sep 1999 12:37:17 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
Re: [HACKERS] SELECT BUG
Tom Lane ha scritto:

> =?iso-8859-1?Q?Jos=E9?= 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
>
> ************

Yes, Tom, now it works, but...
Informix gives me a different result. Who is right ?


prova=> select  mcode, dcode from master m, detail d where mcode=dcode;
mcode|dcode
-----+-----
(0 rows)

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)


INFORMIX:
SQL:   New  Run  Modify  Use-editor  Output  Choose  Save  Info  Drop
Exit
Run the current SQL statements.
----------------------- hygea@hygea ------------ Press CTRL-W for Help
--------
mcode dcode
a1    a1
a13   a13


Josï¿½




 
Tom Lane ha scritto:
=?iso-8859-1?Q?Jos=E9?= 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
************
Yes, Tom, now it works, but...
Informix gives me a different result. Who is right ?
 
prova=> select  mcode, dcode from master m, detail d where
mcode=dcode;
mcode|dcode
-----+-----
(0 rows)
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)
 
INFORMIX:
SQL:   New  Run  Modify  Use-editor 
Output  Choose  Save  Info  Drop  Exit
Run the current SQL statements.
----------------------- hygea@hygea ------------ Press CTRL-W for
Help --------
mcode dcode
a1    a1
a13   a13
 
José
Tue, 07 Sep 1999 12:59:24 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
Re: [HACKERS] SELECT BUG
=?iso-8859-1?Q?Jos=E9?= 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

Tue, 07 Sep 1999 09:35:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SELECT BUG 
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 
> 
> Tom Lane ha scritto:
> 
> > =?iso-8859-1?Q?Jos=E9?= 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, Pennsylvania 19026

Tue, 7 Sep 1999 11:39:56 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] SELECT BUG