SelectValidatorTest.java
/*-
* #%L
* JSQLParser library
* %%
* Copyright (C) 2004 - 2020 JSQLParser
* %%
* Dual licensed under GNU LGPL 2.1 or Apache License 2.0
* #L%
*/
package net.sf.jsqlparser.util.validation.validator;
import java.util.Arrays;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.feature.Feature;
import net.sf.jsqlparser.util.validation.ValidationTestAsserts;
import net.sf.jsqlparser.util.validation.feature.DatabaseType;
import net.sf.jsqlparser.util.validation.feature.FeaturesAllowed;
import net.sf.jsqlparser.util.validation.feature.MariaDbVersion;
import org.junit.jupiter.api.Test;
public class SelectValidatorTest extends ValidationTestAsserts {
@Test
public void testValidationSelectNotAllowed() throws JSQLParserException {
String sql = "SELECT 1";
validateNotAllowed(sql, 1, 1, FeaturesAllowed.DDL, Feature.select);
}
@Test
public void testValidationSelectDistinct() throws JSQLParserException {
String sql = "SELECT DISTINCT a, b FROM tab";
validateNoErrors(sql, 1, DatabaseType.DATABASES);
}
@Test
public void testValidationSelectUnique() throws JSQLParserException {
String sql = "SELECT UNIQUE a, b FROM tab";
validateNoErrors(sql, 1, DatabaseType.ORACLE, MariaDbVersion.ORACLE_MODE);
}
@Test
public void testValidationFetchAndOffset() throws JSQLParserException {
for (String sql : Arrays.asList(
"SELECT * FROM mytable t WHERE t.col = 9 ORDER BY t.id FETCH FIRST 5 ROWS ONLY",
"SELECT * FROM mytable t WHERE t.col = 9 ORDER BY t.id OFFSET 3 ROWS",
"SELECT * FROM mytable t WHERE t.col = 9 ORDER BY t.id OFFSET 3 ROWS FETCH NEXT 5 ROWS ONLY")) {
validateNoErrors(sql, 1, DatabaseType.ORACLE, DatabaseType.SQLSERVER);
}
}
@Test
public void testValidationUnion() throws JSQLParserException {
String sql = "SELECT * FROM mytable WHERE mytable.col = 9 UNION " //
+ "SELECT * FROM mytable3 WHERE mytable3.col = ?";
validateNoErrors(sql, 1, DatabaseType.DATABASES);
}
@Test
public void testValidationSqlIntersect() throws Exception {
String sql = "(SELECT * FROM a) INTERSECT (SELECT * FROM b)";
validateNoErrors(sql, 1, DatabaseType.ORACLE, DatabaseType.SQLSERVER, DatabaseType.MARIADB,
DatabaseType.POSTGRESQL, DatabaseType.H2);
}
@Test
public void testValidationForUpdateWaitWithTimeout() throws JSQLParserException {
String sql = "SELECT * FROM mytable FOR UPDATE WAIT 60";
validateNoErrors(sql, 1, DatabaseType.ORACLE, DatabaseType.MARIADB);
}
@Test
public void testValidationForShare() throws JSQLParserException {
String sql = "SELECT * FROM mytable FOR SHARE";
validateNoErrors(sql, 1, DatabaseType.MYSQL, DatabaseType.POSTGRESQL);
}
@Test
public void testValidationForPostgresShare() throws JSQLParserException {
String sql = "SELECT * FROM mytable FOR KEY SHARE";
validateNoErrors(sql, 1, DatabaseType.POSTGRESQL);
String sql2 = "SELECT * FROM mytable FOR NO KEY UPDATE";
validateNoErrors(sql2, 1, DatabaseType.POSTGRESQL);
// Not familiar with oracle, please modify if supported.
validateNotSupported(sql2, 1, 1, DatabaseType.ORACLE,
Feature.selectForNoKeyUpdate);
}
@Test
public void testValidationForUpdateNoWait() throws JSQLParserException {
String sql = "SELECT * FROM mytable FOR UPDATE NOWAIT";
validateNoErrors(sql, 1, DatabaseType.ORACLE, DatabaseType.MARIADB,
DatabaseType.POSTGRESQL, DatabaseType.MYSQL);
}
@Test
public void testValidationJoinOuterSimple() throws JSQLParserException {
String sql = "SELECT * FROM foo AS f, OUTER bar AS b WHERE f.id = b.id";
validateNotSupported(sql, 1, 1, DatabaseType.ORACLE, Feature.joinOuterSimple);
}
@Test
public void testValidationJoin() throws JSQLParserException {
for (String sql : Arrays.asList(
"SELECT t1.col, t2.col, t1.id FROM tab1 t1, tab2 t2 WHERE t1.id = t2.id",
"SELECT t1.col, t2.col, t1.id FROM tab1 t1 JOIN tab2 t2 ON t1.id = t2.id",
"SELECT t1.col, t2.col, t1.id FROM tab1 t1 INNER JOIN tab2 t2 ON t1.id = t2.id")) {
validateNoErrors(sql, 1, DatabaseType.DATABASES);
}
}
@Test
public void testOracleHierarchicalQuery() throws JSQLParserException {
String sql =
"SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name";
validateNoErrors(sql, 1, DatabaseType.ORACLE);
}
@Test
public void testOracleJoin() throws JSQLParserException {
validateNoErrors("SELECT * FROM tabelle1, tabelle2 WHERE tabelle1.a = tabelle2.b(+)", 1,
DatabaseType.ORACLE);
}
@Test
public void testValidationLeftRightJoin() throws JSQLParserException {
for (String sql : Arrays
.asList("SELECT t1.col, t2.col, t1.id FROM tab1 t1 LEFT JOIN tab2 t2 ON t1.id = t2.id",
"SELECT t1.col, t2.col, t1.id FROM tab1 t1 LEFT OUTER JOIN tab2 t2 ON t1.id = t2.id",
"SELECT t1.col, t2.col, t1.id FROM tab1 t1 RIGHT JOIN tab2 t2 ON t1.id = t2.id",
"SELECT t1.col, t2.col, t1.id FROM tab1 t1 RIGHT OUTER JOIN tab2 t2 ON t1.id = t2.id",
"SELECT t1.col, t2.col, t1.id FROM tab1 t1 OUTER JOIN tab2 t2 ON t1.id = t2.id")) {
validateNoErrors(sql, 1, DatabaseType.DATABASES);
}
}
@Test
public void testValidationWith() throws JSQLParserException {
String statement = "WITH DINFO (DEPTNO, AVGSALARY, EMPCOUNT) AS "
+ "(SELECT OTHERS.WORKDEPT, AVG(OTHERS.SALARY), COUNT(*) FROM EMPLOYEE AS OTHERS "
+ "GROUP BY OTHERS.WORKDEPT), DINFOMAX AS (SELECT MAX(AVGSALARY) AS AVGMAX FROM DINFO) "
+ "SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY, DINFO.AVGSALARY, DINFO.EMPCOUNT, DINFOMAX.AVGMAX "
+ "FROM EMPLOYEE AS THIS_EMP INNER JOIN DINFO INNER JOIN DINFOMAX "
+ "WHERE THIS_EMP.JOB = 'SALESREP' AND THIS_EMP.WORKDEPT = DINFO.DEPTNO";
validateNoErrors(statement, 1, DatabaseType.DATABASES);
}
@Test
public void testValidationWithRecursive() throws JSQLParserException {
String statement =
"WITH RECURSIVE t (n) AS ((SELECT 1) UNION ALL (SELECT n + 1 FROM t WHERE n < 100)) SELECT sum(n) FROM t";
validateNoErrors(statement, 1, DatabaseType.H2, DatabaseType.MARIADB, DatabaseType.MYSQL,
DatabaseType.SQLSERVER, DatabaseType.POSTGRESQL);
validateNotSupported(statement, 1, 1, DatabaseType.ORACLE, Feature.withItemRecursive);
}
@Test
public void testSelectMulipleExpressionList() {
String sql =
"SELECT * FROM mytable WHERE (SSN, SSM) IN (('11111111111111', '22222222222222'))";
validateNoErrors(sql, 1, DatabaseType.DATABASES);
}
@Test
public void testValidatePivotWithAlias() throws JSQLParserException {
validateNoErrors(
"SELECT * FROM (SELECT * FROM mytable LEFT JOIN mytable2 ON Factor_ID = Id) f PIVOT (max(f.value) FOR f.factoryCode IN (ZD, COD, SW, PH))",
1, DatabaseType.SQLSERVER);
}
@Test
public void testValidatePivotXml() throws JSQLParserException {
validateNoErrors("SELECT * FROM mytable PIVOT XML (count(a) FOR b IN ('val1'))", 1,
DatabaseType.SQLSERVER);
}
@Test
public void testValidateUnPivot() throws JSQLParserException {
validateNoErrors(
"select * from pivot_table unpivot (yearly_total for order_mode in (store as 'direct', internet as 'online')) order by year, order_mode",
1, DatabaseType.SQLSERVER);
}
@Test
public void testValidateSubJoin() throws JSQLParserException {
validateNoErrors(
"SELECT * FROM ((tabc c INNER JOIN tabn n ON n.ref = c.id) INNER JOIN taba a ON a.REF = c.id)",
1, DatabaseType.SQLSERVER);
}
@Test
public void testValidateTableFunction() {
for (String sql : Arrays.asList("SELECT f2 FROM SOME_FUNCTION()",
"SELECT f2 FROM SOME_FUNCTION(1, 'val')")) {
validateNoErrors(sql, 1, DatabaseType.POSTGRESQL, DatabaseType.H2,
DatabaseType.SQLSERVER);
}
}
@Test
public void testValidateLateral() throws JSQLParserException {
validateNoErrors(
"SELECT O.ORDERID, O.CUSTNAME, OL.LINETOTAL FROM ORDERS AS O, LATERAL(SELECT SUM(NETAMT) AS LINETOTAL FROM ORDERLINES AS LINES WHERE LINES.ORDERID = O.ORDERID) AS OL",
1, DatabaseType.POSTGRESQL, DatabaseType.ORACLE);
}
@Test
public void testValidateIssue1502() throws JSQLParserException {
validateNoErrors(
"select b.id, name ,(select name from Blog where name = 'sadf') as name2 "
+ ", category, owner, b.update_time "
+ "from Blog as b "
+ "left join Content "
+ "ON b.id = Content.blog_id "
+ "where name = 'sadf' order by Content.title desc",
1, DatabaseType.POSTGRESQL);
}
}