Open YuriyGavrilov opened 10 months ago
@YuriyGavrilov trying to understand is the issue on select/insert with JDBC driver
Hi @mzitnik, on select. Right after on clicking “preview” in Dremio.
@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.
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)
@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.
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/
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.