Re: [BUGS] [SQL] Function Parameters in GROUP BY clause cause errors

#1

"Davidson, Robert" <robdavid (AT) amazon (DOT) com> writes:

Quote:

ERROR: column "em.email_creation_datetime" must appear in the GROUP BY =
clause or be used in an aggregate function
CONTEXT: SQL statement " select to_char(to_timestamp(EXTRACT(HOUR FROM =
em.email_creation_datetime) || ':' || (EXTRACT(MINUTE FROM =
em.email_creation_datetime)::integer/ $1 ) * $2 , 'HH24:MI'), =
'HH24:MI') as TheInterval from test em group by =
to_char(to_timestamp(EXTRACT(HOUR FROM em.email_creation_datetime) || =
':' || (EXTRACT(MINUTE FROM em.email_creation_datetime)::integer/ $3 ) * =
$4 , 'HH24:MI') , 'HH24:MI')"
PL/pgSQL function "emailbyinterval" line 3 at for over select rows

Hmm, this seems like a plpgsql deficiency. It feels it can generate a
separate parameter symbol ($n) for each occurrence of each variable it
passes into a SQL query. But for this query to be legal, the two
instances of IntervalMinutes have to be represented by the *same*
parameter symbol (notice they are not in the regurgitated query).

It would be more efficient anyway to not generate multiple parameters
for the same value, so we oughta fix this.

In the short run, the only workaround I can think of for you is to run
the query using EXECUTE.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#2

I wrote:

Quote:

Hmm, this seems like a plpgsql deficiency. It feels it can generate a
separate parameter symbol ($n) for each occurrence of each variable it
passes into a SQL query. But for this query to be legal, the two
instances of IntervalMinutes have to be represented by the *same*
parameter symbol (notice they are not in the regurgitated query).

It would be more efficient anyway to not generate multiple parameters
for the same value, so we oughta fix this.

Patch applied to HEAD and 8.1 branches.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#3

How amazing is that? I call it a night and come back to find that a bug has=
been identified and patched while I sleep.

When will it appear in the binaries (I see that the release version is stil=
l 8.1.3)? I thought about trying to compile from source, but after looking =
at the mingw page was too intimidated by figuring out what to download to t=
ry compiling source.

Best Regards,

Robert Davidson

-----Original Message-----
From: Tom Lane [mailto:tgl (AT) sss (DOT) pgh.pa.us]=20
Sent: Wednesday, March 22, 2006 8:27 PM
To: Davidson, Robert
Cc: pgsql-sql (AT) postgresql (DOT) org; pgsql-bugs (AT) postgresql (DOT) org
Subject: Re: [SQL] Function Parameters in GROUP BY clause cause errors=20

I wrote:

Quote:

Hmm, this seems like a plpgsql deficiency. It feels it can generate a
separate parameter symbol ($n) for each occurrence of each variable it
passes into a SQL query. But for this query to be legal, the two
instances of IntervalMinutes have to be represented by the *same*
parameter symbol (notice they are not in the regurgitated query).

It would be more efficient anyway to not generate multiple parameters
for the same value, so we oughta fix this.

Patch applied to HEAD and 8.1 branches.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] [SQL] Function Parameters in GROUP BY clause cause errors

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] [SQL] Function Parameters in GROUP BY clause cause errors - 03-22-2006 , 03:55 PM

Re: [BUGS] [SQL] Function Parameters in GROUP BY clause cause errors - 03-22-2006 , 10:28 PM

Re: [BUGS] [SQL] Function Parameters in GROUP BY clause cause errors - 03-23-2006 , 12:15 PM