Re: Correlation with a variable
Actually yes, I meant adding param names in the RexNode itself. I guess
there are valid reasons for why this might be a bad idea so I am not going
to insist on this.
Indeed allowing ('.') field accesses on top of parameters (named or
positional) is not so intuitive given that most of the time the type is
unknown. This may be problematic for the parser/validator but at RexNode
level, where types are known, I would say that we can have parameters
followed by field accesses.
I guess we could support field accesses on top of parameters in the
parser/validator indirectly by first casting to a structured type.
Best,
Stamatis
On Wed, Feb 19, 2020, 6:40 PM Julian Hyde wrote:
> When you say "keep the plan as independent as possible from statement
> and other parts of the execution", if you propose to achieve that by
> adding names into the RexNodes representing parameters, I don't agree.
> I think that RelNode/RexNode should operate based on internal offsets
> (retaining names, if possible, as a debugging aid) but not worry about
> the names used by the client.
>
> That said, we have RelRoot, which acts as a wrapper and mapping layer.
> It adds metadata to the root RelNode, but RelNodes and RexNodes never
> reference it.
>
> Note that old.field and new.field is a special case of named
> parameters, and 'old' and 'new' are reserved words in the standard for
> trigger SQL. I don't think that named parameters in general should
> allow '.'.
>
> Julian
>
> On Wed, Feb 19, 2020 at 12:29 AM Stamatis Zampetakis
> wrote:
> >
> > I don't know if there is already an issue around this topic, I think not.
> >
> > Now regarding point (b) I would prefer to keep the plan as independent as
> > possible from statement and other parts of the execution.
> >
> > Other than that I encountered a few use-cases where named parameters may
> be
> > a better fit:
> > * Plans with parameters that are created from different components of an
> > application and need to be combined together. It is more difficult to
> > ensure that param collisions do not occur when we are using index based
> > parameters. Having named parameters where each component uses a reserved
> > prefix makes things slightly easier to handle.
> > * Using the same parameter (value) in multiple places (which could help
> in
> > simplifications) is semantically easier to express with names.
> > * Printing and debugging a plan.
> >
> > Certainly there are also benefits in keeping a single RexNode class for
> > parameters so I am fine with whatever decision we take in the end.
> >
> > Best,
> > Stamatis
> >
> > On Tue, Feb 18, 2020, 9:18 PM Julian Hyde wrote:
> >
> > > 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 <
> > > christian.bei...@gmail.com> 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
Re: Correlation with a variable
When you say "keep the plan as independent as possible from statement and other parts of the execution", if you propose to achieve that by adding names into the RexNodes representing parameters, I don't agree. I think that RelNode/RexNode should operate based on internal offsets (retaining names, if possible, as a debugging aid) but not worry about the names used by the client. That said, we have RelRoot, which acts as a wrapper and mapping layer. It adds metadata to the root RelNode, but RelNodes and RexNodes never reference it. Note that old.field and new.field is a special case of named parameters, and 'old' and 'new' are reserved words in the standard for trigger SQL. I don't think that named parameters in general should allow '.'. Julian On Wed, Feb 19, 2020 at 12:29 AM Stamatis Zampetakis wrote: > > I don't know if there is already an issue around this topic, I think not. > > Now regarding point (b) I would prefer to keep the plan as independent as > possible from statement and other parts of the execution. > > Other than that I encountered a few use-cases where named parameters may be > a better fit: > * Plans with parameters that are created from different components of an > application and need to be combined together. It is more difficult to > ensure that param collisions do not occur when we are using index based > parameters. Having named parameters where each component uses a reserved > prefix makes things slightly easier to handle. > * Using the same parameter (value) in multiple places (which could help in > simplifications) is semantically easier to express with names. > * Printing and debugging a plan. > > Certainly there are also benefits in keeping a single RexNode class for > parameters so I am fine with whatever decision we take in the end. > > Best, > Stamatis > > On Tue, Feb 18, 2020, 9:18 PM Julian Hyde wrote: > > > 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 < > > christian.bei...@gmail.com> 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 < > > christian.bei...@gmail.com> > > >>
Re: Correlation with a variable
I don't know if there is already an issue around this topic, I think not. Now regarding point (b) I would prefer to keep the plan as independent as possible from statement and other parts of the execution. Other than that I encountered a few use-cases where named parameters may be a better fit: * Plans with parameters that are created from different components of an application and need to be combined together. It is more difficult to ensure that param collisions do not occur when we are using index based parameters. Having named parameters where each component uses a reserved prefix makes things slightly easier to handle. * Using the same parameter (value) in multiple places (which could help in simplifications) is semantically easier to express with names. * Printing and debugging a plan. Certainly there are also benefits in keeping a single RexNode class for parameters so I am fine with whatever decision we take in the end. Best, Stamatis On Tue, Feb 18, 2020, 9:18 PM Julian Hyde wrote: > 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 < > christian.bei...@gmail.com> 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 < > christian.bei...@gmail.com> > >> 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 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 于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, > > >
Re: Correlation with a variable
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 > 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 >> 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 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 于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 < >>> christian.bei...@gmail.com >> ，写道： >>> 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: >>>
Re: Correlation with a variable
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 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 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 于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 < christian.bei...@gmail.com ，写道： 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
Re: Correlation with a variable
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. 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. 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. 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. Best, Stamatis On Tue, Feb 18, 2020 at 7:03 PM Christian Beikov 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 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 于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 < > christian.bei...@gmail.com > > > >，写道： > > > >> 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 > > > >> > > > > > >
Re: Correlation with a variable
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 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 于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 > >，写道： > > >> 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 > > >> > > >
Re: Correlation with a variable
If you want to make NEW a normal sql identifier, you should override it in the parser to make it unreserved. Christian Beikov 于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 >，写道： > >> 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 > >> >
Re: Correlation with a variable
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 ，写道： 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
Re: Correlation with a variable
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 ，写道： > 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 >
Correlation with a variable
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
