https://bz.apache.org/SpamAssassin/show_bug.cgi?id=8098
Bug ID: 8098
Summary: postgres awl update failure, incorrect SQL syntax
Product: Spamassassin
Version: 4.0.0
Hardware: All
OS: All
Status: NEW
Severity: major
Priority: P2
Component: Libraries
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: Undefined
See this thread in the users@ mailing list for details
https://lists.apache.org/thread/hf7o4y4pvgmzxlwf4x3pnnpbd0csgnrv
I don't have the steps to reproduce the error using SpamAssassin and AWL in a
postgres database, but it is easy to show that the SQL query in
SQLBasedAddrList.pm line 310 is not accepted by postgres as valid syntax.
I tested the table and query in the online sql sandbox at
https://www.db-fiddle.com/ by pasting in
CREATE TABLE awl (username varchar(100) NOT NULL default '',
email varchar(255) NOT NULL default '',
ip varchar(40) NOT NULL default '',
msgcount bigint NOT NULL default '0',
totscore float NOT NULL default '0',
signedby varchar(255) NOT NULL default '',
last_hit timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (username,email,signedby,ip)
);
insert into awl (username, email, ip, signedby) values ('john',
'[email protected]', '127.0.0.1', '-');
insert into awl (username, email, ip, signedby) values ('john',
'[email protected]', '127.0.0.1', '-') ON CONFLICT (username, email,
signedby, ip) DO UPDATE set msgcount = 5, totscore = totscore + 10;
which when run, results in the same error that was reported from SpamAssassin
on the mailing list thread
Schema Error: error: column reference "totscore" is ambiguous
The code in SQLBasedAddrList.pm line 310 is for just postgres and SQLite, the
two databases that have the ON CONFLICT syntax for upsert. When I selected
SQLite in the sandbox, it did not have a problem with the above query.
The fix for postgres is to replace the expression "totscore = totscore + 10"
with "totscore =+ 10", and that works for SQLite too, so seems like the
simplest fix that would work.
--
You are receiving this mail because:
You are the assignee for the bug.
