Incorrect 'in not empty filer' for oracle

Bug reports
1

Hi, I've met a problem with version 0.39.2
Visual editor 'is not empty filter'

image
image438×537 13.3 KB

generate the following sql
image
Oracle treats '' as NULL, so expression something <> '' is trivial false due to Oracle's rule that nothing can be equal or not equal to NULL.

Diagnostic info:
{
"browser-info": {
"language": "en-gb",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.0.3 Safari/605.1.15",
"vendor": "Apple Computer, Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_275-b01",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "1.8.0_275",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.275-b01",
"os.name": "Mac OS X",
"os.version": "10.16",
"user.language": "en",
"user.timezone": "Europe/Moscow"
},
"metabase-info": {
"databases": [
"h2"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.39.2-SNAPSHOT",
"date": "2021-04-27",
"branch": "?",
"hash": "6beba48"
},
"settings": {
"report-timezone": null
}
}
}

2

Hi @Max.A
You're seeing this issue:
https://github.com/metabase/metabase/issues/13158 - upvote by clicking :+1: on the first post

3

Hi @flamber
Yes, its my case. Upvoted.
By the way, the last post in that topic, makes me fill that you probable incorrectly indentify rootcause.
For Oracle:
WHERE ("PUBLIC"."PRODUCTS"."CATEGORY" IS NOT NULL AND "PUBLIC"."PRODUCTS"."CATEGORY" <> '') is always empty set
"PUBLIC"."PRODUCTS"."CATEGORY" <> '' is always false.

4

@Max.A Okay, then leave a comment with what you think should be the correct query for "Not Empty".

5

for string type it should be
where "PUBLIC"."PRODUCTS"."CATEGORY" IS NOT NULL
Oracle treats any zero length string as NULL

6

@Max.A So Oracle doesn't allow empty strings. Interesting. Definitely not my favorite database.
But leave the comment on the issue.

7

Hi @flamber
It looks that problem is related to query_proccessor/ correct-null-behaviour.

(defn- correct-null-behaviour
[driver [op & args]]
(let [field-arg (mbql.u/match-one args
FieldInstance &match
:field &match)]
;; We must not transform the head again else we'll have an infinite loop
;; (and we can't do it at the call-site as then it will be harder to fish out field references)
[:or (into [op] (map (partial ->honeysql driver)) args)
[ := (->honeysql driver field-arg) nil]]))
1.:= used for "=" and "!=" , I guess should be somthig like this
[op (->honeysql driver field-arg) nil]]))
2. it looks like correct-null-behaviour executed for any expression.
image
generates sql
image
Not error, but looks a bit strange

8

@Max.A That's because many databases will not return null rows if you column <> 'test'
You are welcome to submit a PR or leave a comment on the issue.