Limitations on number type and value_format

[PaulM1]

The Looker version we are currently on is 7.18.21

Are there inherent limitations when using number and/or value_format?

The following is a quick and dirty example. But this occurs even when the data is pulled directly from the database column.

  dimension: foo1 {
    type: number
    value_format_name: decimal_2
    # value_format: "#,##0.00"
    sql: 111111111111111.11 ;;
  }

  dimension: foo2 {
    type: number
    value_format_name: decimal_2
    # value_format: "#,##0.00"
    sql: 1111111111111111.11 ;;
  }

  dimension: foo3 {
    type: number
    value_format_name: decimal_2
    # value_format: "#,##0.00"
    sql: 11111111111111111.11 ;;
  }

  dimension: foo4 {
    type: number
    value_format_name: decimal_2
    # value_format: "#,##0.00"
    sql: 111111111111111111111.11 ;;
  }

  dimension: foo5 {
    type: number
    value_format_name: decimal_2
    # value_format: "#,##0.00"
    sql: 1111111111111111111111.11 ;;
  }

That code produces the following results:

[izzymiller]

That is… really weird. I see the same thing happening on every website that lets you test these excel-style number formats so this looks like an issue with the underlying formatting system, which excel and google sheets use too. Super interesting… I guess there must be a max number size. Do you run queries with numbers this large often, or is this just an interesting irregularity you discovered and are curious about? 

[PaulM1]

Do you run queries with numbers this large often, or is this just an interesting irregularity you discovered and are curious about? 

I don’t think anyone here has ever run a query on a number that large. And most of our numerical fields are capped well below that. But I found this issue while flattening out an EAV model in our database. All the data is saved as NVARCHAR() and the UI doesn’t limit the user’s numerical inputs. To get the numerical data to Looker I am using a Snowflake view that uses a TRY_TO_NUMBER(<value>,38,2). Someone just happened to put in a couple outrageously large numbers, and that’s when Looker choked.

[izzymiller · Accepted solution]

Cool, good to know that it’s not affecting any regular querying. We’ll take a look, though it may be a tough nut to crack given that it’s happening in the underlying excel number format libraries too. Thanks!

[Dmitri_S]

@izzymiller @PaulM1 It's also reproducible for explore output.

More specific - if you have dimension with type: number and huge integer values over there(example - ids) then output values would be truncated.

Example:  7281392929946619167 -> 7281392929946619000

And then, if you would try to filter by this value using Looker standard functionality it won't return any results.

So, it makes sense as for me to add an additional extended numeric type or so into Looker. WDYT?

P.S. current workaround - set type: string, but I'm not sure it would fit to all dialects