CODEWITHSUNDEEP
google-fusion-tables
prototype
prototype

Reputation: 7970

Fusion Table API bug, not able to handle WHERE clauses with equality on numeric fields?

I'm getting strange results from the FusionTable API. Specifically, it seems unable to handle a simple select statement with equality constraints on numeric values. Any query I try of the following form:

SELECT COUNT() FROM 1Nynh5pPrj1q8JqbalppAm-qzAsgKvL0ZRala7VI WHERE AGE=41

yields zero records:

{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "count()"
 ],
 "rows": [
  [ "0" ]
 ]
}

By contrast, a range constraint works fine:

SELECT COUNT() FROM 1Nynh5pPrj1q8JqbalppAm-qzAsgKvL0ZRala7VI WHERE AGE>40.99 AND AGE<41.01

{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "count()"
 ],
 "rows": [
  [  "362" ]
 ]
}

Maybe the numbers underneath aren't integers? SELECT AGE FROM 1Nynh5pPrj1q8JqbalppAm-qzAsgKvL0ZRala7VI WHERE AGE>40.99 AND AGE<41.01 returns

{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "AGE"
 ],
 "rows": [
  [ "41" ],
  [ "41" ],
  [ "41" ],
  ...359 more...
]}

Now, maybe there's some floating point representation error going on? I thought that small integers can be represented exactly as floats (even if some decimal fractions, e.g. 0.1, are repeating decimals in binary).

It seems unlikely that a bug in Fusion Table SQL would get by without being discovered by others, so perhaps it's there's something unique to how this particular FusionTable is loaded?

UPDATE:

While the query appears to fail using the new Fusion Table API above, it succeeds using the old Fusion Table SQL API (recently deprecated): www.google.com/fusiontables/api/query?sql=SELECT%20COUNT()%20FROM%204579147%20WHERE%20AGE%20LIKE%2041

which returns this JSON response:

count()
362

Also, the new FusionTable API appears confused by numeric values:

Upvotes: 0

Views: 246

Answers (2)

Rod McChesney
Rod McChesney

Reputation: 261

This is a recently introduced bug that will be fixed shortly. As described it does only affect numeric equality queries with aggregation. Sorry for the inconvenience!

Upvotes: 1

geocodezip
geocodezip

Reputation: 161374

There is nothing wrong with AGE = 41 in that table: https://www.google.com/fusiontables/DataSource?snapid=S580613IY6U

Something about the count() is causing the query to fail

Upvotes: 1

Related Questions