Clojurians Log v2
honeysql
Single quote is treated inconsistently:
{:create-table-as [(keyword "'`a-b.b-c.c-d`")]
:select [:*]
:from [(keyword "'`a-b.b-c.c-d`")]}CREATE TABLE \"'`a_b\".b_c.\"c_d`\" AS SELECT * FROM `a-b.b-c.c-dCREATE TABLE `a-b.b-c.c-d` AS SELECT * FROM `a-b.b-c.c-dI don’t know MySQL but in BQ, the backtick is quoting for table names and other database references. It is required when the name includes special characters like a dash.
The NRQL dialect looks like it produces the right result for BQ. I can play around with it to see how well it fits more broadly
The main issue is that in HoneySQL foo.bar is split and quoted as "foo"."bar" or foo.`bar` etc but NRQL doesn't have table/column designations like that so foo.bar is just a column name. I thought BQ had table/column designations like other DBs so I'm a bit surprised that you want a-b.b-c rather than a-b.`b-c`?
I see. In BQ, dot serves as a namespace separator for fully qualified names. In BQ, a fully qualified name is project-name.`dataset`.`name`
fwiw, I don’t believe [:. :project :dataset :name] works within :create-table-asbecause honey doesn’t treat the name as a sql expression and, thus, the :. function is not evaluated
Yeah, I try not to support full expressions where normally only entities are needed... it's a difficult balancing act because different DBs have different rules about entity names and expressions and so on. I may revamp some of the entity name handling at some point to try to remove some of the context dependent logic -- which should make it easier to do "weird" stuff for certain databases... but which will also make it "easier" to accidentally generate bad SQL 😐
With great power… 🙂
One possible solution is to support literals (`[:raw ..]`?) for entities instead of full expression support (I haven’t looked into the code so I don’t know if this proposal simplifies the problem)
It’s really just that I need an escape hatch every once in a while
[:raw ..] and [:. ..] are essentially the same "level" in terms of formatting so it's either "just entities" or "full expressions" right now. I'd need to plumb in some "in-between" option and retrofit entity handling (which has its own special cases) on top of that...
I'll leave that issue open while I think about it...
fwiw, my opinion is for full expression support rather than invent a new “level” that only works for entities. It doesn’t add to the cognitive load of the library and it gives me the option of registering expression functions that are appropriate for entities. In effect, these entity expression functions would act as macros.
Thanks for thinking about this
I would certainly like to remove several of the current special cases around this... but I don't want to break anyone's existing code...
Gotcha
@markaddleman Is the backtick meant to represent the stropping (quoting) of identifiers in BigQuery? i.e., like MySQL?
I ask because NRQL has a similar backtick quoting strategy that doesn't split keywords on dots:
(->
{:create-table-as [:a-b.b-c.c-d]
:select [:*]
:from [:a-b.b-c.c-d]}
(sql/format {:dialect :nrql}))["CREATE TABLE `a-b.b-c.c-d` AS SELECT * FROM `a-b.b-c.c-d`"](the :' syntax has a very specific meaning in HoneySQL and it is context dependent so, yes, of course it is going to behave differently in different contexts)