COUNT(<literal>) emits a fieldless aggregation, and a whole-table HAVING is silently discarded

[fupelaqu]

COUNT(<literal>) counts rows in every dialect, and HAVING without GROUP BY filters the whole table as one implicit group. SoftClient4ES gets both wrong, and the two combine in the single statement Tableau issues to check whether a data source has any rows.

Measured, on a live Tableau Desktop over the JDBC driver

Tableau's data-source preview fails on a plain index, with:

Elasticsearch error during singleSearch: illegal_argument_exception -
Required one of fields [field, script], but none were specified.

The statement, captured verbatim at the JDBC boundary and issued four times per session, in every session:

SELECT SUM(1) AS `cnt_bi_events_..._ok`
FROM `<cluster>`.`bi_events` `bi_events` HAVING (COUNT(1) > 0)

This is not an exotic query. It is the row-existence check Tableau runs against a data source, so the defect lands on the first thing a user does after connecting.

Defect 1 — COUNT(<literal>) emits an aggregation with no field and no script

COUNT(1) counts rows. The engine instead builds a value_count over the literal, which has neither a field nor a script, and Elasticsearch rejects the request outright. Reproduced independently of the SELECT list: SELECT SUM(amount) … HAVING COUNT(1) > 0 fails identically, so COUNT(1) is the blocker rather than the projection.

Standard SQL is unambiguous here: counting a non-null constant counts rows, so COUNT(<literal>) is COUNT(*). COUNT(NULL) is not a row count and must keep returning 0.

Defect 2 — a whole-table HAVING is silently discarded

🔴 The more serious one, and it is only visible once defect 1 is out of the way.

With a field-bearing aggregate the statement succeeds and the predicate evaporates. A HAVING is emitted as a bucket_selector over a parent multi-bucket aggregation; with no GROUP BY there is no bucket aggregation, so there is nothing to select and the filter has nowhere to attach. Measured on a 703-document index:

statement	returned	correct
SELECT COUNT(*) AS c … HAVING COUNT(*) > 10000	703	no rows
SELECT SUM(amount) AS s … HAVING SUM(amount) > 99999	the unfiltered sum	no rows

Both predicates are false and both returned the unfiltered aggregate with HTTP 200. This is the silent-wrong-answer family of #205, #209, #224 and #253.

⚠️ The two must be fixed together. Fixing defect 1 alone turns a loud failure into a plausible wrong answer on Tableau's most frequent statement, which is strictly worse than the error it replaces.

Why a happy-path test would have missed it

The true-predicate case returns the correct aggregate whether or not the predicate is honoured, because the aggregate is identical either way. Only the false-predicate case distinguishes "the filter was applied" from "the filter was dropped". Any regression test for this must assert the pair.

Acceptance

On a 5,000-document index, against real Elasticsearch:

• SELECT SUM(1) AS c … HAVING (COUNT(1) > 0) returns exactly one row carrying 5000
• the same statement with > 10000 returns no rows
• both spellings, COUNT(*) and SUM(amount), behave the same way
• a shape that cannot be honoured is refused, never silently dropped

Fixed in #327.