<p dir="auto"><a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="464954871" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/4753" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/4753/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/4753">#4753</a> causes this regression which impacts test/orm/inheritance/test_poly_perstence.py, which now creates a bad query that still passes but will stop working as of <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="502223351" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/4887" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/4887/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/4887">#4887</a></p>
<p dir="auto">also that test suite needs to be rewritten</p>
<p dir="auto">basic test</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.orm import mapper
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session


class Person(object):
    pass


class Engineer(Person):
    pass


class Manager(Person):
    pass


class Boss(Manager):
    pass


class Company(object):
    pass

metadata = MetaData()

companies = Table(
    &quot;companies&quot;,
    metadata,
    Column(&quot;company_id&quot;, Integer, primary_key=True),
    Column(&quot;name&quot;, String(50)),
)

people = Table(
    &quot;people&quot;,
    metadata,
    Column(&quot;person_id&quot;, Integer, primary_key=True),
    Column(
        &quot;company_id&quot;,
        Integer,
        ForeignKey(&quot;companies.company_id&quot;),
        nullable=False,
    ),
    Column(&quot;name&quot;, String(50)),
    Column(&quot;type&quot;, String(30)),
)

engineers = Table(
    &quot;engineers&quot;,
    metadata,
    Column(
        &quot;person_id&quot;, Integer, ForeignKey(&quot;people.person_id&quot;), primary_key=True
    ),
    Column(&quot;status&quot;, String(30)),
    Column(&quot;engineer_name&quot;, String(50)),
    Column(&quot;primary_language&quot;, String(50)),
)

managers = Table(
    &quot;managers&quot;,
    metadata,
    Column(
        &quot;person_id&quot;, Integer, ForeignKey(&quot;people.person_id&quot;), primary_key=True
    ),
    Column(&quot;status&quot;, String(30)),
    Column(&quot;manager_name&quot;, String(50)),
)

boss = Table(
    &quot;boss&quot;,
    metadata,
    Column(
        &quot;boss_id&quot;, Integer, ForeignKey(&quot;managers.person_id&quot;), primary_key=True
    ),
    Column(&quot;golf_swing&quot;, String(30)),
)


person_with_polymorphic = &quot;*&quot;
manager_with_polymorphic = &quot;*&quot;

person_mapper = mapper(
    Person,
    people,
    with_polymorphic=person_with_polymorphic,
    polymorphic_on=people.c.type,
    polymorphic_identity=&quot;person&quot;,
)

mapper(
    Engineer,
    engineers,
    inherits=person_mapper,
    polymorphic_identity=&quot;engineer&quot;,
)
mapper(
    Manager,
    managers,
    inherits=person_mapper,
    with_polymorphic=manager_with_polymorphic,
    polymorphic_identity=&quot;manager&quot;,
)

mapper(Boss, boss, inherits=Manager, polymorphic_identity=&quot;boss&quot;)

mapper(
    Company,
    companies,
    properties={
        &quot;employees&quot;: relationship(
            Person,
            lazy=&quot;joined&quot;,
            cascade=&quot;all, delete-orphan&quot;,
            backref=&quot;company&quot;,
            order_by=people.c.person_id,
        )
    },
)


