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));
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’ ;
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?
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 .