You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by "egalpin (via GitHub)" <gi...@apache.org> on 2023/09/21 18:23:24 UTC
[GitHub] [pinot] egalpin opened a new issue, #11651: LOOKUP not supported in v2 engine
egalpin opened a new issue, #11651:
URL: https://github.com/apache/pinot/issues/11651
This may be an intentional choice but LOOKUP (dimension table use) does not appear to work in v2 engine. Repro steps are:
- write any query using LOOKUP in v1 engine, validate that it works
- issue the same query to the v2 engine
- note that it fails with a message like:
```
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 2, column 63 to line 2, column 126: No match found for function signature LOOKUP(<CHARACTER>, <CHARACTER>, <CHARACTER>, <NUMERIC>)
at jdk.internal.reflect.GeneratedConstructorAccessor219.newInstance(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
...
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature LOOKUP(<CHARACTER>, <CHARACTER>, <CHARACTER>, <NUMERIC>)
at jdk.internal.reflect.GeneratedConstructorAccessor218.newInstance(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505))
```
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] walterddr commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1740146801
here is a proposal for creating lookup join support in the v2 engine: https://docs.google.com/document/d/1cyFrW21MlcbHEEfl46paKRs6v6qHzwOZbIsnFL7UVnw/edit#heading=h.xdbe7h7na0kx
TL;DR the syntax could look as the following
```
SELECT /*+ joinOptions(join_strategy = ‘lookup’) */
b.playerName, b.teamID, d.teamName
FROM baseballStats as b LEFT JOIN dimBaseballTeams as d
ON b.teamID = d.teamID
```
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] walterddr commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1738093209
based on the draft limitation: https://github.com/apache/pinot/pull/11686 i dont think there's much value to support LOOKUP as a function in v2. basically the limitation means we can't determine the return type unless there's a way to explicitly parse that.
i will propose a different approach to LOOKUP join, one that doesn't require user to understand this Pinot specific dialect
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] Jackie-Jiang commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1734394530
IMO we can just register it and only allow it in leaf stage, similar to other functions specific to v1.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] walterddr commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1734016088
let me dig deeper into the problem. IMO there's 2 aspect of this issue:
1. does multi-stage engine support pre-distributed dim-tables --> i need to double check and see the dispatch logic to handle pre-distributed table
2. does multi-stage engine support LOOKUP syntax --> the answer is no because multi-stage engine supports standard SQL way to express this:
```
SELECT localCol, LOOKUP('foreignTbl', 'foreignCol', 'foreignKey', localKey) FROM tbl
```
is equivalent to
```
SELECT localCol, foreignCol
FROM tbl LEFT JOIN foreignTbl ON localKey = foreignKey
```
3. should we provide an upgrade / backward-compatible transition --> i think it is yes but this, along with SUBQUERY/IDSET and other V1 only functionalities, should be handle systematically.
several ideas i can think of:
a. a standalone query rewriter that translates old syntax to new
b. automatic fallback to v1 syntax (default with v2) until the next release
c. automatic fall-forward to v2 syntax (default with v1) until the next release
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] egalpin commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "egalpin (via GitHub)" <gi...@apache.org>.
egalpin commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1734121849
It's perhaps worth noting that LOOKUP yielded significantly faster results in some cases (depends on the query I'm sure), so if it's possible to keep it as something compatible with v2 engine that would be great to have.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] walterddr commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1736557959
it will likely not work. let me add tests cases to the PR to demonstrate
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] Jackie-Jiang commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1732243631
@walterddr Do you think we need `LOOKUP` in multi-stage engine? For completeness, we might want to just annotate it so that it can be used in multi-stage engine
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] LOOKUP not supported in v2 engine [pinot]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #11651: LOOKUP not supported in v2 engine
URL: https://github.com/apache/pinot/issues/11651
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] LOOKUP not supported in v2 engine [pinot]
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1762238266
automating hints are a much wider topic (getting cost-factory to hook-in, getting table metadata to feed into cost factory etc) and i would like to keep that separate.
but to answer your question, auto setting join to lookup if RHS is dim table doesn't work -->
- there's no guarantee the left side is a normal table, or a join result, or a temp table, etc.
- there's also situation that LHS is a semi-join result following by a LOOKUP, which can apply automatically but can't be determine until we have cost-factory and table metadata feed in.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] walterddr commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1734568705
it is very likely possible but it will only work with v1 and will not work in many of the v2 features.
IMO, we can definitely go down this path by registering an OPAQUE function in v2 and only allow it to run on certain circumstances (similar to ARRAY_TO_MV special logic that only works in v1).
However we should throw explicit warnings and indicate this is going to be deprecated - with the recent warning UI changes: https://github.com/apache/pinot/pull/11611 I think it should be relatively easy for users to comprehend
(assigning to myself)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] walterddr commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1735927087
did a bit of research, it looks like we dont have enough info in v2 to plan the v1 query due to the fact that dim lookup col is not strongly typed and we can't find a way to register the function without a proper return type inference.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] Jackie-Jiang commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "Jackie-Jiang (via GitHub)" <gi...@apache.org>.
Jackie-Jiang commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1736484220
@walterddr Will it throw exception if we pass ANY type into an aggregation such as `SUM`? Or it simply skips the casting?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] walterddr commented on issue #11651: LOOKUP not supported in v2 engine
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1734138861
good point. i think that echos back to the 1st point i mentioned above (e.g. the table is recognized by v2, but it is not considered as pre-distributed table :-( and thus data shuffling will still occur)
@Jackie-Jiang does force the engine think the table is co-partitioned help here? dim-table doesn't have a partition key so i am not sure if this is even possible, we might need a "pre-distributed table abstraction"
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] LOOKUP not supported in v2 engine [pinot]
Posted by "cbalci (via GitHub)" <gi...@apache.org>.
cbalci commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-1762167976
Thanks for the investigation and the proposal @walterddr. 'LOOKUP' as a join strategy makes more sense to me.
I also imagine we could automatically set the join_strategy to 'lookup', if the RHS is a dim table. This would eliminate the need for the hint. What do you think?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] LOOKUP not supported in v2 engine [pinot]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-5723098064
This issue has been automatically marked as stale because it has not had any activity in the last 150 days. It will be closed in 30 days if no further activity occurs. If this issue is still relevant, please leave a comment to keep it open. Thank you for your contributions!
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] LOOKUP not supported in v2 engine [pinot]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #11651:
URL: https://github.com/apache/pinot/issues/11651#issuecomment-6305444264
This issue has been closed because it has been inactive for 180 days. If you believe this issue is still relevant, please feel free to reopen it or create a new issue. Thank you!
--
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.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org