Error on complex generated queries

Help Query Tuning
1

Hello,

I’m trying to make my (generated) queries the migration from PostgreSql to SingleStore, and I’m currently fighting against errors I’m not finding a clear documentation on the limitations:

I’m trying to execute that query:
INSERT INTO dataset_3236a48aec6549538c7417e306c35409 (test_NFD0J3, computedcolumnname_PFXF5O)
SELECT mappingQuery1.pigment_internal_id AS test_NFD0J3, inputSource.count_of AS count_of
FROM (
SELECT count_of
FROM (
SELECT COUNT(pigment_internal_id) AS count_of
FROM dataset_4644cf2c5387410a8ce172e51cb4a3a6
) AS integer_query
) AS inputSource
INNER JOIN dataset_4644cf2c5387410a8ce172e51cb4a3a6 AS mappingQuery1 ON TRUE;

and get the error ERROR 1749 ER_DISTRIBUTED_FEATURE_LOCKDOWN: Feature ‘Singleton select inside of a pushed down query’ is not supported by SingleStore Distributed.
while it runs smoothly if I only use the SELECT part.

When I’m cleaning the query:
INSERT INTO dataset_3236a48aec6549538c7417e306c35409 (test_NFD0J3, computedcolumnname_PFXF5O)
SELECT mappingQuery1.pigment_internal_id AS test_NFD0J3, inputSource.count_of AS count_of
FROM (
SELECT COUNT(pigment_internal_id) AS count_of
FROM dataset_4644cf2c5387410a8ce172e51cb4a3a6
) AS inputSource
INNER JOIN dataset_4644cf2c5387410a8ce172e51cb4a3a6 AS mappingQuery1 ON TRUE;

Can someone explain the limitation, or point to a documentation explaining it please?
This would really help understand what kind of query I may or may not build from my software.

2

Hi,

This limitation is related to this part of the query:

SELECT COUNT(pigment_internal_id) AS count_of
FROM dataset_4644cf2c5387410a8ce172e51cb4a3a6

The query shape will be supported in the 7.6 release due out in a few weeks. In the meantime the easiest work around is to add a GROUP BY NULL to the part of the query above. This does change the query a bit as group by NULL will return no rows when the table is empty.

3

Thank you Adam, so if I clearly understand, the point is you won’t allow a sub-query containing only something that always returns a single row without further aggregation?
Your fix seems easily do-able, thank you.