Re: How to trace a column back to its original column

[JiaTao Tao]

Hi Igor
org.apache.calcite.rel.metadata.RelMetadataQuery#getColumnOrigins may help

Regards!

Aron Tao


Seliverstov Igor  于2020年2月19日周三 下午11:16写道：

> This case each NID column is a calculated value - origin erases.
>
> You may try to infer origin walking through SQL AST recursively
>
> I can’t come up with another way.
>
> Regards,
> Igor
>
> > 19 февр. 2020 г., в 15:02, JiaTao Tao  написал(а):
> >
> > Thanks a lot
> >
> > But I found if the select is from the subquery, `getFieldOrigins`
> returns null, because you can not find the table(see in the pic.).
> > String sql = "SELECT T1.NAME ,\n"
> > + "   T1.NID,\n"
> > + "   T2.NID,\n"
> > + "   T2.COMPANY\n"
> > + "FROM\n"
> > + "  (SELECT (U.ID  +100) AS NID,\n"
> > + "  U.NAME  AS NAME\n"
> > + "   FROM USERS U) T1\n"
> > + "JOIN\n"
> > + "  (SELECT (J.ID  +100) AS NID,\n"
> > + "  J.COMPANY\n"
> > + "   FROM JOBS J) T2 ON T1.NID = T2.NID";
> >
> >
> > Regards!
> > Aron Tao
> >
> >
> > Seliverstov Igor mailto:gvvinbl...@gmail.com>>
> 于2020年2月19日周三 下午5:38写道：
> > Aron Tao,
> >
> > I think you need the next two methods:
> >
> > org.apache.calcite.sql.validate.SqlValidatorImpl#getFieldOrigins
> > java.sql.ResultSetMetaData#getTableName
> >
> > Regards,
> > Igor
> >
> > > 19 февр. 2020 г., в 10:29, JiaTao Tao  taojia...@gmail.com>> написал(а):
> > >
> > > Hi
> > > Thanks a lot.
> > > Can you give more details, say which method/field?
> > >
> > > Regards!
> > >
> > > Aron Tao
> > >
> > >
> > > Seliverstov Igor mailto:gvvinbl...@gmail.com>>
> 于2020年2月19日周三 下午2:31写道：
> > >
> > >> You can use their origins (says where a column came from).
> > >>
> > >> It's accessable from SqlValidatorImpl or jdbc result set
> > >>
> > >> ср, 19 февр. 2020 г., 9:25 JiaTao Tao  t...@apache.org>>:
> > >>
> > >>> What I really need is to collect every part of the SQL, Which
> columns are
> > >>> used as filters, which are used as projection(Columns on the source
> > >>> table), But
> > >>> the existence of nested subqueries complicates the issue.
> > >>>
> > >>>
> > >>> Regards!
> > >>>
> > >>> Aron Tao
> > >>>
> > >>>
> > >>> JiaTao Tao mailto:t...@apache.org>> 于2020年2月19日周三
> 下午2:17写道：
> > >>>
> >  SQL like this:
> >  ```
> >  SELECT T1.NID,
> >    T1.NAME ,
> >    T2.COMPANY
> >  FROM
> >   (SELECT (U.ID  +100) AS NID,
> >   U.NAME  AS NAME
> >    FROM USERS U) T1
> >  JOIN
> >   (SELECT (J.ID  +100) AS NID,
> >   J.COMPANY
> >    FROM JOBS J) T2 ON T1.NID = T2.NID
> >  ```
> >  What I wanted is that T1.NID comes from USER.ID ,
> T1.NAME  comes from
> >  USER.NAME .
> > 
> >  Has anyone done similar work? Is there a ready-made example to refer
> > >> to?
> > 
> > 
> >  Regards!
> > 
> >  Aron Tao
> > 
> > >>>
> > >>
> >
>
>

Re: How to trace a column back to its original column

[Rui Wang]

If you have to traverse the whole AST/Rel tree to find original columns,
this thread discussed that before:
https://lists.apache.org/thread.html/11b66dd7f389f0b6e6bce54ad2d7b49a8a0bdd3be4784c441bfafb81%40%3Cdev.calcite.apache.org%3E



-Rui

On Wed, Feb 19, 2020 at 7:16 AM Seliverstov Igor 
wrote:

