Here is some lookml :
view: debug_test_1 {
derived_table: {
sql: SELECT '14d' as id UNION ALL
SELECT '13d' as id ;;
}
dimension: id {
sql: ${TABLE}.id ;;
primary_key: yes
}
}
view: debug_test_2 {
derived_table: {
sql: SELECT '14d' as id, 12345678901234 as v UNION ALL
SELECT '13d' as id, 1234567890123 as v ;;
}
dimension: id {
sql: ${TABLE}.id ;;
primary_key: yes
}
dimension: val {
sql: ${TABLE}.v ;;
type: number
}
measure: val_sum {
type: sum
sql: ${val} ;;
}
}
explore: debug_test_1 {
join: debug_test_2a {
from: debug_test_2
view_label: "Many to One"
sql_on: ${debug_test_1.id} = ${debug_test_2a.id} ;;
relationship: many_to_one
}
join: debug_test_2b {
from: debug_test_2
view_label: "One to Many"
sql_on: ${debug_test_1.id} = ${debug_test_2b.id} ;;
relationship: one_to_many
}
join: debug_test_2c {
from: debug_test_2
view_label: "Many to Many"
sql_on: ${debug_test_1.id} = ${debug_test_2c.id} ;;
relationship: many_to_many
}
}
If you then open that in an explore and pick the id dimension and val sum measure from the "Many to One/Many" it gives an error:
A LookML model issue occurred.
Non-unique value/primary key (or sql_distinct_key), value overflow or collision when computing sum.
If you filter out the 14digit row then it doesn't give an error, or if you use the "One to Many" version it also doesn't give an error. What's odd is that the sql itself seems to run fine in the SQLRunner (or in BigQuery itself).
Is this a bug? If not are there any workarounds (i've tried casting the sum'ed dimension to float and bignumeric but it didn't help).
So Looker support did get back to me on this and it turns out that if you reduce the `precision` on the `val_sum` measure from the default of 6 to 0 decimal places it works ok in the example given.