ERROR When Using MSSQL XML Functions thru Connection.sql

[jjAtNxtPt]

What happened?

When trying to query an XML field and parse out specific tag values Ibis is changing the XML functions to all uppercase which causes the SQL Server to return error.

Example Error: SQL Error [317] [S0001]: Table-valued function 'NODES' cannot have a column alias.
The above error is returned because Ibis changes the "nodes" in the cross apply to "NODES". The same think happens to the "value" xml function, changes to "VALUE" which causes an error.

Below are example SQL queries supplied to Ibis and what Ibis actually executes based on the Ibis.to_sql() function.

SUPPLIED->

SELECT
TOP 10
  s.RECORDID, /* , rd.FormContent */
  n.c.value('(/*:FORM_ROOT/*:SOME_TAG)[1]', 'float') AS SOME_TAG_VALUE
FROM source_table.dbo.source_data AS s(nolock) 
    CROSS APPLY FormContent.nodes('/*:FORM_ROOT') AS N(C)

EXECUTED->

SELECT
TOP 10
  rd.RECORDID, /* , rd.FormContent */
  n.c.VALUE('(/*:FORM_ROOT/*:SOME_TAG)[1]', 'float') AS SOME_TAG_VALUE
FROM source_table.dbo.source_data AS s(nolock) CROSS APPLY FormContent.NODES('/*:FORM_ROOT') AS N(C)

What version of ibis are you using?

9.1.0

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

MSSQL

Relevant log output

File C:\Python\Python312\Lib\site-packages\ibis\expr\types\core.py:80, in Expr.__repr__(self)
     78 def __repr__(self) -> str:
     79     if ibis.options.interactive:
---> 80         return self._interactive_repr()
     81     else:
     82         return self._noninteractive_repr()

File C:\Python\Python312\Lib\site-packages\ibis\expr\types\core.py:67, in Expr._interactive_repr(self)
     65 with console.capture() as capture:
     66     try:
---> 67         console.print(self)
     68     except TranslationError as e:
     69         lines = [
     70             "Translation to backend failed",
     71             f"Error message: {e!r}",
     72             "Expression repr follows:",
     73             self._noninteractive_repr(),
     74         ]

File C:\Python\Python312\Lib\site-packages\rich\console.py:1700, in Console.print(self, sep, end, style, justify, overflow, no_wrap, emoji, markup, highlight, width, height, crop, soft_wrap, new_line_start, *objects)
   1698 if style is None:
   1699     for renderable in renderables:
-> 1700         extend(render(renderable, render_options))
   1701 else:
   1702     for renderable in renderables:

File C:\Python\Python312\Lib\site-packages\rich\console.py:1312, in Console.render(self, renderable, options)
   1310 renderable = rich_cast(renderable)
   1311 if hasattr(renderable, "__rich_console__") and not isclass(renderable):
-> 1312     render_iterable = renderable.__rich_console__(self, _options)  # type: ignore[union-attr]
   1313 elif isinstance(renderable, str):
   1314     text_renderable = self.render_str(
   1315         renderable, highlight=_options.highlight, markup=_options.markup
   1316     )

File C:\Python\Python312\Lib\site-packages\ibis\expr\types\core.py:118, in Expr.__rich_console__(self, console, options)
    102 except Exception as e:
    103     # In IPython exceptions inside of _repr_mimebundle_ are swallowed to
    104     # allow calling several display functions and choosing to display
   (...)
    115     #
    116     # This restriction is only present in IPython, not in other REPLs.
    117     console.print_exception()
--> 118     raise e
    119 return console.render(rich_object, options=options)

File C:\Python\Python312\Lib\site-packages\ibis\expr\types\core.py:99, in Expr.__rich_console__(self, console, options)
     97 try:
     98     if opts.interactive:
---> 99         rich_object = to_rich(self, console_width=console_width)
    100     else:
    101         rich_object = Text(self._noninteractive_repr())

File C:\Python\Python312\Lib\site-packages\ibis\expr\types\pretty.py:269, in to_rich(expr, max_rows, max_columns, max_length, max_string, max_depth, console_width)
    266 from ibis.expr.types import Scalar
    268 if isinstance(expr, Scalar):
