The same column names in different letters in the clockhouse leads to errors in various other products that are connected to the clickhouse via jdbc

[YuriyGavrilov]

Describe the unexpected behaviour

The same column names in different letters in the clockhouse leads to errors in various other products that are connected to the clickhouse via jdbc

For example we can have two columns with same name like "name" and "Name" in same table.

How to reproduce

https://fiddle.clickhouse.com/

CREATE TABLE users (uid Int16, name String, age Int16, Name String) ENGINE=Memory;

INSERT INTO users VALUES (1231, 'John', 33, 'John');
INSERT INTO users VALUES (6666, 'Ksenia', 48, 'John');
INSERT INTO users VALUES (8888, 'Alice', 50, 'John');

SELECT * FROM users;

• Which ClickHouse server version to use: lattest

Expected behavior Avoid using same name in tables. Add exeption to the jdbc driver to rename same name columns with some index on the fly. UPD: Or to make smart shielding same name columns to avoid getting crazy query tools. Error message and/or stacktrace For example Dremio can't work with same column names and shows this error:

Error while applying rule CLICKHOUSELogicalScanConverter, args [rel#752887705:ScanCrel.NONE.ANY([]).[](table="TR EVENTS".app_events.events_v2,columns=sv_unq_id, event_date, event_timestamp, event_time, event_name, event_previous_timestamp, event_value_in_usd, event_bundle_sequence_id, event_server_timestamp_offset, user_id, user_pseudo_id, user_first_touch_timestamp, stream_id, privacy_info_analytics_storage, privacy_info_ads_storage, privacy_info_uses_transient_token, user_ltv_revenue, user_ltv_currency, device_category, device_mobile_brand_name, device_mobile_model_name, device_mobile_marketing_name, device_mobile_os_hardware_model, device_operating_system, device_operating_system_version, device_vendor_id, device_advertising_id, device_language, device_is_limited_ad_tracking, device_time_zone_offset_seconds, device_browser, device_browser_version, device_web_info_browser, device_web_info_browser_version, device_web_info_hostname, geo_continent, geo_country, geo_region, geo_city, geo_sub_continent, geo_metro, app_info_id, app_info_version, app_info_install_store, app_info_firebase_app_id, app_info_install_source, traffic_source_name, traffic_source_medium, traffic_source_source, event_dimensions_hostname, ecommerce_total_item_quantity, ecommerce_purchase_revenue_in_usd, ecommerce_purchase_revenue, ecommerce_refund_value_in_usd, ecommerce_refund_value, ecommerce_shipping_value_in_usd, ecommerce_shipping_value, ecommerce_tax_value_in_usd, ecommerce_tax_value, ecommerce_unique_items, ecommerce_transaction_id, platform, items_item_id, items_item_name, items_item_brand, items_item_variant, items_item_category, items_item_category2, items_item_category3, items_item_category4, items_item_category5, items_price_in_usd, items_price, items_quantity, items_item_revenue_in_usd, items_item_revenue, items_item_refund_in_usd, items_item_refund, items_coupon, items_affiliation, items_location_id, items_item_list_id, items_item_list_name, items_item_list_index, items_promotion_id, items_promotion_name, items_creative_name, items_creative_slot, event_params_ADL, event_params_adt, event_params_af_currency, event_params_af_order_id, event_params_affiliation, event_params_AIRLINE, event_params_AIRPORT, event_params_AnotherCity, event_params_AnotherDate, event_params_application, event_params_arrival, event_params_arrivalTime, event_params_backToSearch, event_params_BAG, event_params_BAG_REVENUE, event_params_banner_name, event_params_build_type, event_params_campaign, event_params_campaign_id, event_params_campaign_info_source, event_params_categoryCode, event_params_chd, event_params_Checked_in, event_params_Checkin, event_params_CHECKINED, event_params_CITY, event_params_CLASS, event_params_click_timestamp, event_params_client, event_params_Client_ID, event_params_CLOSEABLE, event_params_code, event_params_codeshare, event_params_CONTENT, event_params_conversation_id, event_params_COUNT, event_params_COUNTRY, event_params_CRTF, event_params_currency, event_params_CurrentScreen, event_params_DATE, event_params_DATES, event_params_dclid, event_params_DDATE, event_params_debug_event, event_params_debug_mode, event_params_DEEPLINK, event_params_departure, event_params_departure_date, event_params_departureTime, event_params_depature, event_params_depatureTime, event_params_destination, event_params_DETAIL, event_params_discount, event_params_DOCUMENT, event_params_DTIME, event_params_dynamic_link_accept_time, event_params_dynamic_link_link_id, event_params_dynamic_link_link_name, event_params_element_id, event_params_engaged_session_event, event_params_engagement_time_msec, event_params_entrances, event_params_Error, event_params_error_value, event_params_event_type, event_params_EventAction, event_params_EventCategory, event_params_EventLabel, event_params_EventLabel1, event_params_EventLabel10, event_params_EventLabel11, event_params_EventLabel12, event_params_EventLabel2, event_params_EventLabel3, event_params_EventLabel4, event_params_EventLabel5, event_params_EventLabel6, event_params_EventLabel7, event_params_EventLabel8, event_params_EventLabel9, event_params_EventValue, event_params_fare, event_params_FAREFAMILY, event_params_faremix, event_params_fatal, event_params_FFP, event_params_file_extension, event_params_file_name, event_params_firebase_conversion, event_params_firebase_error, event_params_firebase_event_origin, event_params_firebase_previous_class, event_params_firebase_previous_id, event_params_firebase_screen_class, event_params_firebase_screen_id, event_params_FLIGHT, event_params_flight_duration, event_params_flights, event_params_FLOW, event_params_FROM, event_params_FWAY, event_params_ga_session_id, event_params_ga_session_number, event_params_gclid, event_params_HEALTH_PROMO_CODE, event_params_hit_timestamp, event_params_id, event_params_ignore_referrer, event_params_index, event_params_inf, event_params_INSURANCE, event_params_insurance_count, event_params_insurance_type, event_params_item_category, event_params_item_category2, event_params_item_category3, event_params_item_id, event_params_item_name, event_params_item_variant, event_params_ITINERARY, event_params_IWAYID, event_params_lastKnown_ADL, event_params_lastKnown_affiliation, event_params_lastKnown_AIRLINE, event_params_lastKnown_application, event_params_lastKnown_arrival, event_params_lastKnown_arrivalTime, event_params_lastKnown_BAG, event_params_lastKnown_BAG_REVENUE, event_params_lastKnown_campaign, event_params_lastKnown_categoryCode, event_params_lastKnown_CHD, event_params_lastKnown_CHECKINED, event_params_lastKnown_CLASS, event_params_lastKnown_client, event_params_lastKnown_CLOSEABLE, event_params_lastKnown_code, event_params_lastKnown_codeshare, event_params_lastKnown_CONTENT, event_params_lastKnown_CRTF, event_params_lastKnown_CurrentScreen, event_params_lastKnown_DATES, event_params_lastKnown_DDATE, event_params_lastKnown_DEEPLINK, event_params_lastKnown_departure_date, event_params_lastKnown_depature, event_params_lastKnown_depatureTime, event_params_lastKnown_destination, event_params_lastKnown_DETAIL, event_params_lastKnown_DOCUMENT, event_params_lastKnown_DTIME, event_params_lastKnown_element_id, event_params_lastKnown_error, event_params_lastKnown_event_type, event_params_lastKnown_EventAction, event_params_lastKnown_EventCategory, event_params_lastKnown_EventLabel, event_params_lastKnown_EventLabel1, event_params_lastKnown_EventLabel2, event_params_lastKnown_EventLabel3, event_params_lastKnown_EventLabel4, event_params_lastKnown_EventValue, event_params_lastKnown_fare, event_params_lastKnown_FAREFAMILY, event_params_lastKnown_faremix, event_params_lastKnown_FLIGHT, event_params_lastKnown_flights, event_params_lastKnown_flow, event_params_lastKnown_FROM, event_params_lastKnown_FWAY, event_params_lastKnown_id, event_params_lastKnown_index, event_params_lastKnown_INF, event_params_lastKnown_INSURANCE, event_params_lastKnown_insurance_count, event_params_lastKnown_insurance_type, event_params_lastKnown_item_category2, event_params_lastKnown_item_category3, event_params_lastKnown_item_id, event_params_lastKnown_item_name, event_params_lastKnown_item_variant, event_params_lastKnown_ITINERARY, event_params_lastKnown_LINE, event_params_lastKnown_location, event_params_lastKnown_location_id, event_params_lastKnown_login, event_params_lastKnown_marketing, event_params_lastKnown_MEAL, event_params_lastKnown_MEAL_REVENUE, event_params_lastKnown_medium, event_params_lastKnown_MILES, event_params_lastKnown_NAME, event_params_lastKnown_operatimg, event_params_lastKnown_ORDER_ID, event_params_lastKnown_ORDERID, event_params_lastKnown_origin, event_params_lastKnown_OW_ADT, event_params_lastKnown_OW_CHD, event_params_lastKnown_parameter, event_params_lastKnown_partner, event_params_lastKnown_PERSONAL, event_params_lastKnown_pnr, event_params_lastKnown_POS, event_params_lastKnown_price, event_params_lastKnown_PROMOCODE, event_params_lastKnown_promotion_id, event_params_lastKnown_promotion_name, event_params_lastKnown_QTY, event_params_lastKnown_quantity, event_params_lastKnown_RDATE, event_params_lastKnown_RDMPTN, event_params_lastKnown_result, event_params_lastKnown_REVENUE, event_params_lastKnown_RT_ADT, event_params_lastKnown_RT_CHD, event_params_lastKnown_SCREEN, event_params_lastKnown_SEAT, event_params_lastKnown_SEAT_REVENUE, event_params_lastKnown_SESSION_ID, event_params_lastKnown_slice, event_params_lastKnown_source, event_params_lastKnown_STATUS, event_params_lastKnown_step, event_params_lastKnown_SUB, event_params_lastKnown_SWITCH, event_params_lastKnown_THEME, event_params_lastKnown_TICKETSTYPE, event_params_lastKnown_title, event_params_lastKnown_TO, event_params_lastKnown_transaction_id, event_params_lastKnown_trueEventCategory, event_params_lastKnown_type, event_params_lastKnown_ui, event_params_lastKnown_UX, event_params_lastKnown_ux_ui, event_params_lastKnown_way, event_params_lastKnown_WEIGHT, event_params_LINE, event_params_link_classes, event_params_link_domain, event_params_link_id, event_params_link_text, event_params_link_url, event_params_LOCATION, event_params_location_id, event_params_login, event_params_marketing, event_params_MEAL, event_params_MEAL_REVENUE, event_params_medium, event_params_MILES, event_params_MODEL, event_params_NAME, event_params_NEW, event_params_NONE, event_params_notification_id, event_params_offer_id, event_params_OFFERS, event_params_operatimg, event_params_operating, event_params_ORDER, event_params_ORDER_ID, event_params_ORDERID, event_params_origin, event_params_other_value_coupon_value, event_params_other_value_miles_value, event_params_other_value_sertificate_value, event_params_outbound, event_params_OW_ADT, event_params_OW_CHD, event_params_page_location, event_params_page_referrer, event_params_page_title, event_params_pagePath, event_params_parameter, event_params_partner, event_params_passenger_id, event_params_payment_type, event_params_percent_scrolled, event_params_PERSONAL, event_params_PLACE, event_params_pnr, event_params_POS, event_params_PREVIOUS, event_params_previous_app_version, event_params_previous_first_open_count, event_params_previous_os_version, event_params_PRICE, event_params_product_id, event_params_PROMOCODE, event_params_promotion_id, event_params_promotion_name, event_params_QTY, event_params_quantity, event_params_RDATE, event_params_RDMPTN, event_params_result, event_params_REVENUE, event_params_ROUTE, event_params_RT_ADT, event_params_RT_CHD, event_params_S7_FLIGHT, event_params_S7_NSP, event_params_S7_SPORT, event_params_S7_TRAVEL, event_params_S7_VZR, event_params_SCREEN, event_params_search_term, event_params_SEAT, event_params_SEAT_REVENUE, event_params_segment, event_params_session_engaged, event_params_SESSION_ID, event_params_slice, event_params_slices, event_params_SOCIAL, event_params_source, event_params_spa, event_params_SPNR, event_params_STATUS, event_params_step, event_params_stops, event_params_stops_duration, event_params_SUB, event_params_SUB_WAY, event_params_subCategoryCode, event_params_subsidized, event_params_SWITCH, event_params_system_app, event_params_system_app_update, event_params_TAGID, event_params_term, event_params_THEME, event_params_TICKETSTYPE, event_params_TIME, event_params_timestamp, event_params_title, event_params_TO, event_params_transaction_id, event_params_trueEventAction, event_params_trueEventCategory, event_params_tyapplicationpe, event_params_TYPE, event_params_UI, event_params_unique_search_term, event_params_update_with_analytics, event_params_UPGRADE, event_params_UPGRADE_REVENUE, event_params_url, event_params_User_ID, event_params_ux, event_params_ux_ui, event_params_value, event_params_version_code, event_params_version_name, event_params_video_current_time, event_params_video_duration, event_params_video_percent, event_params_video_provider, event_params_video_title, event_params_video_url, event_params_visible, event_params_way, event_params_wci_info_checkin, event_params_wci_info_checked_in, event_params_wci_info_bp_allowed, event_params_wci_info_mbp_allowed, event_params_WEIGHT, event_params_arrival_date, event_params_company_id, event_params_finish_date, event_params_ond, event_params_products, event_params_search_id, event_params_search_payload, event_params_start_date, user_properties_Apple, user_properties_Checkined, user_properties_Facebook, user_properties_Google, user_properties_Instagram, user_properties_Play_Services, user_properties_Purchased, user_properties_SavedCard, user_properties_SavedCompanion, user_properties_SavedDocument, user_properties_SavedVisa, user_properties_Settings_Currency, user_properties_Settings_Settings_Push, user_properties_Stats_Airport, user_properties_Stats_City, user_properties_Stats_Country, user_properties_Tinkoff, user_properties_User_Active_Flights, user_properties_User_FFP_number, user_properties_User_FFP_status, user_properties_User_Profile_type, user_properties_VK, user_properties__ltv_RUB, user_properties_active_flights, user_properties_activeflights, user_properties_build_type, user_properties_device_id, user_properties_firebase_exp_36, user_properties_firebase_last_notification, user_properties_first_open_time, user_properties_ga_session_id, user_properties_ga_session_number, user_properties_idfa_consent, user_properties_last_advertising_id_reset, user_properties_last_gclid, user_properties_profile_type, user_properties_profiletype, user_properties_user_id, event_params_other_value, event_params_wci_info, user_first_date, user_first_time, event_params_exp, event_params_coupon, user_properties_profile_ab_group, user_properties_user_pseudo_id, event_params_part_cash_part_miles_flag, event_params_item_list_name, collected_traffic_source_manual_campaign_id, collected_traffic_source_manual_campaign_name, collected_traffic_source_manual_source, collected_traffic_source_manual_medium, collected_traffic_source_manual_term, collected_traffic_source_manual_content, collected_traffic_source_gclid, collected_traffic_source_dclid, collected_traffic_source_srsltid, session_params_utm_source, session_params_utm_medium, session_params_utm_campaign, session_params_utm_content, session_params_utm_term,splits=1,tableDigest=-1068618140|TR EVENTS|3e58860e-0ce5-43f9-a83d-ee57dec1e1b1)].

There is also Trino can't work with it.

Additional context it seams there is no problem at all in clickhouse due to it "can" work with different spelling column names but it is a huge annoing to others and gives reason to hate clickhouse.

[mzitnik]

@YuriyGavrilov trying to understand is the issue on select/insert with JDBC driver

[YuriyGavrilov]

Hi @mzitnik, on select. Right after on clicking “preview” in Dremio.

[YuriyGavrilov]

@mzitnik I also make some short discussion with @alexey-milovidov about it. He suggest to plan features in server. To use search in case-insensitive namespace. Maybe better close this issue and open like a future feature in server tree.

[den-crane]

Why this issue is Clickhouse specific? Other databases allows the same

postgresql:

select 1 result, 2 "RESULT", 3 "ReSuLt";
 result | RESULT | ReSuLt
--------+--------+--------
      1 |      2 |      3

mysql

MariaDB [dw]> select 1 result, 2 "RESULT", 3 "ReSuLt";
+--------+--------+--------+
| result | RESULT | ReSuLt |
+--------+--------+--------+
|      1 |      2 |      3 |
+--------+--------+--------+

duckdb

D select 1 result, 2 "RESULT", 3 "ReSuLt";
┌────────┬────────┬────────┐
│ result │ RESULT │ ReSuLt │
│ int32  │ int32  │ int32  │
├────────┼────────┼────────┤
│      1 │      2 │      3 │
└────────┴────────┴────────┘

moreover, pg allows the same names

select 1 a, 2 a, 3 a;;
 a | a | a
---+---+---
 1 | 2 | 3
(1 row)

[YuriyGavrilov]

@den-crane right. but I think only clickhouse jdbc driver can't explain db how to work with the query results. So it make crazy query tools like Dremio, Trino, DBeaver ... etc.