Thread: Agregates in update?

Agregates in update?

From
Aleksey Dashevsky
Date:
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.


escape character \

From
"Jose' Soares Da Silva"
Date:
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'


Re: [HACKERS] Agregates in update?

From
Bruce Momjian
Date:
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)

Re: [HACKERS] escape character \

From
Bruce Momjian
Date:
>
> 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)

Re: [HACKERS] Agregates in update?

From
"Vadim B. Mikheev"
Date:
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

Re: [HACKERS] escape character \

From
"Jose' Soares Da Silva"
Date:
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'


Re: [HACKERS] Agregates in update?

From
dg@illustra.com (David Gould)
Date:
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>



Re: [HACKERS] Agregates in update?

From
Bruce Momjian
Date:
>
> 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)