It’s worth separating this into (a) what the parser should do (i.e. how we represent this in SqlNode land) and (b) how we represent this in RexNode/RelNode land.
I think the parser should generate some flavor of SqlDynamicParam. Currently these only have ‘int index’ but we’d need to hold old/new as a boolean and “somecolumn” as a string. In RexNode land I think these should remain RexDynamicParam with an int index. There would be some mapping table in the statement saying that old.somecolumn should be bound into RexDynamicParam #0, etc. I believe this is what Oracle’s PL/SQL pre-processor does. SqlToRelConverter would need to be changed in order to map between these representations. Julian > On Feb 18, 2020, at 11:15 AM, Christian Beikov <[email protected]> > wrote: > > Hey Stamatis, > > thanks for your help. > > Am 18.02.2020 um 19:34 schrieb Stamatis Zampetakis: >> Hi Christian, >> >> Long story short: maybe you can achieve what you want by adding a new type >> of RexNode. From what I remember quoting is applied to identifiers (schema, >> table, column names) so maybe if OLD/NEW are parameters (e.g., >> RexNamedParam) then possibly it does not make sense to quote them. > Right, and actually it is wrong to quote these variables/parameters i.e. it's > an error as PG complains there is no relation with that name. >> At row level triggers OLD and NEW refer to a single tuple/row at each point >> in time. In this case, OLD and NEW are row type variables, or better say >> parameters, with the same type as the table. In Calcite, there are >> index-based parameters (RexDynamicParam) but not named parameters as the >> one you seem to need. I think named parameters are useful in various >> scenarios so possibly we could expand the RexNode hierarchy. >> In this case to build the plan probably it suffices to create and plug the >> parameter wherever you need. > That sounds great and just about what I would need. Is there an issue for > this already? >> At statement level triggers OLD and NEW refer to set of tuples/rows at each >> point in time. In this case, OLD and NEW appear as (temporary) >> relations/tables with the same type as the table. In terms of >> implementation, I assume that the user defined query acts as a subquery >> correlated with OLD/NEW as necessary. > Correct, but right now I am using row level triggers. I'll try to introduce > somekind of synthetic view that holds the state so I can use a TransientScan > for now. >> In this case to build the plan probably you need to introduce scan >> operations over OLD/NEW tables and create a correlation with the rest of >> the query. > As of PG10 the REFERENCING clause can be used to introduce a temporary view > for an old and new relation. >> Best, >> Stamatis >> >> On Tue, Feb 18, 2020 at 7:03 PM Christian Beikov <[email protected]> >> wrote: >> >>> My issue is not about parsing. I already have the relational model, I >>> parsed a query to which I want to add add a condition to a RelNode. Now I >>> want to add a RexNode to the LogicalFilter node that renders to: >>> NEW."somecolumn" >>> >>> How would I construct a RexNode that renders to that when converting the >>> RelNode to SQL. Do I have to extend the SqlDialect to support that? >>> >>> Danny Chan <[email protected]> schrieb am Di., 18. Feb. 2020, 15:12: >>> >>>> If you want to make NEW a normal sql identifier, you should override it >>> in >>>> the parser to make it unreserved. >>>> >>>> Christian Beikov <[email protected]>于2020年2月18日 周二下午3:11写道： >>>> >>>>> Hey Danny, >>>>> >>>>> it's not a view, it's a variable in PL/SQL with a row type. The thing >>>>> is, variable names must not be quoted, but I have no idea how to avoid >>>>> quoting for this single use case with the relational algebra model in >>>>> Calcite. >>>>> >>>>> Regards, >>>>> >>>>> Christian >>>>> >>>>> Am 18.02.2020 um 04:22 schrieb Danny Chan: >>>>>> From the case you gave, the “variable” seems a view ? Sorry I’m not >>>>> familiar with the traditional RDBMS. >>>>>> Best, >>>>>> Danny Chan >>>>>> 在 2020年2月17日 +0800 PM1:27，Christian Beikov < >>> [email protected] >>>>>> ，写道： >>>>>>> Hello, >>>>>>> >>>>>>> I asked this before but I guess the question got too big, so I >>> thought >>>>>>> splitting it up might be better. >>>>>>> >>>>>>> I am trying to generate a query from a relational model on which I >>>> did a >>>>>>> few tranformations but I don't know how to refer to a "variable". >>>>>>> >>>>>>> In a SQL trigger, there usually are two variable "OLD" and "NEW" >>>> which I >>>>>>> want to be able to refer to. I tried introducing a "transient scan", >>>> but >>>>>>> unfortunately that won't work because this is not a relation and >>> can't >>>>>>> be quoted. I will workaround this for now by introducing a temporary >>>>>>> relation in the trigger so that I can refer to it, but ideally I >>> want >>>> to >>>>>>> refer to the variable directly. >>>>>>> >>>>>>> The simplest example SQL that I want to be able to produce would >>> look >>>>>>> like this: >>>>>>> >>>>>>> select NEW."some_column" >>>>>>> >>>>>>> The tricky part here is that NEW is not quoted. I don't know how I >>> can >>>>>>> represent this in a relation expression. >>>>>>> >>>>>>> Thanks in advance for any help! >>>>>>> >>>>>>> Regards, >>>>>>> >>>>>>> Christian >>>>>>>
