honeysql 2021-12-15 | Slack Archive

dekel06:12:52

Hi, I believe there is a bug around boolean equality comparisons for nullable columns but I just wanted to make sure this is unintended before I opened an issue. I believe the query it generates should use IS NOT instead of <> . Example: Say I have the following table and data:

create table testing(
    id INT PRIMARY KEY,
    yesno BOOLEAN
);

INSERT INTO testing (id, yesno)
		values(1, TRUE), (2, FALSE), (3, NULL);

Say I want to filter based on the yesno column:

(-> (hsql/format {:select [:*]
                  :from [:testing]
                  :where [:is-not :yesno true]})
    (first))
=> "SELECT * FROM testing WHERE yesno <> TRUE"

The problem is that SELECT * FROM testing WHERE yesno <> TRUE would only return the row with ID = 2, whereas I would expect that query to return 2 and 3. In contrast, select * from testing where yesno is not true does return both 2 and 3. The query returns the same result (i.e. only id=2) if I used [:not [:is :yesno true]]. I’m using honeysql 2.1.833 and Postgres 13.2.

seancorfield06:12:56

@dekelpilli Interesting. I'd have to check how that work on other databases. My experience is that WHERE col <> someValue doesn't return NULL matches... Can you open an issue on GitHub so I don't forget to look at this?

👍 1

dekel06:12:12

Created https://github.com/seancorfield/honeysql/issues/374

seancorfield06:12:16

Given all the log4j craziness, which is still ongoing, it might be a few days before I get to this. Just setting expectations.

👍 1

athomasoriginal14:12:39

In peoples experience, what kind of rule of thumb do you apply when extending honeysql V2. For example, I have a scenario (postgres) where I have the following hsql query:

[[[:json_agg [:jsonb_build_object "alias" :column ...]] "alias_name"]]

The above gets a bit verbose after a while and easy to make mistakes. Two options I am exploring

;; option 1 - a function
(defn as-josn-object [m] [:json_agg [:json_agg :jsonb_build_object "alias" :column ...]])

[(as-json-array-of-object ["alias" "name"]) "alias_name"]] 

;; option 2 - extending honeysql
(defn register-fn :json-array-of-objects (fn [m] ....))

[[:json-array-of-objects ["alias" "name"]] "alias_name"]

I am curious which people prefer? (sorry if there are syntax errors, I was going quick)

orestis20:12:06

I prefer the functions

orestis20:12:46

They can be much more clever and descriptive with proper args, docstrings etc

🙏 1

2021-12-15

Channels