com.alibaba.druid.sql.ast.expr.SQLQueryExpr Java Examples

The following examples show how to use com.alibaba.druid.sql.ast.expr.SQLQueryExpr. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void scriptOnFilterWithParams() throws SqlParseException {
    String query = "select * from x where script('doc[\\'field\\'].date.hourOfDay == x','x'=3) ";
    Select select = parser.parseSelect((SQLQueryExpr) queryToExpr(query));
    Condition condition = (Condition) select.getWhere().getWheres().get(0);
    Assert.assertEquals(Condition.OPEAR.SCRIPT, condition.getOpear());
    Assert.assertEquals(null, condition.getName());
    Assert.assertTrue(condition.getValue() instanceof ScriptFilter);
    ScriptFilter scriptFilter = (ScriptFilter) condition.getValue();
    Assert.assertEquals("doc['field'].date.hourOfDay == x", scriptFilter.getScript());
    Assert.assertTrue(scriptFilter.containsParameters());
    Map<String, Object> args = scriptFilter.getArgs();
    Assert.assertEquals(1, args.size());
    Assert.assertTrue(args.containsKey("x"));
    Assert.assertEquals(3, args.get("x"));

}
 
Example #2
Source File: SqlParser.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
public JoinSelect parseJoinSelect(SQLQueryExpr sqlExpr) throws SqlParseException {

        MySqlSelectQueryBlock query = (MySqlSelectQueryBlock) sqlExpr.getSubQuery().getQuery();

        List<From> joinedFrom = findJoinedFrom(query.getFrom());
        if (joinedFrom.size() != 2)
            throw new RuntimeException("currently supports only 2 tables join");

        JoinSelect joinSelect = createBasicJoinSelectAccordingToTableSource((SQLJoinTableSource) query.getFrom());
        List<Hint> hints = parseHints(query.getHints());
        joinSelect.setHints(hints);
        String firstTableAlias = joinedFrom.get(0).getAlias();
        String secondTableAlias = joinedFrom.get(1).getAlias();
        Map<String, Where> aliasToWhere = splitAndFindWhere(query.getWhere(), firstTableAlias, secondTableAlias);
        Map<String, List<SQLSelectOrderByItem>> aliasToOrderBy = splitAndFindOrder(query.getOrderBy(), firstTableAlias, secondTableAlias);
        List<Condition> connectedConditions = getConditionsFlatten(joinSelect.getConnectedWhere());
        joinSelect.setConnectedConditions(connectedConditions);
        fillTableSelectedJoin(joinSelect.getFirstTable(), query, joinedFrom.get(0), aliasToWhere.get(firstTableAlias), aliasToOrderBy.get(firstTableAlias), connectedConditions);
        fillTableSelectedJoin(joinSelect.getSecondTable(), query, joinedFrom.get(1), aliasToWhere.get(secondTableAlias), aliasToOrderBy.get(secondTableAlias), connectedConditions);

        updateJoinLimit(query.getLimit(), joinSelect);

        //todo: throw error feature not supported:  no group bys on joins ?
        return joinSelect;
    }
 
Example #3
Source File: SQLFunctionsTest.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void whereConditionLeftFunctionRightFunctionEqualTest() throws Exception {

    String query = "SELECT " +
            " * from " +
            TEST_INDEX_ACCOUNT + "/account " +
            " where floor(split(address,' ')[0]+0) = floor(split(address,' ')[0]+0) limit 1000  ";

    Select select = parser.parseSelect((SQLQueryExpr) queryToExpr(query));
    Where where = select.getWhere();
    Assert.assertTrue((where.getWheres().size() == 1));
    Assert.assertTrue(((Condition) (where.getWheres().get(0))).getValue() instanceof ScriptFilter);
    ScriptFilter scriptFilter = (ScriptFilter) (((Condition) (where.getWheres().get(0))).getValue());
    Assert.assertTrue(scriptFilter.getScript().contains("doc['address'].value.split(' ')[0]"));
    Pattern pattern = Pattern.compile("\\(\\(Comparable\\)floor_\\d+\\).compareTo\\(floor_\\d+\\) == 0");
    Matcher matcher = pattern.matcher(scriptFilter.getScript());
    Assert.assertTrue(matcher.find());
}
 
