Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")

[Dan Stromberg]

Hi people.

I'm having trouble with a test query.

As the subject line says, I'm getting:

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")

But it seems like tb_br exists, and has an id column - tb_br being an empty table, but still, existent:

mysql> show create table tb_br;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_br | CREATE TABLE `tb_br` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> select * from tb_br;
Empty set (0.03 sec)

The query, along with sample models, looks like:

#!/usr/bin/env python3

"""
A little test program.

Environment variables:
    DBU  Your database user
    DBP  Your database password
    DBH  Your database host
    IDB  Your initial database
"""

import os
import pprint

from sqlalchemy import create_engine, select
from sqlalchemy.orm import aliased, sessionmaker, declarative_base
from sqlalchemy.sql.expression import func
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()
Base = declarative_base()


class NV(Base):
    __tablename__ = "tb_nv"
    __bind_key__ = "testdb"
    __table_args__ = (
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8",
            "mysql_collate": "utf8_general_ci",
        },
    )

    id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)
    builds = db.relationship("Bld", primaryjoin="(NV.id == Bld.variant_id)")


class Vers(Base):
    __tablename__ = "tb_vers"
    __bind_key__ = "testdb"
    __table_args__ = (
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8",
            "mysql_collate": "utf8_general_ci",
        },
    )

    id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)