e = create_engine(&quot;sqlite://&quot;, echo=&quot;debug&quot;)
metadata.create_all(e)
session = Session(e)
c = session.query(Company).first()
"><pre class="notranslate"><code class="notranslate">from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.orm import mapper
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session


class Person(object):
    pass


class Engineer(Person):
    pass


class Manager(Person):
    pass


class Boss(Manager):
    pass


class Company(object):
    pass

metadata = MetaData()

companies = Table(
    "companies",
    metadata,
    Column("company_id", Integer, primary_key=True),
    Column("name", String(50)),
)

people = Table(
    "people",
    metadata,
    Column("person_id", Integer, primary_key=True),
    Column(
        "company_id",
        Integer,
        ForeignKey("companies.company_id"),
        nullable=False,
    ),
    Column("name", String(50)),
    Column("type", String(30)),
)

engineers = Table(
    "engineers",
    metadata,
    Column(
        "person_id", Integer, ForeignKey("people.person_id"), primary_key=True
    ),
    Column("status", String(30)),
    Column("engineer_name", String(50)),
    Column("primary_language", String(50)),
)

managers = Table(
    "managers",
    metadata,
    Column(
        "person_id", Integer, ForeignKey("people.person_id"), primary_key=True
    ),
    Column("status", String(30)),
    Column("manager_name", String(50)),
)

boss = Table(
    "boss",
    metadata,
    Column(
        "boss_id", Integer, ForeignKey("managers.person_id"), primary_key=True
    ),
    Column("golf_swing", String(30)),
)


person_with_polymorphic = "*"
manager_with_polymorphic = "*"

person_mapper = mapper(
    Person,
    people,
    with_polymorphic=person_with_polymorphic,
    polymorphic_on=people.c.type,
    polymorphic_identity="person",
)

mapper(
    Engineer,
    engineers,
    inherits=person_mapper,
    polymorphic_identity="engineer",
)
mapper(
    Manager,
    managers,
    inherits=person_mapper,
    with_polymorphic=manager_with_polymorphic,
    polymorphic_identity="manager",
)

mapper(Boss, boss, inherits=Manager, polymorphic_identity="boss")

mapper(
    Company,
    companies,
    properties={
        "employees": relationship(
            Person,
            lazy="joined",
            cascade="all, delete-orphan",
            backref="company",
            order_by=people.c.person_id,
        )
    },
)


e = create_engine("sqlite://", echo="debug")
metadata.create_all(e)
session = Session(e)
c = session.query(Company).first()

</code></pre></div>
<p dir="auto">the eager query contains unconverted anonymous names which are duplicating:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="
SELECT anon_1.companies_company_id AS anon_1_companies_company_id, anon_1.companies_name AS anon_1_companies_name, people_1.person_id AS people_1_person_id, people_1.company_id AS people_1_company_id, people_1.name AS people_1_name, people_1.type AS people_1_type, engineers_1.person_id AS engineers_1_person_id, people_1.person_id AS &quot;%(139980920883408 people)s_person_id_1&quot;, engineers_1.status AS engineers_1_status, engineers_1.engineer_name AS engineers_1_engineer_name, engineers_1.primary_language AS engineers_1_primary_language, managers_1.person_id AS managers_1_person_id, people_1.person_id AS &quot;%(139980920883408 people)s_person_id_1&quot;, managers_1.status AS managers_1_status, managers_1.manager_name AS managers_1_manager_name, boss_1.boss_id AS boss_1_boss_id, boss_1.golf_swing AS boss_1_golf_swing 
FROM (SELECT companies.company_id AS companies_company_id, companies.name AS companies_name 
FROM companies
 LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN (people AS people_1 LEFT OUTER JOIN engineers AS engineers_1 ON people_1.person_id = engineers_1.person_id LEFT OUTER JOIN managers AS managers_1 ON people_1.person_id = managers_1.person_id LEFT OUTER JOIN boss AS boss_1 ON managers_1.person_id = boss_1.boss_id) ON anon_1.companies_company_id = people_1.company_id ORDER BY people_1.person_id
"><pre class="notranslate"><code class="notranslate">
SELECT anon_1.companies_company_id AS anon_1_companies_company_id, anon_1.companies_name AS anon_1_companies_name, people_1.person_id AS people_1_person_id, people_1.company_id AS people_1_company_id, people_1.name AS people_1_name, people_1.type AS people_1_type, engineers_1.person_id AS engineers_1_person_id, people_1.person_id AS "%(139980920883408 people)s_person_id_1", engineers_1.status AS engineers_1_status, engineers_1.engineer_name AS engineers_1_engineer_name, engineers_1.primary_language AS engineers_1_primary_language, managers_1.person_id AS managers_1_person_id, people_1.person_id AS "%(139980920883408 people)s_person_id_1", managers_1.status AS managers_1_status, managers_1.manager_name AS managers_1_manager_name, boss_1.boss_id AS boss_1_boss_id, boss_1.golf_swing AS boss_1_golf_swing 
FROM (SELECT companies.company_id AS companies_company_id, companies.name AS companies_name 
FROM companies
 LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN (people AS people_1 LEFT OUTER JOIN engineers AS engineers_1 ON people_1.person_id = engineers_1.person_id LEFT OUTER JOIN managers AS managers_1 ON people_1.person_id = managers_1.person_id LEFT OUTER JOIN boss AS boss_1 ON managers_1.person_id = boss_1.boss_id) ON anon_1.companies_company_id = people_1.company_id ORDER BY people_1.person_id

</code></pre></div>
<p dir="auto">clean query:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="
SELECT anon_1.companies_company_id AS anon_1_companies_company_id, anon_1.companies_name AS anon_1_companies_name, people_1.person_id AS people_1_person_id, people_1.company_id AS people_1_company_id, people_1.name AS people_1_name, people_1.type AS people_1_type, engineers_1.person_id AS engineers_1_person_id, engineers_1.status AS engineers_1_status, engineers_1.engineer_name AS engineers_1_engineer_name, engineers_1.primary_language AS engineers_1_primary_language, managers_1.person_id AS managers_1_person_id, managers_1.status AS managers_1_status, managers_1.manager_name AS managers_1_manager_name, boss_1.boss_id AS boss_1_boss_id, boss_1.golf_swing AS boss_1_golf_swing 
FROM (SELECT companies.company_id AS companies_company_id, companies.name AS companies_name 
FROM companies
 LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN (people AS people_1 LEFT OUTER JOIN engineers AS engineers_1 ON people_1.person_id = engineers_1.person_id LEFT OUTER JOIN managers AS managers_1 ON people_1.person_id = managers_1.person_id LEFT OUTER JOIN boss AS boss_1 ON managers_1.person_id = boss_1.boss_id) ON anon_1.companies_company_id = people_1.company_id ORDER BY people_1.person_id
"><pre class="notranslate"><code class="notranslate">
SELECT anon_1.companies_company_id AS anon_1_companies_company_id, anon_1.companies_name AS anon_1_companies_name, people_1.person_id AS people_1_person_id, people_1.company_id AS people_1_company_id, people_1.name AS people_1_name, people_1.type AS people_1_type, engineers_1.person_id AS engineers_1_person_id, engineers_1.status AS engineers_1_status, engineers_1.engineer_name AS engineers_1_engineer_name, engineers_1.primary_language AS engineers_1_primary_language, managers_1.person_id AS managers_1_person_id, managers_1.status AS managers_1_status, managers_1.manager_name AS managers_1_manager_name, boss_1.boss_id AS boss_1_boss_id, boss_1.golf_swing AS boss_1_golf_swing 
FROM (SELECT companies.company_id AS companies_company_id, companies.name AS companies_name 
FROM companies
 LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN (people AS people_1 LEFT OUTER JOIN engineers AS engineers_1 ON people_1.person_id = engineers_1.person_id LEFT OUTER JOIN managers AS managers_1 ON people_1.person_id = managers_1.person_id LEFT OUTER JOIN boss AS boss_1 ON managers_1.person_id = boss_1.boss_id) ON anon_1.companies_company_id = people_1.company_id ORDER BY people_1.person_id

</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto"><a href="https://mariadb.com/kb/en/library/mariadb-1029-changelog/" rel="nofollow">https://mariadb.com/kb/en/library/mariadb-1029-changelog/</a></p>
<p dir="auto">This fixes the CHECK constraint issue <a href="https://jira.mariadb.org/browse/MDEV-13596" rel="nofollow">https://jira.mariadb.org/browse/MDEV-13596</a> that ruins booleans for us, and additionally the DROP issue <a href="https://jira.mariadb.org/browse/MDEV-11114" rel="nofollow">https://jira.mariadb.org/browse/MDEV-11114</a> was fixed as of 10.2.8: <a href="https://mariadb.com/kb/en/library/mariadb-1028-changelog/" rel="nofollow">https://mariadb.com/kb/en/library/mariadb-1028-changelog/</a></p>
<p dir="auto">propose a brief warning if mariadb 10.2[&lt;9] is detected then go forward normally with CI support.  Will need to test if <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="384640259" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/4096" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/4096/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/4096">#4096</a> is still needed.</p>