Example #4
Source File: SQLFunctionsTest.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void whereConditionVariableRightVariableEqualTest() throws Exception {

    String query = "SELECT " +
            " * from " +
            TEST_INDEX_ACCOUNT + "/account " +
            " where a = b limit 1000  ";

    SearchDao searchDao = MainTestSuite.getSearchDao() != null ? MainTestSuite.getSearchDao() : getSearchDao();
    System.out.println(searchDao.explain(query).explain().explain());

    Select select = parser.parseSelect((SQLQueryExpr) queryToExpr(query));
    Where where = select.getWhere();
    Assert.assertTrue((where.getWheres().size() == 1));
    Assert.assertTrue(((Condition) (where.getWheres().get(0))).getValue() instanceof ScriptFilter);
    ScriptFilter scriptFilter = (ScriptFilter) (((Condition) (where.getWheres().get(0))).getValue());
    Assert.assertTrue(scriptFilter.getScript().contains("doc['a'].value == doc['b'].value"));
}
 
Example #5
Source File: SQLFunctionsTest.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void whereConditionLeftFunctionRightPropertyGreatTest() throws Exception {

    String query = "SELECT " +
            " * from " +
            TEST_INDEX_ACCOUNT + "/account " +
            " where floor(split(address,' ')[0]+0) > b limit 1000  ";

    Select select = parser.parseSelect((SQLQueryExpr) queryToExpr(query));
    Where where = select.getWhere();
    Assert.assertTrue((where.getWheres().size() == 1));
    Assert.assertTrue(((Condition) (where.getWheres().get(0))).getValue() instanceof ScriptFilter);
    ScriptFilter scriptFilter = (ScriptFilter) (((Condition) (where.getWheres().get(0))).getValue());

    Assert.assertTrue(scriptFilter.getScript().contains("doc['address'].value.split(' ')[0]"));
    Pattern pattern = Pattern.compile("\\(\\(Comparable\\)floor_\\d+\\).compareTo\\(doc\\['b'\\].value\\) > 0");
    Matcher matcher = pattern.matcher(scriptFilter.getScript());
    Assert.assertTrue(matcher.find());

}
 
Example #6
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void castToLongTest() throws Exception {
    String query = "select cast(insert_time as long) from "+ TEST_INDEX_ACCOUNT + " limit 10";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    Field castField = select.getFields().get(0);
    Assert.assertTrue(castField instanceof MethodField);

    MethodField methodField = (MethodField) castField;
    Assert.assertEquals("script",castField.getName());

    String alias = (String) methodField.getParams().get(0).value;
    String scriptCode = (String) methodField.getParams().get(1).value;
    Assert.assertEquals("cast_insert_time",alias);
    Assert.assertTrue(scriptCode.contains("doc['insert_time'].value"));
    Assert.assertTrue(scriptCode.contains("Double.parseDouble(doc['insert_time'].value.toString()).longValue()"));
}
 
Example #7
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void castToFloatTest() throws Exception {
    String query = "select cast(age as float) from "+ TEST_INDEX_ACCOUNT + " limit 10";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    Field castField = select.getFields().get(0);
    Assert.assertTrue(castField instanceof MethodField);

    MethodField methodField = (MethodField) castField;
    Assert.assertEquals("script",castField.getName());

    String alias = (String) methodField.getParams().get(0).value;
    String scriptCode = (String) methodField.getParams().get(1).value;
    Assert.assertEquals("cast_age",alias);
    Assert.assertTrue(scriptCode.contains("doc['age'].value"));
    Assert.assertTrue(scriptCode.contains("Double.parseDouble(doc['age'].value.toString()).floatValue()"));
}
 
