SQLTableCheckOperator issue with Oracle table alias

[igvog]

Apache Airflow version

2.10.0

If "Other Airflow 2 version" selected, which one?

2

What happened?

SQLTableCheckOperator operator generate SQL with table "AS" alias and table alias with "AS" not working in Oracle database.
Needs to fix sql_check_template depends on connection type remove "AS".

What you think should happen instead?

No response

How to reproduce

from airflow.providers.common.sql.operators.sql import SQLTableCheckOperator

SQLTableCheckOperator(
        task_id="your_task",
        table="yout_table",
        checks={
            "row_count_check": {"check_statement": "COUNT(*) = 1000"}        },
        partition_clause="BALANCE_DATE = to_date('2024-11-11','yyyy-mm-dd')",
        conn_id=connection_should_be_oracle_db
    )

Operating System

ubuntu

Versions of Apache Airflow Providers

apache-airflow-providers-common-sql==1.7.2

Deployment

Official Apache Airflow Helm Chart

Deployment details

No response

Anything else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[tolebiermekov]

I came across a similar issue...

[tolebiermekov]

In Oracle SQL, the AS keyword is not allowed when assigning an alias to a subquery. While AS can be used for column aliases or table aliases in some contexts, Oracle does not permit its use when aliasing subqueries.

[dabla]

Interesting case, now that we have the dialects, we could add a function in the Dialect that would be used by the SQLTableCheckOperator, that way, we could override it when a specific implementation is needed for a database. In this case that would also mean that we would have to introduce an OracleDialect, but that was the whole purpose of it.