Обсуждение: Weird problem with aggregate functions
Hi everybody, I got a problem with aggregate functions from VB 6. SUM, AVG, STDDEV, and VARIANCE doesn't work on int4 types (but they're working on float8), whereas MAX and MIN work also on int4... that's weird! Even more strange, the above functions work both from psql and from pgAdmin II... but not in the following code: Private Sub Form_Load() Dim conn As ADODB.Connection Dim rec As ADODB.Recordset Dim cmd As Command Set conn = New Connection conn.CursorLocation = adUseClient conn.Open "DSN=DatabaseSIA" Set rec = New Recordset rec.Open "Select SUM(popolazione) from tab2531", conn, adOpenStatic, adLockReadOnly Me.Print rec.Fields(0).Name & ": " & rec.Fields(0).Value rec.Close End Sub where tab2531 is defined as follows: CREATE TABLE "tab2531" ( "abbreviazione" text, "regione" int4, "popolazione" int4, "densita" int4, "natalita" float8, "mortalita" float8, "mortalinfant" float8, "nuzialita" float8 ); The program halts with this error (translated from italian): "data provider or another service returned E_FAIL" I tested this with 7.01.0008 and 7.01.0007 both on WinNT and Win2000... same results. I also played with driver options. commlog doesn't report any error. Ah, I use ADO 2.6 (tested also with 2.5, same results) Any idea? Best regards Andrea Aime
Andrea Aime wrote: > > Hi everybody, > I got a problem with aggregate functions from VB 6. SUM, AVG, STDDEV, > and VARIANCE doesn't work on int4 types (but they're working on float8), > whereas MAX and MIN work also on int4... that's weird! > Even more strange, the above functions work both from psql and from > pgAdmin II... but not in the following code: > > Private Sub Form_Load() > Dim conn As ADODB.Connection > Dim rec As ADODB.Recordset > Dim cmd As Command > > Set conn = New Connection > conn.CursorLocation = adUseClient This indicates ADO to use Microsoft Cursor Service for OLE DB. Microsoft Cursor Service seems to expect the server to return e.g. int4 type for sum(int4) but PostgreSQL returns numeric type as sum(int4). Probably you would get the expected result if you use sum(..)::int4. regards, Hiroshi Inoue
Thanks, it works!!! But why doesn't it work without the cast? This is a good workaround, but the standard behaviour is a bug, isn't it? Best regards Andrea Aime Gustavo Boiko wrote: > > if you are using ODBC driver, try to execute "SELECT SUM(field)::int4" or "SELECT > CAST(SUM(field) as int4)" > > The results are OK when I tried this. > > >Hi everybody, > >I got a problem with aggregate functions from VB 6. SUM, AVG, STDDEV, > >and VARIANCE doesn't work on int4 types (but they're working on float8), > >whereas MAX and MIN work also on int4... that's weird! > >Even more strange, the above functions work both from psql and from > >pgAdmin II... but not in the following code: > > > >Private Sub Form_Load() > > Dim conn As ADODB.Connection > > Dim rec As ADODB.Recordset > > Dim cmd As Command > > > > Set conn = New Connection > > conn.CursorLocation = adUseClient > > conn.Open "DSN=DatabaseSIA" > > > > Set rec = New Recordset > > > > rec.Open "Select SUM(popolazione) from tab2531", conn, adOpenStatic, > >adLockReadOnly > > Me.Print rec.Fields(0).Name & ": " & rec.Fields(0).Value > > rec.Close > >End Sub > > > >where tab2531 is defined as follows: > > > >CREATE TABLE "tab2531" ( > > "abbreviazione" text, > > "regione" int4, > > "popolazione" int4, > > "densita" int4, > > "natalita" float8, > > "mortalita" float8, > > "mortalinfant" float8, > > "nuzialita" float8 > >); > > > >The program halts with this error (translated from italian): "data > >provider > >or another service returned E_FAIL" > >I tested this with 7.01.0008 and 7.01.0007 both on WinNT and Win2000... > >same results. I also played with driver options. commlog doesn't report > >any error. Ah, I use ADO 2.6 (tested also with 2.5, same results) > >Any idea? > >Best regards > >Andrea Aime > > > >---------------------------(end of broadcast)--------------------------- > >TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > > > > > > >------=_ieG_NextPart_40087518038424658754559999576835.1 > -- > Gustavo Pichorim Boiko > ---------------------------------------- > Slackware 8.0 - Kernel 2.4.9-ac9 > Linux User: 231003 > ICQ (Licq): 86128708 > ---------------------------------------- > gustavoboiko@ieg.com.br > > http://www.ieg.com.br
"Andrea Aime" <aaime@comune.modena.it> writes:
> Thanks, it works!!! But why doesn't it work without the
> cast? This is a good workaround, but the standard behaviour
> is a bug, isn't it?
No, it isn't. We *used* to return int4 as the result of sum(int4),
etc, but that tends to suffer from overflow. And the deficiency of
using int4 as the result of avg() should be pretty obvious ;-)
The SQL spec says that we can use any exact numeric datatype we please
for these functions:
b) If SUM is specified and DT is exact numeric with scale
S, then the data type of the result is exact numeric with
implementation-defined precision and scale S.
c) If AVG is specified and DT is exact numeric, then the data
type of the result is exact numeric with implementation-
defined precision not less than the precision of DT and
implementation-defined scale not less than the scale of DT.
From what I've heard, VB simply does not recognize Postgres' NUMERIC
datatype at all. I don't know if that's VB's fault or a problem in
our ODBC driver.
regards, tom lane
Tom Lane wrote: > > "Andrea Aime" <aaime@comune.modena.it> writes: > > Thanks, it works!!! But why doesn't it work without the > > cast? This is a good workaround, but the standard behaviour > > is a bug, isn't it? > > No, it isn't. We *used* to return int4 as the result of sum(int4), > etc, but that tends to suffer from overflow. And the deficiency of > using int4 as the result of avg() should be pretty obvious ;-) > [snip] > >From what I've heard, VB simply does not recognize Postgres' NUMERIC > datatype at all. I don't know if that's VB's fault or a problem in > our ODBC driver. It seems a problem of neither VB nor our ODBC driver. We would get the expected result unless we use Microsoft Cursor Service by setting the CursorLocation property as adUseClient. Unfortunately we couldn't get an updatable recordset using ADO unless we use Microsoft Cursor Service. regards, Hiroshi Inoue
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: 06 November 2001 19:03 > To: andrea.aime@comune.modena.it > Cc: gustavoboiko@ieg.com.br; Postgres ODBC > Subject: Re: [ODBC] Weird problem with aggregate functions > > > "Andrea Aime" <aaime@comune.modena.it> writes: > > Thanks, it works!!! But why doesn't it work without the > > cast? This is a good workaround, but the standard behaviour > > is a bug, isn't it? > > No, it isn't. We *used* to return int4 as the result of > sum(int4), etc, but that tends to suffer from overflow. And > the deficiency of using int4 as the result of avg() should be > pretty obvious ;-) > > The SQL spec says that we can use any exact numeric datatype > we please for these functions: > > b) If SUM is specified and DT is exact numeric with scale > S, then the data type of the result is exact > numeric with > implementation-defined precision and scale S. > > c) If AVG is specified and DT is exact numeric, > then the data > type of the result is exact numeric with implementation- > defined precision not less than the precision of DT and > implementation-defined scale not less than the > scale of DT. > > From what I've heard, VB simply does not recognize Postgres' > NUMERIC datatype at all. I don't know if that's VB's fault > or a problem in our ODBC driver. As I recall the problem is not with VB specifically, but with ADO (ActiveX Data Objects) so this problem would affect other tools such as VC++, MS Access (2000/XP use ADO I believe) and Visual Foxpro(?). It would probably also affect Active Server Pages on IIS come to think of it... I'll stick a note about it in the FAQ. Regards, Dave.