Example #8
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void castToIntTest() throws Exception {
    String query = "select cast(age as int) from "+ TEST_INDEX_ACCOUNT + "/account limit 10";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    Field castField = select.getFields().get(0);
    Assert.assertTrue(castField instanceof MethodField);

    MethodField methodField = (MethodField) castField;
    Assert.assertEquals("script",castField.getName());

    String alias = (String) methodField.getParams().get(0).value;
    String scriptCode = (String) methodField.getParams().get(1).value;
    Assert.assertEquals("cast_age",alias);
    Assert.assertTrue(scriptCode.contains("doc['age'].value"));
    Assert.assertTrue(scriptCode.contains("Double.parseDouble(doc['age'].value.toString()).intValue()"));
}
 
Example #9
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void castToStringTest() throws Exception {
    String query = "select cast(age as string) from "+ TEST_INDEX_ACCOUNT + "/account limit 10";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    Field castField = select.getFields().get(0);
    Assert.assertTrue(castField instanceof MethodField);

    MethodField methodField = (MethodField) castField;
    Assert.assertEquals("script",castField.getName());

    String alias = (String) methodField.getParams().get(0).value;
    String scriptCode = (String) methodField.getParams().get(1).value;
    Assert.assertEquals("cast_age",alias);
    Assert.assertTrue(scriptCode.contains("doc['age'].value.toString()"));
}
 
Example #10
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void castToDateTimeTest() throws Exception {
    String query = "select cast(age as datetime) from "+ TEST_INDEX_ACCOUNT + "/account limit 10";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    Field castField = select.getFields().get(0);
    Assert.assertTrue(castField instanceof MethodField);

    MethodField methodField = (MethodField) castField;
    Assert.assertEquals("script",castField.getName());

    String alias = (String) methodField.getParams().get(0).value;
    String scriptCode = (String) methodField.getParams().get(1).value;
    Assert.assertEquals("cast_age",alias);
    Assert.assertTrue(scriptCode.contains("doc['age'].value"));
    Assert.assertTrue(scriptCode.contains("new Date(Double.parseDouble(doc['age'].value.toString()).longValue())"));
}
 
Example #11
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void castToDoubleThenDivideTest() throws Exception {
    String query = "select cast(age as double)/2 from "+ TEST_INDEX_ACCOUNT + "/account limit 10";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    Field castField = select.getFields().get(0);
    Assert.assertTrue(castField instanceof MethodField);

    MethodField methodField = (MethodField) castField;
    Assert.assertEquals("script",castField.getName());

    String alias = (String) methodField.getParams().get(0).value;
    String scriptCode = (String) methodField.getParams().get(1).value;
    Assert.assertTrue(scriptCode.contains("doc['age'].value"));
    Assert.assertTrue(scriptCode.contains("Double.parseDouble(doc['age'].value.toString()).doubleValue()"));
    Assert.assertTrue(scriptCode.contains("/ 2"));
}
 
Example #12
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void castToDoubleTest() throws Exception {
    String query = "select cast(age as double) from "+ TEST_INDEX_ACCOUNT + "/account limit 10";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    Field castField = select.getFields().get(0);
    Assert.assertTrue(castField instanceof MethodField);

    MethodField methodField = (MethodField) castField;
    Assert.assertEquals("script",castField.getName());

    String alias = (String) methodField.getParams().get(0).value;
    String scriptCode = (String) methodField.getParams().get(1).value;
    Assert.assertEquals("cast_age",alias);
    Assert.assertTrue(scriptCode.contains("doc['age'].value"));
    Assert.assertTrue(scriptCode.contains("Double.parseDouble(doc['age'].value.toString()).doubleValue()"));
}
 
