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

The following examples show how to use com.alibaba.druid.sql.ast.expr.SQLQueryExpr. These examples are extracted from open source projects. 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 Project: elasticsearch-sql   Source File: SqlParser.java    License: 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 2
Source Project: elasticsearch-sql   Source File: SQLFunctionsTest.java    License: 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 3
Source Project: elasticsearch-sql   Source File: SQLFunctionsTest.java    License: 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 Project: elasticsearch-sql   Source File: SQLFunctionsTest.java    License: 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 Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 6
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 7
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 8
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 9
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 10
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 11
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 12
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 13
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 14
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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("[email protected]", alias);

    Assert.assertTrue(params.get("where") instanceof Where);
    Where where = (Where) params.get("where");
    Assert.assertEquals(2, where.getWheres().size());
}
 
Example 15
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 [email protected]", alias);

    Assert.assertTrue(params.get("where") instanceof Where);
    Where where = (Where) params.get("where");
    Assert.assertEquals(2, where.getWheres().size());
}
 
Example 16
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 17
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 18
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 19
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 20
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 21
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 22
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 23
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: 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 24
Source Project: elasticsearch-sql   Source File: ESActionFactory.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Create the compatible Query object
 * based on the SQL query.
 * @param sql The SQL query.
 * @return Query object.
 */
   public static QueryAction create(Client client, String sql) throws SqlParseException, SQLFeatureNotSupportedException {
       sql = sql.replaceAll("\n", " ").trim();
       String firstWord = sql.substring(0, sql.indexOf(' '));
       switch (firstWord.toUpperCase()) {
		case "SELECT":
		    //zhongshu-comment 将sql字符串解析成AST,即SQLQueryExpr sqlExpr就是AST了,下面的代码就开始访问AST、从中获取token
			SQLQueryExpr sqlExpr = (SQLQueryExpr) toSqlExpr(sql);
               if(isMulti(sqlExpr)){//zhongshu-comment 判断是不是union查询,union查询两个select语句,btw:子查询也有多个select语句,至少2个
                   MultiQuerySelect multiSelect = new SqlParser().parseMultiSelect((SQLUnionQuery) sqlExpr.getSubQuery().getQuery());
                   handleSubQueries(client,multiSelect.getFirstSelect());
                   handleSubQueries(client,multiSelect.getSecondSelect());
                   return new MultiQueryAction(client, multiSelect);
               }
               else if(isJoin(sqlExpr,sql)){//zhongshu-comment join连接查询
                   JoinSelect joinSelect = new SqlParser().parseJoinSelect(sqlExpr);
                   handleSubQueries(client, joinSelect.getFirstTable());
                   handleSubQueries(client, joinSelect.getSecondTable());
                   return ESJoinQueryActionFactory.createJoinAction(client, joinSelect);
               }
               else {
                   //zhongshu-comment 大部分查询都是走这个分支,先看懂这个分支
                   Select select = new SqlParser().parseSelect(sqlExpr);
                   //todo 看不懂,测试了好几个常见的sql,都没有进去handleSubQueries该方法,那就先不理了,看别的
                   handleSubQueries(client, select);
                   return handleSelect(client, select);
               }
		case "DELETE":
               SQLStatementParser parser = createSqlStatementParser(sql);
			SQLDeleteStatement deleteStatement = parser.parseDeleteStatement();
			Delete delete = new SqlParser().parseDelete(deleteStatement);
			return new DeleteQueryAction(client, delete);
           case "SHOW":
               return new ShowQueryAction(client,sql);
		default:
			throw new SQLFeatureNotSupportedException(String.format("Unsupported query: %s", sql));
	}
}
 
