Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

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

H
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

H
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
lloydtabb
Rank
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.

H
Userlevel 2

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

J
Rank

@herdrick Thanks for letting us know about this!


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

H
Userlevel 2

OK, great, thanks.

H
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.

nbeyer
Rank
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!

H
Userlevel 2

Ah yes, sorry, this is a different issue.


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

L
Rank
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.

H
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!

L
Rank
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

H
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


Terms of Service