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'

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)

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)

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

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&amp;B=5&gt;

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'

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)