Bug: Incorrect SQL generated for `UPSERT INTO ... SELECT`

[makhalin]

Hi guys,

Thank you for the great work on this library.

I've encountered an issue where UPSERT INTO ... SELECT FROM generates invalid YQL. This also likely affects INSERT INTO ... SELECT. YDB supports this syntax natively (UPSERT docs, INSERT docs), but the generated query fails because it incorrectly includes the target table in the FROM clause.

Example of the faulty generated query:

UPSERT INTO my_table (col1, col2) 
SELECT my_table.col1, my_table.col2
FROM as_table(%(my_input)s), my_table

The extra my_table in the FROM clause and columns in brackets causes the query to fail. Could you please look into fixing the query generation logic?
Thanks for your help!

[vgvoleg]

Hi! Can you please attach sqlalchemy code you are trying to use?

[makhalin]

Sure. Not a working example, but the whole picture is visible.

import sqlalchemy as sa
from ydb_sqlalchemy.sqlalchemy.types import ListType
from ydb_sqlalchemy.sqlalchemy.types import StructType
from ydb_sqlalchemy.sqlalchemy import upsert
...
values_dicts = [{"id": 1, "name": "a"}]
type_ = ListType(StructType({column.name: column.type.__class__ for column in TABLE.columns}))

upsert_statement = upsert(TABLE).from_select(
    names=[name for name in TABLE.columns.keys()],
    select=sa.select(TABLE).select_from(sa.func.as_table(sa.bindparam("my_input", values_dicts, type_)))
)
await self._connection.execute(upsert_statement)

...

[makhalin]

Do you have any updates or plans?