Unable to find MD5 in calcite SQL

Question

I am trying to find MD5 checksum value for a column in calcite but the query is throwing an error and i could not trace it.

Please find the results of the sample table and query with error below,

    0: jdbc:calcite:model=src/test/resources/mode> SELECT * FROM emps;
+-------+-------+--------+--------+---------------+-------+------+---------+---------+------------+
| EMPNO | NAME  | DEPTNO | GENDER |     CITY      | EMPID | AGE  | SLACKER | MANAGER |  JOINEDAT  |
+-------+-------+--------+--------+---------------+-------+------+---------+---------+------------+
| 100   | Fred  | 10     |        |               | 30    | 25   | true    | false   | 1996-08-03 |
| 110   | Eric  | 20     | M      | San Francisco | 3     | 80   |         | false   | 2001-01-01 |
| 110   | John  | 40     | M      | Vancouver     | 2     | null | false   | true    | 2002-05-03 |
| 120   | Wilma | 20     | F      |               | 1     | 5    |         | true    | 2005-09-07 |
| 130   | Alice | 40     | F      | Vancouver     | 2     | null | false   | true    | 2007-01-01 |
+-------+-------+--------+--------+---------------+-------+------+---------+---------+------------+
5 rows selected (0.02 seconds)
0: jdbc:calcite:model=src/test/resources/mode> SELECT md5(cast(name as VARCHAR)) FROM emps;
Error: Error while executing SQL "SELECT md5(cast(name as VARCHAR)) FROM emps": From line 1, column 8 to line 1, column 33: No match found for function signature MD5(<CHARACTER>) (state=,code=0)

both queries throws the same error.

SELECT md5(cast(name as VARCHAR)) FROM emps;
SELECT md5(name) FROM emps;

Answer 1

MD5 isn't an standard SQL function, therefore Calcite doesn't enable it by default.

To use it you need to explicitly set the FUN connection parameter to an engine that supports MD5 (i.e. MySQL, PostgresSQL, Google BigQuery).

The documentation has more information about the available operators.

Answer 2

You will receive that error anytime you try to use a library-specific function (such as MD5) without configuring for whichever library supports that function. If you look at Calcite's MD5 operator tests here, you will see that the first test is expected to fail given that a library has not been set. Hope this helps!