Why is the NOT Operator moved in front of the expression?

[mpf82]

Hello,

I have a question about the decision to put NOT before the expression, instead of putting it before the operator, see example below:

sql4 = "SELECT * FROM tbl WHERE a IS NOT NULL AND b NOT IN (5,6)"
p = sqlglot.parse_one(sql4)
print( p.sql() )

produces:
SELECT * FROM tbl WHERE NOT a IS NULL AND NOT b IN (5, 6)

While this does not change the result of the query, it looks strange, at least to me.

As far as I understand, "IS NOT" and "NOT IN" are operators in SQL, just like "not in" is an operator in Python.

Changing "col IS NOT NULL" to "NOT col IS NULL" would technically mean, that instead of using the "IS NOT" operator, the result of the "IS" operator is negated.

But even in the cases where "NOT IN" or therelike is not a stand-alone operator, the syntax is usually descibed as
"expression [NOT] <operator>" (and not as "NOT expression <operator>").

References:
https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver16
https://sqlite.org/lang_expr.html

So my question, is there any reason, why the SQL currently transpiles like this, with the NOT put before the expression, instead of putting NOT between the expression and the operator?

If you want to keep it like this, can we get an option to change the behavior?

[tobymao]

Yea so not x is null is equivalent to x is not null. From an AST perspective it’s easier if there’s only one representation… not (is x) vs is (not x) because is, like, and in are special cases compared to not (1 > 2).

The parser does support both variants x is not null and not x is null, but it converts them both into the "canonical" format.

So when working with the ast you only ever have to worry about the first case for all sql expressions as opposed to the special “reader friendly” version.

You can override this functionality by using a custom Generator. Override the not method to handle is, like, and in specifically or pass it into transforms.

[mpf82]

Thanks for your suggestion.

This is what I came up with:

class Custom(Dialect):
    class Generator(Generator):
        def neq_sql(self, expression):
            return self.binary(expression, "!=")

        def not_sql(self, expression):
            if isinstance(expression.this, (exp.Is, exp.In, exp.Like)):
                expression.this.apply_not_ = True
                return self.sql(expression, 'this')
            return super().not_sql(expression)

        def like_sql(self, expression):
            return self.binary(expression, "NOT LIKE" if hasattr(expression, 'apply_not_') else "LIKE")

        def is_sql(self, expression):
            return self.binary(expression, "IS NOT" if hasattr(expression, 'apply_not_') else "IS")

        def in_sql(self, expression):
            query = expression.args.get("query")
            in_sql = (
                self.wrap(query)
                if query
                else f"({self.expressions(expression, flat=True)})"
            )
            op = "NOT IN" if hasattr(expression, 'apply_not_') else "IN"
            return f"{self.sql(expression, 'this')} {op} {in_sql}"

sql = "SELECT * FROM tbl WHERE a IS NOT NULL AND b NOT IN (5,6) AND x NOT LIKE '%x' AND NOT color != 'black ' and empty NOT IN ('    ', '')"
tokens = sqlglot.parse_one(sql)
print(tokens.sql(dialect=''))
print(tokens.sql(dialect='custom'))

Output:

SELECT * FROM tbl WHERE NOT a IS NULL AND NOT b IN (5, 6) AND NOT x LIKE '%x' AND NOT color <> 'black ' AND NOT empty IN ('    ', '')
SELECT * FROM tbl WHERE a IS NOT NULL AND b NOT IN (5, 6) AND x NOT LIKE '%x' AND NOT color != 'black ' AND empty NOT IN ('    ', '')

I'm not happy about the custom attribute, but I could not get it to work the other ways I've tried. If you've got a better idea feel free to share.

Cheers =)