Using ASTParser and TreeWalker for parsing SQL query

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Using ASTParser and TreeWalker for parsing SQL query

venu
Hi All,
I am new to derby and I am using derby-10.10.1.1 for parsing sql query.
I am using TreeWalker for parse SQL query as Rick mentioned in this post (DERBY-3946)
When I use simple SQL query everything is fine. But, when I use query with 'order by' or 'group by' then TreeWalker  visit method is traversing upto select nodes only.
ex:
SELECT FIRSTNME,TOTAL_PAY FROM EMPLOYEE ORDER BY TOTAL_PAY

Using TreeWalker :
Parsing:
select firstnme,total_pay from employee order by total_pay
    org.apache.derby.impl.sql.compile.CursorNode
        org.apache.derby.impl.sql.compile.SelectNode
            org.apache.derby.impl.sql.compile.ResultColumnList
                org.apache.derby.impl.sql.compile.ResultColumn
                    org.apache.derby.impl.sql.compile.ColumnReference
                org.apache.derby.impl.sql.compile.ResultColumn
                    org.apache.derby.impl.sql.compile.ColumnReference
            org.apache.derby.impl.sql.compile.FromList
                org.apache.derby.impl.sql.compile.FromBaseTable

When I use ASTParser  for printing the tree then I can see the node for order by also.

Using ASTParser  :---
orderByList: 0
        org.apache.derby.impl.sql.compile.OrderByList@f6d64c5
        allAscending: true
        alwaysSort:true
        sortNeeded: true
        columnOrdering:
       
        [0]:
        org.apache.derby.impl.sql.compile.OrderByColumn@56f2c96c
        nullsOrderedLow: false
        ascending; true
        addedColumnOffset: -1
        columnPosition: -1
        expression:
                org.apache.derby.impl.sql.compile.ColumnReference@5d85fe0c
                columnName: TOTAL_PAY
                tableNumber: -1
                columnNumber: 0
                replacesAggregate: false
                replacesWindowFunctionCall: false
                tableName: null
                nestingLevel: -1
                sourceLevel: -1
                dataTypeServices: null
        [1]:
        org.apache.derby.impl.sql.compile.OrderByColumn@7e9f5cc
        nullsOrderedLow: false
        ascending; true
        addedColumnOffset: -1
        columnPosition: -1
        expression:
                org.apache.derby.impl.sql.compile.ColumnReference@11082823
                columnName: FIRSTNME
                tableNumber: -1
                columnNumber: 0
                replacesAggregate: false
                replacesWindowFunctionCall: false
                tableName: null
                nestingLevel: -1
                sourceLevel: -1
                dataTypeServices: null
---
Does anybody have any hint where I went wrong?

Thanks in advance.
Venu
Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

Rick Hillegas
On 6/13/13 8:34 PM, venu wrote:

> Hi All,
> I am new to derby and I am using derby-10.10.1.1 for parsing sql query.
> I am using TreeWalker for parse SQL query as Rick mentioned in this post
> (DERBY-3946)
> When I use simple SQL query everything is fine. But, when I use query with
> 'order by' or 'group by' then TreeWalker  visit method is traversing upto
> select nodes only.
> ex:
> SELECT FIRSTNME,TOTAL_PAY FROM EMPLOYEE ORDER BY TOTAL_PAY
>
> Using TreeWalker :
> Parsing:
> select firstnme,total_pay from employee order by total_pay
>      org.apache.derby.impl.sql.compile.CursorNode
>          org.apache.derby.impl.sql.compile.SelectNode
>              org.apache.derby.impl.sql.compile.ResultColumnList
>                  org.apache.derby.impl.sql.compile.ResultColumn
>                      org.apache.derby.impl.sql.compile.ColumnReference
>                  org.apache.derby.impl.sql.compile.ResultColumn
>                      org.apache.derby.impl.sql.compile.ColumnReference
>              org.apache.derby.impl.sql.compile.FromList
>                  org.apache.derby.impl.sql.compile.FromBaseTable
>
> When I use ASTParser  for printing the tree then I can see the node for
> order by also.
>
> Using ASTParser  :---
> orderByList: 0
> org.apache.derby.impl.sql.compile.OrderByList@f6d64c5
> allAscending: true
> alwaysSort:true
> sortNeeded: true
> columnOrdering:
>
> [0]:
> org.apache.derby.impl.sql.compile.OrderByColumn@56f2c96c
> nullsOrderedLow: false
> ascending; true
> addedColumnOffset: -1
> columnPosition: -1
> expression:
> org.apache.derby.impl.sql.compile.ColumnReference@5d85fe0c
> columnName: TOTAL_PAY
> tableNumber: -1
> columnNumber: 0
> replacesAggregate: false
> replacesWindowFunctionCall: false
> tableName: null
> nestingLevel: -1
> sourceLevel: -1
> dataTypeServices: null
> [1]:
> org.apache.derby.impl.sql.compile.OrderByColumn@7e9f5cc
> nullsOrderedLow: false
> ascending; true
> addedColumnOffset: -1
> columnPosition: -1
> expression:
> org.apache.derby.impl.sql.compile.ColumnReference@11082823
> columnName: FIRSTNME
> tableNumber: -1
> columnNumber: 0
> replacesAggregate: false
> replacesWindowFunctionCall: false
> tableName: null
> nestingLevel: -1
> sourceLevel: -1
> dataTypeServices: null
> ---
> Does anybody have any hint where I went wrong?
>
> Thanks in advance.
> Venu
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Hi Venu,