Example #13
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void joinParseFromsAreSplitedCorrectly() throws SqlParseException {
    String query = "SELECT a.firstname ,a.lastname , a.gender ,  d.holdersName ,d.name  FROM " +
            TEST_INDEX_ACCOUNT +
            "/account a " +
            "LEFT JOIN " +
            TEST_INDEX_DOG +
            "/dog d on d.holdersName = a.firstname" +
            " WHERE a.firstname = 'eliran' AND " +
            " (a.age > 10 OR a.balance > 2000)" +
            " AND d.age > 1";

    JoinSelect joinSelect = parser.parseJoinSelect((SQLQueryExpr) queryToExpr(query));
    List<From> t1From = joinSelect.getFirstTable().getFrom();

    Assert.assertNotNull(t1From);
    Assert.assertEquals(1, t1From.size());
    Assert.assertTrue(checkFrom(t1From.get(0), TEST_INDEX_ACCOUNT, "account", "a"));

    List<From> t2From = joinSelect.getSecondTable().getFrom();
    Assert.assertNotNull(t2From);
    Assert.assertEquals(1, t2From.size());
    Assert.assertTrue(checkFrom(t2From.get(0), TEST_INDEX_DOG, "dog", "d"));
}
 
Example #14
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void joinSplitWhereCorrectly() throws SqlParseException {
    String query = "SELECT a.*, a.firstname ,a.lastname , a.gender ,  d.holdersName ,d.name  FROM " +
            TEST_INDEX_ACCOUNT +
            "/account a " +
            "LEFT JOIN " +
            TEST_INDEX_DOG +
            "/dog d on d.holdersName = a.firstname" +
            " WHERE a.firstname = 'eliran' AND " +
            " (a.age > 10 OR a.balance > 2000)" +
            " AND d.age > 1";

    JoinSelect joinSelect = parser.parseJoinSelect((SQLQueryExpr) queryToExpr(query));
    String s1Where = joinSelect.getFirstTable().getWhere().toString();
    Assert.assertEquals("AND ( AND firstname EQ eliran, AND ( OR age GT 10, OR balance GT 2000 )  ) ", s1Where);
    String s2Where = joinSelect.getSecondTable().getWhere().toString();
    Assert.assertEquals("AND age GT 1", s2Where);
}
 
Example #15
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void filterAggTestWithAliasAsString() throws SqlParseException {
    String query = "select * from myIndex group by a , filter('my filter', a > 3 AND b='3' )";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    List<List<Field>> groupBys = select.getGroupBys();
    Assert.assertEquals(1, groupBys.size());
    Field aAgg = groupBys.get(0).get(0);
    Assert.assertEquals("a", aAgg.getName());
    Field field = groupBys.get(0).get(1);
    Assert.assertTrue("filter field should be method field", field instanceof MethodField);
    MethodField filterAgg = (MethodField) field;
    Assert.assertEquals("filter", filterAgg.getName());
    Map<String, Object> params = filterAgg.getParamsAsMap();
    Assert.assertEquals(2, params.size());
    Object alias = params.get("alias");
    Assert.assertEquals("my filter@FILTER", alias);

    Assert.assertTrue(params.get("where") instanceof Where);
    Where where = (Where) params.get("where");
    Assert.assertEquals(2, where.getWheres().size());
}
 
Example #16
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void filterAggTestWithAlias() throws SqlParseException {
    String query = "select * from myIndex group by a , filter(myFilter, a > 3 AND b='3' )";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    List<List<Field>> groupBys = select.getGroupBys();
    Assert.assertEquals(1, groupBys.size());
    Field aAgg = groupBys.get(0).get(0);
    Assert.assertEquals("a", aAgg.getName());
    Field field = groupBys.get(0).get(1);
    Assert.assertTrue("filter field should be method field", field instanceof MethodField);
    MethodField filterAgg = (MethodField) field;
    Assert.assertEquals("filter", filterAgg.getName());
    Map<String, Object> params = filterAgg.getParamsAsMap();
    Assert.assertEquals(2, params.size());
    Object alias = params.get("alias");
    Assert.assertEquals("myFilter@FILTER", alias);

    Assert.assertTrue(params.get("where") instanceof Where);
    Where where = (Where) params.get("where");
    Assert.assertEquals(2, where.getWheres().size());
}
 
