hi, All!
Just installed PG 6.3.1 -- really great job, thank you guys!

But this morning I decided to play a bit with aggregate functions on
update and got a bit strange(for me, at least :-) result.
Here is an exmaple of what I did:
========================================
Let's create two simple tables
  create table a (name text sum float);  
  create table b (name text ,val float);

--and then populate them with rows

  insert into a values ('bob', 0.0);
  insert into a values ('john', 0.0 );
  insert into a values ('mike', 0.0);

  insert into b values ('bob', 1.0);
  insert into b values ('bob', 2.0);  
  insert into b values ('bob', 3.0);
  insert into b values ('john', 4.0);
  insert into b values ('john', 5.0);
  insert into b values ('john', 6.0);
  insert into b values ('mike', 670);
  insert into b values ('mike', 8.0);  
  insert into b values ('mike', 9.0);

--now I want to update "sum" fields of table a in a way they will conatain
--sums of field "val" from table b groupped by name
--and use for this following query:
update a set sum=sum(b.val) where name=b.name ;
--Now 
  select * from a;
-- gives me:
name|sum
----+---
john|  0
mike|  0
bob |708
(3 rows)

===================
Now I'm wondering if there is reall problem in PostgreSQL or my
misundersanding of something important in SQL.

I'm running Linux-2.0.30(Slackware) and gcc-2.7.2.3

Thank you, 
Aleksey.


Fri, 10 Apr 1998 13:31:02 +0300 (IDT)
Aleksey Dashevsky <postgres@luckynet.co.il>
Agregates in update?
Seems there's a bug using ESCAPE character (\) on LIKE clause:

prova=> create table tmp ( a text);
CREATE
prova=> insert into tmp values('\\');
INSERT 178729 1
prova=> select * from tmp where a = '\\';
a
--
\\
(1 row)

prova=> select * from tmp where a like '%\\%';
a
-
(0 rows)

prova=> select * from tmp where a like '%\\\\%';
a
--
\\
(1 row)

-- how many \ do I have to use? 1, 2, 3, 4 or 5 ???

prova=> select * from tmp where a like '%\\\\\%';
a
--
\\
(1 row)
                                                  Jose'


Thu, 16 Apr 1998 11:10:30 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
escape character \
Added to TODO list.
> 
> hi, All!
> Just installed PG 6.3.1 -- really great job, thank you guys!
> 
> But this morning I decided to play a bit with aggregate functions on
> update and got a bit strange(for me, at least :-) result.
> Here is an exmaple of what I did:
> ========================================
> Let's create two simple tables
>   create table a (name text sum float);  
>   create table b (name text ,val float);
> 
> --and then populate them with rows
> 
>   insert into a values ('bob', 0.0);
>   insert into a values ('john', 0.0 );
>   insert into a values ('mike', 0.0);
> 
>   insert into b values ('bob', 1.0);
>   insert into b values ('bob', 2.0);  
>   insert into b values ('bob', 3.0);
>   insert into b values ('john', 4.0);
>   insert into b values ('john', 5.0);
>   insert into b values ('john', 6.0);
>   insert into b values ('mike', 670);
>   insert into b values ('mike', 8.0);  
>   insert into b values ('mike', 9.0);
> 
> --now I want to update "sum" fields of table a in a way they will conatain
> --sums of field "val" from table b groupped by name
> --and use for this following query:
> update a set sum=sum(b.val) where name=b.name ;
> --Now 
>   select * from a;
> -- gives me:
> name|sum
> ----+---
> john|  0
> mike|  0
> bob |708
> (3 rows)
> 
> ===================
> Now I'm wondering if there is reall problem in PostgreSQL or my
> misundersanding of something important in SQL.
> 
> I'm running Linux-2.0.30(Slackware) and gcc-2.7.2.3
> 
> Thank you, 
> Aleksey.
> 
> 
> 


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sun, 26 Apr 1998 23:21:09 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Agregates in update?
> 
> Seems there's a bug using ESCAPE character (\) on LIKE clause:
> 
> prova=> create table tmp ( a text);
> CREATE
> prova=> insert into tmp values('\\');
> INSERT 178729 1
> prova=> select * from tmp where a = '\\';
> a
> --
> \\
> (1 row)
> 
> prova=> select * from tmp where a like '%\\%';
> a
> -
> (0 rows)
> 
> prova=> select * from tmp where a like '%\\\\%';
> a
> --
> \\
> (1 row)
> 
> -- how many \ do I have to use? 1, 2, 3, 4 or 5 ???
> 
> prova=> select * from tmp where a like '%\\\\\%';
> a
> --
> \\
> (1 row)
>                                                   Jose'

