Re: [BUGS] BUG #3958: Self-Join Group-By Clause Produces Incorrect Results
I respectfully challenge that the aggregation is correct. In the where clause, I specify A2.AUDIT_TYPE_CODE = CONTENT_2, thus returning only 2 rows for A2 and not all of the rows in A2 which happen to have a TXN_COUNT of 1 / row but could in fact be any positive number. I used 1 for simplicity. Similarly, if you take out A1 from the query you receive the following result: audit_date|content_policy_name|sum_2 2008-01-01|TEST POLICY|2 I do not see how/why a self-join changes the condition specified in the where clause and thus returns a sum of 8 rows that do not meet the specified condition in the query? Thanks in advance, Marcus Torres - Original Message From: Tom Lane <[EMAIL PROTECTED]> To: Heikki Linnakangas <[EMAIL PROTECTED]> Cc: Marcus Torres <[EMAIL PROTECTED]>; pgsql-bugs@postgresql.org Sent: Wednesday, February 13, 2008 7:40:32 AM Subject: Re: [BUGS] BUG #3958: Self-Join Group-By Clause Produces Incorrect Results "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Marcus Torres wrote: >> I wrote a simple self-join query to sum the transaction count of different >> types of records in a audit table and the result set for the different sum >> totals was the same which is incorrect. > Looks perfectly correct to me. Me too. The underlying data before grouping/aggregation is regression=# select A1.AUDIT_DATE, P.CONTENT_POLICY_NAME, A1.TXN_COUNT, A2.TXN_COUNT FROM T_AUDIT A1, T_AUDIT A2, T_POLICY P WHERE P.ID = A1.POLICY_ID AND P.ID = A2.POLICY_ID AND A1.POLICY_ID = A2.POLICY_ID AND A1.AUDIT_DATE = A2.AUDIT_DATE AND A1.AUDIT_TYPE_CODE = 'CONTENT_1' AND A2.AUDIT_TYPE_CODE = 'CONTENT_2'; audit_date | content_policy_name | txn_count | txn_count +-+---+--- 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 (10 rows) from which it's clear that given all ones in txn_count, the sums *must* be the same because they're taken over the same number of rows. I suspect what the OP needs is two separate queries (perhaps union'ed together) not a self-join. regards, tom lane Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [BUGS] BUG #3958: Self-Join Group-By Clause Produces Incorrect Results
Please disregard the previous email. After rereading what you sent, I realized that I need an outer join to A2 and not simply a self join...thanks and my apologies! - Original Message From: Tom Lane <[EMAIL PROTECTED]> To: Heikki Linnakangas <[EMAIL PROTECTED]> Cc: Marcus Torres <[EMAIL PROTECTED]>; pgsql-bugs@postgresql.org Sent: Wednesday, February 13, 2008 7:40:32 AM Subject: Re: [BUGS] BUG #3958: Self-Join Group-By Clause Produces Incorrect Results "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Marcus Torres wrote: >> I wrote a simple self-join query to sum the transaction count of different >> types of records in a audit table and the result set for the different sum >> totals was the same which is incorrect. > Looks perfectly correct to me. Me too. The underlying data before grouping/aggregation is regression=# select A1.AUDIT_DATE, P.CONTENT_POLICY_NAME, A1.TXN_COUNT, A2.TXN_COUNT FROM T_AUDIT A1, T_AUDIT A2, T_POLICY P WHERE P.ID = A1.POLICY_ID AND P.ID = A2.POLICY_ID AND A1.POLICY_ID = A2.POLICY_ID AND A1.AUDIT_DATE = A2.AUDIT_DATE AND A1.AUDIT_TYPE_CODE = 'CONTENT_1' AND A2.AUDIT_TYPE_CODE = 'CONTENT_2'; audit_date | content_policy_name | txn_count | txn_count +-+---+--- 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 2008-01-01 | TEST POLICY | 1 | 1 (10 rows) from which it's clear that given all ones in txn_count, the sums *must* be the same because they're taken over the same number of rows. I suspect what the OP needs is two separate queries (perhaps union'ed together) not a self-join. regards, tom lane Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
[BUGS] BUG #3960: initdb.log
The following bug has been logged online: Bug reference: 3960 Logged by: Anto Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.5 Operating system: Windows Vista Description:initdb.log Details: initdb.log is failed to run while installation. Please provide the resolution asap. Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #3961: ecpg lacks SQLSTATE macro definition
The following bug has been logged online: Bug reference: 3961 Logged by: Christoph Dalitz Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.6 Operating system: Linux Description:ecpg lacks SQLSTATE macro definition Details: While ecpg defines the (deprecated) SQLCODE, it does not define the macro SQLSTATE, even though the documentation say, that this latter macro should be used and which values it can take. Please add a macro definition #define SQLSTATEsqlca.sqlstate in ecpglib.h like for SQLSTATE to be compatible with the SQL standard. Thanks, Christoph ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3960: initdb.log
Anto wrote: The following bug has been logged online: Bug reference: 3960 Logged by: Anto Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.5 Operating system: Windows Vista Description:initdb.log Details: initdb.log is failed to run while installation. Please provide the resolution asap. The 8.2 installer does not work properly on Windows Vista. Use 8.3.0 for a version that's supported on Vista, or install without initdb and run that step manually later, if you need 8.2. And if you do need 8.2, you really should be using 8.2.6, not 8.2.5. //Magnus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] BUG #3962: Error on database cluster
The following bug has been logged online: Bug reference: 3962 Logged by: Arjan Bolmer Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.0-1 Operating system: Vista Description:Error on database cluster Details: When installing postgreSQL i ran into an error when it was initialising the database cluster. Previously i had installed postgreSQL on my other pc on which it worked fine however when i install it now i run into the following error: "Faied to run initdb: 1! Please see the logfile in 'C:\Program Files\PostgreSQL\8.3\tmp\initdb.log'. Note! You must read/copy this logfile before you click OK, or it wil be automatically removed." When i check the logfile it gives the following message (was in dutch so ill try to translate it as good as possible): The application couldn't be started cause the configuration next to eachother aren't good. Read the Eventlog for more information. The dutch text was: De toepassing kan niet worden gestart omdat de configuratie naast elkaar onjuist is. Raadpleeg het gebeurtenislogboek Toepassing voor meer informatie. Thanks for any help that can be send, Arjan Bolmer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3961: ecpg lacks SQLSTATE macro definition
Hallo Christoph, On Thu, Feb 14, 2008 at 11:09:11AM +, Christoph Dalitz wrote: > Description:ecpg lacks SQLSTATE macro definition Thanks for reporting this oversight. I added the definition to CVS, also for 8.2. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #3961: ecpg lacks SQLSTATE macro definition
Michael Meskes <[EMAIL PROTECTED]> writes: > Hallo Christoph, > On Thu, Feb 14, 2008 at 11:09:11AM +, Christoph Dalitz wrote: >> Description:ecpg lacks SQLSTATE macro definition > Thanks for reporting this oversight. I added the definition to CVS, also > for 8.2. Michael, we branched 8.3 yesterday, so you need a third commit if you don't want people to see a regression from 8.2.7 to 8.3.x. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #3963: trigger delete
The following bug has been logged online: Bug reference: 3963 Logged by: Ronald Mora Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Operating system: windows xp Description:trigger delete Details: Para la modificacion de los datos de los cliente, cree trigger para cuando se insert,delete o update una tabla, la forma en la que los usuarios hacen las modificacion no hace commit, pero si dispara los trigger a nivel de base de datos y guarda en unas tablas la solicitud del usuario, dicha solicitud llega a un usuario que se encarga de validar y aplicar el delete,insert o update a la base de datos. El problema era que cuando dicho usuario aplicaba este cambio el trigger volvia a insertar un dato mas en la bicatora, el problema lo pude resolver para el insert o update con un campo bandera que me indica cuando disparo o no la insercion de los datos en las tablas bitacora, pero en el caso del delete dicho campo no me funciona. Necesito saber si hay alguna forma de evitar que el trigger se dispare cuando el usuario final va a aplicar el cambio en la base de datos, se que podria desabilitar el trigger pero no se que pasa si cuando se desabilita el trigger otro usuario esta haciendo otra solicitud de update,delete o insert y por estar desabilitado el trigger no guarde los datos en la bitacora. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
