Columns prefixed with table name are not supported

[mxhyxym]

When join two tables, I found that there are no columns prefixed with table name. However, it is not work for clickhouse with the following error:

    org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [352]:
ClickHouse exception, code: 352, host: 172.0.0.1, port: xxxx; Code: 352, 
e.displayText() = DB::Exception: Column 'user_id' is ambiguous 
(version 19.16.7.24 (official build))

Because my SQL have the same column name in two join tables.

The related code in this repository is in /clickhouse-sqlalchemy/drivers/base.py. There are related code:

    def visit_column(self, column, include_table=True, **kwargs):
        # Columns prefixed with table name are not supported
        return super(ClickHouseCompiler, self).visit_column(
            column, include_table=False, **kwargs
        )

There is always False for include_table argument in this function.

[xzkostyan]

According to error from ClickHouse server you should use different label for the second column with name "user_id".

[kdiri]

I'm blocked too. That seems an anomaly cause as in the nature of sqlalchemy, I'm not supposed to use labellization for the join.

Here is the error that I'm getting:

Received exception from server (version 20.1.2):
Code: 352. DB::Exception: Received from localhost:XXXX. DB::Exception: Column 'ref_id' is ambiguous.

[xzkostyan]

OK. Please provide sample of code that reproduces the problem.

[kdiri]

Here is the code that does the job.

class Equipments(Base):
    """
    Clickhouse model for Equipments
    """

    __tablename__ = "equipments"

    id = Column(types.Int64, primary_key=True)
    product_id = Column(types.Int64, nullable=False)
    ref_id = Column(types.Int64, nullable=False)
    creation_date = Column(types.DateTime, nullable=False)
    __table_args__ = (engines.MergeTree(partition_by=product_id, primary_key=id, order_by=(id, creation_date)),)


class References(Base):
    """
    Clickhouse model for References
    """

    __tablename__ = "references"

    ref_id = Column(types.Int64, primary_key=True)
    ref_name = Column(types.Nullable(types.String))
    __table_args__ = (engines.MergeTree(partition_by=ref_id, primary_key=ref_id, order_by=(ref_id)),)

The file, which contains the base object:

import os

from contextlib import contextmanager
from clickhouse_sqlalchemy import make_session
from sqlalchemy import create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base

CLICK_HOUSE_HOST = os.getenv("CLICK_HOUSE_HOST", "XXXX")
CLICK_HOUSE_USER = os.getenv("CLICK_HOUSE_USER", "XXXX")
CLICK_HOUSE_PASSWORD = os.getenv("CLICK_HOUSE_PASSWORD", "XXXX")
CLICK_HOUSE_DB_NAME = os.getenv("CLICK_HOUSE_DB_NAME", "XXXX")

CLICK_HOUSE_URI = f"clickhouse://{CLICK_HOUSE_USER}:{CLICK_HOUSE_PASSWORD}@{CLICK_HOUSE_HOST}/{CLICK_HOUSE_DB_NAME}"

# SQLAlchemy configuration
ENGINE = create_engine(CLICK_HOUSE_URI)
METADATA = MetaData(bind=ENGINE)
Base = declarative_base(metadata=METADATA) 

@contextmanager
def get_db_session():
    """
    Return a thread-safe new DB session.
    """
    session = make_session(ENGINE)
    yield session
    session.close()

How to reproduce it:

from sqlalchemy.orm import Session

def get_value(session: Session):
        res = (
            session.query(Equipments, References.ref_name)
            .join(References, Equipments.ref_id == References.ref_id)
			.all()
        )
        return res

if __name__ == "__main__":
    with get_clickhouse_db_session() as session:
        res = get_value(session)

Here is the query that is runned when I execute my code with clickhouse_sqlalchemy

SELECT 
    id AS equipments_id, 
    product_id AS equipments_product_id, 
    ref_id AS equipments_ref_id, 
    creation_date AS equipments_creation_date, 
    ref_name AS references_ref_name
FROM equipments
INNER JOIN references ON ref_id = ref_id

Received exception from server (version 20.1.2):
Code: 352. DB::Exception: Received from localhost:XXXX. DB::Exception: Column 'ref_id' is ambiguous. 

[xzkostyan]

@kdiri thanks for detailed clarification. The thing was while joining on columns with the same name.

Fix is pushed into latest master.

Columns are now prefixed with table name.

[kdiri]

@xzkostyan That's awesome. Thanks for the fix.