Window Function ORDER BY Clauses in Grouped Row Concatenation Scenarios

Data Grouping Challenges with NULLs and Window Function Ordering

The core challenge revolves around aggregating rows that contain NULL values in a critical grouping column (indexManu) while preserving their original positional order defined by indexRow and parsePos. The solution uses a combination of LAG() window functions, GROUP_CONCAT, and subquery ordering to derive a synthetic grouping key (indexManu_grp). The primary uncertainties are:

1. Whether the logic for handling NULLs via LAG(indexManu) OVER (ORDER BY indexRow) is reliable.
2. How the ordering clauses in the window function (LAG()) and the outer SELECT statement interact, particularly when their ORDER BY clauses differ.

This scenario involves nuanced interactions between window function partitioning/ordering, outer query ordering, and grouping logic. Misalignment between these layers can lead to incorrect grouping keys, misordered concatenations, or unintended row associations.

Root Causes of Grouping Key Misalignment and Ordering Conflicts

1. Window Function Frame Scope vs. Outer Query Ordering

The LAG(indexManu) OVER (ORDER BY indexRow) window function defines its own row ordering independent of the outer query’s ORDER BY indexRow, parsed DESC, parsePos. This creates a disconnect:

• The window function processes rows in indexRow order alone, ignoring parsed and parsePos.
• The outer query sorts rows using additional columns, potentially altering the sequence in which rows are passed to GROUP BY.

If rows with the same indexRow have differing parsed or parsePos values, the window function’s view of "previous row" may not match the outer query’s row sequence. For example, a row with indexRow=270040080750 and parsePos=2 might be processed after parsePos=1 in the window function but before it in the outer query, leading to incorrect indexManu_grp values.

2. Handling NULLs and Synthetic Grouping Key Stability

The CASE statement assigns indexManu_grp as LAG(indexManu) + 0.5 when indexManu is NULL. This assumes that the immediately preceding row in the window function’s order has a valid indexManu. However, if multiple consecutive NULLs exist, this logic will compound the + 0.5 offset, creating keys like 6.5, 7.0 (if another NULL follows), which may not align with the intended grouping strategy.

Additionally, the use of LAG(indexManu) without a PARTITION BY clause means the window spans the entire dataset ordered by indexRow. If the physical order of rows in the table does not match the indexRow sequence (due to deletions, insertions, or updates), the LAG() function may reference unexpected rows.

3. Implicit vs. Explicit Row Ordering in Aggregation

GROUP_CONCAT relies on the order of rows passed to it by the outer query. While the subquery includes ORDER BY indexRow, parsed DESC, parsePos, the grouping operation (GROUP BY indexManu_grp) may reorder rows internally, depending on the database’s query execution plan. This can decouple the intended concatenation order from the grouping logic.

Validating and Correcting Grouping Logic with Window Function Alignment

1. Harmonize Window Function and Outer Query Ordering

To ensure the LAG() function references the correct "previous row," its ORDER BY clause must mirror the outer query’s ordering exactly. Modify the window function to:

LAG(indexManu) OVER (
  ORDER BY indexRow, parsed DESC, parsePos
) 

This aligns the window frame’s row sequence with the outer query’s sorting, ensuring that the "previous row" in the window function matches the outer query’s row order.

Testing this change:

• Compare the output of the subquery’s indexManu_grp before and after the adjustment. Look for discrepancies in NULL rows’ grouping keys, especially when indexRow has multiple entries with varying parsed or parsePos values.
• Example: For indexRow=270040080750, ensure that the NULL row inserted at indexRow=270040080760 correctly references the prior indexManu=6 row, not an earlier or later row.

2. Address Consecutive NULLs and Synthetic Key Generation

When multiple NULL rows appear consecutively, the current logic increments the grouping key by 0.5 each time. To prevent compounding offsets, use a nested window function to track the most recent non-NULL indexManu:

CASE
  WHEN indexManu IS NULL THEN
    LAST_VALUE(indexManu IGNORE NULLS) OVER (
      ORDER BY indexRow, parsed DESC, parsePos
    ) + 0.5
  ELSE indexManu
END AS indexManu_grp  

LAST_VALUE(... IGNORE NULLS) scans backward to find the nearest non-NULL indexManu, avoiding the cumulative addition issue.

Edge case validation:

• Insert a test row with two consecutive NULLs for indexManu and verify that both receive the same indexManu_grp value (e.g., 6.5 for a NULL following indexManu=6).

3. Explicitly Order GROUP_CONCAT Results

To guarantee concatenated results respect the original row order, use GROUP_CONCAT’s built-in ordering:

GROUP_CONCAT(indexManu, ',' ORDER BY indexRow, parsed DESC, parsePos)  

This overrides any internal reordering caused by the GROUP BY operation. Apply this to all GROUP_CONCAT calls.

4. Indexing and Query Performance

The current subquery ordering relies on indexRow, parsed DESC, parsePos. To optimize this:

• Create a composite index on concat_test(indexRow, parsed, parsePos).
• For large datasets, include indexManu in the index to enable covering queries:

  CREATE INDEX idx_concat_test_order ON concat_test(indexRow, parsed, parsePos, indexManu);  
  

5. Final Query Structure

Incorporating all fixes:

SELECT
  GROUP_CONCAT(indexManu, ',' ORDER BY indexRow, parsed DESC, parsePos),
  GROUP_CONCAT(indexManu_grp, ',' ORDER BY indexRow, parsed DESC, parsePos),
  GROUP_CONCAT(content, ',' ORDER BY indexRow, parsed DESC, parsePos)
FROM (
  SELECT
    indexManu,
    content,
    CASE
      WHEN indexManu IS NULL THEN
        LAST_VALUE(indexManu IGNORE NULLS) OVER (
          ORDER BY indexRow, parsed DESC, parsePos
        ) + 0.5
      ELSE indexManu
    END AS indexManu_grp
  FROM concat_test
  ORDER BY indexRow, parsed DESC, parsePos
)
GROUP BY indexManu_grp
ORDER BY MIN(indexRow), MIN(parsed), MIN(parsePos);  

6. Validation via Test Cases

• Single NULL row: Confirm that a NULL row after indexManu=6 gets indexManu_grp=6.5 and groups separately from 6.
• Consecutive NULL rows: Ensure all consecutive NULLs share the same indexManu_grp (e.g., 6.5 for two NULLs after 6).
• Varying parsed and parsePos: Verify that rows with the same indexRow but different parsed/parsePos values are ordered correctly in both the window function and final output.

By systematically aligning ordering clauses, refining NULL handling, and enforcing explicit concatenation order, the query reliably groups rows while preserving their original positional semantics.