Thanks for bringing this discrepancy to our attention. I don't think you
are doing anything wrong. The problem is that the tree printer
(CursorNode.treePrint()) visits ORDER BY, FETCH, and OFFSET clauses but
the Visitor logic (CursorNode.acceptChildren()) doesn't. This looks like
a defect in the Visitor logic. I have logged DERBY-6263 to track this. I
have a candidate fix for this defect, which I will test on the
development trunk. If it works well, I will backport the fix to the
10.10 branch.

By the way: if you are using TreePrinter, you might be interested in
trying out the xml-based tree printing code attached to DERBY-4415.
Here's how you use that code:

-- load the xml-based tree printer
call syscs_util.syscs_register_tool( 'customTool', true, 'ASTInspector',
'XmlASTPrinter' );

-- run your query
select tablename from sys.systables where 1=2 order by tablename;

-- unload the xml-based tree printer
call syscs_util.syscs_register_tool( 'customTool', false, 'ASTInspector' );

Thanks,
-Rick

Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

venu
Hi Rick,
Thanks for the quick reply.

I downloaded the development version and able to build derby jar file.
Now, I can see the nodes for order by clause.
But, I have a doubt will the same patch works for 'group by' also.
Because, when I tested for 'group by' I have the same problem like 'order by'.
Could you please advice me on this?

Thanks in advance,
Venu.
Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

Rick Hillegas
On 6/14/13 11:17 AM, venu wrote:
> Hi Rick,
> Thanks for the quick reply.
>
> I downloaded the development version and able to build derby jar file.
> Now, I can see the nodes for order by clause.
> But, I have a doubt will the same patch works for 'group by' also.
> Because, when I tested for 'group by' I have the same problem like 'order
> by'.
> Could you please advice me on this?
Thanks for bringing this to our attention, too, Venu. This is caused by
a similar defect in SelectNode: several clauses which appear in
treePrint() don't appear in acceptChildren(). I have added those clauses
to acceptChildren() and will run tests.

Thanks,
-Rick

> Thanks in advance,
> Venu.
>
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p131390.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>

Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

venu
Hi Rick,
Thanks for working on these issues..
So, are you using the same id(DERBY-6263) for tracking this issue also?
If not could you please let me know the id for this issue?
Thanks,
Venu
Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

Rick Hillegas
On 6/14/13 10:56 PM, venu wrote:
> Hi Rick,
> Thanks for working on these issues..
> So, are you using the same id(DERBY-6263) for tracking this issue also?
Hi Venu,

I have broadened the scope of DERBY-6263. I have re-phrased it as a
bigger discrepancy between the node inspection done by the debug printer
and the node inspection done by the compiler's node visitors. I suspect
that a fair amount of work may be needed to align the two inspectors. I
have attached a patch to DERBY-6263 to handle the uninspected clauses in
SelectNodes. If the tests pass cleanly, I'll port this fix to the 10.10
branch too.

Currently, there is no requirement that the two inspectors align.
However, I think that the discrepancies indicate a potential problem
that deserves some thought. That said, aligning the inspectors may take
some time. We may be able to point you to a workaround with fewer
problems if you can give us more context about why you are using the
TreeWalker.

Thanks,
-Rick

> If not could you please let me know the id for this issue?
> Thanks,
> Venu
>
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p131432.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>

Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

venu
Hi Rick,
My requirement is , I want to parse the SQL and get the data from the Treewalkers visitednodes hashmap.
I am iterating the visitednodes hashmap and finding the columns data,tables data,where clause data, order by data and group by data.
Is there any simple way to get above data other than iterating the hashmap.?
Can you provide me a simple example for getting the column data from the hashmap? Thanks!
Thanks,
Venu.
Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

