Unsupported nested scalar subselects

Help Query Tuning
1

Getting error: MemSQL does not support this type of query: unsupported nested scalar subselects, while executing below query.

delete from XYZ where processing_dt = ((select distinct processing_dt from XYZ where processing_dt < (select processing_dt from ABC) order by processing_dt DESC LIMIT 2,1));

How to proceed on this.

2

We will be adding support for this in the next release

3

still this is not rectified or implemented? Am getting same error now.

Using 7.6.13 db version and self managed.

4

I have escalated this internally, Ganesh! Thank you for providing the version number you’re running on. :pray:

5

Thanks Maria.

But I have observed one thing and implemented .

When you use aggregate function in sub select query. it will support and wont throw error.

This query will throw error
SELECT
PRODUCT_ID,
G_TYPE,
PERIOD ,
RATE ,
(
SELECT
RATE
FROM
FACT_PRD B
WHERE
B.PRODUCT_ID = A.PRODUCT_ID
AND B.G_TYPE = A.G_TYPE
AND PERIOD = ‘122021’) AS PRE_RATE
FROM
FACT_PRD A
WHERE
PERIOD = ‘032022’ ;

This query will execute. No error. Only i have changed SUM(RATE) even though aggregate function is not required in this case.
SELECT
PRODUCT_ID,
G_TYPE,
PERIOD ,
RATE ,
(
SELECT
SUM(RATE) AS RATE
FROM
FACT_PRD B
WHERE
B.PRODUCT_ID = A.PRODUCT_ID
AND B.G_TYPE = A.G_TYPE
AND PERIOD = ‘12-2021’) AS PRE_RATE
FROM
FACT_PRD A
WHERE
PERIOD = ‘03-2022’ ;

6

It looks like we don’t support scalar SELECTs that are not aggregates in the SELECT list. I’ll pass that along to our engineering team.

For non-numeric types, you can use ANY_VALUE instead of SUM in the workaround described here. ANY_VALUE should work for any type actually.

1 Like
7

That query failed because the system can’t guarantee that the sub-select in the SELECT returns only one row.

Where you working with another product that runs that query successfully? If so, which one? Was there a unique key or other guarantee that the subquery would only return one row?

1 Like
8

Yes .This query is working fine in MYSQL/ORACLE/MSSQL SERVER.

Same table structure and same query has been tried in all environment. Only in SINGLESTORE am facing this issue.

And logically, Subquery should return only one row according to the where clause matching other wise subquery returns more than one row error will come. That is okay. But SINGLESTORE totally not supporting sub select query.

Just I have bypassed the error by using AGGREGATE function .

Thanks

1 Like
9

Got it, thanks for sharing this.

2 Likes