class St(Base):
    __tablename__ = "tb_brst"
    __bind_key__ = "testdb"
    __table_args__ = ({"mysql_engine": "InnoDB", "mysql_charset": "utf8"},)

    id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)
    version_id = db.Column(
        "version_id",
        db.Integer,
        db.ForeignKey(
            "tb_vers.id",
            name="fk_tb_brst_version_id",
            onupdate="CASCADE",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )
    branch_id = db.Column(
        "branch_id",
        db.Integer,
        db.ForeignKey(
            "tb_br.id",
            name="fk_tb_brst_branch_id",
            onupdate="CASCADE",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )
    build_id = db.Column(
        "build_id",
        db.Integer,
        db.ForeignKey(
            "tb_bld.id",
            name="fk_tb_brst_build_id",
            onupdate="CASCADE",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )

    version = db.relationship(
        "Vers", innerjoin=True, primaryjoin="(St.version_id == Vers.id)"
    )
    branch = db.relationship(
        "Br", innerjoin=True, primaryjoin="(St.branch_id == Br.id)"
    )
    build = db.relationship(
        "Bld", innerjoin=True, primaryjoin="(St.build_id == Bld.id)"
    )


class Br(Base):
    __tablename__ = "tb_br"
    __bind_key__ = "testdb"
    __table_args__ = (
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8",
            "mysql_collate": "utf8_general_ci",
        },
    )

    id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)
    name = db.Column("name", db.String(45), nullable=False)


class Bld(Base):
    __tablename__ = "tb_bld"
    __bind_key__ = "testdb"
    __table_args__ = (
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8",
            "mysql_collate": "utf8_general_ci",
        },
    )

    id = db.Column("id", db.Integer, primary_key=True, autoincrement=True)

    name = db.Column("name", db.String(100), nullable=False)

    variant_id = db.Column(
        "variant_id",
        db.Integer,
        db.ForeignKey(
            "tb_nv.id",
            name="fk_tb_bld_variant_id",
            onupdate="CASCADE",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )

    variant = db.relationship("NV")


def display(values):
    """Display values in a decent way."""
    pprint.pprint(values)


def connect():
    """
    Connect to Staging for testing.

    This is based on https://medium.com/analytics-vidhya/translating-sql-queries-to-sqlalchemy-orm-a8603085762b
    ...and ./game-publishing/services/api/deploy/celery/config/staging-base.j2
    """
    conn_str = "mysql://{}:{}@{}/{}".format(
        os.environ["DBU"],
        os.environ["DBP"],
        os.environ["DBH"],
        os.environ["IDB"],
    )
    engine = create_engine(conn_str)

    # Added 2023-03-17
    Base.metadata.create_all(engine)

    session = sessionmaker(bind=engine)
    sess = session()
    return (engine, sess)


def main():
    """A minimal query that exhibits the problem."""
    (engine, session) = connect()

    Base.metadata.create_all(engine)

    v_2 = aliased(Vers, name="v_2")
    bs = aliased(St, name="bs")
    bs_2 = aliased(St, name="bs_2")
    bs_3 = aliased(St, name="bs_3")

    # sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")
    # [SQL: SELECT tb_nv.id, min(bs_3.build_id) AS min_1
    # FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs
    # INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id
    # INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id]
    query = (
        select(NV.id, func.min(bs_3.build_id))
        .select_from(bs)
        .join(v_2, onclause=(bs.version_id == v_2.id))
        .join(bs_2, onclause=(Br.id == bs_2.branch_id))
    )
    result = session.execute(query)

    display(result.scalar().all())


main()

I'm using:

$ python3 -m pip list -v | grep -i sqlalchemy

Flask-SQLAlchemy 2.5.1 /data/home/dstromberg/.local/lib/python3.10/site-packages pip SQLAlchemy 1.4.36 /data/home/dstromberg/.local/lib/python3.10/site-packages pip $ python3 -m pip list -v | grep -i mysql mysqlclient 2.1.1 /data/home/dstromberg/.local/lib/python3.10/site-packages pip PyMySQL 0.8.0 /data/home/dstromberg/.local/lib/python3.10/site-packages pip bash-4.2# mysql --version mysql Ver 14.14 Distrib 5.7.41, for Linux (x86_64) using EditLine wrapper

Any hints?

Thanks!

[Dan Stromberg [External]]

 

Sorry, I don’t know why Google Groups decided to aggregate a few lines into 2 large lines.  Here’s that list of versions again.  Hopefully GG will be appeased this time.

[Mike Bayer]

the query emitted is:

SELECT tb_nv.id, min(bs_3.build_id) AS min_1

FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs

INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id

INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id

the error means that your ON clause refers to a table "tb_br" which is not otherwise in the FROM clause: "ON tb_br.id = bs_2.branch_id"

the ON clause can only refer to columns from tables that are being SELECTed from, such as:

SELECT tb_nv.id, min(bs_3.build_id) AS min_1

FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs

INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id

INNER JOIN tb_br ON tb_br.id = bs_2.branch_id

INNER JOIN tb_brst AS bs_2 ON tb_br.id = bs_2.branch_id

so you'd need to alter your query to include some indication how tb_br is part of what's being joined.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1576c1a1-e108-4bb0-83eb-57ab4125c457n%40googlegroups.com.

[Dan Stromberg [External]]

That makes sense, but…. I’m afraid I don’t know how to add tb_br to the select.

 

I tried:

    query = (

        select(NV.id, func.min(bs_3.build_id))

        .select_from(bs, Br)

        .join(v_2, onclause=(bs.version_id == v_2.id))

        .join(bs_2, onclause=(Br.id == bs_2.branch_id))

    )

…which gave:

1054, "Unknown column 'tb_br.id' in 'on clause'"

 

 

…and I tried:
    query = (

        select(NV.id, func.min(bs_3.build_id), Br)

        .select_from(bs)

        .join(v_2, onclause=(bs.version_id == v_2.id))

        .join(bs_2, onclause=(Br.id == bs_2.branch_id))

    )

…which also gave:

(1054, "Unknown column 'tb_br.id' in 'on clause'")

 

I’m guessing I’m missing something simple, but I have no idea what.

 

Any (further) suggestions?

 

 

From: sqlal...@googlegroups.com <sqlal...@googlegroups.com> on behalf of Mike Bayer <mike_not_...@zzzcomputing.com>
Date: Saturday, March 18, 2023 at 8:01 AM
To: noreply-spamdigest via sqlalchemy <sqlal...@googlegroups.com>
Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")

the query emitted is: SELECT tb_nv. id, min(bs_3. build_id) AS min_1 FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs. version_id = v_2. id INNER JOIN tb_brst AS bs_2 ON tb_br. id = bs_2. branch_id the error means that your

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1896c52f-a997-4bbc-b78f-47a13077e797%40app.fastmail.com.

[Mike Bayer]

what SQL are you going for ?  start with that.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/DM5PR12MB25034195B351C4A186BD7B99C5809%40DM5PR12MB2503.namprd12.prod.outlook.com.

[Dan Stromberg [External]]

 

I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share.  It’s 43 lines of SQL with multiple subqueries.

 

Would just the simplest parts of the from clause work?

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/282a420a-46a6-4fc0-96f2-d4ced697dc13%40app.fastmail.com.

[Dan Stromberg [External]]

Here’s the select, and most of the from clause:

 

select nv.id, min(bs.build_id) as min_build_id
from tb_v as v,
tb_nv as nv,
tb_bs as bs,
tb_br as br,

 

 

From: 'Dan Stromberg [External]' via sqlalchemy <sqlal...@googlegroups.com>
Date: Monday, March 20, 2023 at 2:16 PM
To: sqlal...@googlegroups.com <sqlal...@googlegroups.com>
Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")

I’m getting some pushback internally, from my team lead – he and I both think it’s probably too much detail to share. It’s 43 lines of SQL with multiple subqueries. Would just the simplest parts of the from clause work? From: sqlalchemy@ googlegroups. com

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/UDbK6bbDsDo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/DM5PR12MB25037CCEB4E02BC36F40D831C5809%40DM5PR12MB2503.namprd12.prod.outlook.com.

[Mike Bayer]

OK, not really, you want tables in the FROM clause.  use either the select_from() or join_from() method to do that:

https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#explicit-from-clauses-and-joins

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/DM5PR12MB25037CCEB4E02BC36F40D831C5809%40DM5PR12MB2503.namprd12.prod.outlook.com.

[Dan Stromberg [External]]

 

Alright, using join_from may have led to clearing a hurdle.  I’m now using:

    query = (

        select(NV.id, func.min(bs_3.build_id))

        .select_from(bs)

        .join(v_2, onclause=(bs.version_id == v_2.id))

        .join_from(bs_2, Br, onclause=(Br.id == bs_2.branch_id))

    )

 

..and am getting a new error:

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1140, "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'tact_dev.tb_nv.id'; this is incompatible with sql_mode=only_full_group_by")

 

