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

[balintpto]

Hello, i'm seeing the same issue, and I'm also using oracle 19.

I get an error when running:

con = ib.oracle.connect(

            user=Cfg.db_user, password=Cfg.db_password, host=Cfg.db_host, port=Cfg.db_port, service_name=Cfg.db_service_name

        )

t = con.table(name="TABLE", database="DATABASE")

Full stack trace:

  File "C:*removed*\src\*removed*.py", line 43, in *removed*

    t = con.table(name="TABLE", database="DATABASE")

  File "c:*removed*\.venv\Lib\site-packages\ibis\backends\sql\__init__.py", line 137, in table

    table_schema = self.get_schema(name, catalog=catalog, database=database)

  File "c:*removed*\.venv\Lib\site-packages\ibis\backends\oracle\__init__.py", line 315, in get_schema

    with self._safe_raw_sql(stmt) as cur:

  File "C:*removed*\AppData\Local\Programs\Python\Python311\Lib\contextlib.py", line 137, in __enter__

    return next(self.gen)

  File "c:*removed*\.venv\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:*removed*\.venv\Lib\site-packages\ibis\backends\oracle\__init__.py", line 196, in raw_sql

    cursor.execute(query, **kwargs)

The query:

"SELECT column_name, data_type, data_precision, data_scale, nullable = 'Y' AS nullable

FROM all_tab_columns

WHERE table_name = 'TABLE' AND owner = 'DATABASE'

ORDER BY column_id"

Is there any temporary workaround available?

[gforsyth]

Thanks for the heads up @balintpto -- I have Oracle 18 in a docker image now and I can reproduce some of the failures, hopefully we can get some fixes in soon. I don't think there is an available workaround at the moment.

[gforsyth]

Just following up here -- I believe all of the issues with Oracle < 23c stem from the fact that we (and sqlglot) assume that there are boolean datatypes available. There are definitely NOT boolean datatypes available before version 23. I'm trying to scope out how we can rewrite all of our booleans into case statements.

[cpcloud]

I don't think the FROM x AS y failing has anything to do with boolean support. That looks like a separate syntax that was added (in addition to having supported FROM x y for a long time) in some recent version of Oracle.

[cpcloud]

Ok, I just read through the reports here in more detail and this issue is mixing up two things that are unrelated to each other.

There are two issues, one of which isn't reproducible.

1. FROM x AS y, we don't appear to be generating code for the Oracle backend like that. Additionally, copy-pasting backend-specific SQL from one backend to another is generally not going to work.
2. Generating expressions that would evaluate to a boolean. We definitely do this, but only very recent versions of Oracle support a boolean type. I'll open a separate issue for that with a reproducer.

Closing this issue as wontfix due to the lack of reproducibility.

[cpcloud]

xref: #9378

[gforsyth]

I have a branch fixing some of this. Certainly @balintpto 's issue is from our use of nullable = 'Y' AS nullable, which needs to be rewritten as a CASE statement