https://bz.apache.org/SpamAssassin/show_bug.cgi?id=8191
Bug ID: 8191
Summary: SQLBasedAddrList.pm: INSERT […] ON CONFLICT error with
PostgreSQL
Product: Spamassassin
Version: 4.0.0
Hardware: PC
OS: FreeBSD
Status: NEW
Severity: normal
Priority: P2
Component: Libraries
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: Undefined
The SQL Code in Mail::SpamAssassin::SQLBasedAddrList fails with PostgreSQL when
inserting/updating txrep.totscore:
my $sql = sprintf("INSERT INTO %s (%s) VALUES (%s)", $self->{tablename},
join(',', @fields), join(',', ('?') x @fields));
if ($self->{dsn} =~ /^DBI:(?:pg|SQLite)/i) {
$sql .= " ON CONFLICT (username, email, signedby, ip) DO UPDATE set
msgcount = ?, totscore = totscore + ?";
(Line 307-310 in SQLBasedAddrList.pm)
This causes an SQL error when executing:
ERROR: column reference "totscore" is ambiguous at character 177
STATEMENT: INSERT INTO txrep
(username,email,ip,msgcount,totscore,signedby) VALUES ($1,$2,$3,$4,$5,$6) ON
CONFLICT (username, email, signedby, ip) DO UPDATE set msgcount = $7, totscore
= totscore + $8
When Line 310 in SQLBasedAddrList.pm is changed to the following, it works as
expected:
$sql .= " ON CONFLICT (username, email, signedby, ip) DO UPDATE set
msgcount = ?, totscore = $self->{tablename}.totscore + ?";
The change is added $self->{tablename} at "totscore =
$self->{tablename}.totscore + ?"
--
You are receiving this mail because:
You are the assignee for the bug.