> This case each NID column is a calculated value - origin erases.
>
> You may try to infer origin walking through SQL AST recursively
>
> I can’t come up with another way.
>
> Regards,
> Igor
>
> > 19 февр. 2020 г., в 15:02, JiaTao Tao  написал(а):
> >
> > Thanks a lot
> >
> > But I found if the select is from the subquery, `getFieldOrigins`
> returns null, because you can not find the table(see in the pic.).
> > String sql = "SELECT T1.NAME ,\n"
> > + "   T1.NID,\n"
> > + "   T2.NID,\n"
> > + "   T2.COMPANY\n"
> > + "FROM\n"
> > + "  (SELECT (U.ID  +100) AS NID,\n"
> > + "  U.NAME  AS NAME\n"
> > + "   FROM USERS U) T1\n"
> > + "JOIN\n"
> > + "  (SELECT (J.ID  +100) AS NID,\n"
> > + "  J.COMPANY\n"
> > + "   FROM JOBS J) T2 ON T1.NID = T2.NID";
> >
> >
> > Regards!
> > Aron Tao
> >
> >
> > Seliverstov Igor mailto:gvvinbl...@gmail.com>>
> 于2020年2月19日周三 下午5:38写道：
> > Aron Tao,
> >
> > I think you need the next two methods:
> >
> > org.apache.calcite.sql.validate.SqlValidatorImpl#getFieldOrigins
> > java.sql.ResultSetMetaData#getTableName
> >
> > Regards,
> > Igor
> >
> > > 19 февр. 2020 г., в 10:29, JiaTao Tao  taojia...@gmail.com>> написал(а):
> > >
> > > Hi
> > > Thanks a lot.
> > > Can you give more details, say which method/field?
> > >
> > > Regards!
> > >
> > > Aron Tao
> > >
> > >
> > > Seliverstov Igor mailto:gvvinbl...@gmail.com>>
> 于2020年2月19日周三 下午2:31写道：
> > >
> > >> You can use their origins (says where a column came from).
> > >>
> > >> It's accessable from SqlValidatorImpl or jdbc result set
> > >>
> > >> ср, 19 февр. 2020 г., 9:25 JiaTao Tao  t...@apache.org>>:
> > >>
> > >>> What I really need is to collect every part of the SQL, Which
> columns are
> > >>> used as filters, which are used as projection(Columns on the source
> > >>> table), But
> > >>> the existence of nested subqueries complicates the issue.
> > >>>
> > >>>
> > >>> Regards!
> > >>>
> > >>> Aron Tao
> > >>>
> > >>>
> > >>> JiaTao Tao mailto:t...@apache.org>> 于2020年2月19日周三
> 下午2:17写道：
> > >>>
> >  SQL like this:
> >  ```
> >  SELECT T1.NID,
> >    T1.NAME ,
> >    T2.COMPANY
> >  FROM
> >   (SELECT (U.ID  +100) AS NID,
> >   U.NAME  AS NAME
> >    FROM USERS U) T1
> >  JOIN
> >   (SELECT (J.ID  +100) AS NID,
> >   J.COMPANY
> >    FROM JOBS J) T2 ON T1.NID = T2.NID
> >  ```
> >  What I wanted is that T1.NID comes from USER.ID ,
> T1.NAME  comes from
> >  USER.NAME .
> > 
> >  Has anyone done similar work? Is there a ready-made example to refer
> > >> to?
> > 
> > 
> >  Regards!
> > 
> >  Aron Tao
> > 
> > >>>
> > >>
> >
>
>

Re: How to trace a column back to its original column

[Seliverstov Igor]

This case each NID column is a calculated value - origin erases.

You may try to infer origin walking through SQL AST recursively

I can’t come up with another way.

Regards,
Igor