Do I need to aggregate?  Or perhaps change sql_mode?

 

Thanks!

 

From: sqlal...@googlegroups.com <sqlal...@googlegroups.com> on behalf of Mike Bayer <mike_not_...@zzzcomputing.com>
Date: Monday, March 20, 2023 at 5:33 PM
To: noreply-spamdigest via sqlalchemy <sqlal...@googlegroups.com>
Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")

OK, not really, you want tables in the FROM clause. use either the select_from() or join_from() method to do that: https: //docs. sqlalchemy. org/en/20/tutorial/data_select. html#explicit-from-clauses-and-joins On Mon, Mar 20, 2023, at 5: 16 PM,

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1550511f-e7b1-4160-99ef-8b63191a2ee2%40app.fastmail.com.

[Dan Stromberg [External]]

 

Hoping to save an iteration: the SQL currently looks like:

[SQL: SELECT tb_nv.id, min(bs_3.build_id) AS min_1

FROM tb_nv, tb_brst AS bs_3, tb_brst AS bs INNER JOIN tb_vers AS v_2 ON bs.version_id = v_2.id, tb_brst AS bs_2 INNER JOIN tb_br ON tb_br.id = bs_2.branch_id]

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/DM5PR12MB2503BC50CB1290BF9086D894C5819%40DM5PR12MB2503.namprd12.prod.outlook.com.

[Dan Stromberg [External]]

Granted, it’s difficult to read my (admittedly rather blank) mind.  Maybe I should restate the question:

 

What are my options?  I just want to see the rows from the query below.  Why is it telling me I need to aggregate, and if I do truly need to, what might be an aggregate function that won’t eliminate much of what the query is producing?

 

From: 'Dan Stromberg [External]' via sqlalchemy <sqlal...@googlegroups.com>

Date: Tuesday, March 21, 2023 at 9:05 AM

To: sqlal...@googlegroups.com <sqlal...@googlegroups.com>
Subject: Re: [sqlalchemy] Test query seems to spuriously give sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1054, "Unknown column 'tb_br.id' in 'on clause'")

Alright, using join_from may have led to clearing a hurdle. I’m now using: query = ( select(NV. id, func. min(bs_3. build_id)) .select_from(bs) .join(v_2, onclause=(bs. version_id == v_2. id)) .join_from(bs_2, Br, onclause=(Br. id == bs_2. branch_id))

 

Alright, using join_from may have led to clearing a hurdle.  I’m now using:

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/DM5PR12MB2503BC50CB1290BF9086D894C5819%40DM5PR12MB2503.namprd12.prod.outlook.com.

[Mike Bayer]

MySQL's "only full group by" option causes MySQL to use the SQL standard for GROUP BY, which states that all columns that are not contained within an aggregate function must be in the GROUP BY clause.

That is, this SQL is invalid:

SELECT table.x, max(table.y) FROM table

The reason is that max(table.y) is going to SELECT only one row, the row that has the highest value for "y".  Where does that leave ".x" , does it just indicate the value of "x" that just happens to be at the max(y) row?  What if there are 100 "y" rows all with that max value?    From a SQL standpoint, the function is wrong.

Instead, the correct way to write the above SQL is:

SELECT table.x, max(table.y) FROM table GROUP BY table.x

Above, the max() function is now applied to individual groups of rows, and we get multiple rows back, one row for each value of "x" along with the max(y) for that group.

The MySQL error you are getting is basically asking you to add "NV.id" to the GROUP BY clause.

Background on this concept is at https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html .

it's critical though that you understand the SQL you want to emit and confirm it gets back the result you are looking for.   Just adding elements to the query until MySQL accepts it is not necessarily going to get you the results you want.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/DM5PR12MB2503EC98EAA7D442322B1E1FC5819%40DM5PR12MB2503.namprd12.prod.outlook.com.