.ai counting is always incorrect

[joaquincasares]

This prompt always provides this query:

>   .ai count attendees by country

SELECT country, COUNT(*) FROM conferences.super_awesome ALLOW FILTERING;


┌─────────┬───────┐                                                                                                                                                      
│ country │ count │                                                                                                                                                      
├─────────┼───────┤                                                                                                                                                      
│ Brazil  │ 2     │                                                                                                                                                      
└─────────┴───────┘

However, a SELECT * shows:

┌──────────────────────────────────────┬─────────┬──────────────────────┬───────────────────┬─────────────┬──────────────────────┬───────────────────────┬─────────────┐ 
│ attendee_id                          │ country │ dietary_restrictions │ email             │ full_name   │ registration_date    │ session_interests     │ ticket_type │ 
├──────────────────────────────────────┼─────────┼──────────────────────┼───────────────────┼─────────────┼──────────────────────┼───────────────────────┼─────────────┤ 
│ bf1830f0-b399-4875-911a-635bd275daea │ Brazil  │ [vegetarian]         │ maria@example.com │ Maria Silva │ 2025-09-15T00:00:00Z │ [AI, Cloud]           │ VIP         │ 
│ a4b94d02-4a4f-47fb-a58f-5defa00c0525 │ Japan   │ []                   │ yuki@example.jp   │ Yuki Tanaka │ 2025-09-20T00:00:00Z │ [Databases, Security] │ Standard    │ 
└──────────────────────────────────────┴─────────┴──────────────────────┴───────────────────┴─────────────┴──────────────────────┴───────────────────────┴─────────────┘ 

[hshimizu]

This is an interesting one. I've added the following guidelines in the prompt, but the AI is still not using GROUP BY when generating the query. I'll keep looking into this.

GROUP BY and Aggregation Guidelines:

• CQL GROUP BY has STRICT restrictions: can ONLY group by partition key columns and clustering columns in the EXACT order they appear in the primary key
• You CANNOT group by regular (non-key) columns like you can in SQL
• ALWAYS get the table schema first using get_schema to check the primary key structure before using GROUP BY
• Examples of valid GROUP BY:
  • Table with PRIMARY KEY (country, city, id) → can GROUP BY country, or GROUP BY country, city
  • Table with PRIMARY KEY ((country, region), city) → can GROUP BY country, region, or GROUP BY country, region, city
• Examples of INVALID GROUP BY that will fail:
  • Cannot GROUP BY a non-key column (will error: "Group by is currently only supported on the columns of the PRIMARY KEY")
  • Cannot skip columns (e.g., if key is (country, city, id), cannot GROUP BY country, id - must be contiguous prefix)
• When user asks to "count by X" or "group by X":
  1. Get the schema first to check if X is part of the primary key
  2. If X is NOT in the primary key, explain that CQL cannot GROUP BY non-key columns
  3. Suggest alternatives: use SELECT with ALLOW FILTERING and explain client-side aggregation is needed, or suggest creating a materialized view with X as part of the key
  4. For counting, if it requires scanning with ALLOW FILTERING, warn that results must be counted client-side