GLOB operator is not supported for duckdb and sqlite

[cldellow]

Hi, sqlglot is amazing! Thank you for this library.

I noticed that this fails:

import sqlglot

print(sqlglot.transpile("SELECT 'foo' GLOB '?'", read="duckdb", write="sqlite")[0])

I expected it to print SELECT 'foo' GLOB '?', but it gave an exception:

Traceback (most recent call last):
  File "main.py", line 3, in <module>
    print(sqlglot.transpile("SELECT 'foo' GLOB '?'", read="duckdb", write="sqlite")[0])
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlglot/__init__.py", line 124, in transpile
    for expression in parse(sql, read, error_level=error_level)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlglot/__init__.py", line 60, in parse
    return dialect.parse(sql, **opts)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlglot/dialects/dialect.py", line 151, in parse
    return self.parser(**opts).parse(self.tokenizer.tokenize(sql), sql)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlglot/parser.py", line 684, in parse
    return self._parse(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlglot/parser.py", line 749, in _parse
    self.raise_error("Invalid expression / Unexpected token")
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlglot/parser.py", line 792, in raise_error
    raise error
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 1, Col: 21.
  SELECT 'foo' GLOB '?'

Searching the code base, I think this operator isn't supported for any dialect yet. I'm not sure which flavours of SQL support it - duckdb (docs) and sqlite (docs) do.

I imagine this might be an easy fix, but I don't know where to start

[georgesittas]

Hey!

First of all, since this operator is supported by at least two dialects, e.g. duckdb and sqlite, I'd say it's worth to add in the base Parser, Generator classes. We generally try to make these handle as much SQL as possible, especially when it helps reuse logic across different dialects.

Regarding how you can tackle this issue: look up the Binary class in expressions.py. This represents a binary operator node in the AST and the corresponding SQL generation method defaults to infix style. Binary operators usually inherit from this class. It should be pretty easy to add support for GLOB once you get the tokenizer to recognize it as a token.

Let me know if you need any help in case you wanna tackle this. Otherwise, I can post a fix tomorrow and ping you.

[cldellow]

Wow, thanks for the fast response and helpful pointers. I took at a stab at adding it. There was much monkey see, monkey do, so please feel free to discard/adapt if that's faster, and I'll just watch from the sidelines to learn for next time.