Re: BUG #10972: string_agg function incorrectly concatenating varying delimiter

Hi Tom,

I just read the Wikipedia article on you, fun. Glad to make your acquaintance. My business partner, Dave Yarnall, went to Carnegie Mellon (CCed).

I suppose principles can be relative, but I'll assume you mean good principles and give it a shot.

Primarily, I'd consider whether another function uses non-deterministic rows for its evaluation. I could be wrong, but I don't think any other function uses two different rows results within a single function evaluation. Even aggregates, such as string_agg, evaluate one row at a time which is a well-known behavior.

A similar paradigm might be in general programming if a compiled program randomly chose values off of the stack to place as a parameter into a method call (function 3's parameters are passed into function2). An odd and unexpected behavior for the SQL language and really any language, I think. Although, it is quite creative.

It sounds like you've quickly isolated the line within the source. In an interest in learning more about the code, would you mind pointing my partner and I to the line for this bug?

Thanks,
Jeff

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, July 16, 2014 7:20 AM
To: Jeff Fischer
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #10972: string_agg function incorrectly concatenating varying delimiter

jeff(at)goaldriven(dot)com writes:
> Running the query below will show how the delimiter for the current
> row is actually the subsequent rows delimiter.

Hmm, well, the documentation for string_agg doesn't say what happens when the "delimiter" argument varies across rows; but a quick look at the code finds that the first-call delimiter isn't actually used at all, and on subsequent calls the delimiter is appended to the running result before the associated value is. Which seems to me to be at least as reasonable, and certainly a great deal easier to implement, as what you seem to have in mind. Can you offer a principled argument why it should be the other way around?

regards, tom lane