Skip to Content
  2. Mailing Lists
  3. Contributors
  4. Re: Empty SQL argument and ANY operator

Archives

Contributors

Re: Empty SQL argument and ANY operator

Re: Empty SQL argument and ANY operator

Re: Empty SQL argument and ANY operator

Looks like a bug indeed, in 14.0 and lower. In 15.0 the code seems refactored.

>>> env.cr.execute("select count(1) from res_users where id != any(%s)", (([1,],)))
>>> env.cr.query
b'select count(1) from res_users where id != any(ARRAY[1])'
>>> env.cr.fetchall()
[(248,)]

>>> env.cr.execute("select count(1) from res_users where id != any(%s)", (([],)))
>>> env.cr.query
b"select count(1) from res_users where id != any('{}')"
>>> env.cr.fetchall()
[(0,)]

Probably the Odoo programmers misinterpreted what kind of query psycopg would generate in such a case.


On 2/21/24 15:22, Yann Papouin wrote:

Le mer. 21 févr. 2024 à 15:12, Tom Blauwendraat <notifications@odoo-community.org> a écrit :

For some reason I can't see the image, can you maybe send it as attachment?

Also, can you provide a link to the relevant part of the Odoo source code that defines "except_partner"?

On 2/21/24 12:52, Yann Papouin wrote:
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 email
I 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.

image.png

Any idea ?

--
Yann PAPOUIN, Ingénieur R&D | DEC

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe

by Tom Blauwendraat - 07:51 - 21 Feb 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 email
    I 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.

    image.png

    Any idea ?

    --
    Yann PAPOUIN, Ingénieur R&D | DEC
    by Yann Papouin - 12:52 - 21 Feb 2024

    1 attachments 1 attachments

    image.png