Report native ClickHouse data types and pupulate missing type information by slabko · Pull Request #478 · ClickHouse/clickhouse-odbc

slabko · 2025-03-07T13:57:18Z

Fixes Int64 columns are loaded as binary columns in PowerBI #445

MS Power BI and other applications were not able to recognize the types of uncommon SQL-world data types, such as UInt8 or Int64. It was unable to parse the data types from SQLColumns because the dataset returned by this function did not comply with the requirements. This is fixed by changing the SQLColumns dataset.

Additionally, SQLGetTypeInfo returned SQL types such as INT, SMALLINT, BIGINT, etc. Each of these types had to have two entries in the SQLGetTypeInfo result: one for signed and one for unsigned types. This duplication confused MS Power BI. The fix for this issue involved following the documentation and using database-native types such as Int32, UInt64, etc.

mshustov · 2025-03-12T15:03:53Z

discussed offline: we need to test how the existing dashboards react to the data type mapping changes

slabko · 2025-03-12T21:37:24Z

Unfortunately, after testing, we noticed that this change breaks the MS Power BI Direct Query aggregation functions, such as sum. Power BI relies on conversion to Decimal to avoid integer overflow. However, with the new implementation of the SQLGetTypeInfo function, Power BI selects Float32 instead, which prevents overflow but results in very imprecise calculations.

The RowBinary format is not fully supported in the ODBC driver, and maintaining feature parity while advancing and improving our ODBCDriver2 format gets more and more difficult. For now, we stop any development of the RowBinary format, as it has never been officially supported, and focus solely on the more advanced ODBCDriver2 format.

Update SQLGetTypeInfo results to comply with [SQLGetTypeInfo](https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgettypeinfo-function). Applications must use the type names returned in the TYPE_NAME column of the SQLGetTypeInfo result set in ALTER TABLE and CREATE TABLE statements

driver/test/type_info_it.cpp

slabko · 2025-04-11T10:54:28Z

driver/test/type_info_it.cpp

