Question

Integer precision in type: number

  • 10 July 2018
  • 2 replies
  • 541 views

We have a series of UIDs that are seventeen digits long, stored as bigint in our MySQL tables. When surfaced in Looker as type: number, these UIDs are only precise up to 16 significant figures when displayed, e.g. 76543210987654321 gets truncated to 76543210987654320. We are currently solving this by displaying the UIDs as strings (i.e. declaring type: string or not declaring any type). If two distinct UIDs were sufficiently close as to be equal after rounding to 16 significant figures, would these be aggregated when displayed in a table in Looker? Would it affect any joins on these UIDs if we use the field reference (e…g ${uid}) in our join condition?


2 replies

Userlevel 4
Badge

@michael.king - the limitation will happen at around 16-17 digits and it will round the additional integers into zeros, due to a precision limit of javascript. A potential workaround is by using Liquid’s {{ rendered_value }}. Do note that this is an issue with rendering the number only, not with calculating, since that’s done on the db side.



dimension: id { 

primary_key: yes

type: number

sql: ${TABLE}.id ;;

html: {{ rendered_value }} ;;

}



Or (as I imagine you’re currently doing it), by using two dimensions:



dimension: string_value {

type: string

sql: ${TABLE}.myfield ;;

order_by_field: int_value

}



dimension: int_value {

type: number

sql: ${TABLE}.myfield ;;

}

I see, understood. Thank you for confirming that this is only a display issue, and not a calculation one.

Reply