bug: Oracle Table alias

[Baazion]

What happened?

When Selecting on Oracle query gets parsed with AS for Table-Alias, which is not supported in Oracle

What version of ibis are you using?

9.0.0

What backend(s) are you using, if any?

Oracle

Relevant log output

services = conn.table("SOME_TABLE").select("sst_nr")
expr = services.count()
print(ibis.to_sql(expr))

SELECT
  COUNT(*) AS "CountStar()"
FROM (
  SELECT
    "t0"."sst_nr"
  FROM "SOME_TABLE" AS "t0"
) AS "t1"

oracledb.exceptions.DatabaseError: ORA-00923: FROM keyword not found where expected

Works when manually executing in Oracle:
SELECT
  COUNT(*) AS "CountStar()"
FROM (
  SELECT
    "t0"."SST_NR"
  FROM some_table "t0"
) "t1"

Code of Conduct

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

[gforsyth]

Hi @Baazion -- thanks for reporting this! Can you tell us which version of Oracle you are running against?

We only have a Docker image running 23.4 and table aliasing works as expected there -- I'm not sure which version this was added in?

[Baazion]

SELECT * FROM v$version;

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

[gforsyth]

Ok, well, 19c is a LTS release and they promise support for another 2 years, so we can try to remove keyword that's causing the syntax error. Thanks, @Baazion !

[cpcloud]

This may need to be reported upstream to SQLGlot, and hopefully in the meantime there's a SQLGlot compiler flag we can use to set the aliasing style.

[gforsyth]

Hmm, looks like sqlglot does the right thing -- I wonder where we're adding the AS:

[nav] In [3]: expr = sg.parse_one(
         ...:     '''SELECT
         ...:   COUNT(*) AS "CountStar()"
         ...: FROM (
         ...:   SELECT
         ...:     "t0"."sst_nr"
         ...:   FROM "SOME_TABLE" AS "t0"
         ...: ) AS "t1"''',
         ...:     read="oracle",
         ...: )

[ins] In [4]: expr.sql(dialect="oracle")
Out[4]: 'SELECT COUNT(*) AS "CountStar()" FROM (SELECT "t0"."sst_nr" FROM "SOME_TABLE" "t0") "t1"'

[cpcloud]

Seems like we're potentially misusing alias or alias has a bug:

In [21]: print(sg.alias(expr, 'foo').sql('oracle'))
SELECT COUNT(*) AS "CountStar()" FROM (SELECT "t0"."sst_nr" FROM "SOME_TABLE" "t0") "t1" AS foo

In [22]: print(sg.alias(expr.subquery(), 'foo').sql('oracle'))
(SELECT COUNT(*) AS "CountStar()" FROM (SELECT "t0"."sst_nr" FROM "SOME_TABLE" "t0") "t1") foo

In [23]: print(expr.subquery('foo').sql('oracle'))
(SELECT COUNT(*) AS "CountStar()" FROM (SELECT "t0"."sst_nr" FROM "SOME_TABLE" "t0") "t1") foo

[tobymao]

check out the table arg in alias_

[gforsyth]

Hey @Baazion -- I've been digging into this -- can you clarify how you are running the Oracle query?

Are you generating the SQL and then executing it manually? Or are you hitting this error when using to_pandas() (or similar)?

[Baazion]

executed code (slightly sanitised):

import ibis


def quack_me():
    conn = ibis.duckdb.connect()
    conn.read_parquet("services_parquet/services_*.parquet", table_name="SOME_TABLE")
    return conn


def get_oracle_conn_oracle():
    conn = ibis.oracle.connect(
        user="REPLACED",
        password="REPLACED",
        host="REPLACED",
        port=1526,
        database="REPLACED"
    )
    return conn


def count(conn):
    services = conn.table("SOME_TABLE").select("REPLACED")
    expr = services.count()
    print(expr.execute())


def main():
    print("Ibis Version:", ibis.__version__)
    duckdb_conn = quack_me()
    count(duckdb_conn)
    oracle_conn = get_oracle_conn_oracle()
    count(oracle_conn)


if __name__ == '__main__':
    main()

output:

Ibis Version: 9.0.0
178452926
Traceback (most recent call last):
  File "C:\Users\a05852\PycharmProjects\dbt_duckdb\ibis_try.py", line 55, in <module>
    main()
  File "C:\Users\a05852\PycharmProjects\dbt_duckdb\ibis_try.py", line 51, in main
    count(oracle_conn)
  File "C:\Users\a05852\PycharmProjects\dbt_duckdb\ibis_try.py", line 41, in count
    services = conn.table("SERVICES").select("sst_nr")
  File "C:\Users\a05852\.virtualenvs\dbt_duckdb-Xt-JRUJb\lib\site-packages\ibis\backends\sql\__init__.py", line 137, in table
    table_schema = self.get_schema(name, catalog=catalog, database=database)
  File "C:\Users\a05852\.virtualenvs\dbt_duckdb-Xt-JRUJb\lib\site-packages\ibis\backends\oracle\__init__.py", line 315, in get_schema
    with self._safe_raw_sql(stmt) as cur:
  File "C:\Users\a05852\AppData\Local\Programs\Python\Python39\lib\contextlib.py", line 119, in __enter__
    return next(self.gen)
  File "C:\Users\a05852\.virtualenvs\dbt_duckdb-Xt-JRUJb\lib\site-packages\ibis\backends\oracle\__init__.py", line 185, in _safe_raw_sql
    with contextlib.closing(self.raw_sql(*args, **kwargs)) as result:
  File "C:\Users\a05852\.virtualenvs\dbt_duckdb-Xt-JRUJb\lib\site-packages\ibis\backends\oracle\__init__.py", line 196, in raw_sql
    cursor.execute(query, **kwargs)
  File "C:\Users\a05852\.virtualenvs\dbt_duckdb-Xt-JRUJb\lib\site-packages\oracledb\cursor.py", line 701, in execute
    impl.execute(self)
  File "src\\oracledb\\impl/thin/cursor.pyx", line 178, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src\\oracledb\\impl/thin/protocol.pyx", line 438, in oracledb.thin_impl.Protocol._process_single_message
  File "src\\oracledb\\impl/thin/protocol.pyx", line 439, in oracledb.thin_impl.Protocol._process_single_message
  File "src\\oracledb\\impl/thin/protocol.pyx", line 432, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-00923: FROM keyword not found where expected
Help: https://docs.oracle.com/error-help/db/ora-00923/

Process finished with exit code 1

Executing with DuckDB-Conn returns a count.
Oracle produces the error message.

[gforsyth]

Thanks @Baazion -- I'm still having trouble reproducing this locally.

Can you try upgrading sqlglot? I don't think that's the issue, but it would be good to rule out.

Then, if that doesn't do it and you're game, you can try installing an ibis prerelease, which has a few fixes which may have helped with this, by running

python -m pip install -U --pre 'ibis-framework[oracle]'

[Baazion]

Ran this with:
Ibis Version: 10.0.0.dev71
SqlGlot Version: 24.0.2

but got the same Error