Example 25
Source Project: elasticsearch-sql   Source File: WhereParser.java    License: Apache License 2.0 5 votes vote down vote up
private Object[] getMethodValuesWithSubQueries(SQLMethodInvokeExpr method) throws SqlParseException {
    List<Object> values = new ArrayList<>();
    for (SQLExpr innerExpr : method.getParameters()) {
        if (innerExpr instanceof SQLQueryExpr) {
            Select select = sqlParser.parseSelect((MySqlSelectQueryBlock) ((SQLQueryExpr) innerExpr).getSubQuery().getQuery());
            values.add(new SubQueryExpression(select));
        } else if (innerExpr instanceof SQLTextLiteralExpr) {
            values.add(((SQLTextLiteralExpr) innerExpr).getText());
        } else {
            values.add(innerExpr);
        }

    }
    return values.toArray();
}
 
Example 26
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void multiSelectMinusScrollCheckDefaultsAllDefaults() throws SqlParseException {
    String query = "select /*! MINUS_SCROLL_FETCH_AND_RESULT_LIMITS*/ pk from firstIndex minus  select pk from secondIndex ";
    MultiQuerySelect select = parser.parseMultiSelect((com.alibaba.druid.sql.ast.statement.SQLUnionQuery) ((SQLQueryExpr) queryToExpr(query)).getSubQuery().getQuery());
    List<Hint> hints = select.getFirstSelect().getHints();
    Assert.assertEquals(1, hints.size());
    Hint hint = hints.get(0);
    Assert.assertEquals(HintType.MINUS_FETCH_AND_RESULT_LIMITS,hint.getType());
    Object[] params = hint.getParams();
    Assert.assertEquals(100000, params[0]);
    Assert.assertEquals(100000, params[1]);
    Assert.assertEquals(1000, params[2]);
}
 
Example 27
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void multiSelectMinusScrollCheckDefaultsOneDefault() throws SqlParseException {
    String query = "select /*! MINUS_SCROLL_FETCH_AND_RESULT_LIMITS(50,100)*/ pk from firstIndex minus  select pk from secondIndex ";
    MultiQuerySelect select = parser.parseMultiSelect((com.alibaba.druid.sql.ast.statement.SQLUnionQuery) ((SQLQueryExpr) queryToExpr(query)).getSubQuery().getQuery());
    List<Hint> hints = select.getFirstSelect().getHints();
    Assert.assertEquals(1, hints.size());
    Hint hint = hints.get(0);
    Assert.assertEquals(HintType.MINUS_FETCH_AND_RESULT_LIMITS,hint.getType());
    Object[] params = hint.getParams();
    Assert.assertEquals(50, params[0]);
    Assert.assertEquals(100, params[1]);
    Assert.assertEquals(1000, params[2]);
}
 
Example 28
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void joinConditionWithComplexObjectComparisonRightSide() throws SqlParseException {
    String query = String.format("select c.name.firstname,c.parents.father , h.name,h.words from %s/gotCharacters c " +
            "JOIN %s/gotCharacters h " +
            "on h.name = c.name.lastname  " +
            "where c.name.firstname='Daenerys'", TEST_INDEX_GAME_OF_THRONES, TEST_INDEX_GAME_OF_THRONES);
    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: h.name = c.name.lastname", conditionExist(conditions, "h.name", "c.name.lastname", Condition.OPEAR.EQ));
}
 
Example 29
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void joinConditionWithComplexObjectComparisonLeftSide() throws SQLFeatureNotSupportedException, IOException, SqlParseException {
    String query = String.format("select c.name.firstname,c.parents.father , h.name,h.words from %s/gotCharacters c " +
            "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<Condition> conditions = joinSelect.getConnectedConditions();
    Assert.assertNotNull(conditions);
    Assert.assertEquals(1, conditions.size());
    Assert.assertTrue("condition not exist: c.name.lastname = h.name", conditionExist(conditions, "c.name.lastname", "h.name", Condition.OPEAR.EQ));
}
 
Example 30
Source Project: elasticsearch-sql   Source File: SqlParserTests.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void hashTermsFilterHint() throws SqlParseException {
    String query = String.format("select /*! HASH_WITH_TERMS_FILTER*/ 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.HASH_WITH_TERMS_FILTER, hint.getType());
}