Not implemented? SQLSubqueryTableSource cannot be cast to SQLJoinTableSource

[elyday]

Hi

i want to use two select queries in one query with ( ). The SQL Query looks like:

SELECT * FROM (
	SELECT column, column2, column3, column4, SUM(column5) AS column6
	FROM jdbc
  	WHERE column2 >= 2016 AND column2 <= 2016 AND column3 = 'BIA' AND column4 IS NULL
  	GROUP BY column, column2, column3, column4
) WHERE column6 = 13315

The Stacktrace say this:

{"error":{"root_cause":[{"type":"class_cast_exception","reason":"com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource cannot be cast to com.alibaba.druid.sql.ast.statement.SQLJoinTableSource"}],"type":"class_cast_exception","reason":"com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource cannot be cast to com.alibaba.druid.sql.ast.statement.SQLJoinTableSource"},"status":500}

Have i an error in my sql query? Or is this not implemented yet?

Greetings

[eliranmoyal]

Hey , sorry this type of query is not implemented yet

[elyday]

Is this on a todo list or it isn't planed to implement this?

[eliranmoyal]

For the more generic idea there is none (we are not planing querying data not by elasticsearch) But I am planing to add something like "having clause" to help with this specific kind of queries. This called bucket selector aggregation https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-selector-aggregation.html

[elyday]

Okay, thats look really nice

[allwefantasy]

If wanna sql supports join,subquery, you may try es-hadoop project which provide spark-sql support

On Tue, Nov 29, 2016 at 10:51 PM, Lars [email protected] wrote:

Okay, thats look really nice

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/NLPchina/elasticsearch-sql/issues/333#issuecomment-263590503, or mute the thread https://github.com/notifications/unsubscribe-auth/AAwsPkXwv0EAFfZo_1CGq4xSHEu-5h7xks5rDDv0gaJpZM4K93uX .

-- Best Regards

---

开阔视野 专注开发 WilliamZhu 祝海林 [email protected] 产品事业部-基础平台-搜索&数据挖掘 手机：18601315052 MSN：[email protected] 微博：@PrinceCharmingJ http://weibo.com/PrinceCharmingJ 地址：北京市朝阳区广顺北大街33号院1号楼福码大厦B座12层

---

http://www.csdn.net You're the One 全球最大中文IT技术社区 一切由你开始

http://www.iteye.net 程序员深度交流社区

[michael-ancestor]

crate这个项目底层用的也是es,看它的document，支持了sub query、group by...having以及join,希望[ealsticsearch-sql]也能赶上

[justgig8]

Do we have support for HAVING clause now?

I wish to run the following SQL:-

select CUSTMR_ID.keyword, count(1) as count from engaged where ACTION = 'OPEN' group by CUSTMR_ID.keyword having count(1) > 4

Right now am trying to run this instead:-

select * from (select CUSTMR_ID.keyword, count(1) as count from engaged where ACTION = 'OPEN' group by CUSTMR_ID.keyword) a where a.count > 4

and am getting same exception as:-

{"error":{"root_cause":[{"type":"class_cast_exception","reason":"com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource cannot be cast to com.alibaba.druid.sql.ast.statement.SQLJoinTableSource"}],"type":"class_cast_exception","reason":"com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource cannot be cast to com.alibaba.druid.sql.ast.statement.SQLJoinTableSource"},"status":500}

[Desperado2]

I wish to run the following SQL: SELECT name FROM (SELECT * FROM people) T GROUP BY name

and I getting same exception as: java.lang.ClassCastException: com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource cannot be cast to com.alibaba.druid.sql.ast.statement.SQLJoinTableSource

is this not implemented yet?

[shi-yuan]

SELECT name FROM people GROUP BY name

[iProcess]

I wish to run the following SQL: select count(1) from (SELECT username,age,last,datetime,weight from table_config where username='aaa') and I getting same exception as: Caused by: java.lang.ClassCastException: com. alibaba.druid.sql.ast.statement.SQLSubqueryTableSource cannot be cast to com. alibaba.druid.sql.ast.statement.SQLJoinTableSource