Issues with IGNORE CASE query on a table and using Allow large results
I need case insensitivity in my queries, so I found IGNORE CASE
that works great when used in queries that target the browser (I'm talking about the BQ web interface). If I select the destination table (an absolute commitment for me) and select Allow Large Results
(with unchecked Flatten Results
) I get a cryptic error like:
Error: Unexpected LIMIT clause: 2.200 - 2.206
Even though this official Google BigQuery post and feature response tracking seems to be talking about the same issue, and while the issue seems to have been confirmed back in January 2015, the solution isn't clear.
I could use a bunch of bottom search column temporary tables as a workaround, but that sounds awfully complicated with the number of tables and columns I have and the complex queries I intend to run.
Any other possible workarounds? Why doesn't this work for BQ yet?
source to share
Yes, this is a known issue and has not been ignored. The code changes to fix it is (surprisingly) not trivial, but they mostly get executed. Not a team is looking closely at how to enable and deploy them. I can't give you a graph, but a problem with this problem will come.
The only workarounds at the same time are to wrap all comparisons of strings, GROUP BY strings, and ORDER BY strings, and convert them to LOWER () (or UPPER ()) operands.
source to share