Rick Hillegas
On 6/17/13 7:02 AM, venu wrote:
> Hi Rick,
> My requirement is , I want to parse the SQL and get the data from the
> Treewalkers visitednodes hashmap.
> I am iterating the visitednodes hashmap and finding the columns data,tables
> data,where clause data, order by data and group by data.
> Is there any simple way to get above data other than iterating the hashmap.?
If you need to understand the structure of those clauses, then you're
right, I think that you will need some kind of Visitor and you will want
the Visitor logic to visit every node in the AST. I have added more
clauses to the Visitor logic for SelectNode and ported that change to
the 10.10 branch.  Hopefully, this will help you move forward.

Thanks,
-Rick

> Can you provide me a simple example for getting the column data from the
> hashmap? Thanks!
> Thanks,
> Venu.
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p131475.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>

Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

venu
Hi Rick,
Thanks for the reply.
I took the development trunk code
(svn checkout https://svn.apache.org/repos/asf/db/derby/code/trunk/) and replaced SelectNode.java file with the given SelectedNode.java file and managed to generate a derby.jar file.

But, when I run Treewalker or ASTParser I got this error.
Does I did anything wrong while making jar file?
or Is this a issue ?

java.sql.SQLException: Java exception: 'ASSERT FAILED Six-argument init() not implemented for org.apache.derby.impl.sql.compile.SelectNode: org.apache.derby.shared.common.sanity.AssertFailure'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:103)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:141)
        at org.apache.derby.impl.jdbc.Util.javaException(Util.java:335)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:436)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2395)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:152)
        at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Driver40.java:98)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1733)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1561)
        at com.ibm.tivoli.tnpm.dal.jdbc.sqlparser.TreeWalker.execute(TreeWalker.java:100)

Thanks,
Venu
Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

Rick Hillegas
On 6/17/13 12:02 PM, venu wrote:

> Hi Rick,
> Thanks for the reply.
> I took the development trunk code
> (svn checkout https://svn.apache.org/repos/asf/db/derby/code/trunk/) and
> replaced SelectNode.java file with the given SelectedNode.java file and
> managed to generate a derby.jar file.
>
> But, when I run Treewalker or ASTParser I got this error.
> Does I did anything wrong while making jar file?
> or Is this a issue ?
>
> java.sql.SQLException: Java exception: 'ASSERT FAILED Six-argument init()
> not implemented for org.apache.derby.impl.sql.compile.SelectNode:
> org.apache.derby.shared.common.sanity.AssertFailure'.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:103)
> at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:141)
> at org.apache.derby.impl.jdbc.Util.javaException(Util.java:335)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:436)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
> at
> org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2395)
> at
> org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
> at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:152)
> at
> org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Driver40.java:98)
> at
> org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1733)
> at
> org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1561)
> at
> com.ibm.tivoli.tnpm.dal.jdbc.sqlparser.TreeWalker.execute(TreeWalker.java:100)
>
> Thanks,
> Venu
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p131496.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Hi Venu,

I also ported the fix to the 10.10 branch. That may be a more stable
place to look for the change to SelectNode. The error you are seeing
suggests that you have tripped across some interference from the work
being done on the NodeFactory.

Hope this helps,
-Rick
Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

Rick Hillegas
On 6/17/13 1:01 PM, Rick Hillegas wrote:

> On 6/17/13 12:02 PM, venu wrote:
>> Hi Rick,
>> Thanks for the reply.
>> I took the development trunk code
>> (svn checkout https://svn.apache.org/repos/asf/db/derby/code/trunk/) and
>> replaced SelectNode.java file with the given SelectedNode.java file and
>> managed to generate a derby.jar file.
>>
>> But, when I run Treewalker or ASTParser I got this error.
>> Does I did anything wrong while making jar file?
>> or Is this a issue ?
>>
>> java.sql.SQLException: Java exception: 'ASSERT FAILED Six-argument
>> init()
>> not implemented for org.apache.derby.impl.sql.compile.SelectNode:
>> org.apache.derby.shared.common.sanity.AssertFailure'.
>>     at
>> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:103)
>>
>>     at
>> org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:141)
>>     at org.apache.derby.impl.jdbc.Util.javaException(Util.java:335)
>>     at
>> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:436)
>>
>>     at
>> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
>>
>>     at
>> org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2395)
>>
>>     at
>> org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
>>
>>     at
>> org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:152)
>>
>>     at
>> org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Driver40.java:98)
>>
>>     at
>> org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1733)
>>
>>     at
>> org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1561)
>>
>>     at
>> com.ibm.tivoli.tnpm.dal.jdbc.sqlparser.TreeWalker.execute(TreeWalker.java:100)
>>
>>
>> Thanks,
>> Venu
>>
>>
>>
>> --
>> View this message in context:
>> http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p131496.html
>> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>>
> Hi Venu,
>
> I also ported the fix to the 10.10 branch. That may be a more stable
> place to look for the change to SelectNode. The error you are seeing
> suggests that you have tripped across some interference from the work
> being done on the NodeFactory.
>
> Hope this helps,
> -Rick
>
I'm told that the NodeFactory work hasn't been checked in yet. So that's
not the explanation. What's the query you're running through ASTParser
and TreeWalker? Does the query work standalone when you just give it to
Derby?

