Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SAWarning: SELECT statement has a cartesian product between FROM element(s) #305

Open
indiVar0508 opened this issue Sep 3, 2022 · 2 comments
Open

SAWarning: SELECT statement has a cartesian product between FROM element(s) #305

indiVar0508 opened this issue Sep 3, 2022 · 2 comments

Comments

@indiVar0508
Copy link
Contributor

indiVar0508 commented Sep 3, 2022 •
edited

Getting this warning when running pytest tests/relationships/test_non_versioned_classes.py::TestManyToManyRelationshipToNonVersionedClass::test_single_insert

sqlalchemy_continuum/relationship_builder.py:86: SAWarning: SELECT statement has a cartesian product between FROM element(s) "tag" and FROM element "article_tag_version".  Apply join condition(s) between each element to resolve.
    return query.all()

I created a normal py-script from testfile to replicate the issue

import sqlalchemy as sa
from sqlalchemy.orm import declarative_base, sessionmaker, configure_mappers
from sqlalchemy_continuum import make_versioned

make_versioned(user_cls=None)
Base = declarative_base()

class Article(Base):
    __tablename__ = 'article'
    __versioned__ = {
        'base_classes': (Base, )
    }
    id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
    name = sa.Column(sa.Unicode(255))

article_tag = sa.Table(
    'article_tag',
    Base.metadata,
    sa.Column(
        'article_id',
        sa.Integer,
        sa.ForeignKey('article.id'),
        primary_key=True,
    ),
    sa.Column(
        'tag_id',
        sa.Integer,
        sa.ForeignKey('tag.id'),
        primary_key=True
    )
)

class Tag(Base):
    __tablename__ = 'tag'

    id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
    name = sa.Column(sa.Unicode(255))

Tag.articles = sa.orm.relationship(
    Article,
    secondary=article_tag,
    backref='tags'
)
configure_mappers()
engine = sa.create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)

session = sessionmaker(bind=engine)()

article = Article()
article.name = u'Some article'
article.content = u'Some content'
tag = Tag(name=u'some tag')
article.tags.append(tag)
session.add(article)
session.commit()
article.versions[0].tags

Logs:

2022-09-03 11:56:16,131 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-03 11:56:16,132 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("article")
2022-09-03 11:56:16,132 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,132 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("article")
2022-09-03 11:56:16,132 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,133 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("article_tag")
2022-09-03 11:56:16,133 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,133 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("article_tag")
2022-09-03 11:56:16,133 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,133 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("tag")
2022-09-03 11:56:16,133 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,134 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("tag")
2022-09-03 11:56:16,134 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,134 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("article_version")
2022-09-03 11:56:16,134 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,134 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("article_version")
2022-09-03 11:56:16,134 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,135 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("transaction")
2022-09-03 11:56:16,135 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,135 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("transaction")
2022-09-03 11:56:16,135 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,135 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("article_tag_version")
2022-09-03 11:56:16,135 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,136 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("article_tag_version")
2022-09-03 11:56:16,136 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-03 11:56:16,137 INFO sqlalchemy.engine.Engine 
CREATE TABLE article (
        id INTEGER NOT NULL, 
        name VARCHAR(255), 
        PRIMARY KEY (id)
)


2022-09-03 11:56:16,137 INFO sqlalchemy.engine.Engine [no key 0.00018s] ()
2022-09-03 11:56:16,138 INFO sqlalchemy.engine.Engine 
CREATE TABLE tag (
        id INTEGER NOT NULL, 
        name VARCHAR(255), 
        PRIMARY KEY (id)
)


2022-09-03 11:56:16,138 INFO sqlalchemy.engine.Engine [no key 0.00015s] ()
2022-09-03 11:56:16,139 INFO sqlalchemy.engine.Engine 
CREATE TABLE article_version (
        id INTEGER NOT NULL, 
        name VARCHAR(255), 
        transaction_id INTEGER NOT NULL, 
        end_transaction_id INTEGER, 
        operation_type SMALLINT NOT NULL, 
        PRIMARY KEY (id, transaction_id)
)


