ODBC Driver

This section describes which settings the ODBC driver modifies and why.

**[cast\_keep\_nullable](https://clickhouse.com/docs/operations/settings/settings#cast_keep_nullable)**

By default, ClickHouse doesn't allow converting nullable types to non-nullable types. However, many BI tools don't
distinguish between nullable and non-nullable types when performing type conversions. As a result, it's not uncommon to
see queries like the following generated by BI tools:

```
SELECT sum(CAST(value, 'Int32'))
FROM values
```

By default, when the `value` column is nullable, this query will fail with the message:

```
DB::Exception: Cannot convert NULL value to non-Nullable type: while executing 'FUNCTION CAST(__table1.value :: 2,
'Int32'_String :: 1) -> CAST(__table1.value, 'Int32'_String) Int32 : 0'. (CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN)
```

Enabling `cast_keep_nullable` changes the behavior of `CAST` so it preserves the nullability of its arguments. This
makes ClickHouse's behavior closer to other databases and the SQL standard for this kind of conversion.

**[prefer\_column\_name\_to\_alias](https://clickhouse.com/docs/operations/settings/settings#prefer_column_name_to_alias)**

ClickHouse allows referencing expressions in the same `SELECT` list by their aliases. For example, this query avoids
repetition and is easier to write:

```
SELECT
    sum(value) AS S,
    count() AS C,
    S / C
FROM test
```

This feature is widely used, but other databases typically don't resolve aliases this way in the same `SELECT` list,
and such queries would error. Problems are most visible when an alias has the same name as a column. For example:

```
SELECT
    sum(value) AS value,
    avg(value)
FROM test
```

Which `value` should `avg(value)` aggregate? By default, ClickHouse prefers the alias, effectively turning this into a
nested aggregate, which isn't what most tools expect.

On its own this is rarely an issue, but some BI tools generate queries with subqueries that reuse column aliases. For
example, Power BI often generates queries similar to the following:

```
SELECT
    sum(C1) AS C1,
    count(C1) AS C2
FROM
(
    SELECT sum(value) AS C1
    FROM test
    GROUP BY group_index
) AS TBL
```

References to `C1` can produce the following error:

```
Code: 184. DB::Exception: Received from localhost:9000. DB::Exception: Aggregate function sum(C1) AS C1 is found
inside another aggregate function in query. (ILLEGAL_AGGREGATION)
```

Other databases typically don't resolve aliases at the same level this way and instead treat `C1` as a column from the
subquery. To preserve similar behavior in ClickHouse and allow such queries to run without errors, the ODBC driver
enables `prefer_column_name_to_alias`.

In most cases, enabling these settings shouldn't be a problem. However, users with the readonly setting set to `1`
can't change any settings, even for `SELECT` queries. For such users, enabling `SqlCompatibilitySettings` will result
in an error. The following section explains how to make this configuration parameter work for read-only users.

## Making SQL compatibility settings work for read-only users[​](#readonly-users "Direct link to Making SQL compatibility settings work for read-only users")

When connecting to ClickHouse through the ODBC driver with the `SqlCompatibilitySettings` parameter enabled, a user with
the readonly setting set to `1` will encounter an error because the driver attempts to modify query settings:

```
Code: 164. DB::Exception: Cannot modify 'cast_keep_nullable' setting in readonly mode. (READONLY)
Code: 164. DB::Exception: Cannot modify 'prefer_column_name_to_alias' setting in readonly mode. (READONLY)
```

This happens because users in read-only mode aren't allowed to change settings, even for individual `SELECT` queries.
There are several ways to fix this.

**Option 1. Setting `readonly` to `2`**

This is the simplest option. Setting `readonly` to `2` allows changing settings while keeping the user in read-only
mode.

```
ALTER USER your_odbc_user MODIFY SETTING
    readonly = 2
```

In most cases, setting `readonly` to 2 is the easiest and recommended way to solve this problem. If
that doesn't work for you, use the second option.

**Option 2. Changing user settings to match the settings set by the ODBC driver.**

This is also straightforward: update the user settings so they already match what the ODBC driver tries to set.

```
ALTER USER your_odbc_user MODIFY SETTING
    cast_keep_nullable = 1,
    prefer_column_name_to_alias = 1
```

With this change, the ODBC driver can still attempt to apply the settings, but because the values already match, no
effective change is made and the error is avoided.

This option is also simple, but it requires maintenance: newer driver versions may change the list of settings or add
new ones for compatibility. If you hard-code these settings on your ODBC user, you may need to update them whenever the
ODBC driver starts applying additional settings.

- [Installation on Windows](#installation-on-windows)- [Testing](#testing)- [Configuration parameters](#configuration-parameters)- [Microsoft Power BI Integration](#powerbi-integration)- [SQL compatibility settings](#sql-compatibility-settings)
          - [ClickHouse settings enabled by SqlCompatibilitySettings configuration parameter](#sql-compatibility-settings-list)- [Making SQL compatibility settings work for read-only users](#readonly-users)

Was this page helpful?