Thanks,
-Rick
Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

venu
Hi Rick,
I took 10.10 branch code and able to resolve all issues related with group by.
I have some doubts could you please help me on these things.
1. I can see FromList and Groupbylist but can not find OrderByList node.
2. When I provide any function then the node tree will be like this.
Ex:
expression:
                        org.apache.derby.impl.sql.compile.JavaToSQLValueNode@6460029d
                        dataTypeServices: null
                        javaNode:
                                org.apache.derby.impl.sql.compile.StaticMethodCallNode@5b4bc4e6
                                javaClassName: null
                                methodName: TO_CHAR
                                methodParms[0] :
                                        org.apache.derby.impl.sql.compile.SQLToJavaValueNode@4b6c06dd

                                        value:
                                                org.apache.derby.impl.sql.compile.ColumnReference@2136bdda
                                                columnName: TIME
                                                tableNumber: -1
                                                columnNumber: 0
                                                replacesAggregate: false
                                                replacesWindowFunctionCall: false
                                                tableName: null
                                                nestingLevel: -1
                                                sourceLevel: -1
                                                dataTypeServices: null

Here I want to get value node of SQLToJavaValueNode object. But, there is no such method for getting sub node or value node SQLToJavaValueNode.

3. How can I get having clause from the select node.

Thanks,
Venu.
Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

Rick Hillegas
On 6/19/13 9:47 AM, venu wrote:
> Hi Rick,
> I took 10.10 branch code and able to resolve all issues related with group
> by.
> I have some doubts could you please help me on these things.
> 1. I can see FromList and Groupbylist but can not find OrderByList node.
Hi Venu,

Using the following query...

   select tablename from sys.systables where tablename is null order by
tablename

...I see the ORDER BY list in the output from both TreeWalker and
ASTParser. TreeWalker shows this it as a
org.apache.derby.impl.sql.compile.OrderByColumn node. ASTParser clearly
labels it as an orderByList. Can you be more specific about the problem
you are seeing with ORDER BY lists?

> 2. When I provide any function then the node tree will be like this.
> Ex:
> expression:
> org.apache.derby.impl.sql.compile.JavaToSQLValueNode@6460029d
> dataTypeServices: null
> javaNode:
> org.apache.derby.impl.sql.compile.StaticMethodCallNode@5b4bc4e6
> javaClassName: null
> methodName: TO_CHAR
> methodParms[0] :
> org.apache.derby.impl.sql.compile.SQLToJavaValueNode@4b6c06dd
>
> value:
> org.apache.derby.impl.sql.compile.ColumnReference@2136bdda
> columnName: TIME
> tableNumber: -1
> columnNumber: 0
> replacesAggregate: false
> replacesWindowFunctionCall: false
> tableName: null
> nestingLevel: -1
> sourceLevel: -1
> dataTypeServices: null
>
> Here I want to get value node of SQLToJavaValueNode object. But, there is no
> such method for getting sub node or value node SQLToJavaValueNode.
I'm afraid I don't understand what's missing. In the above summary, I
see a call to the function TO_CHAR, taking one argument, a column called
TIME, whose table is not resolved yet.
> 3. How can I get having clause from the select node.
When I run the following query...

  select tablename, max( tableid ) from sys.systables group by tablename
having max( tableid ) is null

...I see the HAVING clause in the output of both TreeWalker and
ASTParser. For TreeWalker, it appears as an
org.apache.derby.impl.sql.compile.IsNullNode node. For ASTParser, it is
clearly labelled as a havingClause.

I'm beginning to sense that TreeWalker and ASTParser are very general
diagnostic tools which you may not be able to press into service to do
the sophisticated query analysis you need to perform. At this point, I
recommend that you study TreeWalker to see how to extract the AST from
the query. I'm afraid that you may need to walk the AST in your own code
in order to perform sophisticated analysis which is specific to your
application.

Of course, you can always write your own Visitor for this purpose. But
using a Visitor to analyze something like a HAVING clause is going to be
tricky. That is because the Visitor does not have enough context to know
when a ValueNode represents a HAVING clause. That knowledge is in the
SelectNode itself.

Hope this helps,
-Rick

> Thanks,
> Venu.
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p131603.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>

Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

venu
Hi Rick,
Thanks for spending your valuable time on this issue.
Sorry if I ask the same question again.

Could you please help me on this ?
ex:
select colName from tableName where id=100 having COUNT(*) > 1
When I go through the nodes, I can see the where clause and having clause both have defined in a same way (node tree structure).

Both doesn't have parent node. Both are using BinaryRelationalOperatorNode as parent node if, both have the condition with some operator (like = in where clause and > in having clause).