2022-09-03 11:56:16,139 INFO sqlalchemy.engine.Engine [no key 0.00017s] ()
2022-09-03 11:56:16,139 INFO sqlalchemy.engine.Engine CREATE INDEX ix_article_version_transaction_id ON article_version (transaction_id)
2022-09-03 11:56:16,140 INFO sqlalchemy.engine.Engine [no key 0.00016s] ()
2022-09-03 11:56:16,140 INFO sqlalchemy.engine.Engine CREATE INDEX ix_article_version_end_transaction_id ON article_version (end_transaction_id)
2022-09-03 11:56:16,140 INFO sqlalchemy.engine.Engine [no key 0.00013s] ()
2022-09-03 11:56:16,141 INFO sqlalchemy.engine.Engine CREATE INDEX ix_article_version_operation_type ON article_version (operation_type)
2022-09-03 11:56:16,141 INFO sqlalchemy.engine.Engine [no key 0.00014s] ()
2022-09-03 11:56:16,142 INFO sqlalchemy.engine.Engine 
CREATE TABLE "transaction" (
        issued_at DATETIME, 
        id INTEGER NOT NULL, 
        remote_addr VARCHAR(50), 
        PRIMARY KEY (id)
)


2022-09-03 11:56:16,142 INFO sqlalchemy.engine.Engine [no key 0.00018s] ()
2022-09-03 11:56:16,143 INFO sqlalchemy.engine.Engine 
CREATE TABLE article_tag_version (
        article_id INTEGER NOT NULL, 
        tag_id INTEGER NOT NULL, 
        transaction_id INTEGER NOT NULL, 
        end_transaction_id INTEGER, 
        operation_type SMALLINT NOT NULL, 
        PRIMARY KEY (article_id, tag_id, transaction_id)
)


2022-09-03 11:56:16,143 INFO sqlalchemy.engine.Engine [no key 0.00016s] ()
2022-09-03 11:56:16,143 INFO sqlalchemy.engine.Engine CREATE INDEX ix_article_tag_version_end_transaction_id ON article_tag_version (end_transaction_id)
2022-09-03 11:56:16,144 INFO sqlalchemy.engine.Engine [no key 0.00015s] ()
2022-09-03 11:56:16,144 INFO sqlalchemy.engine.Engine CREATE INDEX ix_article_tag_version_operation_type ON article_tag_version (operation_type)
2022-09-03 11:56:16,144 INFO sqlalchemy.engine.Engine [no key 0.00013s] ()
2022-09-03 11:56:16,145 INFO sqlalchemy.engine.Engine CREATE INDEX ix_article_tag_version_transaction_id ON article_tag_version (transaction_id)
2022-09-03 11:56:16,145 INFO sqlalchemy.engine.Engine [no key 0.00014s] ()
2022-09-03 11:56:16,146 INFO sqlalchemy.engine.Engine 
CREATE TABLE article_tag (
        article_id INTEGER NOT NULL, 
        tag_id INTEGER NOT NULL, 
        PRIMARY KEY (article_id, tag_id), 
        FOREIGN KEY(article_id) REFERENCES article (id), 
        FOREIGN KEY(tag_id) REFERENCES tag (id)
)


