Some failing tests for Exasol

Driver Development
1

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

metabase.query-processor-test.remapping-test/self-referencing-test

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
LIMIT 4

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 ta_self_referencing_user_users.id is ambiguous.

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

metabase.query-processor-test.remapping-test/remappings-with-implicit-joins-test

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?

metabase.query-processor.reducible-test/cancelation-test

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

expected: (thrown-with-msg?
           clojure.lang.ExceptionInfo
           #"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.

2

Hi @kaklakariada
I would look at the new BigQuery driver (or possibly Oracle) for inspiration on how to modify the identifier (aka alias):
https://github.com/metabase/metabase/blob/master/modules/drivers/bigquery-cloud-sdk/src/metabase/driver/bigquery_cloud_sdk/query_processor.clj#L464
https://github.com/metabase/metabase/blob/master/modules/drivers/oracle/src/metabase/driver/oracle.clj#L201-L224

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.

3

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")])))
      identifier)))

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 -> ta_self_referencing_user_users.name
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
LIMIT 4;

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
LIMIT 4;

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.

4

@kaklakariada Sounds like you might be seeing one of these issues:
https://github.com/metabase/metabase/issues?q=is%3Aopen+label%3A"Querying%2FNested+Queries"+alias+reference
Or perhaps something similar to this: https://github.com/metabase/metabase/issues/15978

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).

5

@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.

6

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

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

See run-integration-tests.sh.

1 Like