public void testFunctions() throws JSQLParserException {
    String statement = "SELECT MAX(id) as max FROM mytable WHERE mytable.col = 9";
    PlainSelect select =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals("max", ((SelectExpressionItem) select.getSelectItems().get(0)).getAlias());
    assertEquals(statement.toUpperCase(), select.toString().toUpperCase());

    statement = "SELECT MAX(id), AVG(pro) as myavg FROM mytable WHERE mytable.col = 9 GROUP BY pro";
    select =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals("myavg", ((SelectExpressionItem) select.getSelectItems().get(1)).getAlias());
    assertEquals(statement.toUpperCase(), select.toString().toUpperCase());

    statement = "SELECT MAX(a, b, c), COUNT(*), D FROM tab1 GROUP BY D";
    PlainSelect plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    Function fun =
        (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getExpression();
    assertEquals("MAX", fun.getName());
    assertEquals("b", ((Column) fun.getParameters().getExpressions().get(1)).getWholeColumnName());
    assertTrue(
        ((Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
            .isAllColumns());
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());

    statement = "SELECT {fn MAX(a, b, c)}, COUNT(*), D FROM tab1 GROUP BY D";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    fun = (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getExpression();
    assertTrue(fun.isEscaped());
    assertEquals("MAX", fun.getName());
    assertEquals("b", ((Column) fun.getParameters().getExpressions().get(1)).getWholeColumnName());
    assertTrue(
        ((Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
            .isAllColumns());
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());

    statement = "SELECT ab.MAX(a, b, c), cd.COUNT(*), D FROM tab1 GROUP BY D";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    fun = (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getExpression();
    assertEquals("ab.MAX", fun.getName());
    assertEquals("b", ((Column) fun.getParameters().getExpressions().get(1)).getWholeColumnName());
    fun = (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression();
    assertEquals("cd.COUNT", fun.getName());
    assertTrue(fun.isAllColumns());
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
  }
  public void testFrom() throws JSQLParserException {
    String statement =
        "SELECT * FROM mytable as mytable0, mytable1 alias_tab1, mytable2 as alias_tab2, (SELECT * FROM mytable3) AS mytable4 WHERE mytable.col = 9";
    String statementToString =
        "SELECT * FROM mytable as mytable0, mytable1 as alias_tab1, mytable2 as alias_tab2, (SELECT * FROM mytable3) AS mytable4 WHERE mytable.col = 9";

    PlainSelect plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals(3, plainSelect.getJoins().size());
    assertEquals("mytable0", ((Table) plainSelect.getFromItem()).getAlias());
    assertEquals("alias_tab1", ((Join) plainSelect.getJoins().get(0)).getRightItem().getAlias());
    assertEquals("alias_tab2", ((Join) plainSelect.getJoins().get(1)).getRightItem().getAlias());
    assertEquals("mytable4", ((Join) plainSelect.getJoins().get(2)).getRightItem().getAlias());
    assertEquals(statementToString.toUpperCase(), plainSelect.toString().toUpperCase());
  }
 public void testDistinct() throws JSQLParserException {
   String statement = "SELECT DISTINCT ON (myid) myid, mycol FROM mytable WHERE mytable.col = 9";
   PlainSelect plainSelect =
       (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
   assertEquals(
       "myid",
       ((Column)
               ((SelectExpressionItem) plainSelect.getDistinct().getOnSelectItems().get(0))
                   .getExpression())
           .getColumnName());
   assertEquals(
       "mycol",
       ((Column) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
           .getColumnName());
   assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
 }
  public void testJoin() throws JSQLParserException {
    String statement = "SELECT * FROM tab1 LEFT outer JOIN tab2 ON tab1.id = tab2.id";
    PlainSelect plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals(1, plainSelect.getJoins().size());
    assertEquals(
        "tab2",
        ((Table) ((Join) plainSelect.getJoins().get(0)).getRightItem()).getWholeTableName());
    assertEquals(
        "tab1.id",
        ((Column)
                ((EqualsTo) ((Join) plainSelect.getJoins().get(0)).getOnExpression())
                    .getLeftExpression())
            .getWholeColumnName());
    assertTrue(((Join) plainSelect.getJoins().get(0)).isOuter());
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());

    statement = "SELECT * FROM tab1 LEFT outer JOIN tab2 ON tab1.id = tab2.id INNER JOIN tab3";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals(2, plainSelect.getJoins().size());
    assertEquals(
        "tab3",
        ((Table) ((Join) plainSelect.getJoins().get(1)).getRightItem()).getWholeTableName());
    assertFalse(((Join) plainSelect.getJoins().get(1)).isOuter());
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());

    statement = "SELECT * FROM tab1 LEFT outer JOIN tab2 ON tab1.id = tab2.id JOIN tab3";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals(2, plainSelect.getJoins().size());
    assertEquals(
        "tab3",
        ((Table) ((Join) plainSelect.getJoins().get(1)).getRightItem()).getWholeTableName());
    assertFalse(((Join) plainSelect.getJoins().get(1)).isOuter());

    // implicit INNER
    statement = "SELECT * FROM tab1 LEFT outer JOIN tab2 ON tab1.id = tab2.id INNER JOIN tab3";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());

    statement =
        "SELECT * FROM TA2 LEFT outer JOIN O USING (col1, col2) where D.OasSD = 'asdf' And (kj >= 4 OR l < 'sdf')";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());

    statement = "SELECT * FROM tab1 INNER JOIN tab2 USING (id, id2)";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals(1, plainSelect.getJoins().size());
    assertEquals(
        "tab2",
        ((Table) ((Join) plainSelect.getJoins().get(0)).getRightItem()).getWholeTableName());
    assertFalse(((Join) plainSelect.getJoins().get(0)).isOuter());
    assertEquals(2, ((Join) plainSelect.getJoins().get(0)).getUsingColumns().size());
    assertEquals(
        "id2",
        ((Column) ((Join) plainSelect.getJoins().get(0)).getUsingColumns().get(1))
            .getWholeColumnName());
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());

    statement = "SELECT * FROM tab1 RIGHT OUTER JOIN tab2 USING (id, id2)";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());

    statement =
        "select * from foo as f LEFT INNER JOIN (bar as b RIGHT OUTER JOIN baz as z ON f.id = z.id) ON f.id = b.id";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
  }