Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.4.0-incubating
    • Fix Version/s: 1.6.0
    • Component/s: avatica
    • Labels:
      None
    • Environment:

      JavaSE1.7

      Description

      Hi All,

      I am trying to run below queries using SQLParser object, the first query is executing as expected but when am adding sum(ppd.value) function I am getting exception, but same query I am able to run in SQL SERVER.

      Working query:

      insert into planning_point_data_cache (ts_business_name_id,planning_point_seq,detail_id,detail_id,period_id,cache_value)
                                select ppd.ts_business_name_id,denorm.input_ppt_cache_seq,denorm.detail_id,denorm.period_id,cache_value
                                from rule_output_input_map_normalize denorm join planning_point_data ppd on denorm.input_ppt_seq=ppd.planning_point_seq
                                where denorm.param_order='raghu' and denorm.input_ppt_cache_seq 0 group by ppd.ts_business_name_id.
      

      Problem facing query:

      insert into planning_point_data_cache(ts_business_name_id,planning_point_seq,detail_id,detail_id,period_id,cache_value)
                                selectppd.ts_business_name_id,denorm.input_ppt_cache_seq,denorm.detail_id,denorm.period_id,sum(ppd.value)
                                from rule_output_input_map_normalize denorm join planning_point_data ppd ondenorm.input_ppt_seq=ppd.planning_point_seq
                                where denorm.param_order='raghu' and denorm.input_ppt_cache_seq 0 group byppd.ts_business_name_id,ppd.value
      

      Below is the stack trace:

      org.apache.calcite.sql.parser.SqlParseException: Encountered ". value" at line 1, column 221.
      Was expecting one of:
          ")" ...
          "ORDER" ...
          "LIMIT" ...
          "OFFSET" ...
          "FETCH" ...
          "," ...
          "UNION" ...
          "INTERSECT" ...
          "EXCEPT" ...
          "." ...
          "NOT" ...
          "IN" ...
          "BETWEEN" ...
          "LIKE" ...
          "SIMILAR" ...
          "=" ...
          ">" ...
          "<" ...
          "<=" ...
          ">=" ...
          "<>" ...
          "+" ...
          "-" ...
          "*" ...
          "/" ...
          "||" ...
          "AND" ...
          "OR" ...
          "IS" ...
          "MEMBER" ...
          "SUBMULTISET" ...
          "MULTISET" ...
          "[" ...
          "." <IDENTIFIER> ...
          "." <QUOTED_IDENTIFIER> ...
          "." <BACK_QUOTED_IDENTIFIER> ...
          "." <BRACKET_QUOTED_IDENTIFIER> ...
          "." <UNICODE_QUOTED_IDENTIFIER> ...
          "." "*" ...
          "(" ...
          
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:388)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:118)
      	at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:130)
      	at TestSqlParser.main(TestSqlParser.java:30)
      Caused by: org.apache.calcite.sql.parser.impl.ParseException: Encountered ". value" at line 1, column 221.
      Was expecting one of:
          ")" ...
          "ORDER" ...
          "LIMIT" ...
          "OFFSET" ...
          "FETCH" ...
          "," ...
          "UNION" ...
          "INTERSECT" ...
          "EXCEPT" ...
          "." ...
          "NOT" ...
          "IN" ...
          "BETWEEN" ...
          "LIKE" ...
          "SIMILAR" ...
          "=" ...
          ">" ...
          "<" ...
          "<=" ...
          ">=" ...
          "<>" ...
          "+" ...
          "-" ...
          "*" ...
          "/" ...
          "||" ...
          "AND" ...
          "OR" ...
          "IS" ...
          "MEMBER" ...
          "SUBMULTISET" ...
          "MULTISET" ...
          "[" ...
          "." <IDENTIFIER> ...
          "." <QUOTED_IDENTIFIER> ...
          "." <BACK_QUOTED_IDENTIFIER> ...
          "." <BRACKET_QUOTED_IDENTIFIER> ...
          "." <UNICODE_QUOTED_IDENTIFIER> ...
          "." "*" ...
          "(" ...
          
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.generateParseException(SqlParserImpl.java:16646)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.jj_consume_token(SqlParserImpl.java:16475)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.FunctionParameterList(SqlParserImpl.java:725)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.NamedFunctionCall(SqlParserImpl.java:4146)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.AtomicRowExpression(SqlParserImpl.java:2718)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression3(SqlParserImpl.java:2562)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2(SqlParserImpl.java:2394)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression(SqlParserImpl.java:2366)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectExpression(SqlParserImpl.java:1326)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectItem(SqlParserImpl.java:1298)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectList(SqlParserImpl.java:1284)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlSelect(SqlParserImpl.java:819)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQuery(SqlParserImpl.java:578)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:2351)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:2270)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:496)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlInsert(SqlParserImpl.java:1009)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmt(SqlParserImpl.java:754)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:776)
      	at org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlStmtEof(SqlParserImpl.java:177)
      	at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:123)
      	... 1 more
      

        Activity

        Hide
        eraghavender Raghavender added a comment - - edited

        Please find the Java code which i have ran.

        import org.apache.calcite.avatica.util.Casing;
        import org.apache.calcite.avatica.util.Quoting;
        import org.apache.calcite.sql.SqlNode;
        import org.apache.calcite.sql.parser.SqlParseException;
        import org.apache.calcite.sql.parser.SqlParser;
        import org.apache.calcite.sql.parser.SqlParser.ConfigBuilder;
        
        public class TestSqlParser {
        
        	public static void main(String[] args) {
        
        		String sql =  
        				"insert into planning_point_data_cache (ts_business_name_id,planning_point_seq,detail_id,detail_id,period_id,cache_value)" +
        				" select ppd.ts_business_name_id,denorm.input_ppt_cache_seq,denorm.detail_id,denorm.period_id,sum(ppd.value) " +
        				"from rule_output_input_map_normalize denorm join planning_point_data ppd on denorm.input_ppt_seq=ppd.planning_point_seq" +
        				" where denorm.param_order='raghu' and denorm.input_ppt_cache_seq>0 group by ppd.ts_business_name_id";
        				
        				
        				
        		Quoting quoting = Quoting.DOUBLE_QUOTE;
        		Casing unquotedCasing = Casing.TO_UPPER;
        		Casing quotedCasing = Casing.UNCHANGED;
        		SqlParser parser = SqlParser.create(sql,
        		SqlParser.configBuilder().setQuoting(quoting)
        		.setUnquotedCasing(unquotedCasing)
        		.setQuotedCasing(quotedCasing).build());
        
        		try {
        		System.out.println("Here goes");
        		SqlNode sqlNode = parser.parseQuery();
        		System.out.println ("Done Parsing:" + sqlNode);
        		} catch (SqlParseException e) {
        		e.printStackTrace();
        		}
        		}
        
        }
        Show
        eraghavender Raghavender added a comment - - edited Please find the Java code which i have ran. import org.apache.calcite.avatica.util.Casing; import org.apache.calcite.avatica.util.Quoting; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.parser.SqlParseException; import org.apache.calcite.sql.parser.SqlParser; import org.apache.calcite.sql.parser.SqlParser.ConfigBuilder; public class TestSqlParser { public static void main( String [] args) { String sql = "insert into planning_point_data_cache (ts_business_name_id,planning_point_seq,detail_id,detail_id,period_id,cache_value)" + " select ppd.ts_business_name_id,denorm.input_ppt_cache_seq,denorm.detail_id,denorm.period_id,sum(ppd.value) " + "from rule_output_input_map_normalize denorm join planning_point_data ppd on denorm.input_ppt_seq=ppd.planning_point_seq" + " where denorm.param_order='raghu' and denorm.input_ppt_cache_seq>0 group by ppd.ts_business_name_id" ; Quoting quoting = Quoting.DOUBLE_QUOTE; Casing unquotedCasing = Casing.TO_UPPER; Casing quotedCasing = Casing.UNCHANGED; SqlParser parser = SqlParser.create(sql, SqlParser.configBuilder().setQuoting(quoting) .setUnquotedCasing(unquotedCasing) .setQuotedCasing(quotedCasing).build()); try { System .out.println( "Here goes" ); SqlNode sqlNode = parser.parseQuery(); System .out.println ( "Done Parsing:" + sqlNode); } catch (SqlParseException e) { e.printStackTrace(); } } }
        Hide
        julianhyde Julian Hyde added a comment -

        Your problem is that you have a column called "VALUE". VALUE is a reserved word. The solution is to enclose it in double-quotes. Also convert it to upper-case, because I presume your column is called "VALUE" not "value".

            String sql =
                "insert into planning_point_data_cache (ts_business_name_id,planning_point_seq,detail_id,detail_id,period_id,cache_value)" +
                    " select ppd.ts_business_name_id,denorm.input_ppt_cache_seq,denorm.detail_id,denorm.period_id,sum(ppd.\"VALUE\") " +
           ...
        
        Show
        julianhyde Julian Hyde added a comment - Your problem is that you have a column called "VALUE". VALUE is a reserved word. The solution is to enclose it in double-quotes. Also convert it to upper-case, because I presume your column is called "VALUE" not "value". String sql = "insert into planning_point_data_cache (ts_business_name_id,planning_point_seq,detail_id,detail_id,period_id,cache_value)" + " select ppd.ts_business_name_id,denorm.input_ppt_cache_seq,denorm.detail_id,denorm.period_id,sum(ppd.\" VALUE\ ") " + ...
        Hide
        eraghavender Raghavender added a comment -

        Thanks Julian for prompt response. May i know the reserved list so that i'll take care those while parsing in next test.

        Show
        eraghavender Raghavender added a comment - Thanks Julian for prompt response. May i know the reserved list so that i'll take care those while parsing in next test.
        Hide
        eraghavender Raghavender added a comment -

        Please provide all the reserved words, so that i can take care in next test.

        Show
        eraghavender Raghavender added a comment - Please provide all the reserved words, so that i can take care in next test.
        Hide
        julianhyde Julian Hyde added a comment -
        Show
        julianhyde Julian Hyde added a comment - I have put a temporary list here: http://calcite.hydromatic.net/docs/reference.html#key-words
        Hide
        eraghavender Raghavender added a comment -

        Can we use @ symbol in sql query as mention place holder.

        Show
        eraghavender Raghavender added a comment - Can we use @ symbol in sql query as mention place holder.
        Hide
        julianhyde Julian Hyde added a comment - - edited

        Raghavender, Please don't log (or re-open) jira cases to ask questions. Best place to ask questions is on the dev list. By all means log a jira case if you are running into a bug but "product not working as you expect" is not necessarily a bug - it might mean you need to read the documentation more carefully.

        Show
        julianhyde Julian Hyde added a comment - - edited Raghavender , Please don't log (or re-open) jira cases to ask questions. Best place to ask questions is on the dev list. By all means log a jira case if you are running into a bug but "product not working as you expect" is not necessarily a bug - it might mean you need to read the documentation more carefully.
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/6c35f3b8 . Keywords are now documented at http://calcite.apache.org/docs/reference.html#keywords .
        Hide
        julianhyde Julian Hyde added a comment -

        Resolved in release 1.6.0 (2016-01-22).

        Show
        julianhyde Julian Hyde added a comment - Resolved in release 1.6.0 (2016-01-22).

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            eraghavender Raghavender
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development