Archives
Contributors
Re: Empty SQL argument and ANY operator
After a few months, I'm coming back on this issue as my initial dirty fix was wrong, the culprit comes from the hard-coded SQL query because the following statement is invalid:
and p.id
!= ANY(%s)
and should be replaced with following statement to have the expected result
and not p.id = ANY(%s)
That's awkward as it works perfectly for
and (p.email != ANY(%s) or p.email is null)
---------------------------------------------------------------
SELECT
P.ID, P.EMAIL
FROM
RES_PARTNER P
"id","email"
1,"contact1@dec.sarl"
2,"contact2@dec.sarl"
3,"contact3@dec.sarl"
4,null
---------------------------------------------------------------
SELECT
P.ID,
P.EMAIL
FROM
RES_PARTNER P
WHERE
p.id != ANY((ARRAY[1,2]))
"id","email"
1,"contact1@dec.sarl"
2,"contact2@dec.sarl"
3,"contact3@dec.sarl"
4,NULL
---------------------------------------------------------------
SELECT
P.ID,
P.EMAIL
FROM
RES_PARTNER P
WHERE
not p.id = ANY((ARRAY[1,2]))
"id","email"
3,"contact3@dec.sarl"
4,NULL
---------------------------------------------------------------
SELECT
P.ID,
P.EMAIL
FROM
RES_PARTNER P
WHERE
NOT P.EMAIL = ANY ((ARRAY['contact1@dec.sarl']))
OR P.EMAIL IS NULL
"id","email"
2,"contact2@dec.sarl"
3,"contact3@dec.sarl"
4,NULL
---------------------------------------------------------------
SELECT
P.ID,
P.EMAIL
FROM
RES_PARTNER P
WHERE
P.EMAIL != ANY ((ARRAY['contact1@dec.sarl']))
OR P.EMAIL IS NULL
"id","email"
2,"contact2@dec.sarl"
3,"contact3@dec.sarl"
4,NULL
P.ID, P.EMAIL
FROM
RES_PARTNER P
"id","email"
1,"contact1@dec.sarl"
2,"contact2@dec.sarl"
3,"contact3@dec.sarl"
4,null
---------------------------------------------------------------
SELECT
P.ID,
P.EMAIL
FROM
RES_PARTNER P
WHERE
p.id != ANY((ARRAY[1,2]))
"id","email"
1,"contact1@dec.sarl"
2,"contact2@dec.sarl"
3,"contact3@dec.sarl"
4,NULL
---------------------------------------------------------------
SELECT
P.ID,
P.EMAIL
FROM
RES_PARTNER P
WHERE
not p.id = ANY((ARRAY[1,2]))
"id","email"
3,"contact3@dec.sarl"
4,NULL
---------------------------------------------------------------
SELECT
P.ID,
P.EMAIL
FROM
RES_PARTNER P
WHERE
NOT P.EMAIL = ANY ((ARRAY['contact1@dec.sarl']))
OR P.EMAIL IS NULL
"id","email"
2,"contact2@dec.sarl"
3,"contact3@dec.sarl"
4,NULL
---------------------------------------------------------------
SELECT
P.ID,
P.EMAIL
FROM
RES_PARTNER P
WHERE
P.EMAIL != ANY ((ARRAY['contact1@dec.sarl']))
OR P.EMAIL IS NULL
"id","email"
2,"contact2@dec.sarl"
3,"contact3@dec.sarl"
4,NULL
by Yann Papouin - 09:09 - 29 May 2024
Reference
-
Empty SQL argument and ANY operator
Hello everyone,I'm trying to debug Odoo 14.0 to understand why some emails received on one of our public channel are not forwarded to all members of the list.The SQL query is made to retrieve all partners members of a channel that will be notified with a copy of the received emailI tracked down the issue to the fact that an empty list (except_partner) is converted to '{}' (empty array literal) when used as an argument in the SQL query and the result of the query with this condition is always empty.I'm pretty sure that it is something tricky around the SQL language but as I'm not an expert on this, I don't know how to fix it.My current dirty fix is to add except_partner.append(0) to have a valid query but I would prefer to have the real SQL fix.
Any idea ?--
Yann PAPOUIN, Ingénieur R&D | DEC
by Yann Papouin - 12:52 - 21 Feb 2024