But, for differentiate these two we don't have any parent node.
For example if we take a look at from or groupby or orderby they have root nodes like fromlist, groupbylist and orderbylist.

for where clause and having clause both doesn't have parent node.
-----------------
        whereClause:
                org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode@35b835b8
                operator: =
                methodName: equals
                dataTypeServices: null
                leftOperand:
                        org.apache.derby.impl.sql.compile.ColumnReference@33c433c4
                        columnName: ID
                        tableNumber: -1
                        columnNumber: 0
                        replacesAggregate: false
                        replacesWindowFunctionCall: false
                        tableName: null
                        nestingLevel: -1
                        sourceLevel: -1
                        dataTypeServices: null
                rightOperand:
                        org.apache.derby.impl.sql.compile.NumericConstantNode@34a434a4
                        value: 100
                        dataTypeServices: INTEGER NOT NULL
        havingClause:
                org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode@24622462
                operator: >
                methodName: greaterThan
                dataTypeServices: null
                leftOperand:
                        org.apache.derby.impl.sql.compile.AggregateNode@73c273c2
                        aggregateName: COUNT(*)
                        distinct: false
                        operator: null
                        methodName: null
                        dataTypeServices: null
                rightOperand:
                        org.apache.derby.impl.sql.compile.NumericConstantNode@23722372
                        value: 1
                        dataTypeServices: INTEGER NOT NULL
-----------------------------

If I provide any conditional operator like AND or OR to where clause or having clause, then the structure is different for both of these two.

My problem is, I want to take having clause data and where clause data from the query.

So, not able to get the data properly due to the structure is changing for these two items every time.

Is there any way to differentiate these two elements ?
or can we add any parent node for these two like others?
If not possible could you please give some advice on 'where I need to change or which way I need to move' for solving my issue ?

Thanks in advance,
Venu.
Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

Rick Hillegas
On 6/30/13 9:09 PM, venu wrote:

> Hi Rick,
> Thanks for spending your valuable time on this issue.
> Sorry if I ask the same question again.
>
> Could you please help me on this ?
> ex:
> select colName from tableName where id=100 having COUNT(*)>  1
> When I go through the nodes, I can see the where clause and having clause
> both have defined in a same way (node tree structure).
>
> Both doesn't have parent node. Both are using BinaryRelationalOperatorNode
> as parent node if, both have the condition with some operator (like = in
> where clause and>  in having clause).
>
> But, for differentiate these two we don't have any parent node.
> For example if we take a look at from or groupby or orderby they have root
> nodes like fromlist, groupbylist and orderbylist.
>
> for where clause and having clause both doesn't have parent node.
> -----------------
> whereClause:
> org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode@35b835b8
> operator: =
> methodName: equals
> dataTypeServices: null
> leftOperand:
> org.apache.derby.impl.sql.compile.ColumnReference@33c433c4
> columnName: ID
> tableNumber: -1
> columnNumber: 0
> replacesAggregate: false
> replacesWindowFunctionCall: false
> tableName: null
> nestingLevel: -1
> sourceLevel: -1
> dataTypeServices: null
> rightOperand:
> org.apache.derby.impl.sql.compile.NumericConstantNode@34a434a4
> value: 100
> dataTypeServices: INTEGER NOT NULL
> havingClause:
> org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode@24622462
> operator:>
> methodName: greaterThan
> dataTypeServices: null
> leftOperand:
> org.apache.derby.impl.sql.compile.AggregateNode@73c273c2
> aggregateName: COUNT(*)
> distinct: false
> operator: null
> methodName: null
> dataTypeServices: null
> rightOperand:
> org.apache.derby.impl.sql.compile.NumericConstantNode@23722372
> value: 1
> dataTypeServices: INTEGER NOT NULL
> -----------------------------
>
> If I provide any conditional operator like AND or OR to where clause or
> having clause, then the structure is different for both of these two.
>
> My problem is, I want to take having clause data and where clause data from
> the query.
>
> So, not able to get the data properly due to the structure is changing for
> these two items every time.
>
> Is there any way to differentiate these two elements ?
> or can we add any parent node for these two like others?
> If not possible could you please give some advice on 'where I need to change
> or which way I need to move' for solving my issue ?
>
> Thanks in advance,
> Venu.
Hi Venu,

If I understand correctly, the problem you are facing is that your
Visitor can't tell the difference between a whereClause and a
havingClause. They are both ValueNodes rather some more refined types
specific to each kind of clause. And they don't contain backpointers to
their parent SelectNodes so you can't get more information out of the
parent node when processing the whereClause and havingClause.

