Memsql 7.1.5 TO_JSON() is not honoring Limit Clause

Help Query Tuning
1

The TO_JSON() function is not honoring the LIMIT clause. It’s enforcing the where clause fine, but I have to nest a query to enforce the Limit Clause. Similar to the issue with LIMIT in procedures when not using dynamic sql.

select
*
FROM search_results
where search_id = 2175
limit 2

—yields 2 records—

vs

select
concat(’[’,group_concat(TO_JSON(search_results.*)),’]’) as Details
FROM search_results
where search_id = 2175
limit 2;

—yields all records—

This is the workaround for now:

select
concat(’[’,group_concat(TO_JSON(x.*)),’]’) as Details
from(
select *
FROM search_results
where search_id = 2175
limit 2) x

2

The behavior you are observing is correct SQL behavior. group_concat is an aggregate, and aggregates happen before limit. Therefore, for your second query

select
concat(’[’,group_concat(TO_JSON(search_results.*)),’]’) as Details
FROM search_results
where search_id = 2175
limit 2;

the operations, in order, are:

  • read search_results where search_id = 2175
  • group_concat all the records together
  • limit 2 (which doesn’t do anything because there’s only 1 record now, after the aggregation)

Your 3rd query is the correct way to do what you want.