You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by besquared <jo...@modeanalytics.com> on 2018/03/19 23:56:20 UTC
Basic Subquery Failure
I'm running 2.4.0 locally on a single node and I'm trying to get a basic SQL
subquery to work and I seem to be having problems.
The following is the simplest thing I could find to reproduce to the error:
The error is as follows:
Any help for what I'm doing wrong here would be appreciated. I'm not sure
exactly when this query translation step takes place and who is responsible
for building this secondary query during the reduce phase. Is there any
documentation about this?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Basic Subquery Failure
Posted by Taras Ledkov <tl...@gridgain.com>.
Hi,
Thanks a lot.
The problem has been already fixed. Take a look:
https://issues.apache.org/jira/browse/IGNITE-7879
On 21.03.2018 3:12, besquared wrote:
> Sorry about that I put them as raw text but it looks like it didn't go
> through.. I'll just try without markup this time
>
> CREATE TABLE people (
> id LONG PRIMARY KEY,
> name VARCHAR
> );
>
> SELECT people.name,
> COUNT(1) AS rowcount
> FROM people
> JOIN ( SELECT DISTINCT name FROM people ) sub
> ON people.name = sub.name
> GROUP BY people.name
>
> ! Causing: javax.cache.CacheException: Failed to run map query
> remotely.Failed to execute map query on the node:
> c738645b-7f7a-4e39-a283-e49cb990e93d, class
> org.apache.ignite.IgniteCheckedException:Failed to execute SQL query. Column
> "__Z0.NAME" must be in the GROUP BY list; SQL statement:
> ! SELECT
> ! COUNT(1) __C0_0,
> ! __Z0.NAME __C0_1
> ! FROM PUBLIC.PEOPLE __Z0 [90016-195]
>
> EXPLAIN plan is
>
> "SELECT
> COUNT(1) AS __C0_0,
> __Z0.NAME AS __C0_1
> FROM PUBLIC.PEOPLE __Z0
> /* PUBLIC.PEOPLE.__SCAN_ */"
> "SELECT DISTINCT
> __Z1.NAME AS __C1_0
> FROM PUBLIC.PEOPLE __Z1
> /* PUBLIC.PEOPLE.__SCAN_ */
> ORDER BY 1"
> "SELECT
> __Z0__NAME AS NAME,
> __X0__ROWCOUNT AS ROWCOUNT
> FROM (
> SELECT
> CAST(SUM(__C0_0) AS BIGINT) AS __X0__ROWCOUNT,
> __C0_1 AS __Z0__NAME
> FROM PUBLIC.__T0
> ORDER BY 2
> ) __Z3
> /* SELECT
> CAST(SUM(__C0_0) AS BIGINT) AS __X0__ROWCOUNT,
> __C0_1 AS __Z0__NAME
> FROM PUBLIC.__T0
> /++ PUBLIC."merge_scan" ++/
> ORDER BY 2
> */
> INNER JOIN (
> SELECT DISTINCT
> __C1_0 AS NAME
> FROM PUBLIC.__T1
> ORDER BY 1
> ) SUB__Z2
> /* SELECT DISTINCT
> __C1_0 AS NAME
> FROM PUBLIC.__T1
> /++ PUBLIC."merge_sorted": __C1_0 IS ?1 ++/
> WHERE __C1_0 IS ?1
> ORDER BY 1
> /++ index sorted ++/: NAME = __Z0__NAME
> */
> ON 1=1
> WHERE __Z0__NAME = SUB__Z2.NAME
> GROUP BY __Z0__NAME"
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
--
Taras Ledkov
Mail-To: tledkov@gridgain.com
Re: Basic Subquery Failure
Posted by besquared <jo...@modeanalytics.com>.
Sorry about that I put them as raw text but it looks like it didn't go
through.. I'll just try without markup this time
CREATE TABLE people (
id LONG PRIMARY KEY,
name VARCHAR
);
SELECT people.name,
COUNT(1) AS rowcount
FROM people
JOIN ( SELECT DISTINCT name FROM people ) sub
ON people.name = sub.name
GROUP BY people.name
! Causing: javax.cache.CacheException: Failed to run map query
remotely.Failed to execute map query on the node:
c738645b-7f7a-4e39-a283-e49cb990e93d, class
org.apache.ignite.IgniteCheckedException:Failed to execute SQL query. Column
"__Z0.NAME" must be in the GROUP BY list; SQL statement:
! SELECT
! COUNT(1) __C0_0,
! __Z0.NAME __C0_1
! FROM PUBLIC.PEOPLE __Z0 [90016-195]
EXPLAIN plan is
"SELECT
COUNT(1) AS __C0_0,
__Z0.NAME AS __C0_1
FROM PUBLIC.PEOPLE __Z0
/* PUBLIC.PEOPLE.__SCAN_ */"
"SELECT DISTINCT
__Z1.NAME AS __C1_0
FROM PUBLIC.PEOPLE __Z1
/* PUBLIC.PEOPLE.__SCAN_ */
ORDER BY 1"
"SELECT
__Z0__NAME AS NAME,
__X0__ROWCOUNT AS ROWCOUNT
FROM (
SELECT
CAST(SUM(__C0_0) AS BIGINT) AS __X0__ROWCOUNT,
__C0_1 AS __Z0__NAME
FROM PUBLIC.__T0
ORDER BY 2
) __Z3
/* SELECT
CAST(SUM(__C0_0) AS BIGINT) AS __X0__ROWCOUNT,
__C0_1 AS __Z0__NAME
FROM PUBLIC.__T0
/++ PUBLIC."merge_scan" ++/
ORDER BY 2
*/
INNER JOIN (
SELECT DISTINCT
__C1_0 AS NAME
FROM PUBLIC.__T1
ORDER BY 1
) SUB__Z2
/* SELECT DISTINCT
__C1_0 AS NAME
FROM PUBLIC.__T1
/++ PUBLIC."merge_sorted": __C1_0 IS ?1 ++/
WHERE __C1_0 IS ?1
ORDER BY 1
/++ index sorted ++/: NAME = __Z0__NAME
*/
ON 1=1
WHERE __Z0__NAME = SUB__Z2.NAME
GROUP BY __Z0__NAME"
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Basic Subquery Failure
Posted by Denis Magda <dm...@apache.org>.
Hi,
I can see neither the query nor the stack trace. Most likely you attached
them as images. Please paste as text or HTML text.
--
Denis
On Mon, Mar 19, 2018 at 4:56 PM, besquared <jo...@modeanalytics.com> wrote:
> I'm running 2.4.0 locally on a single node and I'm trying to get a basic
> SQL
> subquery to work and I seem to be having problems.
>
> The following is the simplest thing I could find to reproduce to the error:
>
>
>
> The error is as follows:
>
>
>
> Any help for what I'm doing wrong here would be appreciated. I'm not sure
> exactly when this query translation step takes place and who is responsible
> for building this secondary query during the reduce phase. Is there any
> documentation about this?
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>