pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: column doesn't get calculated - update # 2
2+ messages / 2 participants
[nested] [flat]
* Re: column doesn't get calculated - update # 2
@ 2003-07-16 16:54 floyds@4peakstech.com
2003-07-16 17:44 ` Re: column doesn't get calculated - update # 2 Barry Lind <blind@xythos.com>
0 siblings, 1 reply; 2+ messages in thread
From: floyds@4peakstech.com @ 2003-07-16 16:54 UTC (permalink / raw)
To: Pgsql-Jdbc <pgsql-jdbc@postgresql.org>; pgsql-sql; +Cc: Josh Wardle <josh@truckmaster.com>; Gregory S. Dodson <greg@truckmaster.com>
i was wrong. it doesn't work as a prepared statement nor as a dynamic string
using jdbc.
it works fine if i paste it into psql.
is it possible that a problem with a calculated column and a subselect in
conjunction is a jdbc bug?
Regards,
Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX: 702.995.6462
EMAIL: FloydS@4PeaksTech.com
ICQ #: 161371538
PGP Fone at private.fwshackelford.com on request
Shackelford Motto: ACTA NON VERBA - Actions, not words
Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights
The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf
-----Original Message-----
From: floyds@4peakstech.com [mailto:floyds@4peakstech.com]
Sent: Wednesday, July 16, 2003 10:05 AM
To: Pgsql-Sql
Cc: Josh Wardle; Gregory S. Dodson
Subject: RE: column doesn't get calculated - updated
when i copy/paste the select stmt into psql, it works. or if i use it
"dynamically". it doesn't work properly when i use it in a prepared
statement -- which is what i am doing.
Regards,
Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX: 702.995.6462
EMAIL: FloydS@4PeaksTech.com
ICQ #: 161371538
PGP Fone at private.fwshackelford.com on request
Shackelford Motto: ACTA NON VERBA - Actions, not words
Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights
The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf
-----Original Message-----
From: floyds@4peakstech.com [mailto:floyds@4peakstech.com]
Sent: Tuesday, July 15, 2003 11:14 AM
To: Pgsql-Sql
Subject: column doesn't get calculated
this must be a problem with my sql, but this one has me stumped. the column:
Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" in the long sql statement
below comes out as the literal: Debit."cumm_dbt_blnce" -
Credit."cumm_crd_blnce" rather than as the calculated value. WHY!?!?!
this works with simple sql in psql:
select a."field1" - b."field2" from (select field1 as "field1" from
someTable) as a, (select field2 as "field2" from someOtherTable) as b;
but with my more complex sql, it doesn't. it comes out as a literal string.
it's almost like postgresql forgot to process this column.
select
cred_vend_acct_table.num as "num" ,
cred_vend_acct_table.name as "name" ,
abs_vend_acct_type_table.name as "name" ,
Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" as "undefined" ,
Debit."cumm_dbt_blnce" as "cumm_dbt_blnce" ,
Credit."cumm_crd_blnce" as "cumm_crd_blnce" ,
cred_vend_acct_table.objid as "__OBJID__" ,
cred_vend_acct_table.clsref as "__CLSREF__"
from
only cred_vend_acct_table ,
abs_vend_acct_type_table ,
( select
daily_acct_blnce_table.cumm_dbt_blnce as "cumm_dbt_blnce"
from
only daily_acct_blnce_table
where
( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and
( ( daily_acct_blnce_table.status = 'e' ) ) and
( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid ) and
( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref ) and
daily_acct_blnce_table.dte =
( select
max(daily_acct_blnce_table.dte) as "dte"
from
only daily_acct_blnce_table
where
( ( ( daily_acct_blnce_table.company_objid *=
147483647 ) ) and
( ( daily_acct_blnce_table.status = 'e' ) ) and
( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid ) and
( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref ) ) ) ) )
as Debit ,
( select
daily_acct_blnce_table.cumm_crd_blnce as "cumm_crd_blnce"
from
only daily_acct_blnce_table
where
( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) )
and
( ( daily_acct_blnce_table.status = 'e' ) ) and
( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid ) and
( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref ) and
daily_acct_blnce_table.dte =
( select
max(daily_acct_blnce_table.dte) as "dte"
from
only daily_acct_blnce_table
where
( ( ( daily_acct_blnce_table.company_objid *=
2147483647 ) ) and
( ( daily_acct_blnce_table.status = 'e' ) ) and
( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid ) and
( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref ) ) ) ) )
as Credit
where
( ( ( cred_vend_acct_table.company_objid *= 2147483647 ) and
( abs_vend_acct_type_table.company_objid *= 2147483647 ) ) and
( ( cred_vend_acct_table.status = 'e' ) and
( abs_vend_acct_type_table.status = 'e' ) ) and
cred_vend_acct_table.owner_objref[1] = 100110 and
cred_vend_acct_table.owner_objref[2] = 2147483647 and
( ( ( cred_vend_acct_table.abs_acct_type_objref[1] =
abs_vend_acct_type_table.clsref ) and
( cred_vend_acct_table.abs_acct_type_objref[2] =
abs_vend_acct_type_table.objid ) ) ) )
order by 2 asc
limit 100
Regards,
Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX: 702.995.6462
EMAIL: FloydS@4PeaksTech.com
ICQ #: 161371538
PGP Fone at private.fwshackelford.com on request
Shackelford Motto: ACTA NON VERBA - Actions, not words
Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights
The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: column doesn't get calculated - update # 2
2003-07-16 16:54 Re: column doesn't get calculated - update # 2 floyds@4peakstech.com
@ 2003-07-16 17:44 ` Barry Lind <blind@xythos.com>
0 siblings, 0 replies; 2+ messages in thread
From: Barry Lind @ 2003-07-16 17:44 UTC (permalink / raw)
To: floyds@4peakstech.com; +Cc: Pgsql-Jdbc <pgsql-jdbc@postgresql.org>; pgsql-sql; Josh Wardle <josh@truckmaster.com>; Gregory S. Dodson <greg@truckmaster.com>
Floyd,
I would recommend turning sql statement tracing on in the database to
see the exact sql text that the driver is sending to the database (in
case it is somehow munging it). Then take that exact same text (as
found in the server log files) and run it in psql to see how it works there.
--Barry
floyds@4peakstech.com wrote:
> i was wrong. it doesn't work as a prepared statement nor as a dynamic string
> using jdbc.
>
> it works fine if i paste it into psql.
>
> is it possible that a problem with a calculated column and a subselect in
> conjunction is a jdbc bug?
>
> Regards,
>
> Floyd Shackelford
> 4 Peaks Technology Group, Inc.
> VOICE: 334.735.9428
> FAX: 702.995.6462
> EMAIL: FloydS@4PeaksTech.com
> ICQ #: 161371538
> PGP Fone at private.fwshackelford.com on request
>
> Shackelford Motto: ACTA NON VERBA - Actions, not words
>
> Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
> Rights
>
> The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf
>
>
>
> -----Original Message-----
> From: floyds@4peakstech.com [mailto:floyds@4peakstech.com]
> Sent: Wednesday, July 16, 2003 10:05 AM
> To: Pgsql-Sql
> Cc: Josh Wardle; Gregory S. Dodson
> Subject: RE: column doesn't get calculated - updated
>
>
>
> when i copy/paste the select stmt into psql, it works. or if i use it
> "dynamically". it doesn't work properly when i use it in a prepared
> statement -- which is what i am doing.
>
>
> Regards,
>
> Floyd Shackelford
> 4 Peaks Technology Group, Inc.
> VOICE: 334.735.9428
> FAX: 702.995.6462
> EMAIL: FloydS@4PeaksTech.com
> ICQ #: 161371538
> PGP Fone at private.fwshackelford.com on request
>
> Shackelford Motto: ACTA NON VERBA - Actions, not words
>
> Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
> Rights
>
> The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf
>
>
>
> -----Original Message-----
> From: floyds@4peakstech.com [mailto:floyds@4peakstech.com]
> Sent: Tuesday, July 15, 2003 11:14 AM
> To: Pgsql-Sql
> Subject: column doesn't get calculated
>
>
>
>
> this must be a problem with my sql, but this one has me stumped. the column:
> Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" in the long sql statement
> below comes out as the literal: Debit."cumm_dbt_blnce" -
> Credit."cumm_crd_blnce" rather than as the calculated value. WHY!?!?!
>
> this works with simple sql in psql:
>
> select a."field1" - b."field2" from (select field1 as "field1" from
> someTable) as a, (select field2 as "field2" from someOtherTable) as b;
>
> but with my more complex sql, it doesn't. it comes out as a literal string.
> it's almost like postgresql forgot to process this column.
>
>
> select
> cred_vend_acct_table.num as "num" ,
> cred_vend_acct_table.name as "name" ,
> abs_vend_acct_type_table.name as "name" ,
> Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" as "undefined" ,
> Debit."cumm_dbt_blnce" as "cumm_dbt_blnce" ,
> Credit."cumm_crd_blnce" as "cumm_crd_blnce" ,
> cred_vend_acct_table.objid as "__OBJID__" ,
> cred_vend_acct_table.clsref as "__CLSREF__"
> from
> only cred_vend_acct_table ,
> abs_vend_acct_type_table ,
> ( select
> daily_acct_blnce_table.cumm_dbt_blnce as "cumm_dbt_blnce"
> from
> only daily_acct_blnce_table
> where
> ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and
> ( ( daily_acct_blnce_table.status = 'e' ) ) and
> ( daily_acct_blnce_table.abs_acct_objref[2] =
> cred_vend_acct_table.objid ) and
> ( daily_acct_blnce_table.abs_acct_objref[1] =
> cred_vend_acct_table.clsref ) and
> daily_acct_blnce_table.dte =
> ( select
> max(daily_acct_blnce_table.dte) as "dte"
> from
> only daily_acct_blnce_table
> where
> ( ( ( daily_acct_blnce_table.company_objid *=
> 147483647 ) ) and
> ( ( daily_acct_blnce_table.status = 'e' ) ) and
> ( daily_acct_blnce_table.abs_acct_objref[2] =
> cred_vend_acct_table.objid ) and
> ( daily_acct_blnce_table.abs_acct_objref[1] =
> cred_vend_acct_table.clsref ) ) ) ) )
> as Debit ,
> ( select
> daily_acct_blnce_table.cumm_crd_blnce as "cumm_crd_blnce"
> from
> only daily_acct_blnce_table
> where
> ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) )
> and
> ( ( daily_acct_blnce_table.status = 'e' ) ) and
> ( daily_acct_blnce_table.abs_acct_objref[2] =
> cred_vend_acct_table.objid ) and
> ( daily_acct_blnce_table.abs_acct_objref[1] =
> cred_vend_acct_table.clsref ) and
> daily_acct_blnce_table.dte =
> ( select
> max(daily_acct_blnce_table.dte) as "dte"
> from
> only daily_acct_blnce_table
> where
> ( ( ( daily_acct_blnce_table.company_objid *=
> 2147483647 ) ) and
> ( ( daily_acct_blnce_table.status = 'e' ) ) and
> ( daily_acct_blnce_table.abs_acct_objref[2] =
> cred_vend_acct_table.objid ) and
> ( daily_acct_blnce_table.abs_acct_objref[1] =
> cred_vend_acct_table.clsref ) ) ) ) )
> as Credit
> where
> ( ( ( cred_vend_acct_table.company_objid *= 2147483647 ) and
> ( abs_vend_acct_type_table.company_objid *= 2147483647 ) ) and
> ( ( cred_vend_acct_table.status = 'e' ) and
> ( abs_vend_acct_type_table.status = 'e' ) ) and
> cred_vend_acct_table.owner_objref[1] = 100110 and
> cred_vend_acct_table.owner_objref[2] = 2147483647 and
> ( ( ( cred_vend_acct_table.abs_acct_type_objref[1] =
> abs_vend_acct_type_table.clsref ) and
> ( cred_vend_acct_table.abs_acct_type_objref[2] =
> abs_vend_acct_type_table.objid ) ) ) )
> order by 2 asc
> limit 100
>
>
> Regards,
>
> Floyd Shackelford
> 4 Peaks Technology Group, Inc.
> VOICE: 334.735.9428
> FAX: 702.995.6462
> EMAIL: FloydS@4PeaksTech.com
> ICQ #: 161371538
> PGP Fone at private.fwshackelford.com on request
>
> Shackelford Motto: ACTA NON VERBA - Actions, not words
>
> Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
> Rights
>
> The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-07-16 17:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-16 16:54 Re: column doesn't get calculated - update # 2 floyds@4peakstech.com
2003-07-16 17:44 ` Barry Lind <blind@xythos.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox