How to encode the evaluation order for a SQL query using Java?

Question

I am trying to develop a SQL query evaluator using JSQLParser, and I am really confused as to how do I decide on the evaluation order, like if I have a query of the following form

Select * 
From A,B,C 
Where A.id=B.id 
  And B.id=C.id 
  And A.id=C.id

the problem is how do I build a parse tree or an expression tree out of it. I need some pointers to solve this problem. Also To add, I am thinking of solving this problem using a couple of stacks that is an operator stack and an operand stack. I need some confirmation as to if this is a possible way to comprehend this problem?

Answer 1

That is an interesting question. So may I introduce another fork of JSqlParser (https://github.com/JSQLParser/JSqlParser). I used it so far for metadata analysis of SQLs and till now evaluation had not to be done.

Here now my two cents. The operator precedence is forged partly within JSqlParsers grammer. (@jbaliuka: I looked into your fork and I think it was based on an older version of JSqlParser. The latest developments at sourceforge (2010?) introduced operator precedence for addition and multiplication. Additional your analytic expression parsing triggered some changes at my fork. It would be kind if you could give it a try.)

Here is a very simple expression evaluator for addition and multiplication using JSqlParser V0.8.8. First JSqlParser delivers a parse tree or more precise a from this tree inherited object hierarchy which I traverse using the visitor structure of JSqlParser. Sure for a complete version much work still has to be done. This is more a prove of concept. It was interesting to see for me, that even parenthesis precedence is correctly done for my examples.

import java.util.Stack;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.arithmetic.Addition;
import net.sf.jsqlparser.expression.operators.arithmetic.Multiplication;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.util.deparser.ExpressionDeParser;

public class SimpleEvaluateExpr {

    public static void main( String[] args ) throws JSQLParserException {
        //examples
        evaluate("4+5*6");
        evaluate("4*5+6");
        evaluate("4*(5+6)");
        evaluate("4*(5+6)*(2+3)");
    }

    static void evaluate(String expr) throws JSQLParserException {
        //here is the stack you mentioned ;)
        final Stack<Long> stack = new Stack<Long>();

        System.out.println("expr=" + expr);
        Expression parseExpression = CCJSqlParserUtil.parseExpression(expr);
        //Deparser traverses the complete expression hierarchy. It was mainly used
        //for SQL output but this is now done using toString but is always a useful
        //traversal tool.
        ExpressionDeParser deparser = new ExpressionDeParser() {
            @Override
            public void visit(Addition addition) {
                super.visit(addition); 

                long sum1 = stack.pop();
                long sum2 = stack.pop();

                stack.push(sum1 + sum2);
            }

            @Override
            public void visit(Multiplication multiplication) {
                super.visit(multiplication); 

                long fac1 = stack.pop();
                long fac2 = stack.pop();

                stack.push(fac1 * fac2);
            }

            @Override
            public void visit(LongValue longValue) {
                super.visit(longValue); 
                stack.push(longValue.getValue());
            }
        };
        StringBuilder b = new StringBuilder();
        deparser.setBuffer(b);
        parseExpression.accept(deparser);

        System.out.println(expr + " = " + stack.pop() );
    }
}

Here is the output:

expr=4+5*6
4+5*6 = 34
expr=4*5+6
4*5+6 = 26
expr=4*(5+6)
4*(5+6) = 44
expr=4*(5+6)*(2+3)
4*(5+6)*(2+3) = 220

So the parse tree for the first example expression is something like:

• Addition
  • LongValue (4)
  • Multiplication
    • LongValue(5)
    • LongValue(6)

The visitor traverses in depth first order.

Answer 2

Operator precedence is normally defined by grammar and visitor should visit nodes in defined order recursively, it should be possible to use plain recursion to evaluate expressions in visitor implementation. Stack approach is correct too but it is not necessary.
I am not sure about jsqlparse grammar, it works fine for AST transformations but I did not test it for evaluation. If it won't work then you will have to fix it by yourself because this library is not maintained anymore, I maintain one of jsqlparser forks by myself too, this fork ignores operator precedence.