HTTP status code : 400 or 404 or 500 with TABLEAU Serveur query

[Fred5778]

I use TABLEAU Desktop with the lastest clickhouse-odbc driver. I can connect the Clichouse Database with a Custom SQL query definition and browse some Dashboards but others give errors.

For example : when I try to retrieve data from TABLEAU I have this error :

Impossible de terminer l'action
HTTP status code: 400
Received error:
Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 1442 (line 5, col 42): LEFT(`Requ_te_SQL_personnalis_e`.`infra`,6)} ELSE NULL END = 'dsl/o1') AND (CASE WHEN 8 >= 0 THEN {fn LEFT(`Requ_te_SQL_personnalis_e`.`infra`,8)} ELSE NULL END. Expected colon between name and type (version 19.15.3.6 (official build))

Code d'erreur : 1E953F46

An other exemple :

JOIN ON seems to be not supported. 

HTTP status code: 500 Received error: Code: 403, e.displayText() = DB::Exception: Invalid expression for JOIN ON. Expected equals expression, got (zipcode = t0.zipcode) OR (isNull(zipcode) AND isNull(t0.zipcode)). Supported syntax: JOIN ON Expr([table.]column, ...) = Expr([table.]column, ...) [AND Expr([table.]column, ...) = Expr([table.]column, ...) ...] (version 19.15.3.6 (official build)) 
SELECT (CASE WHEN (NOT (`t0`.`Xtemp1_output` IS NULL)) THEN NULL WHEN NOT (NOT (`t0`.`Xtemp1_output` IS NULL)) THEN `Requ_te_SQL_personnalis_e`.`zipcode` ELSE NULL END) AS `Calculation_805299945551982593`, SUM({fn CONVERT(1, SQL_BIGINT)}) AS `TEMP_Calculation_721138909734989825__1055644946__0_`, SUM({fn CONVERT((CASE WHEN ((CASE WHEN (`Requ_te_SQL_personnalis_e`.`percent_indispo_dsl_1day_old` > 1) THEN 1 ELSE 0 END) = 1) THEN 1 WHEN NOT ((CASE WHEN (`Requ_te_SQL_personnalis_e`.`percent_indispo_dsl_1day_old` > 1) THEN 1 ELSE 0 END) = 1) THEN 0 ELSE NULL END), SQL_BIGINT)}) AS `TEMP_Calculation_721138909734989825__3517553545__0_`, SUM({fn CONVERT(1, SQL_BIGINT)}) AS `sum_Number_of_Records_ok`, SUM({fn CONVERT((CASE WHEN ((CASE WHEN (`Requ_te_SQL_personnalis_e`.`percent_indispo_dsl_1day_old` > 1) THEN 1 ELSE 0 END) = 1) THEN 1 WHEN NOT ((CASE WHEN (`Requ_te_SQL_personnalis_e`.`percent_indispo_dsl_1day_old` > 1) THEN 1 ELSE 0 END) = 1) THEN 0 ELSE NULL END), SQL_BIGINT)}) AS `sum__NbClientIndispo___Option_1__copie__ok` FROM ( select idur , cdate , sum_deltaiaduptime_1day_old , sum_reboot_1day_old , sum_deltadsluptime_1day_old , sum_deltadslsynctotal_1day_old , sum_deltadslcountersunavailabletime_1day_old , sum_deltadslcounterscrcdown_1day_old , moy_dslbitratedown_1day_old , moy_dslatteignabledown_1day_old , moy_dslsnrdown_1day_old , percent_indispo_dsl_1day_old , nbpushtotal_1day_old , net00_bas , net01_routerco , net02_rescollecte , net03_boucleura , net04_piedgrappe , net05_ura , net06_switchcaa , net07_iam , net08_dslam , net09_rack , net10_chassis , net11_carte , infra , sum_deltaiaduptime_15days_old , sum_reboot_15days_old , sum_deltadsluptime_15days_old , sum_deltadslsynctotal_15days_old , sum_deltadslcountersunavailabletime_15days_old , sum_deltadslcounterscrcdown_15days_old , moy_dslbitratedown_15days_old , moy_dslatteignabledown_15days_old , moy_dslsnrdown_15days_old , percent_indispo_dsl_15days_old , sum_deltaiaduptime_5days_old , sum_reboot_5days_old , sum_deltadsluptime_5days_old , sum_deltadslsynctotal_5days_old , sum_deltadslcountersunavailabletime_5days_old , sum_deltadslcounterscrcdown_5days_old , moy_dslbitratedown_5days_old , moy_dslatteignabledown_5days_old , moy_dslsnrdown_5days_old , percent_indispo_dsl_5days_old , year , month , day ,ndi , ndi_9t , code_insee , nom_ville , zipcode , code_departement , departement , code_region , region from qoe_fixe.cubedsl_derivecounters_daily_ndi ) `Requ_te_SQL_personnalis_e` LEFT JOIN ( SELECT 1 AS `Xtemp1_output`, `Requ_te_SQL_personnalis_e`.`zipcode` AS `zipcode` FROM ( select idur , cdate , sum_deltaiaduptime_1day_old , sum_reboot_1day_old , sum_deltadsluptime_1day_old , sum_deltadslsynctotal_1day_old , sum_deltadslcountersunavailabletime_1day_old , sum_deltadslcounterscrcdown_1day_old , moy_dslbitratedown_1day_old , moy_dslatteignabledown_1day_old , moy_dslsnrdown_1day_old , percent_indispo_dsl_1day_old , nbpushtotal_1day_old , net00_bas , net01_routerco , net02_rescollecte , net03_boucleura , net04_piedgrappe , net05_ura , net06_switchcaa , net07_iam , net08_dslam , net09_rack , net10_chassis , net11_carte , infra , sum_deltaiaduptime_15days_old , sum_reboot_15days_old , sum_deltadsluptime_15days_old , sum_deltadslsynctotal_15days_old , sum_deltadslcountersunavailabletime_15days_old , sum_deltadslcounterscrcdown_15days_old , moy_dslbitratedown_15days_old , moy_dslatteignabledown_15days_old , moy_dslsnrdown_15days_old , percent_indispo_dsl_15days_old , sum_deltaiaduptime_5days_old , sum_reboot_5days_old , sum_deltadsluptime_5days_old , sum_deltadslsynctotal_5days_old , sum_deltadslcountersunavailabletime_5days_old , sum_deltadslcounterscrcdown_5days_old , moy_dslbitratedown_5days_old , moy_dslatteignabledown_5days_old , moy_dslsnrdown_5days_old , percent_indispo_dsl_5days_old , year , month , day ,ndi , ndi_9t , code_insee , nom_ville , zipcode , code_departement , departement , code_region , region from qoe_fixe.cubedsl_derivecounters_daily_ndi ) `Requ_te_SQL_personnalis_e` WHERE ((NOT (`Requ_te_SQL_personnalis_e`.`nbpushtotal_1day_old` IS NULL)) AND (`Requ_te_SQL_personnalis_e`.`net09_rack` NOT IN ('GAMBETTA-CAEN-01_R1', 'KOENIGSHOFFEN-01_R2', 'LONGCHAMP-IDF-03_R2', 'SOPHIA-ANTIPOLIS-01_R1', 'BORDEAUX-NORD-01_R2', 'STGEORGESDIRECT-01_R1', 'GENNEVILLIERS-01_R2', 'MUL-BARTHOLDI-02_R3', 'CIRCUIT-CAROLE-01_R1', 'GENNEVILLIERS-02_R1', 'CANNES-LASOURCE-01_R1', 'MAISONS-NEUVES-02_R1', 'DIJON-VOLTAIRE-01_R2', 'JAS-DE-BOUFFAN-01_R2', 'BOISSY-ST-LEGER-02_R1', 'CHATEAURENARD-01_R1', 'PESSAC-ALOUETTE-01_R1', 'LEPLESSISBOUCHARD-02_R3', 'BOISGUILLAUME-01_R1', 'STGERM-CORBEIL-02_R2', 'MAISONS-NEUVES-02_R3', 'TROYES-EUROPE-01_R2', 'ROUBAIX-MARTIN-01_R1', 'PHILIPPEAUGUSTE-01_R1', 'LE-HAVRE-EYRIES-01_R1', 'CLF-LAVOISIER-01_R1', 'GARGE-LES-GONESSE-02_R2', 'TUILERIE-NARBONNE-01_R2', 'LAGNYSURMARNE-01_R2', 'STPIERREDESCORPS-01_R1', 'PHILIPPEAUGUSTE-02_R2', 'PONT-DE-CLAIX-01_R2', 'IENA-CARCASSONNE-01_R1', 'MUL-BARTHOLDI-02_R1', 'SAINT-AVERTIN-01_R1', 'VIRYCHATILLON-01_R1', 'BRETIGNYSURORGE-01_R1', 'CROIX-BONNEAU-01_R2', 'STLAURENT-DU-VAR-01_R2', 'CHANTELOUPLESVIGN-01_R1', 'STMARTIN-DHERES-01_R1', 'STEMARGUERITE-02_R1', 'NEUILLY-PLAIS-02_R1', 'ROBERT-KELLER-02_R1', 'SAINTOUENLAUMONE-01_R1', 'BUSSYSTGEORGES-01_R1', 'LONGCHAMP-IDF-02_R2', 'ROUBAIX-ANTOINE-01_R1', 'ROBERT-KELLER-02_R3', 'LEPLESSISBOUCHARD-02_R2', 'BURESSURYVETTE-02_R2', 'AMIENS-QUEBEC-02_R2', 'ATHISMONS-91-94-01_R2', 'CALAIS-BOSSUET-01_R2', 'AUBERGENVILLE-01_R2', 'MAISONS-NEUVES-03_R1', 'KENNEDY-AVIGNON-01_R1', 'TOULOUSE-JAURES-01_R1', 'PLAN-DE-CUQUES-01_R1', 'MESNILSTDENIS-01_R1', 'TROYES-EUROPE-01_R1', 'LE-HAVRE-EYRIES-01_R2', 'PHILIPPEAUGUSTE-01_R2', 'CLF-LAVOISIER-01_R2', 'CAENUNIVERSITE-01_R1', 'VITRYLEFRANCOIS-01_R1', 'BOISSY-ST-LEGER-02_R2', 'PESSAC-ALOUETTE-01_R2', 'STGERM-CORBEIL-02_R1', 'BORDEAUX-NORD-01_R3', 'PONT-DE-CLAIX-01_R1', 'GARGE-LES-GONESSE-02_R1', 'MUL-BARTHOLDI-02_R2', 'IENA-CARCASSONNE-01_R2', 'TUILERIE-NARBONNE-01_R1', 'LAGNYSURMARNE-01_R1', 'LONGCHAMP-IDF-03_R3', 'PHILIPPEAUGUSTE-02_R1', 'STGEORGESDIRECT-01_R2', 'GENNEVILLIERS-01_R1', 'ROBERT-KELLER-02_R4', 'LA-GRANDE-MOTTE-01_R1', 'KOENIGSHOFFEN-01_R1', 'LONGCHAMP-IDF-03_R1', 'MAGNYLEHONGRE-01_R1', 'PHILIPPEAUGUSTE-02_R3', 'DIJON-VOLTAIRE-01_R1', 'REPUBLIQUE-SALON-01_R1', 'JAS-DE-BOUFFAN-01_R1', 'GENNEVILLIERS-02_R2', 'LA-PENNE-HUVEAUNE-01_R1', 'MAISONS-NEUVES-02_R2', 'ROUBAIX-ANTOINE-01_R2', 'LONGCHAMP-IDF-02_R1', 'J-MOULIN-BEZIERS-01_R1', 'BURESSURYVETTE-02_R1', 'AMIENS-QUEBEC-02_R1', 'MAISONSLAFFITTE-02_R2', 'LEPLESSISBOUCHARD-02_R1', 'AUBERGENVILLE-01_R1', 'PLAN-DE-CUQUES-01_R2', 'RILLIEUX-LA-PAPE-01_R1', 'ATHISMONS-91-94-01_R1', 'CALAIS-BOSSUET-01_R1', 'FONTAINESURSAONE-01_R1', 'STLAURENT-DU-VAR-01_R1', 'VIRYCHATILLON-01_R2', 'BRETIGNYSURORGE-01_R2', 'JUAN-LES-PINS-01_R1', 'ROBERT-KELLER-02_R2', 'SAINTOUENLAUMONE-01_R2', 'BURESSURYVETTE-02_R3', 'BUSSYSTGEORGES-01_R2', 'CHANTELOUPLESVIGN-01_R2', 'STMARTIN-DHERES-01_R2')) AND (NOT (`Requ_te_SQL_personnalis_e`.`net09_rack` IS NULL)) AND (CAST(`Requ_te_SQL_personnalis_e`.`cdate` AS DATE) = {ts '2020-03-18 00:00:00'})) GROUP BY `zipcode` HAVING ((CASE WHEN SUM({fn CONVERT(1, SQL_BIGINT)}) = 0 THEN NULL ELSE CAST(SUM({fn CONVERT((CASE WHEN ((CASE WHEN (`Requ_te_SQL_personnalis_e`.`percent_indispo_dsl_1day_old` > 1) THEN 1 ELSE 0 END) = 1) THEN 1 WHEN NOT ((CASE WHEN (`Requ_te_SQL_personnalis_e`.`percent_indispo_dsl_1day_old` > 1) THEN 1 ELSE 0 END) = 1) THEN 0 ELSE NULL END), SQL_BIGINT)}) AS FLOAT) / SUM({fn CONVERT(1, SQL_BIGINT)}) END) > 0.050000000000000003) ) `t0` ON ((`Requ_te_SQL_personnalis_e`.`zipcode` = `t0`.`zipcode`) OR ((`Requ_te_SQL_personnalis_e`.`zipcode` IS NULL) AND (`t0`.`zipcode` IS NULL))) WHERE ((NOT (`Requ_te_SQL_personnalis_e`.`nbpushtotal_1day_old` IS NULL)) AND (`Requ_te_SQL_personnalis_e`.`net09_rack` NOT IN ('GAMBETTA-CAEN-01_R1', 'KOENIGSHOFFEN-01_R2', 'LONGCHAMP-IDF-03_R2', 'SOPHIA-ANTIPOLIS-01_R1', 'BORDEAUX-NORD-01_R2', 'STGEORGESDIRECT-01_R1', 'GENNEVILLIERS-01_R2', 'MUL-BARTHOLDI-02_R3', 'CIRCUIT-CAROLE-01_R1', 'GENNEVILLIERS-02_R1', 'CANNES-LASOURCE-01_R1', 'MAISONS-NEUVES-02_R1', 'DIJON-VOLTAIRE-01_R2', 'JAS-DE-BOUFFAN-01_R2', 'BOISSY-ST-LEGER-02_R1', 'CHATEAURENARD-01_R1', 'PESSAC-ALOUETTE-01_R1', 'LEPLESSISBOUCHARD-02_R3', 'BOISGUILLAUME-01_R1', 'STGERM-CORBEIL-02_R2', 'MAISONS-NEUVES-02_R3', 'TROYES-EUROPE-01_R2', 'ROUBAIX-MARTIN-01_R1', 'PHILIPPEAUGUSTE-01_R1', 'LE-HAVRE-EYRIES-01_R1', 'CLF-LAVOISIER-01_R1', 'GARGE-LES-GONESSE-02_R2', 'TUILERIE-NARBONNE-01_R2', 'LAGNYSURMARNE-01_R2', 'STPIERREDESCORPS-01_R1', 'PHILIPPEAUGUSTE-02_R2', 'PONT-DE-CLAIX-01_R2', 'IENA-CARCASSONNE-01_R1', 'MUL-BARTHOLDI-02_R1', 'SAINT-AVERTIN-01_R1', 'VIRYCHATILLON-01_R1', 'BRETIGNYSURORGE-01_R1', 'CROIX-BONNEAU-01_R2', 'STLAURENT-DU-VAR-01_R2', 'CHANTELOUPLESVIGN-01_R1', 'STMARTIN-DHERES-01_R1', 'STEMARGUERITE-02_R1', 'NEUILLY-PLAIS-02_R1', 'ROBERT-KELLER-02_R1', 'SAINTOUENLAUMONE-01_R1', 'BUSSYSTGEORGES-01_R1', 'LONGCHAMP-IDF-02_R2', 'ROUBAIX-ANTOINE-01_R1', 'ROBERT-KELLER-02_R3', 'LEPLESSISBOUCHARD-02_R2', 'BURESSURYVETTE-02_R2', 'AMIENS-QUEBEC-02_R2', 'ATHISMONS-91-94-01_R2', 'CALAIS-BOSSUET-01_R2', 'AUBERGENVILLE-01_R2', 'MAISONS-NEUVES-03_R1', 'KENNEDY-AVIGNON-01_R1', 'TOULOUSE-JAURES-01_R1', 'PLAN-DE-CUQUES-01_R1', 'MESNILSTDENIS-01_R1', 'TROYES-EUROPE-01_R1', 'LE-HAVRE-EYRIES-01_R2', 'PHILIPPEAUGUSTE-01_R2', 'CLF-LAVOISIER-01_R2', 'CAENUNIVERSITE-01_R1', 'VITRYLEFRANCOIS-01_R1', 'BOISSY-ST-LEGER-02_R2', 'PESSAC-ALOUETTE-01_R2', 'STGERM-CORBEIL-02_R1', 'BORDEAUX-NORD-01_R3', 'PONT-DE-CLAIX-01_R1', 'GARGE-LES-GONESSE-02_R1', 'MUL-BARTHOLDI-02_R2', 'IENA-CARCASSONNE-01_R2', 'TUILERIE-NARBONNE-01_R1', 'LAGNYSURMARNE-01_R1', 'LONGCHAMP-IDF-03_R3', 'PHILIPPEAUGUSTE-02_R1', 'STGEORGESDIRECT-01_R2', 'GENNEVILLIERS-01_R1', 'ROBERT-KELLER-02_R4', 'LA-GRANDE-MOTTE-01_R1', 'KOENIGSHOFFEN-01_R1', 'LONGCHAMP-IDF-03_R1', 'MAGNYLEHONGRE-01_R1', 'PHILIPPEAUGUSTE-02_R3', 'DIJON-VOLTAIRE-01_R1', 'REPUBLIQUE-SALON-01_R1', 'JAS-DE-BOUFFAN-01_R1', 'GENNEVILLIERS-02_R2', 'LA-PENNE-HUVEAUNE-01_R1', 'MAISONS-NEUVES-02_R2', 'ROUBAIX-ANTOINE-01_R2', 'LONGCHAMP-IDF-02_R1', 'J-MOULIN-BEZIERS-01_R1', 'BURESSURYVETTE-02_R1', 'AMIENS-QUEBEC-02_R1', 'MAISONSLAFFITTE-02_R2', 'LEPLESSISBOUCHARD-02_R1', 'AUBERGENVILLE-01_R1', 'PLAN-DE-CUQUES-01_R2', 'RILLIEUX-LA-PAPE-01_R1', 'ATHISMONS-91-94-01_R1', 'CALAIS-BOSSUET-01_R1', 'FONTAINESURSAONE-01_R1', 'STLAURENT-DU-VAR-01_R1', 'VIRYCHATILLON-01_R2', 'BRETIGNYSURORGE-01_R2', 'JUAN-LES-PINS-01_R1', 'ROBERT-KELLER-02_R2', 'SAINTOUENLAUMONE-01_R2', 'BURESSURYVETTE-02_R3', 'BUSSYSTGEORGES-01_R2', 'CHANTELOUPLESVIGN-01_R2', 'STMARTIN-DHERES-01_R2')) AND (NOT (`Requ_te_SQL_personnalis_e`.`net09_rack` IS NULL)) AND (CAST(`Requ_te_SQL_personnalis_e`.`cdate` AS DATE) = {ts '2020-03-18 00:00:00'})) GROUP BY (CASE WHEN (NOT (`t0`.`Xtemp1_output` IS NULL)) THEN NULL WHEN NOT (NOT (`t0`.`Xtemp1_output` IS NULL)) THEN `Requ_te_SQL_personnalis_e`.`zipcode` ELSE NULL END)

or

HTTP status code: 404 Received error: Code: 47, e.displayText() = DB::Exception: Unknown identifier (in GROUP BY): day (version 19.15.3.6 (official build)) 

Is it possible to have those incompatibilities corrected in the Clickhouse ODBC driver? Do you have a list of all the TABLEAU SQL functions that are not supported yet with CLickhouse?

Regards,

Fred

[traceon]

This looks more like a syntax not supported by ClickHouse server itself. Try running these same queries that Tableau generates and ClickHouse ODBC driver retransmits, manually through ClickHouse CLI client.