Add support for "as-is" function calls for case-sensitive contexts (ClickHouse, BigQuery)

[evilsneer]

It makes trouble when working with some sql-oriented dbs.

Example:

{:select [[[:domain :ref]]]}
produces
SELECT DOMAIN(ref)

[seancorfield]

Can you provide a concrete example of a database/JDBC driver combination where this causes a problem, so that I can set up a repro test case?

[evilsneer]

sure
clickhouse with [ru.yandex.clickhouse/clickhouse-jdbc "0.3.1"]

[seancorfield]

Never heard of it. Looks like I can run it via Docker: https://hub.docker.com/r/yandex/clickhouse-server/

Can you provide a short Clojure script that, starting with an empty default instance of ClickHouse via that Docker container, creates a database, a table, and then does something that breaks specifically because of the uppercasing of the function?

That will give me a good testing framework so that I can compare behavior across some other DB/drivers and see if this is specific to ClickHouse and then I can look at providing some option for opting out of the default behavior somehow, in a way that won't break anything for users of any other system (this is not just about uppercasing function calls because the logic around that syntax is there to support other SQL syntax -- and this would also have to potentially affect every other context that HoneySQL currently treats as a function call).

You may just be better off using :raw to construct lowercase function calls for the (few?) cases where they actually break?

dev=> (sql/format {:select [[[:raw "domain(ref)"]]]})
["SELECT domain(ref)"]

[evilsneer]

I have got sql for this

CREATE table fuzz ("ref" String) ENGINE = MergeTree() ORDER BY "ref";
INSERT INTO fuzz VALUES ('http://hello.com');

SELECT DOMAIN("ref") from fuzz - won't work with

DB::Exception: Unknown function DOMAIN. Maybe you meant: ['domain']: While processing DOMAIN(ref) (version 21.8.5.7 (official build))

expected

SELECT domain("ref") from fuzz

[seancorfield]

Much appreciated @evilsneer -- I'll take a look at this during my next OSS window (which probably won't be for a week or two, just to set expectations).

[seancorfield]

I'm still thinking about how best to tackle this since it is non-trivial.

[seancorfield]

BigQuery is another use case for this.

[seancorfield]

develop now supports function keywords that start with ' to produce literal names:

dev=> (sql/format {:select [[[:domain :ref]]]})
["SELECT DOMAIN(ref)"]
dev=> (sql/format {:select [[[:'domain :ref]]]})
["SELECT domain(ref)"]

Note: there is no literal symbol equivalent to this (although you could do (symbol "'domain")):

dev=> (sql/format {:select [[[(symbol "'domain") :ref]]]})
["SELECT domain(ref)"]

/cc @shay-rf

[seancorfield]

@evilsneer You posted a comment and seem to have deleted it -- was your question answered?

[evilsneer]

Yes, i thought about some use cases with swapping sql backends below hsql. But at second thought think it’s not such useful, so differences of hsql representation btw backends is ok i guess

[evilsneer]

Hi again! Should it works already?

I have

[com.github.seancorfield/honeysql "2.2.861"]

and

(-> 
  (honey.sql.helpers/select :*) 
  (honey.sql.helpers/from :foo)
  (honey.sql.helpers/where [:'my-schema.SomeFunction :bar 0])
  (honey/format))
=> ["SELECT * FROM foo WHERE 'MY SCHEMA.SOMEFUNCTION(bar, ?)" 0]

(honey/format {:select [[[:'domain :ref]]]})
=> ["SELECT 'DOMAIN(ref)"]

(honey/format {:select [[[(symbol "'domain") :ref]]]})
=> ["SELECT 'DOMAIN(ref)"]

I see there is upcoming 2.2.next according to Changelog, any plans about release date?=)

[seancorfield]

It's available on develop via git deps. It has not yet been released. See https://github.com/seancorfield/honeysql/blob/develop/CHANGELOG.md