I don't have any clever solution to this problem. If I were tackling
this problem, I would maintain some extra state in the Visitor, say a
HashSet of whereClause references and another HashSet of havingClause
references. I would fill in these HashSets as I processed the
SelectNodes. Then when the Visitor got to the actual whereClause and
havingClause, it could look them up in the HashSets in order to figure
out what it was handing at the moment. Because the clause fields are
package private, the Visitor would need to live in the same package as
SelectNode. So in order to not incur a sealing violation, the Visitor
would have to be injected into derby.jar.

That's a little complicated, but it might help.

Hope that's useful,
-Rick
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p132167.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>

Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

venu
This post was updated on .
Hi Rick,
I tried your solution (regarding AST parser) and its working perfectly.
I have some problems could you please give me some advice on these issues.

1. We added TreeWalker.java file in java\engine\org\apache\derby\impl\sql\compile folder.
While generating derby.jar the class file was generated but class file was not inserted into the jar file.
How can I add  TreeWalker.class into derby.jar file ?
I added the class into DBMSnodes.properties file but still the class file not found in jar.
Could you please tell me how to add this? or if possible add this file in the source then I can modify it.

        2. I did some changes on these files for solving order by and group by issue. If you have some time, could you please look into these codes ?
        If you feel these codes are correct could you please add those into the official version or even development version? If not, advice me on 'where to change or strategy which I need to follow'.

\java\engine\org\apache\derby\impl\sql\compile\ValueNodeList.java   --> added below method

public ArrayList getValueNodes(){
        ArrayList valueNode = new ArrayList();
        for (int index = 0; index < size(); index++) {
              valueNode.add((QueryTreeNode)elementAt(index));
        }
        return valueNode;
}

\java\engine\org\apache\derby\impl\sql\compile\CastNode.java  --> added below method

public ValueNode getCastOperand(){
        return castOperand;
}


\java\engine\org\apache\derby\impl\sql\compile\SelectNode.java   --> modified below method

        void acceptChildren(Visitor v)  throws StandardException
{
        super.acceptChildren(v);

        if (fromList != null)
        {
                NodeFilterSingletone.getInstance().setFromListState(true);   --> Added this line
                fromList = (FromList)fromList.accept(v);
                NodeFilterSingletone.getInstance().setFromListState(false);  -->  Added this line

        }

        if (whereClause != null)
        {
                NodeFilterSingletone.getInstance().setWhereClauseState(true);  -->  Added this line
                whereClause = (ValueNode)whereClause.accept(v);
                NodeFilterSingletone.getInstance().setWhereClauseState(false);  --> Added this line
        }

        if (wherePredicates != null)
        {
                wherePredicates = (PredicateList)wherePredicates.accept(v);
        }

        if (havingClause != null) {
                NodeFilterSingletone.getInstance().setHavingClauseState(true);  --> Added this line
                havingClause = (ValueNode)havingClause.accept(v);
                NodeFilterSingletone.getInstance().setHavingClauseState(false);  --> Added this line
        }

        // visiting these clauses was added as part of DERBY-6263. a better fix might be to fix the
        // visitor rather than skip it.
        if ( !(v instanceof HasCorrelatedCRsVisitor) )
        {
            if (selectSubquerys != null)
            {
                selectSubquerys = (SubqueryList) selectSubquerys.accept( v );
            }

            if (whereSubquerys != null)
            {
                whereSubquerys = (SubqueryList) whereSubquerys.accept( v );
            }

            if (groupByList != null) {
                NodeFilterSingletone.getInstance().setGroupbyState(true);  --> Added this line
                groupByList = (GroupByList) groupByList.accept( v );
                NodeFilterSingletone.getInstance().setGroupbyState(false);  --> Added this line
            }

            if (orderByLists[0] != null) {
                for (int i = 0; i < orderByLists.length; i++) {
                        NodeFilterSingletone.getInstance().setOrderbyState(true);  --> Added this line
                        orderByLists[i] = (OrderByList) orderByLists[ i ].accept( v );
                        NodeFilterSingletone.getInstance().setOrderbyState(false);  --> Added this line
                }

            }

            if (offset != null) {
                offset = (ValueNode) offset.accept( v );
            }

            if (fetchFirst != null) {
                fetchFirst = (ValueNode) fetchFirst.accept( v );
            }

            if (preJoinFL != null)
            {
                preJoinFL = (FromList) preJoinFL.accept( v );
            }

            if (windows != null)
            {
                windows = (WindowList) windows.accept( v );
            }
        }
}

\java\engine\org\apache\derby\impl\sql\compile\NodeFilterSingletone.java   --> Added this class along with TreeWalker.java class

public class NodeFilterSingletone {

        private static NodeFilterSingletone instance = null;

        private boolean fromListState = false;
        private boolean whereClauseState = false;
        private boolean havingClauseState = false;
        private boolean groupbyState = false;
        private boolean orderbyState = false;

