StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFusion Table API bug, not able to handle WHERE clauses with equality on numeric fields?
text
Body
copied!<p>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:</p> <p><a href="https://www.googleapis.com/fusiontables/v1/query?sql=SELECT+COUNT%28%29+FROM+1Nynh5pPrj1q8JqbalppAm-qzAsgKvL0ZRala7VI+WHERE+AGE%3D41&key=AIzaSyBT_ZCriV-Tm78KsJiQp6KKhjDwMhP0tYM" rel="nofollow">SELECT COUNT() FROM 1Nynh5pPrj1q8JqbalppAm-qzAsgKvL0ZRala7VI WHERE AGE=41</a></p> <p>yields zero records:</p> <pre><code>{ "kind": "fusiontables#sqlresponse", "columns": [ "count()" ], "rows": [ [ "0" ] ] } </code></pre> <p>By contrast, a range constraint works fine:</p> <p><a href="https://www.googleapis.com/fusiontables/v1/query?sql=SELECT+COUNT%28%29+FROM+1Nynh5pPrj1q8JqbalppAm-qzAsgKvL0ZRala7VI+WHERE+AGE+%3E40.99+AND+AGE%3C41.01&key=AIzaSyBT_ZCriV-Tm78KsJiQp6KKhjDwMhP0tYM" rel="nofollow">SELECT COUNT() FROM 1Nynh5pPrj1q8JqbalppAm-qzAsgKvL0ZRala7VI WHERE AGE>40.99 AND AGE<41.01</a></p> <pre><code>{ "kind": "fusiontables#sqlresponse", "columns": [ "count()" ], "rows": [ [ "362" ] ] } </code></pre> <p>Maybe the numbers underneath aren't integers? <a href="https://www.googleapis.com/fusiontables/v1/query?sql=SELECT+AGE+FROM+1Nynh5pPrj1q8JqbalppAm-qzAsgKvL0ZRala7VI+WHERE+AGE%3E40.99+AND+AGE%3C41.01&key=AIzaSyBT_ZCriV-Tm78KsJiQp6KKhjDwMhP0tYM" rel="nofollow">SELECT AGE FROM 1Nynh5pPrj1q8JqbalppAm-qzAsgKvL0ZRala7VI WHERE AGE>40.99 AND AGE<41.01</a> returns</p> <pre><code>{ "kind": "fusiontables#sqlresponse", "columns": [ "AGE" ], "rows": [ [ "41" ], [ "41" ], [ "41" ], ...359 more... ]} </code></pre> <p>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). </p> <p>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 <a href="https://www.google.com/fusiontables/DataSource?docid=1Nynh5pPrj1q8JqbalppAm-qzAsgKvL0ZRala7VI" rel="nofollow">this particular FusionTable</a> is loaded?</p> <p><strong>UPDATE:</strong></p> <p>While the query appears to fail using the new Fusion Table API above, it succeeds using the old Fusion Table SQL API (recently deprecated): <a href="http://www.google.com/fusiontables/api/query?sql=SELECT%20COUNT%28%29%20FROM%204579147%20WHERE%20AGE%20LIKE%2041" rel="nofollow">www.google.com/fusiontables/api/query?sql=SELECT%20COUNT()%20FROM%204579147%20WHERE%20AGE%20LIKE%2041</a></p> <p>which returns this JSON response:</p> <pre><code>count() 362 </code></pre> <p>Also, the new FusionTable API appears confused by numeric values:</p> <ul> <li><p><code>SELECT COUNT() FROM 4579147 WHERE AGE = 41</code> yields <code>0</code> (incorrect)</p></li> <li><p><code>SELECT COUNT() FROM 4579147 WHERE AGE = "41"</code> yields <code>0</code> (incorrect)</p></li> <li><p><code>SELECT COUNT() FROM 4579147 WHERE AGE MATCHES 41</code> yields <code>362</code></p></li> <li><p><code>SELECT COUNT() FROM 4579147 WHERE AGE LIKE 41</code> yields <code>362</code></p></li> <li><p><code>SELECT COUNT() FROM 4579147 WHERE AGE LIKE "41"</code> yields <code>362</code></p></li> <li><p><code>SELECT COUNT() FROM 4579147 WHERE AGE LIKE "%41%"</code> yields <code>362</code></p></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload