https://bz.apache.org/SpamAssassin/show_bug.cgi?id=8098
--- Comment #6 from Sidney Markowitz <[email protected]> --- Thanks Benny and Martin. I find the explanation in the SQLite documentation more clear than that in the Postgres documentation. The SQLite one even mentions that Postgres requires the column to be qualified, and it explains the difference in behavior if this expression had awl.totscore vs EXCLUDED.totscore. Depending on the value attempting to be inserted they might not be the same. And if they aren't the same only one of them could be correct. So, I can conclude that the correct expression either is the one in the patch I posted or EXCLUDED.totscore, and we have to figure out which it is supposed to be. I have just looked over the code, and this is what I think it is doing. Someone please correct me if I have this wrong, as I am not an expert in SQL. The table awl has columns (username email ip msgcount totscore) and a row is being inserted with the value for totscore column being $score if the row does not already exist. However, if the row already exists, it is supposed to be updated, with totscore being set to the old value of totscore + $score If I correctly read the explanations in postgres and SQLite documentation for EXCLUDED in the ON CONFLICT of an upsert, awl.totscore should refer to the value in the totscore column before the row is updated, but EXCLUDED.totscore refers to the value of the totscore column in the attempted insert, which is $score. Thus only awl.totscore would get the correct result. totscore = EXCLUDED.totscore + ? would end up setting the column to $score + $score. That's easy to test now that I have a test setup with postgres, and ... done: With the table name (awl) uzed, i.e., using the patch I posted, t/sql_based_welcomelist.t passes If I make it totscore = EXCLUDED.totscore + ? then t/sql_based_welcomelist.t fails with totscore in the database not getting incremented, consistent with my understanding of what it means as I explained above. -- You are receiving this mail because: You are the assignee for the bug.