> 19 февр. 2020 г., в 15:02, JiaTao Tao  написал(а):
> 
> Thanks a lot
> 
> But I found if the select is from the subquery, `getFieldOrigins` returns 
> null, because you can not find the table(see in the pic.).
> String sql = "SELECT T1.NAME ,\n"
> + "   T1.NID,\n"
> + "   T2.NID,\n"
> + "   T2.COMPANY\n"
> + "FROM\n"
> + "  (SELECT (U.ID  +100) AS NID,\n"
> + "  U.NAME  AS NAME\n"
> + "   FROM USERS U) T1\n"
> + "JOIN\n"
> + "  (SELECT (J.ID  +100) AS NID,\n"
> + "  J.COMPANY\n"
> + "   FROM JOBS J) T2 ON T1.NID = T2.NID";
> 
> 
> Regards!
> Aron Tao
> 
> 
> Seliverstov Igor mailto:gvvinbl...@gmail.com>> 
> 于2020年2月19日周三 下午5:38写道：
> Aron Tao,
> 
> I think you need the next two methods:
> 
> org.apache.calcite.sql.validate.SqlValidatorImpl#getFieldOrigins
> java.sql.ResultSetMetaData#getTableName
> 
> Regards,
> Igor
> 
> > 19 февр. 2020 г., в 10:29, JiaTao Tao  > > написал(а):
> > 
> > Hi
> > Thanks a lot.
> > Can you give more details, say which method/field?
> > 
> > Regards!
> > 
> > Aron Tao
> > 
> > 
> > Seliverstov Igor mailto:gvvinbl...@gmail.com>> 
> > 于2020年2月19日周三 下午2:31写道：
> > 
> >> You can use their origins (says where a column came from).
> >> 
> >> It's accessable from SqlValidatorImpl or jdbc result set
> >> 
> >> ср, 19 февр. 2020 г., 9:25 JiaTao Tao  >> >:
> >> 
> >>> What I really need is to collect every part of the SQL, Which columns are
> >>> used as filters, which are used as projection(Columns on the source
> >>> table), But
> >>> the existence of nested subqueries complicates the issue.
> >>> 
> >>> 
> >>> Regards!
> >>> 
> >>> Aron Tao
> >>> 
> >>> 
> >>> JiaTao Tao mailto:t...@apache.org>> 于2020年2月19日周三 
> >>> 下午2:17写道：
> >>> 
>  SQL like this:
>  ```
>  SELECT T1.NID,
>    T1.NAME ,
>    T2.COMPANY
>  FROM
>   (SELECT (U.ID  +100) AS NID,
>   U.NAME  AS NAME
>    FROM USERS U) T1
>  JOIN
>   (SELECT (J.ID  +100) AS NID,
>   J.COMPANY
>    FROM JOBS J) T2 ON T1.NID = T2.NID
>  ```
>  What I wanted is that T1.NID comes from USER.ID , 
>  T1.NAME  comes from
>  USER.NAME .
>  
>  Has anyone done similar work? Is there a ready-made example to refer
> >> to?
>  
>  
>  Regards!
>  
>  Aron Tao
>  
> >>> 
> >> 
> 

Re: How to trace a column back to its original column

[JiaTao Tao]

Thanks a lot

But I found if the select is from the subquery, `getFieldOrigins` returns
null, because you can not find the table(see in the pic.).

String sql = "SELECT T1.NAME,\n"
+ "   T1.NID,\n"
+ "   T2.NID,\n"
+ "   T2.COMPANY\n"
+ "FROM\n"
+ "  (SELECT (U.ID +100) AS NID,\n"
+ "  U.NAME AS NAME\n"
+ "   FROM USERS U) T1\n"
+ "JOIN\n"
+ "  (SELECT (J.ID +100) AS NID,\n"
+ "  J.COMPANY\n"
+ "   FROM JOBS J) T2 ON T1.NID = T2.NID";

[image: image.png]


Regards!

Aron Tao


Seliverstov Igor  于2020年2月19日周三 下午5:38写道：

> Aron Tao,
>
> I think you need the next two methods:
>
> org.apache.calcite.sql.validate.SqlValidatorImpl#getFieldOrigins
> java.sql.ResultSetMetaData#getTableName
>
> Regards,
> Igor
>
> > 19 февр. 2020 г., в 10:29, JiaTao Tao  написал(а):
> >
> > Hi
> > Thanks a lot.
> > Can you give more details, say which method/field?
> >
> > Regards!
> >
> > Aron Tao
> >
> >
> > Seliverstov Igor  于2020年2月19日周三 下午2:31写道：
> >
> >> You can use their origins (says where a column came from).
> >>
> >> It's accessable from SqlValidatorImpl or jdbc result set
> >>
> >> ср, 19 февр. 2020 г., 9:25 JiaTao Tao :
> >>
> >>> What I really need is to collect every part of the SQL, Which columns
> are
> >>> used as filters, which are used as projection(Columns on the source
> >>> table), But
> >>> the existence of nested subqueries complicates the issue.
> >>>
> >>>
> >>> Regards!
> >>>
> >>> Aron Tao
> >>>
> >>>
> >>> JiaTao Tao  于2020年2月19日周三 下午2:17写道：
> >>>
>  SQL like this:
>  ```
>  SELECT T1.NID,
>    T1.NAME,
>    T2.COMPANY
>  FROM
>   (SELECT (U.ID +100) AS NID,
>   U.NAME AS NAME
>    FROM USERS U) T1
>  JOIN
>   (SELECT (J.ID +100) AS NID,
>   J.COMPANY
>    FROM JOBS J) T2 ON T1.NID = T2.NID
>  ```
>  What I wanted is that T1.NID comes from USER.ID, T1.NAME comes from
>  USER.NAME.
> 
>  Has anyone done similar work? Is there a ready-made example to refer
> >> to?
> 
> 
>  Regards!
> 
>  Aron Tao
> 
> >>>
> >>
>
>

