10

I am building dynamic sql generator at work and have difficulty with union queries. When i run the following query: 

SELECT NULL AS field;

the column type is BOOL by default and it can be used on union with integer types columns as follows:

SELECT  field
FROM 
    (SELECT 21 AS field),
    (SELECT NULL AS field)

this returns successful result. But when I run similar query with string type on one subquery, it fails Here is the query failing with Cannot union tables : Incompatible types. 'field' : STRING 'field' : BOOL:

SELECT  field
FROM
    (SELECT 'hello' AS field),
    (SELECT NULL AS field)

What is the best way to infer types and cast null values. My sql generator should work with several data types on union and I thought null can be used with all types. if null can not be used with string and some other types, is there any 'super' type that can be used to replace null fields?

koPytok
  • 3,453
  • 1
  • 14
  • 29
Sherali Obidov
  • 119
  • 1
  • 2
  • 9
  • cast(null as desireddatatyp)? – jarlh Jun 18 '18 at 13:39
  • the desired data type can be any type. i cannot find out types of all columns to cast null when generating sql ) . – Sherali Obidov Jun 18 '18 at 13:43
  • Even a null value must have a specified data type, either explicit or implicit. – jarlh Jun 18 '18 at 13:50
  • I'm not familiar with BigQuery, but is that actually how you write a `UNION` statement in that language? That seems more like an older comma-syntax `JOIN` rather than a `UNION`. Regardless, if you can `CAST` the `NULL` value, then do it. I'm not sure how it can be "any" datatype. Some shouldn't be compatible with each other. EDIT: If you have `string`s in your data, `CAST()` both `field` values as a `string` type. – Shawn Jun 18 '18 at 13:52
  • @Shawn I forgot to mention that this is legacy sql in which comma-separated sub queries can be used as union. Datatypes can be different based on user input that states what tables and columns to select from. – Sherali Obidov Jun 18 '18 at 14:01
  • Why use legacy SQL instead of standard SQL? If you enable standard SQL in BigQuery, this should work if you use UNION ALL instead of the comma operator. – Elliott Brossard Jun 18 '18 at 14:21
  • @ElliottBrossard Could you post your comment as an answer, please? – Rubén C. Jun 22 '18 at 16:20
  • It may not be a useful answer since the OP was trying to write a query using legacy SQL. – Elliott Brossard Jun 22 '18 at 17:34
  • @ElliottBrossard, thanks for your comment. It made it clear and I decided to change legacy query to standart sql – Sherali Obidov Jun 25 '18 at 21:43

1 Answers1

29

BigQuery assigns NULL values a type, which by default is an integer. To get a string NULL, convert:

cast(null as string)

I don't know how the generator works, but you might be able to arrange a query that refers to a properly casted value:

with params as (
      select cast(null as string) as null_str
     )
select null_str
from params cross join
     . . .
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786