Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

bug: examples broken on mssql backend #9095

Open
1 task done
chloeh13q opened this issue May 1, 2024 · 6 comments
Open
1 task done

bug: examples broken on mssql backend #9095

chloeh13q opened this issue May 1, 2024 · 6 comments
Labels
bug Incorrect behavior inside of ibis

Comments

@chloeh13q
Copy link
Contributor

What happened?

Examples are broken on mssql backend.

Python 3.11.8 | packaged by conda-forge | (main, Feb 16 2024, 20:49:36) [Clang 16.0.6 ] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import ibis
>>> con = ibis.mssql.connect(user="sa", host="localhost", password="1bis_Testing!", driver="ODBC Driver 18 for SQL Server", TrustServerCertificate="yes", database="ibis_testing")
>>> ibis.set_backend(con)
>>> ibis.examples.CO2.fetch()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/chloe/Desktop/ibis/ibis/examples/__init__.py", line 85, in fetch
    return backend.create_table(table_name, obj, temp=True, overwrite=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/chloe/Desktop/ibis/ibis/backends/mssql/__init__.py", line 503, in create_table
    with self._safe_raw_sql(create_stmt) as cur:
  File "/opt/anaconda3/envs/ibis-dev-arm64/lib/python3.11/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/Users/chloe/Desktop/ibis/ibis/backends/mssql/__init__.py", line 269, in _safe_raw_sql
    cur.execute(query, *args, **kwargs)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near 'ibis_mssql_table_4ksb7p5z7vfqffu77cve3a3kbq'. (102) (SQLExecDirectW)")

Seems to be broken on all the examples.

What version of ibis are you using?

main

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

mssql

Relevant log output

No response

Code of Conduct

  • I agree to follow this project's Code of Conduct
@gforsyth
Copy link
Member

gforsyth commented May 1, 2024

Hmm, yeah, something funky is definitely happening here. The create table SQL we're generating (this is for the starwars example): is

'CREATE TABLE #[ibis_mssql_table_sf22odm3xvcjxhkiuk5pk6o4py] ([name] VARCHAR(max), [height] BIGINT, [mass] FLOAT, [hair_color] VARCHAR(max), [skin_color] VARCHAR(max), [eye_color] VARCHAR(max), [birth_year] FLOAT, [sex] VARCHAR(max), [gender] VARCHAR(max), [homeworld] VARCHAR(max), [species] VARCHAR(max), [films] VARCHAR(max), [vehicles] VARCHAR(max), [starships] VARCHAR(max))'

Definitely don't need or want the #[] around the table name

@gforsyth
Copy link
Member

gforsyth commented May 1, 2024

hmm, wait, I guess that's the mssql syntax for temporary tables? digging in a bit more

@gforsyth
Copy link
Member

gforsyth commented May 1, 2024

I think this is a sqlglot bug, although we may be constructing the expression incorrectly.

[nav] In [14]: query = """CREATE TABLE #crunch_no_brackets ([name] VARCHAR(max))"""

[ins] In [15]: with con.raw_sql(query) as c:
          ...:     pass

[ins] In [16]: query = """CREATE TABLE [brackets_no_crunch] ([name] VARCHAR(max))"""

[ins] In [17]: with con.raw_sql(query) as c:
          ...:     pass

[ins] In [18]: query = """CREATE TABLE #[brackets_with_crunch] ([name] VARCHAR(max))"""

[ins] In [19]: with con.raw_sql(query) as c:
          ...:     pass
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
Cell In[19], line 1
----> 1 with con.raw_sql(query) as c:
      2     pass

File ~/github.com/ibis-project/ibis/ibis/backends/mssql/__init__.py:279, in Backend.raw_sql(self, query, **kwargs)
    276 cursor = con.cursor()
    278 try:
--> 279     cursor.execute(query, **kwargs)
    280 except Exception:
    281     con.rollback()

ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near 'brackets_with_crunch'. (102) (SQLExecDirectW)")

@gforsyth
Copy link
Member

gforsyth commented May 1, 2024

I think we're doing something wrong:

[ins] In [25]: exp = sg.parse_one("CREATE TEMPORARY TABLE temptest (name VARCHAR);",
          ...: dialect="duckdb")

[ins] In [26]: exp
Out[26]: 
Create(
  this=Schema(
    this=Table(
      this=Identifier(this=temptest, quoted=False)),
    expressions=[
      ColumnDef(
        this=Identifier(this=name, quoted=False),
        kind=DataType(this=Type.VARCHAR, nested=False))]),
  kind=TABLE,
  properties=Properties(
    expressions=[
      TemporaryProperty()]))

[ins] In [27]: exp.sql(dialect="mssql")
Out[27]: 'CREATE TABLE #temptest (name VARCHAR)'

@gforsyth
Copy link
Member

gforsyth commented May 1, 2024

Ok, I think that there are some edge-cases that we are definitely not handling correctly.

That said, there is also a sqlglot bug:

[ins] In [1]: import sqlglot as sg

[ins] In [2]: exp = sg.parse_one(
         ...:     "CREATE TEMPORARY TABLE 'temptest' (name VARCHAR);", dialect="duckd
         ...: b"
         ...: )

[ins] In [3]: exp
Out[3]: 
Create(
  this=Schema(
    this=Table(
      this=Identifier(this=temptest, quoted=True)),
    expressions=[
      ColumnDef(
        this=Identifier(this=name, quoted=False),
        kind=DataType(this=Type.VARCHAR, nested=False))]),
  kind=TABLE,
  properties=Properties(
    expressions=[
      TemporaryProperty()]))

[ins] In [4]: exp.sql(dialect="tsql")
Out[4]: 'CREATE TABLE #[temptest] (name VARCHAR)'

That last should be CREATE TABLE [#temptest] (name VARCHAR)

@georgesittas
Copy link

FYI the temp table fix has been deployed with 23.13.0.

gforsyth pushed a commit that referenced this issue May 6, 2024
… dataframe (#9094)

## Description of changes

Examples were broken on MySQL backend and Postgres backend when there are
null values in a numeric column.

Druid, PySpark, RW don't support examples.

- Exasol - did not test
- Flink - broken
- Impala - did not test
- MSSQL - broken #9095 
- MySQL - fixed
- Oracle - did not test
- PostgreSQL - fixed
- Snowflake - did not test

## Issues closed

#8792

---------

Co-authored-by: Chloe He <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Incorrect behavior inside of ibis
Projects
Status: backlog
Development

No branches or pull requests

3 participants