Re: How to trace a column back to its original column

[JiaTao Tao]

Thanks a lot!

seems I can use
org.apache.calcite.rel.metadata.RelMetadataQuery#getColumnOrigin.

Regards!

Aron Tao


Walaa Eldin Moustafa  于2020年2月19日周三 下午2:36写道：

> You might check out this class [1].
>
> [1]
> https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/metadata/RelMdExpressionLineage.java
>
> On Tue, Feb 18, 2020 at 10:31 PM Seliverstov Igor 
> wrote:
> >
> > You can use their origins (says where a column came from).
> >
> > It's accessable from SqlValidatorImpl or jdbc result set
> >
> > ср, 19 февр. 2020 г., 9:25 JiaTao Tao :
> >
> > > What I really need is to collect every part of the SQL, Which columns
> are
> > > used as filters, which are used as projection(Columns on the source
> > > table), But
> > > the existence of nested subqueries complicates the issue.
> > >
> > >
> > > Regards!
> > >
> > > Aron Tao
> > >
> > >
> > > JiaTao Tao  于2020年2月19日周三 下午2:17写道：
> > >
> > > > SQL like this:
> > > > ```
> > > > SELECT T1.NID,
> > > >T1.NAME,
> > > >T2.COMPANY
> > > > FROM
> > > >   (SELECT (U.ID +100) AS NID,
> > > >   U.NAME AS NAME
> > > >FROM USERS U) T1
> > > > JOIN
> > > >   (SELECT (J.ID +100) AS NID,
> > > >   J.COMPANY
> > > >FROM JOBS J) T2 ON T1.NID = T2.NID
> > > > ```
> > > > What I wanted is that T1.NID comes from USER.ID, T1.NAME comes from
> > > > USER.NAME.
> > > >
> > > > Has anyone done similar work? Is there a ready-made example to refer
> to?
> > > >
> > > >
> > > > Regards!
> > > >
> > > > Aron Tao
> > > >
> > >
>

Re: How to trace a column back to its original column

[Seliverstov Igor]

Aron Tao,

I think you need the next two methods:

org.apache.calcite.sql.validate.SqlValidatorImpl#getFieldOrigins
java.sql.ResultSetMetaData#getTableName

Regards,
Igor

> 19 февр. 2020 г., в 10:29, JiaTao Tao  написал(а):
> 
> Hi
> Thanks a lot.
> Can you give more details, say which method/field?
> 
> Regards!
> 
> Aron Tao
> 
> 
> Seliverstov Igor  于2020年2月19日周三 下午2:31写道：
> 
>> You can use their origins (says where a column came from).
>> 
>> It's accessable from SqlValidatorImpl or jdbc result set
>> 
>> ср, 19 февр. 2020 г., 9:25 JiaTao Tao :
>> 
>>> What I really need is to collect every part of the SQL, Which columns are
>>> used as filters, which are used as projection(Columns on the source
>>> table), But
>>> the existence of nested subqueries complicates the issue.
>>> 
>>> 
>>> Regards!
>>> 
>>> Aron Tao
>>> 
>>> 
>>> JiaTao Tao  于2020年2月19日周三 下午2:17写道：
>>> 
 SQL like this:
 ```
 SELECT T1.NID,
   T1.NAME,
   T2.COMPANY
 FROM
  (SELECT (U.ID +100) AS NID,
  U.NAME AS NAME
   FROM USERS U) T1
 JOIN
  (SELECT (J.ID +100) AS NID,
  J.COMPANY
   FROM JOBS J) T2 ON T1.NID = T2.NID
 ```
 What I wanted is that T1.NID comes from USER.ID, T1.NAME comes from
 USER.NAME.
 
 Has anyone done similar work? Is there a ready-made example to refer
>> to?
 
 
 Regards!
 
 Aron Tao
 
>>> 
>> 

Re: How to trace a column back to its original column

[JiaTao Tao]

Hi
Thanks a lot.
Can you give more details, say which method/field?

Regards!

Aron Tao


Seliverstov Igor  于2020年2月19日周三 下午2:31写道：