The problem is that that \\ is need to input a backslash, and we support
\ to escape special characters like %, so \\\\ is need to test for a
backslash in a LIKE.  Is this not standard?  I suppose not.  Should we
remove the special use of \ in LIKE?  Comments?


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sun, 26 Apr 1998 23:34:25 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] escape character \
Bruce Momjian wrote:
> 
> Added to TODO list.
> > update a set sum=sum(b.val) where name=b.name ;

Is this in standards ???
I thought that subselects should be used in such cases...
And this is one of my plans for 6.4...

Vadim

Mon, 27 Apr 1998 15:26:10 +0800
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Agregates in update?
Vadim wrote: 
> Bruce Momjian wrote:
> > 
> > Added to TODO list.
> > > update a set sum=sum(b.val) where name=b.name ;
> 
> Is this in standards ???
> I thought that subselects should be used in such cases...
> And this is one of my plans for 6.4...
> 
> Vadim

I tried this with Illustra:

	create table a (name text, sum float);  
	create table b (name text, val float);
 
	--and then populate them with rows
	insert into a values ('bob', 0.0);
	...
	insert into b values ('mike', 9.0);
	
	--now I want to update "sum" fields of table a in a way they will
        --conatain sums of field "val" from table b groupped by name
	--and use for this following query:
	update a set sum=sum(b.val) where name=b.name ;
	XL0002:schema b does not exist
	

The problem of course is that the query

    update a set sum=sum(b.val) where name=b.name;

is as Vadim points out, not valid SQL. Probably we should return an error.
I am not especially thrilled with the message above about schemas, but I can
see how it got there as the parser tried to find something (in the absence of
a from list) to give meaning to the term 'b.*'.

-dg

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468 
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
"(Windows NT) version 5.0 will build on a proven system architecture
 and incorporate tens of thousands of bug fixes from version 4.0."
                 -- <http://www.microsoft.com/y2k.asp?A=7&B=5>



Mon, 27 Apr 1998 01:49:10 -0700 (PDT)
dg@illustra.com (David Gould)
Re: [HACKERS] Agregates in update?
On Sun, 26 Apr 1998, Bruce Momjian wrote:

> > 
> > Seems there's a bug using ESCAPE character (\) on LIKE clause:
> > 
> > prova=> create table tmp ( a text);
> > CREATE
> > prova=> insert into tmp values('\\');
> > INSERT 178729 1
> > prova=> select * from tmp where a = '\\';
> > a
> > --
> > \\
> > (1 row)
> > 
> > prova=> select * from tmp where a like '%\\%';
> > a
> > -
> > (0 rows)
> > 
> > prova=> select * from tmp where a like '%\\\\%';
> > a
> > --
> > \\
> > (1 row)
> > 
> > -- how many \ do I have to use? 1, 2, 3, 4 or 5 ???
> > 
> > prova=> select * from tmp where a like '%\\\\\%';
> > a
> > --
> > \\
> > (1 row)
> >                                                   Jose'
> 
> The problem is that that \\ is need to input a backslash, and we support
> \ to escape special characters like %, so \\\\ is need to test for a
> backslash in a LIKE.  Is this not standard?  I suppose not.

The LIKE standard SQL92 has the keyword ESCAPE to specify a character
as escape, like this:

       SELECT * FROM my_table WHERE my_col LIKE '#_pluto' ESCAPE '#';

> Should we remove the special use of \ in LIKE?  Comments?

Obviously we need a character escape (back slash or other) to escape
_ or/and %, but before remove use of back slashes we need to have the
LIKE SQL92 syntax.
                                              Jose'


Mon, 27 Apr 1998 10:01:40 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
Re: [HACKERS] escape character \
> 
> Bruce Momjian wrote:
> > 
> > Added to TODO list.
> > > update a set sum=sum(b.val) where name=b.name ;
> 
> Is this in standards ???
> I thought that subselects should be used in such cases...
> And this is one of my plans for 6.4...

No, not standard, but either need to dis-allow it, or make it work properly.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Mon, 27 Apr 1998 09:25:37 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Agregates in update?