Example #17
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void explicitScriptOnAggregation() throws SqlParseException {
    String query = "SELECT avg( script('add','doc[\\'field1\\'].value + doc[\\'field2\\'].value') ) FROM index/type";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    List<Field> fields = select.getFields();
    Assert.assertEquals(1, fields.size());
    Field field = fields.get(0);
    Assert.assertTrue(field instanceof MethodField);
    MethodField avgMethodField = (MethodField) field;
    Assert.assertEquals("avg", avgMethodField.getName().toLowerCase());
    Assert.assertEquals(1, avgMethodField.getParams().size());
    MethodField scriptMethod = (MethodField) avgMethodField.getParams().get(0).value;
    Assert.assertEquals("script", scriptMethod.getName().toLowerCase());
    Assert.assertEquals(2, scriptMethod.getParams().size());
    Assert.assertEquals("doc['field1'].value + doc['field2'].value", scriptMethod.getParams().get(1).toString());
}
 
Example #18
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void multipleHints() throws SqlParseException {
    String query = String.format("select /*! HASH_WITH_TERMS_FILTER*/ /*! JOIN_TABLES_LIMIT(1000,null) */ " +
            " /*! JOIN_TABLES_LIMIT(100,200) */ " +
            "c.name.firstname,c.parents.father , h.name,h.words from %s/gotCharacters c " +
            "use KEY (termsFilter) " +
            "JOIN %s/gotCharacters h " +
            "on c.name.lastname = h.name  " +
            "where c.name.firstname='Daenerys'", TEST_INDEX_GAME_OF_THRONES, TEST_INDEX_GAME_OF_THRONES);

    JoinSelect joinSelect = parser.parseJoinSelect((SQLQueryExpr) queryToExpr(query));
    List<Hint> hints = joinSelect.getHints();

    Assert.assertNotNull(hints);
    Assert.assertEquals("hints size was not 3", 3, hints.size());
    Hint firstHint = hints.get(0);
    Assert.assertEquals(HintType.HASH_WITH_TERMS_FILTER, firstHint.getType());
    Hint secondHint = hints.get(1);
    Assert.assertEquals(HintType.JOIN_LIMIT, secondHint.getType());
    Assert.assertEquals(1000, secondHint.getParams()[0]);
    Assert.assertEquals(null, secondHint.getParams()[1]);
    Hint thirdHint = hints.get(2);
    Assert.assertEquals(100, thirdHint.getParams()[0]);
    Assert.assertEquals(200, thirdHint.getParams()[1]);
    Assert.assertEquals(HintType.JOIN_LIMIT, thirdHint.getType());
}
 
Example #19
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void implicitScriptOnAggregation() throws SqlParseException {
    String query = "SELECT avg(field(field1) + field(field2)) FROM index/type";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    List<Field> fields = select.getFields();
    Assert.assertEquals(1, fields.size());
    Field field = fields.get(0);
    Assert.assertTrue(field instanceof MethodField);
    MethodField avgMethodField = (MethodField) field;
    Assert.assertEquals("avg", avgMethodField.getName().toLowerCase());
    Assert.assertEquals(1, avgMethodField.getParams().size());
    Assert.assertTrue(avgMethodField.getParams().get(0).value.toString().contains("doc['field1'].value"));
    Assert.assertTrue(avgMethodField.getParams().get(0).value.toString().contains("doc['field2'].value"));

}
 
Example #20
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void limitHintsOnJoin() throws SqlParseException {
    String query = String.format("select /*! JOIN_TABLES_LIMIT(1000,null) */ c.name.firstname,c.parents.father , h.name,h.words from %s/gotCharacters c " +
            "use KEY (termsFilter) " +
            "JOIN %s/gotCharacters h " +
            "on c.name.lastname = h.name  " +
            "where c.name.firstname='Daenerys'", TEST_INDEX_GAME_OF_THRONES, TEST_INDEX_GAME_OF_THRONES);
    JoinSelect joinSelect = parser.parseJoinSelect((SQLQueryExpr) queryToExpr(query));
    List<Hint> hints = joinSelect.getHints();
    Assert.assertNotNull(hints);
    Assert.assertEquals("hints size was not 1", 1, hints.size());
    Hint hint = hints.get(0);
    Assert.assertEquals(HintType.JOIN_LIMIT, hint.getType());
    Object[] params = hint.getParams();
    Assert.assertNotNull(params);
    Assert.assertEquals("params size was not 2", 2, params.length);
    Assert.assertEquals(1000, params[0]);
    Assert.assertEquals(null, params[1]);
}
 
