You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2020/05/18 20:26:53 UTC
[GitHub] [incubator-superset] esilver opened a new issue #9836: Filter generated alias cause bigquery error: 400 Unrecognized name
esilver opened a new issue #9836:
URL: https://github.com/apache/incubator-superset/issues/9836
When using a chart filter on a nested Big Query field, for example, field.field_name with the `like` operator %name%, superset will generate an alias in the `WHERE` clause of the query, which will lead to a syntax error:
`bigquery error: 400 Unrecognized name: field_field_name_0b211 `
### Expected results
Superset chart returns filtered result
### Actual results
Error:
`bigquery error: 400 Unrecognized name: field_field_name_0b211 `
#### How to reproduce the bug
1. Go to a chart
2. Click on query filter at the bottom of a chart
3. Select the like operator on a nested Bigquery field
4. run the query
5. See error
### Environment
- superset version: `0.36.0`
- python version: `3.6`
- node.js version: `12`
- npm version: `6.12`
### Checklist
Make sure these boxes are checked before submitting your issue - thank you!
- [x] I have checked the superset logs for python stacktraces and included it here as text if there are any.
- [x] I have reproduced the issue with at least the latest released version of superset.
- [x] I have checked the issue tracker for the same issue and I haven't found one similar.
### Additional context
Using the community docker image (https://hub.docker.com/r/amancevice/superset/tags
)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #9836: Filter generated alias cause bigquery error: 400 Unrecognized name
Posted by GitBox <gi...@apache.org>.
villebro commented on issue #9836:
URL: https://github.com/apache/incubator-superset/issues/9836#issuecomment-630901283
Researching this further turned this up:
> Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.
https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html
So it appears we should remove the alias from WHERE clauses by default. Will post a PR shortly.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #9836: Filter generated alias cause bigquery error: 400 Unrecognized name
Posted by GitBox <gi...@apache.org>.
villebro commented on issue #9836:
URL: https://github.com/apache/incubator-superset/issues/9836#issuecomment-630883421
IMO this is a bug in the BigQuery dialect, and should optimally be fixed there, as I see no reason why aliases can be used for everything else except WHERE clauses. But I don't expect Google to respond quickly to a request to change their dialect, so we're probably left with the option of either opening a PR on `pybigquery` to ignore the alias on WHERE clauses or ignoring the alias for WHERE clauses in Superset. As the latter is fairly simple to do and can be fixed quickly, I'd almost propose doing that and leaving a `TODO` in the code to check back from time to time if it has been fixed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] stale[bot] closed issue #9836: Filter generated alias cause bigquery error: 400 Unrecognized name
Posted by GitBox <gi...@apache.org>.
stale[bot] closed issue #9836:
URL: https://github.com/apache/incubator-superset/issues/9836
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #9836: Filter generated alias cause bigquery error: 400 Unrecognized name
Posted by GitBox <gi...@apache.org>.
villebro commented on issue #9836:
URL: https://github.com/apache/incubator-superset/issues/9836#issuecomment-630577399
Thanks @esilver , I have not used nested fields on BigQuery with Superset, hence this has not come up in my testing. To make it easier to reproduce, can you shed some additional context on what the table metadata looks like in Superset (=how the nested fields are defined as columns on the table), and/or how the filter was defined?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] esilver commented on issue #9836: Filter generated alias cause bigquery error: 400 Unrecognized name
Posted by GitBox <gi...@apache.org>.
esilver commented on issue #9836:
URL: https://github.com/apache/incubator-superset/issues/9836#issuecomment-630930332
Nice find! Thanks for the quick response!
One more thing to consider is the way superset changes nested records from `field.field_name` to `field_field_name` with the underscore, which is why it applies the alias in the first place. Not sure how this plays out after removing the alias.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] stale[bot] commented on issue #9836: Filter generated alias cause bigquery error: 400 Unrecognized name
Posted by GitBox <gi...@apache.org>.
stale[bot] commented on issue #9836:
URL: https://github.com/apache/incubator-superset/issues/9836#issuecomment-660521645
This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue `.pinned` to prevent stale bot from closing the issue.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #9836: Filter generated alias cause bigquery error: 400 Unrecognized name
Posted by GitBox <gi...@apache.org>.
villebro commented on issue #9836:
URL: https://github.com/apache/incubator-superset/issues/9836#issuecomment-630968277
BigQuery is very picky about characters in the column name, and if memory serves me well, does not allow for periods in the alias. Therefore the a snippet of the MD5 hash of the original column name is added to the end of mutated column names to minimize collision risk. So the name mutation has to take place.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #9836: Filter generated alias cause bigquery error: 400 Unrecognized name
Posted by GitBox <gi...@apache.org>.
villebro commented on issue #9836:
URL: https://github.com/apache/incubator-superset/issues/9836#issuecomment-630795267
I was able to reproduce the problem, will try to figure out a good solution.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [incubator-superset] villebro commented on issue #9836: Filter generated alias cause bigquery error: 400 Unrecognized name
Posted by GitBox <gi...@apache.org>.
villebro commented on issue #9836:
URL: https://github.com/apache/incubator-superset/issues/9836#issuecomment-630885901
Actually, this is what is said in the official docs:
> You cannot reference column aliases from the SELECT list in the WHERE clause.
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#where_clause
So it's a feature, not a bug™️, and therefore the alias should be ignored by `pybigquery`.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org