[Sqlalchemy-tickets] Issue #3041: ORM primary key retreival can populate unrelated column in mappers used for inheritance (zzzeek/sqlalchemy)

[ebnull <iss...@bi...>]

New issue 3041: ORM primary key retreival can populate unrelated column in mappers used for inheritance
https://bitbucket.org/zzzeek/sqlalchemy/issue/3041/orm-primary-key-retreival-can-populate

ebnull:

When in an inheritance relationship, SA can overwrite a child table's column when setting the parent's primary key (as grabbed from the database) with the parent's primary key if the the child's column's attribute name matches the parent's attribute name.

See attached bug2.py for example. Sample output follows:


```
$ python bug2.py
INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE bug_parent (
        discriminator VARCHAR(50), 
        id INTEGER NOT NULL, 
        parent_attr INTEGER, 
        PRIMARY KEY (id)
)


INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE bug_child_a (
        id INTEGER NOT NULL, 
        parent_id INTEGER NOT NULL, 
        child_attr INTEGER, 
        PRIMARY KEY (id),         UNIQUE (parent_id), 
        FOREIGN KEY(parent_id) REFERENCES bug_parent (id)
)

INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE bug_child_b (
        id INTEGER NOT NULL,         parent_id INTEGER NOT NULL, 
        child_attr INTEGER, 
        PRIMARY KEY (id), 
        UNIQUE (parent_id), 
        FOREIGN KEY(parent_id) REFERENCES bug_parent (id)
)
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE bug_child_c (
        some_primary_key INTEGER NOT NULL, 
        id INTEGER, 
        parent_id INTEGER NOT NULL, 
        child_attr INTEGER, 
        PRIMARY KEY (some_primary_key), 
        UNIQUE (parent_id), 
        FOREIGN KEY(parent_id) REFERENCES bug_parent (id)
)


INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT


INFO:__main__:The following SQL should not be setting bug_child_a.id (incorrect)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_parent (discriminator, parent_attr) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:('child_a', 100)
DEBUG:__main__:Validating ChildA.id=1
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_child_a (id, parent_id, child_attr) VALUES (?, ?, ?)
INFO:sqlalchemy.engine.base.Engine:(1, 1, 200)


INFO:__main__:The following SQL does not set bug_child_b.id (debatably correct)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_parent (discriminator, parent_attr) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:('child_b', 300)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_child_b (parent_id, child_attr) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:(2, 400)
DEBUG:__main__:Validating ChildB.child_extra_id=1


INFO:__main__:The following SQL sets bug_child_c.id to None (correct)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_parent (discriminator, parent_attr) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:('child_c', 500)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO bug_child_c (id, parent_id, child_attr) VALUES (?, ?, ?)
INFO:sqlalchemy.engine.base.Engine:(None, 3, 600)

NFO:sqlalchemy.engine.base.Engine:COMMIT
Testing objects
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:SELECT bug_parent.discriminator AS bug_parent_discriminator, bug_child_a.id AS bug_child_a_id, bug_parent.id AS bug_parent_id, bug_parent.parent_attr AS bug_parent_parent_attr, bug_child_a.parent_id AS bug_child_a_parent_id, bug_child_
a.child_attr AS bug_child_a_child_attr 
FROM bug_parent JOIN bug_child_a ON bug_parent.id = bug_child_a.parent_id 
WHERE bug_parent.parent_attr = ?
INFO:sqlalchemy.engine.base.Engine:(100,)
INFO:sqlalchemy.engine.base.Engine:SELECT bug_child_b.id AS bug_child_b_id, bug_parent.discriminator AS bug_parent_discriminator, bug_parent.id AS bug_parent_id, bug_parent.parent_attr AS bug_parent_parent_attr, bug_child_b.parent_id AS bug_child_b_parent_id, bug_child_
b.child_attr AS bug_child_b_child_attr 
FROM bug_parent JOIN bug_child_b ON bug_parent.id = bug_child_b.parent_id 
WHERE bug_parent.parent_attr = ?
INFO:sqlalchemy.engine.base.Engine:(300,)
INFO:sqlalchemy.engine.base.Engine:SELECT bug_child_c.id AS bug_child_c_id, bug_parent.discriminator AS bug_parent_discriminator, bug_parent.id AS bug_parent_id, bug_parent.parent_attr AS bug_parent_parent_attr, bug_child_c.some_primary_key AS bug_child_c_some_primary_k
ey, bug_child_c.parent_id AS bug_child_c_parent_id, bug_child_c.child_attr AS bug_child_c_child_attr 
FROM bug_parent JOIN bug_child_c ON bug_parent.id = bug_child_c.parent_id 
WHERE bug_parent.parent_attr = ?
INFO:sqlalchemy.engine.base.Engine:(500,)
{'ChildA.parent_attr': 100, 'ChildA.child_attr': 200, 'ChildA._discriminator': u'child_a', 'ChildA.id': 1, 'ChildA.parent_id': 1}
{'ChildB._discriminator': u'child_b', 'ChildB.parent_id': 2, 'ChildB.parent_attr': 300, 'ChildB.id': 2, 'ChildB.child_attr': 400, 'ChildB.child_extra_id': 1}
{'ChildC.id': 3, 'ChildC.child_attr': 600, 'ChildC._discriminator': u'child_c', 'ChildC.parent_attr': 500, 'ChildC.some_primary_key': 1, 'ChildC.child_extra_id': None, 'ChildC.parent_id': 3}
Traceback (most recent call last):
  File "bug2.py", line 124, in <module>
    assert cb.child_extra_id == None, "SQLA set bug_child_b.id to a non-null value"
AssertionError: SQLA set bug_child_b.id to a non-null value
```