2022-09-03 11:56:16,146 INFO sqlalchemy.engine.Engine [no key 0.00017s] ()
2022-09-03 11:56:16,146 INFO sqlalchemy.engine.Engine COMMIT
2022-09-03 11:56:16,147 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-03 11:56:16,151 INFO sqlalchemy.engine.Engine INSERT INTO "transaction" (issued_at, remote_addr) VALUES (?, ?)
2022-09-03 11:56:16,151 INFO sqlalchemy.engine.Engine [generated in 0.00042s] ('2022-09-03 06:26:16.151053', None)
2022-09-03 11:56:16,153 INFO sqlalchemy.engine.Engine INSERT INTO article (name) VALUES (?)
2022-09-03 11:56:16,154 INFO sqlalchemy.engine.Engine [generated in 0.00034s] ('Some article',)
2022-09-03 11:56:16,155 INFO sqlalchemy.engine.Engine INSERT INTO tag (name) VALUES (?)
2022-09-03 11:56:16,155 INFO sqlalchemy.engine.Engine [generated in 0.00031s] ('some tag',)
2022-09-03 11:56:16,157 INFO sqlalchemy.engine.Engine INSERT INTO article_tag (article_id, tag_id) VALUES (?, ?)
2022-09-03 11:56:16,157 INFO sqlalchemy.engine.Engine [generated in 0.00061s] (1, 1)
2022-09-03 11:56:16,159 INFO sqlalchemy.engine.Engine INSERT INTO article_tag_version (article_id, tag_id, transaction_id, operation_type) VALUES (?, ?, ?, ?)
2022-09-03 11:56:16,159 INFO sqlalchemy.engine.Engine [generated in 0.00032s] (1, 1, 1, 0)
2022-09-03 11:56:16,166 INFO sqlalchemy.engine.Engine UPDATE article_version SET end_transaction_id=? WHERE article_version.transaction_id = (SELECT max(article_version_1.transaction_id) AS max_1 
FROM article_version AS article_version_1 
WHERE article_version_1.transaction_id < ? AND article_version_1.id = ?) AND article_version.id = ?
2022-09-03 11:56:16,166 INFO sqlalchemy.engine.Engine [generated in 0.00041s] (1, 1, 1, 1)
2022-09-03 11:56:16,168 INFO sqlalchemy.engine.Engine INSERT INTO article_version (id, name, transaction_id, end_transaction_id, operation_type) VALUES (?, ?, ?, ?, ?)
2022-09-03 11:56:16,169 INFO sqlalchemy.engine.Engine [generated in 0.00037s] (1, 'Some article', 1, None, 0)
2022-09-03 11:56:16,170 INFO sqlalchemy.engine.Engine COMMIT
2022-09-03 11:56:16,171 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-03 11:56:16,177 INFO sqlalchemy.engine.Engine SELECT article.id AS article_id, article.name AS article_name 
FROM article 
WHERE article.id = ?
2022-09-03 11:56:16,177 INFO sqlalchemy.engine.Engine [generated in 0.00038s] (1,)
2022-09-03 11:56:16,178 INFO sqlalchemy.engine.Engine SELECT article_version.id AS article_version_id, article_version.name AS article_version_name, article_version.transaction_id AS article_version_transaction_id, article_version.end_transaction_id AS article_version_end_transaction_id, article_version.operation_type AS article_version_operation_type 
FROM article_version 
WHERE ? = article_version.id ORDER BY article_version.transaction_id
 LIMIT ? OFFSET ?
2022-09-03 11:56:16,178 INFO sqlalchemy.engine.Engine [generated in 0.00363s] (1, 1, 0)
/home/indivar/github/sqlalchemy-continuum/sqlalchemy_continuum/relationship_builder.py:86: SAWarning: SELECT statement has a cartesian product between FROM element(s) "article_tag_version" and FROM element "tag".  Apply join condition(s) between each element to resolve.
  return query.all()
2022-09-03 11:56:16,182 INFO sqlalchemy.engine.Engine SELECT tag.id AS tag_id, tag.name AS tag_name 
FROM tag, article_tag_version 
WHERE ? = article_tag_version.article_id
2022-09-03 11:56:16,182 INFO sqlalchemy.engine.Engine [generated in 0.00036s] (1,)

This seem to be to happening only for SQLA > 1.4
@indiVar0508
Copy link
Contributor Author

indiVar0508 commented Sep 3, 2022 •
edited

This seem to be happening due to a change in SQLA from this thread,
not sure i tried to look into it, might be happening due expression_reflector but not sure how , i can be completely wrong

Edit:
I think issue lies in criteria method of relationship builder for MANYTOMANY direction for non-versioned object(s),

@indiVar0508 indiVar0508 mentioned this issue Sep 3, 2022
Draft
@jbhanks
Copy link

jbhanks commented Sep 16, 2023

