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