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());
}