Some failing tests for Exasol

I am working on a new driver for Exasol and have issues getting the tests running.


This fails because the generated query fails:

-- Metabase
SELECT "CAM_42"."ta_self_referencing_user_users"."id" AS "id",
    "CAM_42"."ta_self_referencing_user_users"."name" AS "name",
    "CAM_42"."ta_self_referencing_user_users"."last_login" AS "last_login",
    "CAM_42"."ta_self_referencing_user_users"."created_by" AS "created_by",
    "ta_self_referencing_user_users"."name" AS "ta_self_referencing_user_users__name"
FROM "CAM_42"."ta_self_referencing_user_users"
    LEFT JOIN "CAM_42"."ta_self_referencing_user_users" "ta_self_referencing_user_users" ON -- <--
       "CAM_42"."ta_self_referencing_user_users"."created_by" = "ta_self_referencing_user_users"."id"
ORDER BY "CAM_42"."ta_self_referencing_user_users"."name" ASC

The issues is that the table alias in the marked line (<--) is equal to the database table name which confuses Exasol and causes an exception:

java.sql.SQLException: identifier is ambiguous.

How can I customize the LEFT JOIN clause in my driver to use a different alias than the table name?


This fails because the expected value is "2019-11-06T16:38:50.134Z" but the actual value is "2019-11-06 16:38:50.134" (without the Z timezone).

Which multimethod do I need to add in the driver to use the Z timezone?


This fails because numbers are formatted with , instead of . for decimal point:

expected: (thrown-with-msg?
           #"Timed out after 1000\.0 µs\."
           (if (instance? Throwable result) (throw result) result))
  actual: #<clojure.lang.ExceptionInfo@141971c7 clojure.lang.ExceptionInfo: Timed out after 1000,0 µs. {:status :timed-out, :type :timed-out}>

I tried to environment variables MB_SITE_LOCALE=en when starting the tests, but this broke some other tests that expected spanish text. Setting LANG=en did not help either.

Hi @kaklakariada
I would look at the new BigQuery driver (or possibly Oracle) for inspiration on how to modify the identifier (aka alias):

The Z means UTC, so if you don't have anything, then it's a column "without time zone". It's difficult to know how you have created your driver, but look at the other drivers for inspiration about timezones.

And the comma vs dot. That doesn't look like a language problem, but locale formatting. And most likely comes from your system/Java. Try running on an English system and compare your locale settings.

Hi @flamber, thank you for your reply and tip. Adding this method fixed the problem with the ambiguous identifier:

(defmethod sql.qp/->honeysql [:exasol metabase.util.honeysql_extensions.Identifier]
  [_ identifier]
  (let [field-identifier (last (:components identifier))
        identifier-type (:identifier-type identifier)]
    (if (= :table-alias identifier-type)
      (update identifier :components (fn [components]
                                       (concat (butlast components)
                                               [(str field-identifier "_table_alias")])))

Now the tests fails because it gets the wrong result:

FAIL in metabase.query-processor-test.remapping-test/self-referencing-test (remapping_test.clj:178)

:exasol using test-data-self-referencing-user dataset With FK remapping ta_self_referencing_user_users.created_by ->
expected: ["Dwight Gresham" "Shad Ferdynand" "Kfir Caj" "Plato Yeshua"]
  actual: (("Broen Olujimi"
            "Conchúr Tihomir"
            "Dwight Gresham"
            "Felipinho Asklepios"))

The reason is, that the generated query does not contain aliases in the right places:

-- Original query generated by Test, gives wrong result
SELECT "CAM_179"."ta_self_referencing_user_users"."id" AS "id",
    "CAM_179"."ta_self_referencing_user_users"."name" AS "name",
    "CAM_179"."ta_self_referencing_user_users"."last_login" AS "last_login",
    "CAM_179"."ta_self_referencing_user_users"."created_by" AS "created_by",
    "ta_self_referencing_user_users"."name" AS "ta_self_referencing_user_users__name"
FROM "CAM_179"."ta_self_referencing_user_users"
    LEFT JOIN "CAM_179"."ta_self_referencing_user_users" "ta_self_referencing_user_users_table_alias" 
      ON "CAM_179"."ta_self_referencing_user_users"."created_by" = "ta_self_referencing_user_users"."id"
ORDER BY "CAM_179"."ta_self_referencing_user_users"."name" ASC

Modified query, correct result, added suffix '_table_alias' in the marked lines (<==)

SELECT "CAM_179"."ta_self_referencing_user_users"."id" AS "id",
    "CAM_179"."ta_self_referencing_user_users"."name" AS "name",
    "CAM_179"."ta_self_referencing_user_users"."last_login" AS "last_login",
    "CAM_179"."ta_self_referencing_user_users"."created_by" AS "created_by",
    "ta_self_referencing_user_users_table_alias"."name" AS "ta_self_referencing_user_users__name" -- <==
FROM "CAM_179"."ta_self_referencing_user_users"
    LEFT JOIN "CAM_179"."ta_self_referencing_user_users" "ta_self_referencing_user_users_table_alias" 
      ON "CAM_179"."ta_self_referencing_user_users"."created_by" = "ta_self_referencing_user_users_table_alias"."id" -- <==
ORDER BY "CAM_179"."ta_self_referencing_user_users"."name" ASC

How can I tell Metabase that it should use the table alias also in the other two places?

Regarding the locale: I have LANG=en_GB.UTF-8 on my system. Changing it to LANG=en_US didn't change anything.

I think clojure already uses -Duser.language=en as defined in deps.edn when running the tests with clojure -X:dev:ci:drivers:drivers-dev:test.

@kaklakariada Sounds like you might be seeing one of these issues:"Querying%2FNested+Queries"+alias+reference
Or perhaps something similar to this:

Locale formatting is defined in something like LC_NUMERIC, not the language. I'm guessing that your system is not setup as English originally, so try on a new system (or container/VM).

@flamber Thank you!
I guess then I will skip this test until the issues are resolved in Metabase.

Regarding the locale: Tests run under Linux but fail on my Mac. Setting LANG and the LC_ variables did not help. I will investigate this later.

Found out how to set the locale and fix the tests: define system properties and user.language when starting the tests:

clojure -J-Duser.language=en -X:dev:ci:drivers:drivers-dev:test


1 Like