--> 269     return _to_rich_scalar(
    270         expr, max_length=max_length, max_string=max_string, max_depth=max_depth
    271     )
    272 else:
    273     return _to_rich_table(
    274         expr,
    275         max_rows=max_rows,
   (...)
    280         console_width=console_width,
    281     )

File C:\Python\Python312\Lib\site-packages\ibis\expr\types\pretty.py:292, in _to_rich_scalar(expr, max_length, max_string, max_depth)
    284 def _to_rich_scalar(
    285     expr: Scalar,
    286     *,
   (...)
    289     max_depth: int | None = None,
    290 ) -> Pretty:
    291     scalar = Pretty(
--> 292         expr.execute(),
    293         max_length=max_length or ibis.options.repr.interactive.max_length,
    294         max_string=max_string or ibis.options.repr.interactive.max_string,
    295         max_depth=max_depth or ibis.options.repr.interactive.max_depth,
    296     )
    297     return Panel(scalar, expand=False, box=box.SQUARE)

File C:\Python\Python312\Lib\site-packages\ibis\expr\types\core.py:393, in Expr.execute(self, limit, params, **kwargs)
    375 def execute(
    376     self,
    377     limit: int | str | None = "default",
    378     params: Mapping[ir.Value, Any] | None = None,
    379     **kwargs: Any,
    380 ):
    381     """Execute an expression against its backend if one exists.
    382 
    383     Parameters
   (...)
    391         Keyword arguments
    392     """
--> 393     return self._find_backend(use_default=True).execute(
    394         self, limit=limit, params=params, **kwargs
    395     )

File C:\Python\Python312\Lib\site-packages\ibis\backends\sql\__init__.py:301, in SQLBackend.execute(self, expr, params, limit, **kwargs)
    297 schema = table.schema()
    299 # TODO(kszucs): these methods should be abstractmethods or this default
    300 # implementation should be removed
--> 301 with self._safe_raw_sql(sql) as cur:
    302     result = self._fetch_from_cursor(cur, schema)
    303 return expr.__pandas_result__(result)

File C:\Python\Python312\Lib\contextlib.py:137, in _GeneratorContextManager.__enter__(self)
    135 del self.args, self.kwds, self.func
    136 try:
--> 137     return next(self.gen)
    138 except StopIteration:
    139     raise RuntimeError("generator didn't yield") from None

File C:\Python\Python312\Lib\site-packages\ibis\backends\mssql\__init__.py:269, in Backend._safe_raw_sql(self, query, *args, **kwargs)
    266     query = query.sql(self.dialect)
    268 with self.begin() as cur:
--> 269     cur.execute(query, *args, **kwargs)
    270     yield cur

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Table-valued function 'NODES' cannot have a column alias. (317) (SQLExecDirectW)")

Code of Conduct

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

[cpcloud]

This is an issue with sqlglot, it's not an issue with Ibis. I've opened tobymao/sqlglot#3967 upstream.

In [4]: sql = """
   ...: SELECT
   ...: TOP 10
   ...:   s.RECORDID, /* , rd.FormContent */
   ...:   n.c.value('(/*:FORM_ROOT/*:SOME_TAG)[1]', 'float') AS SOME_TAG_VALUE
   ...: FROM source_table.dbo.source_data AS s(nolock)
   ...:     CROSS APPLY FormContent.nodes('/*:FORM_ROOT') AS N(C)
   ...: """

In [5]: import sqlglot.expressions as sge, sqlglot as sg

In [6]: print(sg.parse_one(sql, read="tsql").sql('tsql', pretty=True))
SELECT
TOP 10
  s.RECORDID, /* , rd.FormContent */
  n.c.VALUE('(/*:FORM_ROOT/*:SOME_TAG)[1]', 'float') AS SOME_TAG_VALUE
FROM source_table.dbo.source_data AS s(nolock) CROSS APPLY FormContent.NODES('/*:FORM_ROOT') AS N(C)

[jjAtNxtPt]

I admit to not knowing all the ins and outs of this kind of situation so I'm going to ask: Do I need to do anything to update my version of Ibis or sqlglot in order to get this corrected?

[gforsyth]

Hiya @jjAtNxtPt ! It looks like the fix for this issue was released in sqlglot version 25.18, which is supported by Ibis 9.4 So if you upgrade ibis-framework to 9.4 and ensure that sqlglot is upgraded to version 25.18, you should be good to go.