        protected NodeFilterSingletone() {
        }

        public static NodeFilterSingletone getInstance() {
                if (instance == null) {
                        instance = new NodeFilterSingletone();
                }
                return instance;
        }

        public boolean isFromListState() {
                return fromListState;
        }

        public void setFromListState(boolean fromListState) {
                this.fromListState = fromListState;
        }

        public boolean isWhereClauseState() {
                return whereClauseState;
        }

        public void setWhereClauseState(boolean whereClauseState) {
                this.whereClauseState = whereClauseState;
        }

        public boolean isHavingClauseState() {
                return havingClauseState;
        }

        public void setHavingClauseState(boolean havingClauseState) {
                this.havingClauseState = havingClauseState;
        }

        public boolean isGroupbyState() {
                return groupbyState;
        }

        public void setGroupbyState(boolean groupbyState) {
                this.groupbyState = groupbyState;
        }

        public boolean isOrderbyState() {
                return orderbyState;
        }

        public void setOrderbyState(boolean orderbyState) {
                this.orderbyState = orderbyState;
        }
}
\java\engine\org\apache\derby\impl\sql\compile\TreeWalker.java:
(Modified this method)

public Visitable visit(Visitable node) throws StandardException {
                if (!visitedNodes.contains(node)) {
                        depth++;
                       
                        if(NodeFilterSingletone.getInstance().isFromListState()){
                                fromListNodes.add(node);
                        }
                        if(NodeFilterSingletone.getInstance().isWhereClauseState()){
                                whereClauseNodes.add(node);
                        }
                        if(NodeFilterSingletone.getInstance().isHavingClauseState()){
                                havingClauseNodes.add(node);
                        }
                        if(NodeFilterSingletone.getInstance().isGroupbyState()){
                                groupbyNodes.add(node);
                        }
                        if(NodeFilterSingletone.getInstance().isOrderbyState()){
                                orderbyNodes.add(node);
                        }
                        visitedNodes.add(node);
                        visitedNodesList.add(node);
                       
                        StringBuffer buffer = new StringBuffer();

                        for (int i = 0; i < depth; i++) {
                                buffer.append(INDENTATION);
                        }
                        buffer.append(node.getClass().getName());

                        println(buffer.toString());
                        node.accept(this);

                        depth--;
                }

                return node;
        }

Thanks,
Venu.
(Sorry for adding so much code in the post)
Reply | Threaded
Open this post in threaded view
|

Re: Using ASTParser and TreeWalker for parsing SQL query

Rick Hillegas
Hi Venu,

Glad to hear that this technique is working for you. A couple general
comments:

A) If you need code added to the Derby engine, the best approach would
be to file a JIRA and attach a patch file. That makes it easier for
committers to evaluate your improvements and track changes to the codebase.

B) I see that you want to introduce a new NodeFilterSingletone class,
whose purpose seems to be to mark the boundaries of SQL clauses. Keep an
eye on https://issues.apache.org/jira/browse/DERBY-6434. I expect to
propose a different solution to that problem in an upcoming patch. You
may be able to use it.

C) It's probably best to move this discussion to derby-dev. That's a
better forum for discussing changes to the Derby codebase.

One other comment inline...

On 1/9/14 6:47 PM, venu wrote:

> Hi Rick,
> I tried your solution (regarding AST parser) and its working perfectly.
> I have some problems could you please give me some advice on these issues.
>
> 1. We added TreeWalker.java file in
> java\engine\org\apache\derby\impl\sql\compile folder.
> While generating derby.jar the class file was generated but class file was
> not inserted into the jar file.
> How can I add  TreeWalker.class into derby.jar file ?
> I added the class into DBMSnodes.properties file but still the class file
> not found in jar.
> Could you please tell me how to add this? or if possible add this file in
> the source then I can modify it.
Try adding the class to tools/jar/extraDBMSclasses.properties.

Hope this helps,
-Rick

