bug: NULL string returned instead of actual null

[rad-pat]

Search before asking

• [X] I had searched in the issues and found no similar issues.

Version

v1.2.634

What's Wrong?

Since https://github.com/datafuselabs/databend/pull/16120, nulls are coming back through SQLAlchemy queries as NULL string instead of actual nulls. The setting format_null_as_str is defaulted to on which seems incorrect. I just wanted to check this was the intention and I have to change the setting on the database, or for each session I connect through SQLALchemy/BendSQL?

How to Reproduce?

No response

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

[dosubot[bot]]

Yes, it is intentional that nulls are returned as the string "NULL" instead of actual nulls in SQLAlchemy queries due to the format_null_as_str setting being defaulted to on in Databend v1.2.634. This behavior is controlled by the format_null_as_str setting in the FormatSettings struct.

You can change this setting either on the database or for each session you connect through SQLAlchemy/BendSQL. To change it for each session, you can modify the session settings in your SQLAlchemy/BendSQL connection configuration.

Here is an example of how to change the setting for each session:

from sqlalchemy import create_engine

# Example connection string, adjust as needed
engine = create_engine('benddb://user:password@host:port/database')

# Set the session setting for format_null_as_str
with engine.connect() as connection:
    connection.execute("SET format_null_as_str = false")

Alternatively, you can change the setting at the database level if you have the necessary permissions ^[1][2].

To continue talking to Dosu, mention @dosu.

[rad-pat]

OK, we will set it to 0 on our instances, seems the wrong default to me.