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
10

When can we expect this to get solved? We are currently evaluating the product for another UseCase, but this is kind of a limiting factor.

11

Hi @christian.hirsch. What’s your specific use case for this? Can you give a schema and query that’s your issue? We’re actively working on some unsupported nested selects and expect improvements this calendar year.

12

We made a significant improvement to support more scalar subselects in the 8.7 release. E.g. this works now but failed in 8.5:

create table salesperson(id int, name varchar(30), specialty varchar(30));
create table sale(sid int, sp_id int, d date, amount float, category varchar(30));
insert salesperson values(1,"Jane","condos"),(2,"Bob","houses");
insert sale values(1, 1, now(), 100, "condos");
insert sale values(2, 1, now(), 350, "condos");
insert sale values(3, 1, now(), 700, "houses");
insert sale values(4, 2, now(), 200, "condos");
insert sale values(5, 2, now(), 250, "condos");
insert sale values(6, 2, now(), 300, "houses");

select sp.name, s.*
from salesperson sp, sale s
where sp.id = s.sp_id
and s.amount >= (select avg(s2.amount)
                 from sale s2
                 where sp.id = s2.sp_id
                 OR (sp.specialty = s2.category) );

The OR in the subselect is the issue.

Internally we call the improvement a generalized subselect to join (SSTJ) transformation.