I don't think this can be done in SqlRexConvertlet, which converts SqlNode to RexNode. You might need to massage the SqlToRelConverter to create the RelNode that you want.
BTW, I still think we need RexNode for IN/ANY. - Haisheng ------------------------------------------------------------------ 发件人：Peter Wicks (pwicks)<[email protected]> 日 期：2019年10月04日 04:03:51 收件人：[email protected]<[email protected]> 主 题：RE: [EXT] Re: SqlRexConvertlet that Replicates "IN" Conversion Logic Haisheng, Yes, that is what I would like to do. Unfortunately, I’m not sure how to proceed to actually do that. I was hoping for a pointer to an example that is similar? Thanks! Peter From: Haisheng Yuan <[email protected]> Sent: Thursday, October 3, 2019 1:35 PM To: Peter Wicks (pwicks) <[email protected]>; [email protected] Subject: [EXT] Re: SqlRexConvertlet that Replicates "IN" Conversion Logic Currently Calcite doesn't have IN RexNode, only has IN SqlNode, unfortunately. You can create a Values node with these authorization data, and make a semi join with the table and Values you created. - Haisheng ------------------------------------------------------------------ 发件人：Peter Wicks (pwicks)<[email protected]<mailto:[email protected]>> 日 期：2019年10月04日 02:34:02 收件人：[email protected]<[email protected]<mailto:[email protected]%[email protected]>> 主 题：SqlRexConvertlet that Replicates "IN" Conversion Logic A little detail about what I'm trying to do: I have an external API that contains authorization information on a per user basis. I want users to be able to include an operation in their query that will filter data based on this authorization data. Using Calcite v1.16 / Java 1.8 / RHEL7, I built a class that implements SqlRexConvertlet, and I am able to get this working. The user includes in their predicate statement `custom_authorize(column)`, my convertlet queries the API, gets the authorization rules, builds an OR statement, and the results come back. This works sometimes, but other times the OR condition becomes too large, and I run into CALCITE-2792: https://issues.apache.org/jira/browse/CALCITE-2792, which causes a stackoverflow and my query dies. So I tried converting to an IN statement, having read that IN statements are automatically converted to a sub query join when the default limit of 20 is exceeded. The problem is that this appears only to be true for IN statements that are included in the initial query. IN statements created as the result of a convertlet do not get modified, and are sent as an IN statement, which results in a failure to parse the query. I looked at how Calcite normally does this translation from IN to exists using a join, but it depends on a lot of classes/instances that aren't available in the SqlRexContext space. Is it possible to rewrite my IN statement to a join/exists query like Calcite normally does? Also, am I doing things all wrong? Is there a better way to go about this? Code Sample below is for the OR version, the commented code can be swapped in to see how I was building the IN statement. @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) { HashSet<String> keyList = null; try { keyList = new Manager().getAllowedIDs(getContextInformation().getQueryUser()); } catch (SQLException e) { e.printStackTrace(); } final RexBuilder rexBuilder = cx.getRexBuilder(); final RexNode column = cx.convertExpression(call.operand(0)); final List<RexNode> nodes = new ArrayList<>(); for(String s: keyList) { nodes.add(rexBuilder.makeCall(EQUALS, column, rexBuilder.makeLiteral(s))); //nodes.add(rexBuilder.makeLiteral(s)); } final RexNode in = rexBuilder.makeCall(SqlStdOperatorTable.OR, nodes); //final RexNode in = inBuilder(rexBuilder, column, nodes.toArray(new RexNode[0])); return in; } protected RexNode inBuilder(RexBuilder rexBuilder, RexNode node, RexNode... nodes) { return rexBuilder.makeCall(SqlStdOperatorTable.IN, ImmutableList.<RexNode>builder().add(node).add(nodes).build()); } Thanks, Peter
