GROUP BY Month(dateField) not working

[bobwoofix]

A valid H2 Database query is not working with JSQLParser.

Here is the original query:
SELECT SUM(orderTotalValue) as value, MONTH(invoiceDate) as month, YEAR(invoiceDate) as year FROM invoice.Invoices WHERE projectID = 1 GROUP BY MONTH(invoiceDate), YEAR(invoiceDate) ORDER BY YEAR(invoiceDate) DESC, MONTH(invoiceDate) DESC

Having pulled it to bits until we discovered which part was not working, we discovered the GROUP BY MONTH(invoiceDate), YEAR(invoiceDate) was the issue. Even with just GROUP BY MONTH(invoiceDate) it still didn't work.

The error thrown was:
sf. jsqlparser. parser. ParseException: Encountered " "(" "( "" at line 1, column 142. Was expecting one of: "LIMIT" . . . "ORDER" . . . "OFFSET" . . . "HAVING" . . . ";" . . . "," . . . ". " . . . ". " . . . "," . . . "HAVING" . . . "ORDER" . . . "LIMIT" . . . "OFFSET" . . . "LIMIT" . . . at net. sf. jsqlparser. parser. CCJSqlParser. generateParseException(CCJSqlParser. java:5359) at net. sf. jsqlparser. parser. CCJSqlParser. jj_consume_token(CCJSqlParser. java:5235) at net. sf. jsqlparser. parser. CCJSqlParser. Statement(CCJSqlParser. java:146) at net. sf. jsqlparser. parser. CCJSqlParserManager. parse(CCJSqlParserManager. java:38)

Have updated to the latest version - still not working.

[bobwoofix]

Have we missed something...? Is there something we are meant to be calling to make this work? Or is it not implimented yet?

We cannot find any reference to this issue or implimentation anywhere.

[wumpz]

I have introduced a test using your SQL (SelectTest.testGroupByProblemIssue482). It parses without any parser modification.
As I was a bit lazy deploying a compiled version of 1.2-SNAPSHOT. Could you try to build it by your own and test this version? Did you compile already? Which version did you reference on?

[bobwoofix]

We are using the latest (1.1).

Here is our code - I am not sure if we are missing something.

EDIT: Trying to format code to make it easier to read - sorry, didn't mean to close issue.

`package com.woogloo.api.data.userdb;

import com.woogloo.common.DataAPI;
import com.woogloo.common.Log;
import java.io.StringReader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.AllComparisonExpression;
import net.sf.jsqlparser.expression.AnyComparisonExpression;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.CaseExpression;
import net.sf.jsqlparser.expression.DateValue;
import net.sf.jsqlparser.expression.DoubleValue;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.ExpressionVisitor;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.InverseExpression;
import net.sf.jsqlparser.expression.JdbcParameter;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.NullValue;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.TimeValue;
import net.sf.jsqlparser.expression.TimestampValue;
import net.sf.jsqlparser.expression.WhenClause;
import net.sf.jsqlparser.expression.operators.arithmetic.Addition;
import net.sf.jsqlparser.expression.operators.arithmetic.Division;
import net.sf.jsqlparser.expression.operators.arithmetic.Multiplication;
import net.sf.jsqlparser.expression.operators.arithmetic.Subtraction;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.Between;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.IsNullExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsListVisitor;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.expression.operators.relational.MinorThan;
import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.FromItemVisitor;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectVisitor;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.Union;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.deparser.StatementDeParser;

/**

• Analyses SQL statements to get data out of them.

• @author Erin Drummond
  */
  public class SQLAnalyser {

  private Log log = Log.getInstance(SQLAnalyser.class);
  private String origQuery, schema, projectUrl;
  private Statement query;
  public static List reservedTables = Arrays.asList(new String[] { "USER", "USERGROUP" });

  /**

  • Creates a new SQLAnalyser. Basically, this is responsible for
  • rewriting user-supplied queries to restrict data access.
  • @param query The query to mutate
  • @param schema The schema to inject
  • @param projectUrl The projectUrl of the calling website, used for expanding the reserved tables into application-specific views
    */
    public SQLAnalyser(String query, String schema, String projectUrl) throws JSQLParserException {
    this.origQuery = query;
    this.schema = schema;
    this.projectUrl = projectUrl;
    CCJSqlParserManager sql = new CCJSqlParserManager();
    this.query = sql.parse(new StringReader(query));
    }

  /**

  • @return "SELECT", "INSERT", "UPDATE", "DELETE" or "UNKNOWN" depending on query type
    */
    public String getType() {
    if (query instanceof Insert) {
    return "INSERT";
    } else if (query instanceof Update) {
    return "UPDATE";
    } else if (query instanceof Delete) {
    return "DELETE";
    } else if (query instanceof Select) {
    return "SELECT";
    }
    return "UNKNOWN";
    }

  /**

  • Returns all the tables involved in the query
  • @return The list of tables involved
    */
    public List getTables() {
    TablesNamesFinder tnf = new TablesNamesFinder();
    if (query instanceof Insert) {
    return tnf.getTableList((Insert) query);
    } else if (query instanceof Update) {
    return tnf.getTableList((Update) query);
    } else if (query instanceof Delete) {
    return tnf.getTableList((Delete) query);
    } else if (query instanceof Select) {
    return tnf.getTableList((Select) query);
    }
    return new ArrayList();
    }

  public static boolean contains(List tables, String[] tableArray){
  if(tables != null && tableArray != null && tableArray.length > 0){
  for(String eachTable:tables){
  for(String otherTable: tableArray){
  if(eachTable.equalsIgnoreCase(otherTable)){
  return true;
  }
  }
  }
  }
  return false;
  }

  /**

  • Expands Woogloo View identifiers into actual views.
  • Eg, User expands to Public.Users_On_{sitename} and UserGroup expands to
  • Public.UserGroups_On_{sitename}. It is the responsibility of the caller
  • to make sure these views exist.
  • Also, this method injects the appropriate schema into the tables, overwriting
  • any user-specified schema.
  • @return The query, with expanded view identifiers
    */
    public String setTables() {
    TablesNamesSetter tns = new TablesNamesSetter();
    if (query instanceof Insert) {
    return deParse(tns.setTableList((Insert) query));
    } else if (query instanceof Update) {
    return deParse(tns.setTableList((Update) query));
    } else if (query instanceof Delete) {
    return deParse(tns.setTableList((Delete) query));
    } else if (query instanceof Select) {
    return tns.setTableList((Select) query).toString();
    }
    return origQuery;
    }

  private String deParse(Statement s) {
  StringBuffer sb = new StringBuffer();
  StatementDeParser sdp = new StatementDeParser(sb);
  if (s instanceof Insert) {
  sdp.visit((Insert) s);
  } else if (s instanceof Update) {
  sdp.visit((Update) s);
  } else if (s instanceof Delete) {
  sdp.visit((Delete) s);
  } else if (s instanceof Select) {
  sdp.visit((Select) s);
  }
  if (sb.toString().equals("")) {
  sb.append(origQuery);
  }
  return sb.toString();
  }

  class TablesNamesFinder implements SelectVisitor, FromItemVisitor, ExpressionVisitor, ItemsListVisitor {

   private List<String> tables = new ArrayList<String>();
  
   public List<String> getTableList(Select select) {
       select.getSelectBody().accept(this);
       return mutateList(tables);
   }
  
   public List<String> getTableList(Insert i) {
       tables.add(i.getTable().getName());
       i.getItemsList().accept(this);
       return tables;
   }
  
   public List<String> getTableList(Update u) {
       tables.add(u.getTable().getName());
       for (Expression exp : (List<Expression>) u.getExpressions()) {
           exp.accept(this);
       }
       if (u.getWhere() != null) {
           u.getWhere().accept(this);
       }
       return tables;
   }
  
   public List<String> getTableList(Delete d) {
       tables.add(d.getTable().getName());
       if (d.getWhere() != null) {
           d.getWhere().accept(this);
       }
       return tables;
   }
  
   private List<String> mutateList(List<String> tables) {
       List<String> newList = new ArrayList<String>();
       for (String t : tables) {
           if (reservedTables.contains(t.toUpperCase())) {
               String nt = t + "_On_" + DataAPI.cleanNameForTable(projectUrl);
               newList.add(nt);
           } else {
               newList.add(t);
           }
       }
       return newList;
   }
  
   public void visit(PlainSelect plainSelect) {
       plainSelect.getFromItem().accept(this);
  
       if (plainSelect.getJoins() != null) {
           for (Iterator joinsIt = plainSelect.getJoins().iterator(); joinsIt.hasNext();) {
               Join join = (Join) joinsIt.next();
               join.getRightItem().accept(this);
           }
       }
       if (plainSelect.getWhere() != null) {
           plainSelect.getWhere().accept(this);
       }
  
   }
  
   public void visit(Union union) {
       for (Iterator iter = union.getPlainSelects().iterator(); iter.hasNext();) {
           PlainSelect plainSelect = (PlainSelect) iter.next();
           visit(plainSelect);
       }
   }
  
   public void visit(Table tableName) {
       String tableWholeName = tableName.getWholeTableName();
       tables.add(tableWholeName);
   }
  
   public void visit(SubSelect subSelect) {
       subSelect.getSelectBody().accept(this);
   }
  
   public void visit(Addition addition) {
       visitBinaryExpression(addition);
   }
  
   public void visit(AndExpression andExpression) {
       visitBinaryExpression(andExpression);
   }
  
   public void visit(Between between) {
       between.getLeftExpression().accept(this);
       between.getBetweenExpressionStart().accept(this);
       between.getBetweenExpressionEnd().accept(this);
   }
  
   public void visit(Column tableColumn) {
   }
  
   public void visit(Division division) {
       visitBinaryExpression(division);
   }
  
   public void visit(DoubleValue doubleValue) {
   }
  
   public void visit(EqualsTo equalsTo) {
       visitBinaryExpression(equalsTo);
   }
  
   public void visit(Function function) {
   }
  
   public void visit(GreaterThan greaterThan) {
       visitBinaryExpression(greaterThan);
   }
  
   public void visit(GreaterThanEquals greaterThanEquals) {
       visitBinaryExpression(greaterThanEquals);
   }
  
   public void visit(InExpression inExpression) {
       inExpression.getLeftExpression().accept(this);
       inExpression.getItemsList().accept(this);
   }
  
   public void visit(InverseExpression inverseExpression) {
       inverseExpression.getExpression().accept(this);
   }
  
   public void visit(IsNullExpression isNullExpression) {
   }
  
   public void visit(JdbcParameter jdbcParameter) {
   }
  
   public void visit(LikeExpression likeExpression) {
       visitBinaryExpression(likeExpression);
   }
  
   public void visit(ExistsExpression existsExpression) {
       existsExpression.getRightExpression().accept(this);
   }
  
   public void visit(LongValue longValue) {
   }
  
   public void visit(MinorThan minorThan) {
       visitBinaryExpression(minorThan);
   }
  
   public void visit(MinorThanEquals minorThanEquals) {
       visitBinaryExpression(minorThanEquals);
   }
  
   public void visit(Multiplication multiplication) {
       visitBinaryExpression(multiplication);
   }
  
   public void visit(NotEqualsTo notEqualsTo) {
       visitBinaryExpression(notEqualsTo);
   }
  
   public void visit(NullValue nullValue) {
   }
  
   public void visit(OrExpression orExpression) {
       visitBinaryExpression(orExpression);
   }
  
   public void visit(Parenthesis parenthesis) {
       parenthesis.getExpression().accept(this);
   }
  
   public void visit(StringValue stringValue) {
   }
  
   public void visit(Subtraction subtraction) {
       visitBinaryExpression(subtraction);
   }
  
   public void visitBinaryExpression(BinaryExpression binaryExpression) {
       binaryExpression.getLeftExpression().accept(this);
       binaryExpression.getRightExpression().accept(this);
   }
  
   public void visit(ExpressionList expressionList) {
       for (Iterator iter = expressionList.getExpressions().iterator(); iter.hasNext();) {
           Expression expression = (Expression) iter.next();
           expression.accept(this);
       }
  
   }
  
   public void visit(DateValue dateValue) {
   }
  
   public void visit(TimestampValue timestampValue) {
   }
  
   public void visit(TimeValue timeValue) {
   }
  
   public void visit(CaseExpression caseExpression) {
   }
  
   public void visit(WhenClause whenClause) {
   }
  
   public void visit(AllComparisonExpression allComparisonExpression) {
       allComparisonExpression.GetSubSelect().getSelectBody().accept(this);
   }
  
   public void visit(AnyComparisonExpression anyComparisonExpression) {
       anyComparisonExpression.GetSubSelect().getSelectBody().accept(this);
   }
  
   public void visit(SubJoin subjoin) {
       subjoin.getLeft().accept(this);
       subjoin.getJoin().getRightItem().accept(this);
   }
  

  }

  class TablesNamesSetter implements SelectVisitor, FromItemVisitor, ExpressionVisitor, ItemsListVisitor {

   public Select setTableList(Select select) {
       select.getSelectBody().accept(this);
       return select;
   }
  
   public Insert setTableList(Insert i) {
       i.getTable().setSchemaName(schema);
       i.getItemsList().accept(this);
       return i;
   }
  
   public Update setTableList(Update u) {
       u.getTable().setSchemaName(schema);
       for (Expression exp : (List<Expression>) u.getExpressions()) {
           exp.accept(this);
       }
       if (u.getWhere() != null) {
           u.getWhere().accept(this);
       }
       return u;
   }
  
   public Delete setTableList(Delete d) {
       d.getTable().setSchemaName(schema);
       if (d.getWhere() != null) {
           d.getWhere().accept(this);
       }
       return d;
   }
  
   public void visit(PlainSelect plainSelect) {
       plainSelect.getFromItem().accept(this);
  
       if (plainSelect.getJoins() != null) {
           for (Iterator joinsIt = plainSelect.getJoins().iterator(); joinsIt.hasNext();) {
               Join join = (Join) joinsIt.next();
               join.getRightItem().accept(this);
           }
       }
       if (plainSelect.getWhere() != null) {
           plainSelect.getWhere().accept(this);
       }
  
   }
  
   public void visit(Union union) {
       for (Iterator iter = union.getPlainSelects().iterator(); iter.hasNext();) {
           PlainSelect plainSelect = (PlainSelect) iter.next();
           visit(plainSelect);
       }
   }
  
   public void visit(Table tableName) {
       String name = tableName.getName();
       if (reservedTables.contains(name.toUpperCase())) {
           String newName = name + "_On_" + DataAPI.cleanNameForTable(projectUrl);
           tableName.setName(newName);
           tableName.setSchemaName("Public");
       } else {
           tableName.setSchemaName(schema);
       }
   }
  
   public void visit(SubSelect subSelect) {
       subSelect.getSelectBody().accept(this);
   }
  
   public void visit(Addition addition) {
       visitBinaryExpression(addition);
   }
  
   public void visit(AndExpression andExpression) {
       visitBinaryExpression(andExpression);
   }
  
   public void visit(Between between) {
       between.getLeftExpression().accept(this);
       between.getBetweenExpressionStart().accept(this);
       between.getBetweenExpressionEnd().accept(this);
   }
  
   public void visit(Column tableColumn) {
   }
  
   public void visit(Division division) {
       visitBinaryExpression(division);
   }
  
   public void visit(DoubleValue doubleValue) {
   }
  
   public void visit(EqualsTo equalsTo) {
       visitBinaryExpression(equalsTo);
   }
  
   public void visit(Function function) {
   }
  
   public void visit(GreaterThan greaterThan) {
       visitBinaryExpression(greaterThan);
   }
  
   public void visit(GreaterThanEquals greaterThanEquals) {
       visitBinaryExpression(greaterThanEquals);
   }
  
   public void visit(InExpression inExpression) {
       inExpression.getLeftExpression().accept(this);
       inExpression.getItemsList().accept(this);
   }
  
   public void visit(InverseExpression inverseExpression) {
       inverseExpression.getExpression().accept(this);
   }
  
   public void visit(IsNullExpression isNullExpression) {
   }
  
   public void visit(JdbcParameter jdbcParameter) {
   }
  
   public void visit(LikeExpression likeExpression) {
       visitBinaryExpression(likeExpression);
   }
  
   public void visit(ExistsExpression existsExpression) {
       existsExpression.getRightExpression().accept(this);
   }
  
   public void visit(LongValue longValue) {
   }
  
   public void visit(MinorThan minorThan) {
       visitBinaryExpression(minorThan);
   }
  
   public void visit(MinorThanEquals minorThanEquals) {
       visitBinaryExpression(minorThanEquals);
   }
  
   public void visit(Multiplication multiplication) {
       visitBinaryExpression(multiplication);
   }
  
   public void visit(NotEqualsTo notEqualsTo) {
       visitBinaryExpression(notEqualsTo);
   }
  
   public void visit(NullValue nullValue) {
   }
  
   public void visit(OrExpression orExpression) {
       visitBinaryExpression(orExpression);
   }
  
   public void visit(Parenthesis parenthesis) {
       parenthesis.getExpression().accept(this);
   }
  
   public void visit(StringValue stringValue) {
   }
  
   public void visit(Subtraction subtraction) {
       visitBinaryExpression(subtraction);
   }
  
   public void visitBinaryExpression(BinaryExpression binaryExpression) {
       binaryExpression.getLeftExpression().accept(this);
       binaryExpression.getRightExpression().accept(this);
   }
  
   public void visit(ExpressionList expressionList) {
       for (Iterator iter = expressionList.getExpressions().iterator(); iter.hasNext();) {
           Expression expression = (Expression) iter.next();
           expression.accept(this);
       }
  
   }
  
   public void visit(DateValue dateValue) {
   }
  
   public void visit(TimestampValue timestampValue) {
   }
  
   public void visit(TimeValue timeValue) {
   }
  
   public void visit(CaseExpression caseExpression) {
   }
  
   public void visit(WhenClause whenClause) {
   }
  
   public void visit(AllComparisonExpression allComparisonExpression) {
       allComparisonExpression.GetSubSelect().getSelectBody().accept(this);
   }
  
   public void visit(AnyComparisonExpression anyComparisonExpression) {
       anyComparisonExpression.GetSubSelect().getSelectBody().accept(this);
   }
  
   public void visit(SubJoin subjoin) {
       subjoin.getLeft().accept(this);
       subjoin.getJoin().getRightItem().accept(this);
   }
  

  }
  }
  `

[wumpz]

Tested the following code snipped using JSqlParser 0.9.5 with success. No problems in parsing your SQL:
EDIT: Tested the following with V0.9 as well with success.

public static void main(String args[]) throws JSQLParserException {
        String sql = "SELECT SUM(orderTotalValue) as value, MONTH(invoiceDate) as month, YEAR(invoiceDate) as year FROM invoice.Invoices WHERE projectID = 1 GROUP BY MONTH(invoiceDate), YEAR(invoiceDate) ORDER BY YEAR(invoiceDate) DESC, MONTH(invoiceDate) DESC";
		Select select = (Select)CCJSqlParserUtil.parse(sql);
		System.out.println(select.toString());
        
        Statements stmts = CCJSqlParserUtil.parseStatements(sql);
        System.out.println(stmts.toString());
    }

I assume the exception is thrown within your constructor, right? Then the rest of your code is not relevant.

Maybe there are some invisible control characters within your sql, that are stripped by copy paste.
Are you able to run this example? Could you build jSqlParser 1.2-SNAPSHOT with your test?

You see, I have no clue, why you get this exception.

[AnEmortalKid]

Worked for me for 1.2-SNAPSHOT:

import org.junit.Test;

import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.select.Select;

public class Issue482Test {

    @Test
    public void test() throws Exception {
        String sql = "SELECT SUM(orderTotalValue) as value, MONTH(invoiceDate) as month, YEAR(invoiceDate) as year FROM invoice.Invoices WHERE projectID = 1 GROUP BY MONTH(invoiceDate), YEAR(invoiceDate) ORDER BY YEAR(invoiceDate) DESC, MONTH(invoiceDate) DESC";
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        System.out.println(select.toString());

        Statements stmts = CCJSqlParserUtil.parseStatements(sql);
        System.out.println(stmts.toString());
    }

}

I can attach the result of mvn surefire:test -Dtest=Issue482Test >> out.log , it's about 10K lines.

out.log

[wumpz]

So at least two developers are able to parse your query successfully. Should we close it? Do our simple examples work for you?

[wumpz]

closing due to no response