'False' values for boolean field show up as NULL in SQL Runner and query results


Userlevel 2

I’m having an issue that I traced to this unexpected (by me) behavior: ‘False’ values for boolean fields show up as NULL in SQL Runner, and of course in query results as well.


Here’s a simple example. I made a very simple table, seen here as queried from Postgres’s command line client:


=> select bar, count(*) from foo group by bar;

bar | count
-----+-------
| 1
f | 1
t | 1
(3 rows)

You can see there is one NULL, one False, and one True.


Now run that same SQL in SQL Runner:


Results
Query returned 3 rows in 0.003s

bar count
∅ 1
∅ 1
true 1

So that’s weird. Why is Looker declaring False values to be NULL? And then, why isn’t it grouping those NULL values together?


12 replies

Userlevel 2

FWIW I made a simple explore view on that table, with this LookML:


- view: foo
fields:

- dimension: bar
type: yesno
sql: ${TABLE}.bar

- dimension: bar_untyped
sql: ${TABLE}.bar

- measure: count
type: count
drill_fields: []

Note I added bar_untyped to get the raw value of bar. A query using all fields generates this SQL:


SELECT 
CASE WHEN foo.bar THEN 'Yes' ELSE 'No' END AS "foo.bar",
foo.bar AS "foo.bar_untyped",
COUNT(*) AS "foo.count"
FROM foo

GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500;

And you get essentially the same results:


Results
Query returned 3 rows in 0.003s

foo.bar foo.bar_untyped foo.count
Yes true 1
No ∅ 1
No ∅ 1
Userlevel 6
Badge

Thanks @herdrick


Wow, sounds like a bug. What dialect are you using? We’ll take a look. Looks like we can’t differentiate the false and NULL in a result set.

Userlevel 2

I’m on Looker 3.18, Postgres 9.2. OK, glad to hear it, thanks!

@herdrick Thanks for letting us know about this!


It has been fixed and the fix will be available in 3.20.

Userlevel 2

OK, great, thanks.

Userlevel 2

Hi folks - this is still happening.


It’s generating this SQL:


SELECT 
CASE WHEN foo.bar THEN 'Yes' ELSE 'No' END AS "foo.bar",
foo.bar AS "foo.bar_untyped",
COUNT(*) AS "foo.count"
FROM foo

GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

I’m on Version 3.26.16.

Userlevel 3

Apologizes Ethan, looks like we thought you referring to a slightly different issue (this except specifically in SQL Runner) when we said the fix was in 3.20.

We have filed this as a separate but related issue with engineering and they are taking a look at it as I type!


Thanks for alerting us to the issue!

Userlevel 2

Ah yes, sorry, this is a different issue.


Any news on this? It’s still happening for me.

Userlevel 5
Badge

Hey Ethan - engineering is still working on this. For more context, part of the reason this is happening is because Looker does not have a type: boolean, so boolean values that are not a yesno are not fully supported. I would recommend using either sql_case or CASE WHEN to get this dimension to return strings in the form of ‘true’ and ‘false’ in the mean time.

Userlevel 2

Hi Lindsey,


The issue I posted about on Sept 1 is different. It’s sort of the inverse of the original issue of this thread.


Here’s this issue: NULL values in yesno fields are showing up as ‘No’. I would have expected them to show up as ∅. So, given this table:


dod=> select * from foo;

bar


t

f

(3 rows)


… and the SQL that Looker generates for a yesno field:


SELECT

CASE WHEN foo.bar THEN ‘Yes’ ELSE ‘No’ END AS “foo.bar”,

foo.bar AS “foo.bar_untyped”,

COUNT(*) AS “foo.count”

FROM foo


GROUP BY 1,2

ORDER BY 3 DESC

LIMIT 500

;


… you get this:


foo.bar | foo.bar_untyped | foo.count

---------±----------------±----------

No | | 1

Yes | t | 1

No | f | 1

(3 rows)


Does that make sense? I’m guessing that this is by design. But it isn’t consistent with SQL, where casting NULL to boolean yields NULL.


Thanks!

Userlevel 5
Badge

This is indeed by design. yesno fields will always only return Yes or No (NULL will never be an option). The SQL you specify in the sql paramter of a yesno is the qualifier for Yes, and all other rows evaluate to No.


If you would like a yesno field that also returns nulls, I would recommend writing your own CASE WHEN that has three possible cases: Yes, No, and NULL

Userlevel 2

OK. I must say that I don’t agree with this design decision. It makes for more confusing reports, and I’m not sure what the benefits of it are. But I understand that my perspective is much more limited than that of you folks.

Reply