+    };
+
+    std::vector<TypeMappingTestEntry> types = {
+        {"Bool", "0", SQL_VARCHAR},


Should be SQL_BIT

Bools are currently represented as strings: Bool columns are represented as a string. This is a known issue and should definitely be fixed. However, this extends the scope of this PR a bit too much, so it will be addressed in a different one.

slabko · 2025-04-11T10:55:50Z

driver/test/type_info_it.cpp

+        {"Int16", "0", SQL_SMALLINT},
+        {"UInt16", "0", SQL_SMALLINT},
+        {"Int32", "0", SQL_INTEGER},
+        {"UInt32", "0", SQL_BIGINT},


Should be SQL_INTEGER (although we used to support some Perl's weird behavior, that should no longer be a problem)

pyodbc, and probably other libraries in other languages, binds SQL_INTEGER to int32_t producing -1 for the max value of UInt32: 4294967295. So UInt32 have to be SQL_BIGINT to avoid this problem. Not sure if this is even a bug.

slabko · 2025-04-11T10:57:09Z

driver/test/type_info_it.cpp

+        {"String", "0", SQL_VARCHAR},
+        {"FixedString(1)", "'0'", SQL_VARCHAR},
+        {"Date", "0", SQL_TYPE_DATE},
+        {"Date32", "0", SQL_VARCHAR},


Should be SQL_TYPE_DATE

Note, ~~Date32~~, DateTime64 are missing in our Python integration tests

The driver does not currently support Date32: Date32 in not supported. The missing test for Date32 will be added as part of the fix for this issue.

slabko · 2025-04-11T10:59:13Z

driver/test/type_info_it.cpp

+    {"LowCardinality(String)", {
+        SQL_VARCHAR,       "String",      msz, 0,   na,   na,    false, SQL_VARCHAR,  na,  msz }},
+    // TODO(slabko): Low cardinality nullable types do not detect nullability correctly
+    // Notice `null` is set to `false` here, but must be `true`


Create an issue in GitHub for that

LowCardinality(Nullable(String)) is not marked as nullable

Copilot

Copilot reviewed 4 out of 19 changed files in this pull request and generated no comments.

Files not reviewed (15)

driver/CMakeLists.txt: Language not supported
driver/api/impl/impl.cpp: Language not supported
driver/api/odbc.cpp: Language not supported
driver/api/sql_columns_resultset_mutator.cpp: Language not supported
driver/api/sql_columns_resultset_mutator.h: Language not supported
driver/environment.cpp: Language not supported
driver/result_set.cpp: Language not supported
driver/test/CMakeLists.txt: Language not supported
driver/test/result_set_reader.hpp: Language not supported
driver/test/statement_parameters_it.cpp: Language not supported
driver/test/utils_ut.cpp: Language not supported
driver/utils/sql_encoding.h: Language not supported
driver/utils/type_info.cpp: Language not supported
driver/utils/type_info.h: Language not supported
driver/utils/utils.h: Language not supported

Comments suppressed due to low confidence (4)

.github/workflows/macOS.yml:104

Removing 'ClickHouse DSN (ANSI, RBWNAT)' from the TEST_DSN_LIST may reduce overall test coverage. Please verify that this DSN configuration is no longer required in any test scenarios.

+        -DTEST_DSN_LIST="ClickHouse DSN (ANSI);ClickHouse DSN (Unicode)"

.github/workflows/macOS.yml:168

The removal of the DSN mapping for 'ClickHouse DSN (ANSI, RBWNAT)' should be double-checked to ensure no critical testing scenarios are lost.

    -        ClickHouse DSN (ANSI, RBWNAT) = ClickHouse ODBC Driver (ANSI)

.github/workflows/Windows.yml:81

Ensure that removing 'ClickHouse DSN (ANSI, RBWNAT)' from the TEST_DSN_LIST in Windows does not omit necessary test coverage for specific DSN configurations.

+        -DTEST_DSN_LIST="ClickHouse DSN (ANSI);ClickHouse DSN (Unicode)"

.github/workflows/Linux.yml:102

Verify that the removal of 'ClickHouse DSN (ANSI, RBWNAT)' from the TEST_DSN_LIST does not exclude important test cases for DSN connectivity on Linux.

+        -DTEST_DSN_LIST="ClickHouse DSN (ANSI);ClickHouse DSN (Unicode)"

CLAassistant · 2025-04-15T17:25:16Z

All committers have signed the CLA.

The driver previously bound SQL_TYPE_TIMESTMP exclusively to `DateTime`, which meant it could not bind values with fractions of seconds. Consequently, it was not possible to use precise timestamps in WHERE clauses. This change updates the driver to bind SQL_TYPE_TIMESTMP to `DateTime64`. This allows inserting values into both `DateTime` and `DateTime64` columns and using precise values in WHERE clauses.

mshustov · 2025-05-31T06:16:40Z

/windsurf-review

windsurf-bot · 2025-05-31T06:21:50Z

I ran into an unexpected issue while reviewing this PR. Please try again later.

mshustov · 2025-06-02T07:45:55Z

/windsurf-review

windsurf-bot · 2025-06-02T07:50:30Z

I ran into an unexpected issue while reviewing this PR. Please try again later.

slabko changed the title ~~[WIP]: Fix SQLGetTypeInfo output to match documentation~~ Fix Numeric Data Type Loading in Power BI Mar 12, 2025

slabko marked this pull request as ready for review March 12, 2025 11:23

mshustov requested a review from slvrtrn March 12, 2025 15:03

slabko marked this pull request as draft March 12, 2025 21:32

slabko added 2 commits April 1, 2025 13:52

slabko marked this pull request as ready for review April 1, 2025 11:52

slabko commented Apr 11, 2025

View reviewed changes

driver/test/type_info_it.cpp Outdated Show resolved Hide resolved

slabko commented Apr 11, 2025

View reviewed changes

driver/test/type_info_it.cpp Outdated Show resolved Hide resolved

slabko commented Apr 11, 2025

View reviewed changes

mshustov requested a review from Copilot April 14, 2025 09:55

Copilot AI reviewed Apr 14, 2025

View reviewed changes

slabko added 3 commits April 16, 2025 16:10

Add missing information to SQLColumns

270b601

Fix capitalization of a function name

f09c9c3

slabko changed the base branch from master to unique-type-names-beta April 16, 2025 18:37

slabko changed the title ~~Fix Numeric Data Type Loading in Power BI~~ Report native ClickHouse data types and pupulate missing type information Apr 17, 2025

slabko merged commit 96dbab6 into ClickHouse:unique-type-names-beta Apr 17, 2025
15 checks passed

mshustov mentioned this pull request May 19, 2025

Int64 columns are loaded as binary columns in PowerBI #445

Closed

Conversation

slabko commented Mar 7, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

mshustov commented Mar 12, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

slabko commented Mar 12, 2025

Uh oh!

Uh oh!

Uh oh!

slabko Apr 11, 2025

Choose a reason for hiding this comment

Uh oh!

slabko Apr 16, 2025

Choose a reason for hiding this comment

Uh oh!

slabko Apr 11, 2025

Choose a reason for hiding this comment

Uh oh!

slabko Apr 17, 2025

Choose a reason for hiding this comment

Uh oh!

slabko Apr 11, 2025

Choose a reason for hiding this comment

Uh oh!

slabko Apr 11, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

slabko Apr 15, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

slabko Apr 11, 2025

Choose a reason for hiding this comment

Uh oh!

slabko Apr 16, 2025

Choose a reason for hiding this comment

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Uh oh!

CLAassistant commented Apr 15, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

mshustov commented May 31, 2025

Uh oh!

windsurf-bot bot commented May 31, 2025

Uh oh!

mshustov commented Jun 2, 2025

Uh oh!

windsurf-bot bot commented Jun 2, 2025

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

slabko commented Mar 7, 2025 •

edited

Loading

mshustov commented Mar 12, 2025 •

edited

Loading

slabko Apr 11, 2025 •

edited

Loading

slabko Apr 15, 2025 •

edited

Loading

CLAassistant commented Apr 15, 2025 •

edited

Loading