Example #21
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void filterAggTestNoAlias() throws SqlParseException {
    String query = "select * from myIndex group by a , filter(  a > 3 AND b='3' )";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    List<List<Field>> groupBys = select.getGroupBys();
    Assert.assertEquals(1, groupBys.size());
    Field aAgg = groupBys.get(0).get(0);
    Assert.assertEquals("a", aAgg.getName());
    Field field = groupBys.get(0).get(1);
    Assert.assertTrue("filter field should be method field", field instanceof MethodField);
    MethodField filterAgg = (MethodField) field;
    Assert.assertEquals("filter", filterAgg.getName());
    Map<String, Object> params = filterAgg.getParamsAsMap();
    Assert.assertEquals(2, params.size());
    Object alias = params.get("alias");
    Assert.assertEquals("filter(a > 3 AND b = '3')@FILTER", alias);

    Assert.assertTrue(params.get("where") instanceof Where);
    Where where = (Where) params.get("where");
    Assert.assertEquals(2, where.getWheres().size());
}
 
Example #22
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void joinParseConditionsTestOneCondition() throws SqlParseException {
    String query = "SELECT a.*, a.firstname ,a.lastname , a.gender ,  d.holdersName ,d.name  FROM " +
            TEST_INDEX_ACCOUNT +
            "/account a " +
            "LEFT JOIN " +
            TEST_INDEX_DOG +
            "/dog d on d.holdersName = a.firstname" +
            " WHERE a.firstname = 'eliran' AND " +
            " (a.age > 10 OR a.balance > 2000)" +
            " AND d.age > 1";

    JoinSelect joinSelect = parser.parseJoinSelect((SQLQueryExpr) queryToExpr(query));
    List<Condition> conditions = joinSelect.getConnectedConditions();
    Assert.assertNotNull(conditions);
    Assert.assertEquals(1, conditions.size());
    Assert.assertTrue("condition not exist: d.holdersName = a.firstname", conditionExist(conditions, "d.holdersName", "a.firstname", Condition.OPEAR.EQ));
}
 
Example #23
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 6 votes vote down vote up
@Test
public void joinParseCheckConnectedFields() throws SqlParseException {
    String query = "SELECT a.firstname ,a.lastname , a.gender ,  d.holdersName ,d.name  FROM " +
            TEST_INDEX_ACCOUNT +
            "/account a " +
            "LEFT JOIN " +
            TEST_INDEX_DOG +
            "/dog d on d.holdersName = a.firstname " +
            " AND d.age < a.age " +
            " WHERE a.firstname = 'eliran' AND " +
            " (a.age > 10 OR a.balance > 2000)" +
            " AND d.age > 1";

    JoinSelect joinSelect = parser.parseJoinSelect((SQLQueryExpr) queryToExpr(query));

    List<Field> t1Fields = joinSelect.getFirstTable().getConnectedFields();
    Assert.assertEquals(t1Fields.size(), 2);
    Assert.assertTrue(fieldExist(t1Fields, "firstname"));
    Assert.assertTrue(fieldExist(t1Fields, "age"));

    List<Field> t2Fields = joinSelect.getSecondTable().getConnectedFields();
    Assert.assertEquals(t2Fields.size(), 2);
    Assert.assertTrue(fieldExist(t2Fields, "holdersName"));
    Assert.assertTrue(fieldExist(t2Fields, "age"));
}
 