I think I am probably experiencing the same thing. I get the warning but as far as I can tell it isn't a problem as the output is exactly what I intended.

The schema:

class MediaType(Base):
    __tablename__ = "media_types"
    media_type: Mapped[str] = mapped_column(init=True, primary_key=True)
    file_format: Mapped[str] = mapped_column(
        default=None, unique=True, nullable=True
    )


class Collection(Base):
    __tablename__ = "collections"
    collection_id: Mapped[int] = mapped_column(
        init=False, primary_key=True, autoincrement=True
    )
    collection_name: Mapped[str] = mapped_column(default=None)
    collection_description: Mapped[str] = mapped_column(
        default=None, unique=False, nullable=True
    )
    media_type_id: Mapped[str] = mapped_column(
         "media_type",
        ForeignKey("media_types.media_type"), default=None
    )
    media_type: Mapped[MediaType] = relationship(init=False)
    collection_ezname: Mapped[str] = mapped_column(Computed(func.LOWER(func.REGEXP_REPLACE((collection_name), '[ /%<>#";?:@&=+$,.]', '_', 'g'))), unique=True, init=False)
    tags: Mapped[list]  = mapped_column(ARRAY(TEXT, dimensions=1), default=None, nullable=True)
    date_added: Mapped[datetime.datetime] = mapped_column(default=None, nullable=True)

    __mapper_args__ = {
        "polymorphic_identity": "collections",
        "eager_defaults": True,
    }


class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(init=False, primary_key=True)
    title: Mapped[str] = mapped_column(default=None, nullable=True)
    description: Mapped[str] = mapped_column(default=None, nullable=True)

    collection_id: Mapped[int] = mapped_column(
        ForeignKey("collections.collection_id"), init=False
    )
    collection: Mapped[Collection] = relationship(kw_only=True)

    media_type: Mapped[str] = mapped_column(
        ForeignKey("media_types.media_type"), init=False
    )
    media_type_reference: Mapped[MediaType] = relationship(init=False)
    tags: Mapped[list]  = mapped_column(ARRAY(TEXT, dimensions=1), default=None, nullable=True)
    views: Mapped[int] = mapped_column(default=0, nullable=True)
    social_media: Mapped[dict] =mapped_column(JSONB, default=None, nullable=True)
    date_added: Mapped[datetime.datetime] = mapped_column(default=None, nullable=True)
    date_modified: Mapped[datetime.datetime] = mapped_column(default=None, nullable=True)
    rss_pub_date: Mapped[str] = mapped_column(default=None, nullable=True)
    added_to_rss: Mapped[bool] = mapped_column(default=False, nullable=True)

    __mapper_args__ = {
        "polymorphic_identity": "posts",
        "polymorphic_on": "media_type",
        "eager_defaults": True,
    }


class Link(Post):
    __tablename__ = "links"
    id: Mapped[int] = mapped_column(
        ForeignKey("posts.id"), init=False, primary_key=True
    )
    url: Mapped[str] = mapped_column(default=None, unique=True)
    other_info: Mapped[str] = mapped_column(default=None, nullable=True)
    clicks: Mapped[int] = mapped_column(default=0, nullable=True)

    __mapper_args__ = {
        "polymorphic_identity": "links",
        "eager_defaults": True,
    }

The line that makes the error:

    link_collections = db.session.query(Collection).where(MediaType.media_type == "links").all()
    print(link_collections)

The output (which, I stress, was the output I desired):

[Collection(collection_id=1, collection_name='Essentials', collection_description='', media_type_id='links', media_type=MediaType(media_type='links', file_format=None), collection_ezname='essentials', tags=None, date_added=None), Collection(collection_id=2, collection_name='NYC', collection_description='', media_type_id='links', media_type=MediaType(media_type='links', file_format=None), collection_ezname='nyc', tags=None, date_added=None), Collection(collection_id=3, collection_name='Amazing Art', collection_description='', media_type_id='links', media_type=MediaType(media_type='links', file_format=None), collection_ezname='amazing_art', tags=None, date_added=None), 
....
]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@jbhanks @indiVar0508