>          2. I did some changes on these files for solving order by and group
> by issue. If you have some time, could you please look into these codes ?
>          If you feel these codes are correct could you please add those into
> the official version or even development version? If not, advice me on
> 'where to change or strategy which I need to follow'.
>
> \java\engine\org\apache\derby\impl\sql\compile\ValueNodeList.java   -->
> added below method
>
> public ArrayList getValueNodes(){
>          ArrayList valueNode = new ArrayList();
>          for (int index = 0; index<  size(); index++) {
>                valueNode.add((QueryTreeNode)elementAt(index));
>          }
>          return valueNode;
> }
>
> \java\engine\org\apache\derby\impl\sql\compile\CastNode.java  -->  added
> below method
>
> public ValueNode getCastOperand(){
>          return castOperand;
> }
>
>
> \java\engine\org\apache\derby\impl\sql\compile\SelectNode.java   -->
> modified below method
>
>          void acceptChildren(Visitor v)  throws StandardException
> {
>          super.acceptChildren(v);
>
>          if (fromList != null)
>          {
>                  NodeFilterSingletone.getInstance().setFromListState(true);
> -->  Added this line
>                  fromList = (FromList)fromList.accept(v);
>                  NodeFilterSingletone.getInstance().setFromListState(false);
> -->   Added this line
>
>          }
>
>          if (whereClause != null)
>          {
>
> NodeFilterSingletone.getInstance().setWhereClauseState(true);  -->   Added
> this line
>                  whereClause = (ValueNode)whereClause.accept(v);
>
> NodeFilterSingletone.getInstance().setWhereClauseState(false);  -->  Added
> this line
>          }
>
>          if (wherePredicates != null)
>          {
>                  wherePredicates = (PredicateList)wherePredicates.accept(v);
>          }
>
>          if (havingClause != null) {
>
> NodeFilterSingletone.getInstance().setHavingClauseState(true);  -->  Added
> this line
>                  havingClause = (ValueNode)havingClause.accept(v);
>
> NodeFilterSingletone.getInstance().setHavingClauseState(false);  -->  Added
> this line
>          }
>
>          // visiting these clauses was added as part of DERBY-6263. a better
> fix might be to fix the
>          // visitor rather than skip it.
>          if ( !(v instanceof HasCorrelatedCRsVisitor) )
>          {
>              if (selectSubquerys != null)
>              {
>                  selectSubquerys = (SubqueryList) selectSubquerys.accept( v
> );
>              }
>
>              if (whereSubquerys != null)
>              {
>                  whereSubquerys = (SubqueryList) whereSubquerys.accept( v );
>              }
>
>              if (groupByList != null) {
>                  NodeFilterSingletone.getInstance().setGroupbyState(true);
> -->  Added this line
>                  groupByList = (GroupByList) groupByList.accept( v );
>                  NodeFilterSingletone.getInstance().setGroupbyState(false);
> -->  Added this line
>              }
>
>              if (orderByLists[0] != null) {
>                  for (int i = 0; i<  orderByLists.length; i++) {
>
> NodeFilterSingletone.getInstance().setOrderbyState(true);  -->  Added this
> line
>                          orderByLists[i] = (OrderByList) orderByLists[ i
> ].accept( v );
>
> NodeFilterSingletone.getInstance().setOrderbyState(false);  -->  Added this
> line
>                  }
>
>              }
>
>              if (offset != null) {
>                  offset = (ValueNode) offset.accept( v );
>              }
>
>              if (fetchFirst != null) {
>                  fetchFirst = (ValueNode) fetchFirst.accept( v );
>              }
>
>              if (preJoinFL != null)
>              {
>                  preJoinFL = (FromList) preJoinFL.accept( v );
>              }
>
>              if (windows != null)
>              {
>                  windows = (WindowList) windows.accept( v );
>              }
>          }
> }
>
> \java\engine\org\apache\derby\impl\sql\compile\ NodeFilterSingletone.java
> -->  Added this class along with TreeWalker.java class
>
> public class NodeFilterSingletone {
>
>          private static NodeFilterSingletone instance = null;
>
>          private boolean fromListState = false;
>          private boolean whereClauseState = false;
>          private boolean havingClauseState = false;
>          private boolean groupbyState = false;
>          private boolean orderbyState = false;
>
>          protected NodeFilterSingletone() {
>          }
>
>          public static NodeFilterSingletone getInstance() {
>                  if (instance == null) {
>                          instance = new NodeFilterSingletone();
>                  }
>                  return instance;
>          }
>
>          public boolean isFromListState() {
>                  return fromListState;
>          }
>
>          public void setFromListState(boolean fromListState) {
>                  this.fromListState = fromListState;
>          }
>
>          public boolean isWhereClauseState() {
>                  return whereClauseState;
>          }
>
>          public void setWhereClauseState(boolean whereClauseState) {
>                  this.whereClauseState = whereClauseState;
>          }
>
>          public boolean isHavingClauseState() {
>                  return havingClauseState;
>          }
>
>          public void setHavingClauseState(boolean havingClauseState) {
>                  this.havingClauseState = havingClauseState;
>          }
>
>          public boolean isGroupbyState() {
>                  return groupbyState;
>          }
>
>          public void setGroupbyState(boolean groupbyState) {
>                  this.groupbyState = groupbyState;
>          }
>
>          public boolean isOrderbyState() {
>                  return orderbyState;
>          }
>
>          public void setOrderbyState(boolean orderbyState) {
>                  this.orderbyState = orderbyState;
>          }
> }
>
>
> Thanks,
> Venu.
> (Sorry for adding so much code in the post)
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p136426.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>