Example #24
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 5 votes vote down vote up
@Test
public void parseJoinWithOneTableOrderByRemoveAlias() throws SqlParseException {
    String query = String.format("select c.name.firstname , d.words from %s/gotCharacters c " +
                    "JOIN %s/gotCharacters d on d.name = c.house " +
                    "order by c.name.firstname"
            , TEST_INDEX_GAME_OF_THRONES, TEST_INDEX_GAME_OF_THRONES);

    JoinSelect joinSelect = parser.parseJoinSelect((SQLQueryExpr) queryToExpr(query));
    List<Order> orderBys = joinSelect.getFirstTable().getOrderBys();
    Assert.assertEquals(1, orderBys.size());
    Order order = orderBys.get(0);
    Assert.assertEquals("name.firstname", order.getName());

}
 
Example #25
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 5 votes vote down vote up
@Test
public void complexNestedTest() throws SqlParseException {
    String query = "select * from x where nested('y',y.b = 'a' and y.c  = 'd') ";
    Select select = parser.parseSelect((SQLQueryExpr) queryToExpr(query));
    Condition condition = (Condition) select.getWhere().getWheres().get(0);
    Assert.assertEquals(Condition.OPEAR.NESTED_COMPLEX, condition.getOpear());
    Assert.assertEquals("y", condition.getName());
    Assert.assertTrue(condition.getValue() instanceof Where);
    Where where = (Where) condition.getValue();
    Assert.assertEquals(2, where.getWheres().size());
}
 
Example #26
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 5 votes vote down vote up
@Test
public void termWithStringTest() throws SqlParseException {
    String query = "select * from x where y = TERM('a')";
    Select select = parser.parseSelect((SQLQueryExpr) queryToExpr(query));
    Condition condition = (Condition) select.getWhere().getWheres().get(0);
    Object[] values = (Object[]) condition.getValue();
    Assert.assertEquals("a", values[0]);
}
 
Example #27
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 5 votes vote down vote up
@Test
public void termsWithStringTest() throws SqlParseException {
    String query = "select * from x where y = IN_TERMS('a','b')";
    Select select = parser.parseSelect((SQLQueryExpr) queryToExpr(query));
    Condition condition = (Condition) select.getWhere().getWheres().get(0);
    Object[] values = (Object[]) condition.getValue();
    Assert.assertEquals("a", values[0]);
    Assert.assertEquals("b", values[1]);
}
 
Example #28
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 5 votes vote down vote up
@Test
public void doubleOrderByTest() throws SqlParseException {
    String query = "select * from indexName order by a asc, b desc";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    List<Order> orderBys = select.getOrderBys();
    Assert.assertEquals(2, orderBys.size());
    Assert.assertEquals("a", orderBys.get(0).getName());
    Assert.assertEquals("ASC", orderBys.get(0).getType());

    Assert.assertEquals("b", orderBys.get(1).getName());
    Assert.assertEquals("DESC", orderBys.get(1).getType());
}
 
Example #29
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 5 votes vote down vote up
@Test
public void scriptLiteralPlusLiteralTest() throws SqlParseException {
    String query = "SELECT 1 + 2  FROM index/type";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    List<Field> fields = select.getFields();
    Assert.assertEquals(1, fields.size());
    Field field = fields.get(0);
    Assert.assertTrue(field instanceof MethodField);
    MethodField scriptMethod = (MethodField) field;
    Assert.assertEquals("script", scriptMethod.getName().toLowerCase());
    Assert.assertEquals(2, scriptMethod.getParams().size());
    Assert.assertTrue(scriptMethod.getParams().get(1).toString().contains("1 + 2"));
}
 
Example #30
Source File: SqlParserTests.java    From elasticsearch-sql with Apache License 2.0 5 votes vote down vote up
@Test
public void nestedFieldOnGroupByWithPath() throws SqlParseException {
    String query = "select * from myIndex group by nested(message.name,message)";
    SQLExpr sqlExpr = queryToExpr(query);
    Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
    Field field = select.getGroupBys().get(0).get(0);
    Assert.assertTrue("condition should be nested", field.isNested());
    Assert.assertEquals("message", field.getNestedPath());
    Assert.assertEquals("message.name", field.getName());
}