基于LibreOffice HSQLDB的LEFT JOIN与MAX函数结合的WHERE子句问题排查

Let's break down the issue and fix your query step by step. The core problem with your original WHERE-clause attempt is that WHERE runs before grouping and aggregation — the Last Payment alias doesn't exist yet when the WHERE clause is evaluated. Here are two working solutions tailored to HSQLDB 1.8's syntax limitations:

Method 1: Use HAVING Clause (Filter After Grouping) #

Since HAVING operates after grouping and aggregation, it can directly work with the MAX() result. Note that HSQLDB 1.8 doesn't support referencing aliases like Last Payment in HAVING, so you'll need to repeat the MAX() function:

SELECT "Members"."Key", "Members"."Name", MAX( "Payments"."Payment Date" ) AS "Last Payment"
FROM { oj "Members" LEFT OUTER JOIN "Payments" ON "Members"."Key" = "Payments"."MemberKey" }
GROUP BY "Members"."Key", "Members"."Name"
HAVING MAX( "Payments"."Payment Date" ) >= '2020-01-01' 
   OR MAX( "Payments"."Payment Date" ) IS NULL;

This query keeps:

• Members with no payment history (MAX(...) IS NULL → Ellen)
• Members whose last payment was on or after 2020-01-01 (Abby and Dave)
• Automatically excludes long-inactive members like Bob (last payment in 2016)

Method 2: Use a Subquery (Precompute Last Payments First) #

If you prefer working with the alias, wrap your original valid query as a subquery, then filter in the outer query:

SELECT *
FROM (
    SELECT "Members"."Key", "Members"."Name", MAX( "Payments"."Payment Date" ) AS "Last Payment"
    FROM { oj "Members" LEFT OUTER JOIN "Payments" ON "Members"."Key" = "Payments"."MemberKey" }
    GROUP BY "Members"."Key", "Members"."Name"
) AS MemberPaymentSummary
WHERE "Last Payment" >= '2020-01-01' 
   OR "Last Payment" IS NULL;

This achieves the same result but makes the filtering logic more readable by using the Last Payment alias directly.

Why Your Original Query Failed #

When you added WHERE "Last Payment" >= '2020-01-01', the database tries to evaluate this condition before calculating the MAX() value. Since Last Payment hasn't been computed yet, HSQLDB throws the "Not a condition" error. Always use WHERE for filtering rows before grouping, and HAVING for filtering grouped/aggregated results.

Both solutions will return your desired output:

+-----+----------+--------------+
| Key | Name     | Last Payment |
+-----+----------+--------------+
| 0   | Abby     | 2021-06-12   |
| 2   | Dave     | 2020-07-02   |
| 3   | Ellen    |              |
+-----+----------+--------------+

内容的提问来源于stack exchange，提问作者Mr Curved Limos