OD224 sql-unknown-column¶
A literal SQL query names a column its model's table does not have.
| Category | Severity | Series | Needs the index |
|---|---|---|---|
| Correctness | Error | all | yes |
What it reports¶
A table.column or alias.column reference in a query, when no stored field of the table's model
gives that column: the model has no field of that name, the field is not stored, or it is a
One2many or a Many2many. A missing name x is reported with x_id as the fix when the model has a
stored field of that name.
The rule reads the text of each Odoo SQL("...") where it is built: one whose name starts from a
name a module-level import of odoo binds, as SQL from odoo.tools, tools.SQL and
odoo.tools.SQL do. An import inside a function does not count.
It also reads the query env.execute_query() or an Odoo cursor's execute() runs when it is a
literal, "..." % values, "...".format(), another SQL("..."), such as psycopg2's sql.SQL, or
a name the function binds once, with a plain =; a literal run at several calls is read once.
psycopg2's sql.SQL("...").format(...) is not read. An
Odoo cursor is the _cr of records, the cr of an environment or of request, as in
self.env.cr, records.env.cr or self.env(su=True).cr, one registry.cursor() or
self.pool.cursor() opens, alone or in contextlib.closing(), self.cr in a test case or in a
class that binds it to one of those, or a name bound once to one of those. A cursor the function is
given as a parameter, or opens with psycopg2 or db_connect(), may read another database. A text
is read only when its quotes, comments and brackets close, so an f-string with a value in it is not
read.
The FROM list and the joins of a query, UPDATE, INSERT INTO and DELETE ... USING bind each
table to its alias, or to its own name when it has none, and EXCLUDED to the table an
INSERT ... ON CONFLICT writes; a subquery also sees the names around it, and each branch of a
UNION, INTERSECT or EXCEPT binds its own. A reference is skipped when its qualifier is
unbound, bound twice, or bound to a subquery, a function, a CTE, a placeholder, a table given
column aliases, as t AS v(a, b) is, or a table of a schema other than public, and when it has
three parts, is a call, or touches a placeholder, as l.price_{} and l."%s" do. A fragment that
does not open with SELECT, WITH, VALUES, INSERT, UPDATE or DELETE binds nothing.
On a table Odoo builds from the fields, these columns are never reported: id, the log columns such
as create_date when the model logs access, parent_path on a model with _parent_store, and the
field an _inherits entry names when no class declares it. A declared one is judged as any other
field, so one that is related and not stored is reported. The entries of the models in _inherit
count, and a later entry for a parent replaces an earlier one; of the models one class lists in
_inherit, the entry of the first listed wins. Odoo creates the undeclared field before saas~16.4,
then only for an x_ name until saas~18.2, and otherwise fails to set up the registry; this rule
stays silent either way. It is silent too on any name no field has when an _inherits, or an
_inherit the model reaches, is not a literal, since an entry the scan cannot see may give it, and
on a name an entry gives when the order the entries merge in leaves unknown whether that entry is
the last for its parent. That order is unknown between the entries of two listed models when
different classes of the model list them, and when the class giving an entry is reached through two
listed models, as a class of base always is under two since every model merges it. Under one
listed model, base merges first. This order applies to _inherits entries only: the definitions
of a field from two listed models stay unordered, as Python's method resolution order merges them,
which may not follow the list.
From saas~14.4, Odoo adds the log columns only from the class that introduces the model, so they are
reported when every such class sets _log_access to False, whatever its extensions set. Before,
the classes merge and the last setting wins, and Odoo never drops a column, so they are reported
when the module that introduces the model sets it to False and no class sets anything else. When
the series is unknown, they are reported only when both rules report them.
display_name is reported unless a class stores it, since the ORM computes it, and so is
__last_update up to 16.0. From saas~16.1, and when the series is unknown, __last_update is a
name no field has. Binary, Image and company-dependent fields are not reported: whether they have
a column depends on attachment and on the series. Nor is a column the module or a dependency
adds by hand with literal names, in ALTER TABLE <table> ADD COLUMN <name> or
create_column(cr, "<table>", "<name>", ...), as auth_totp adds totp_secret to res_users for
a field Odoo does not store, nor res.users.password, whose column base creates in SQL. Each
ALTER TABLE of a text that runs several statements is read, and comments between its actions are
skipped. A ; or comma inside a string, dollar-quoted or E'...' one included, or inside a quoted
name, ends nothing.
A table is read only when exactly one model has it, the class that introduces that model with
_name is in the module or its dependencies, the model gets that table there, and Odoo builds the
table from the fields: no class of the model is abstract, sets _auto to anything but True, or
sets _table_query or _table_sql. A module that Odoo always installs with base, such as web,
counts as in depends. The table of a model with _auto = False is read as the view its init()
creates when OD225 reads that view's columns, and a column is then reported unless the
view selects one of that name. A field that only a module outside the dependencies defines is not
reported, nor is one an ir.model.fields record adds, whatever its type, as OD304
describes, nor one whose definitions disagree on its type in an order the index cannot tell. Odoo
runs init() and _auto_init() ahead of the module's own data files, and a failure there refuses
the install, so for their queries only a record of a dependency counts: define the field in Python,
or move the record to a dependency. The rule skips migrations/ and upgrades/, a model with a
plain Python base class or an _inherit element that is not a literal, and a module when it or one
of its dependencies depends on a module that is not indexed.
Why it matters¶
Odoo creates a column for each stored field that has a column type, and none for a One2many, a
Many2many, whose rows live in a relation table, or a field it computes on the fly. The fields a
model reaches through _inherits are related fields on it, stored in the parent's table. A query
that names any other column fails in PostgreSQL when it runs, and the error aborts the transaction.
Renamed fields are the usual cause, and nothing flags the stale name until that query runs.
Name the column of a stored field, join the table that holds the value, or read it through the ORM.
Example¶
class AccountMove(models.Model):
_inherit = "account.move"
def _x_units_by_product(self):
return self.env.execute_query(SQL(
"""
SELECT l.product_id, l.product_uom, SUM(l.quantity)
FROM account_move_line l
WHERE l.move_id IN %s
GROUP BY l.product_id, l.product_uom
""",
tuple(self.ids),
))
In a module that depends on account, this is reported: the unit of measure of a journal item is
product_uom_id, so PostgreSQL refuses the query.
class AccountMove(models.Model):
_inherit = "account.move"
def _x_units_by_product(self):
return self.env.execute_query(SQL(
"""
SELECT l.product_id, l.product_uom_id, SUM(l.quantity)
FROM account_move_line l
WHERE l.move_id IN %s
GROUP BY l.product_id, l.product_uom_id
""",
tuple(self.ids),
))
Suppressing¶
A finding sits on the column when the query is a single literal whose source spells its text as
is, without an escape such as \n; an SQL comment on that line then covers it:
Otherwise it sits where the literal starts, and a # odooscan: ignore[OD224] alone on the line
above that one covers it.