> You can use their origins (says where a column came from).
>
> It's accessable from SqlValidatorImpl or jdbc result set
>
> ср, 19 февр. 2020 г., 9:25 JiaTao Tao :
>
> > What I really need is to collect every part of the SQL, Which columns are
> > used as filters, which are used as projection(Columns on the source
> > table), But
> > the existence of nested subqueries complicates the issue.
> >
> >
> > Regards!
> >
> > Aron Tao
> >
> >
> > JiaTao Tao  于2020年2月19日周三 下午2:17写道：
> >
> > > SQL like this:
> > > ```
> > > SELECT T1.NID,
> > >T1.NAME,
> > >T2.COMPANY
> > > FROM
> > >   (SELECT (U.ID +100) AS NID,
> > >   U.NAME AS NAME
> > >FROM USERS U) T1
> > > JOIN
> > >   (SELECT (J.ID +100) AS NID,
> > >   J.COMPANY
> > >FROM JOBS J) T2 ON T1.NID = T2.NID
> > > ```
> > > What I wanted is that T1.NID comes from USER.ID, T1.NAME comes from
> > > USER.NAME.
> > >
> > > Has anyone done similar work? Is there a ready-made example to refer
> to?
> > >
> > >
> > > Regards!
> > >
> > > Aron Tao
> > >
> >
>

Re: How to trace a column back to its original column

[Walaa Eldin Moustafa]

You might check out this class [1].

[1] 
https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/metadata/RelMdExpressionLineage.java

On Tue, Feb 18, 2020 at 10:31 PM Seliverstov Igor  wrote:
>
> You can use their origins (says where a column came from).
>
> It's accessable from SqlValidatorImpl or jdbc result set
>
> ср, 19 февр. 2020 г., 9:25 JiaTao Tao :
>
> > What I really need is to collect every part of the SQL, Which columns are
> > used as filters, which are used as projection(Columns on the source
> > table), But
> > the existence of nested subqueries complicates the issue.
> >
> >
> > Regards!
> >
> > Aron Tao
> >
> >
> > JiaTao Tao  于2020年2月19日周三 下午2:17写道：
> >
> > > SQL like this:
> > > ```
> > > SELECT T1.NID,
> > >T1.NAME,
> > >T2.COMPANY
> > > FROM
> > >   (SELECT (U.ID +100) AS NID,
> > >   U.NAME AS NAME
> > >FROM USERS U) T1
> > > JOIN
> > >   (SELECT (J.ID +100) AS NID,
> > >   J.COMPANY
> > >FROM JOBS J) T2 ON T1.NID = T2.NID
> > > ```
> > > What I wanted is that T1.NID comes from USER.ID, T1.NAME comes from
> > > USER.NAME.
> > >
> > > Has anyone done similar work? Is there a ready-made example to refer to?
> > >
> > >
> > > Regards!
> > >
> > > Aron Tao
> > >
> >

Re: How to trace a column back to its original column

[Seliverstov Igor]

You can use their origins (says where a column came from).

It's accessable from SqlValidatorImpl or jdbc result set

ср, 19 февр. 2020 г., 9:25 JiaTao Tao :

> What I really need is to collect every part of the SQL, Which columns are
> used as filters, which are used as projection(Columns on the source
> table), But
> the existence of nested subqueries complicates the issue.
>
>
> Regards!
>
> Aron Tao
>
>
> JiaTao Tao  于2020年2月19日周三 下午2:17写道：
>
> > SQL like this:
> > ```
> > SELECT T1.NID,
> >T1.NAME,
> >T2.COMPANY
> > FROM
> >   (SELECT (U.ID +100) AS NID,
> >   U.NAME AS NAME
> >FROM USERS U) T1
> > JOIN
> >   (SELECT (J.ID +100) AS NID,
> >   J.COMPANY
> >FROM JOBS J) T2 ON T1.NID = T2.NID
> > ```
> > What I wanted is that T1.NID comes from USER.ID, T1.NAME comes from
> > USER.NAME.
> >
> > Has anyone done similar work? Is there a ready-made example to refer to?
> >
> >
> > Regards!
> >
> > Aron Tao
> >
>

Re: How to trace a column back to its original column

[JiaTao Tao]

What I really need is to collect every part of the SQL, Which columns are
used as filters, which are used as projection(Columns on the source table), But
the existence of nested subqueries complicates the issue.


Regards!

Aron Tao


JiaTao Tao  于2020年2月19日周三 下午2:17写道：

> SQL like this:
> ```
> SELECT T1.NID,
>T1.NAME,
>T2.COMPANY
> FROM
>   (SELECT (U.ID +100) AS NID,
>   U.NAME AS NAME
>FROM USERS U) T1
> JOIN
>   (SELECT (J.ID +100) AS NID,
>   J.COMPANY
>FROM JOBS J) T2 ON T1.NID = T2.NID
> ```
> What I wanted is that T1.NID comes from USER.ID, T1.NAME comes from
> USER.NAME.
>
> Has anyone done similar work? Is there a ready-made example to refer to?
>
>
> Regards!
>
> Aron Tao
>