IGNORE CASE查询将问题保存到表中并使用“允许大量结果” [英] IGNORE CASE query problems saving to a table and using Allow large results

问题描述

我的查询需要不区分大小写，所以我找到了 IGNORE CASE ，它在针对浏览器的查询（我正在谈论BQ Web UI）中使用时非常出色。如果我选择一个目标表（绝对必须适用于我）并选择允许大型结果（未选中展平结果）然后我得到一个像这样的神秘错误：
$ b

错误：意外的LIMIT子句在：2.200 - 2.206

即使官方的Google BigQuery问题和功能请求跟踪器帖子似乎也提到了同样的问题，尽管问题似乎在2015年1月得到了承认，但解决方案并不明显。

我可能会使用一堆带有小写搜索列的临时表作为解决方法，但对于我拥有的表和列的数量以及我打算运行的复杂查询，这听起来非常困难。 / p>

其他任何可能的解决方法？为什么在BQ上这个工作还没有完成？

解决方案

是的，这是一个已知的问题，并没有被忽略。代码修改是为了解决这个问题（令人惊讶的是）并不重要，但它们大部分完成了。不是团队正在仔细寻找如何启用和部署它们。我不能给你一个时间表，但这个问题的解决方法即将到来。

在此期间唯一的解决方法是包装所有字符串比较，字符串GROUP BY和字符串ORDER BY转换为操作数的LOWER（）（或UPPER（））。

I need case insensitivity in my queries so I found IGNORE CASE which works superbly when used in queries that target the browser (I am talking about BQ web UI). If I choose a destination table (an absolute must for me) and select Allow Large Results (with unchecked Flatten Results) then I get a cryptic error like this:

Error: unexpected LIMIT clause at: 2.200 - 2.206

Even though this Official Google BigQuery issue and feature request tracker post seems to speak of the same issue and even though the problem seems to have been acknowledged back in Jan 2015 the solution isn't apparent.

I could potentially use a bunch of temp tables with lowercased search columns as a workaround but that sounds awfully difficult with the number of tables and columns that I have and the complex queries that I intend to run.

Any other possible workarounds? Why isn't this working yet on BQ?

解决方案

Yes, it is a known problem, and it has not been neglected. The code changes to fix it are (surprisingly) not trivial, but they are mostly done. Not team is carefully looking how to enable and deploy them. I cannot give you a timeline, but the fix to this problem is coming.

The only workarounds in the meantime, are to wrap all the string comparisons, string GROUP BYs and string ORDER BYs with conversion to LOWER() (or UPPER()) of operands.

这篇关于IGNORE CASE查询将问题保存到表中并使用“允许大量结果”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！