Views
No views yet
<|im_start|>system
You are an expert in SQL++ query generation. Given a document schema and a natural language query, generate a valid SQL++ query.
Task Instructions:
- Backtick-quote field names that are reserved keywords or contain spaces/special characters.
WRONG: SELECT value, Enrollment (K-12) ...
RIGHT: SELECT `value`, `Enrollment (K-12)` ...
- SUBSTR is 0-based: SUBSTR(str, 0, 4) returns the first 4 characters. Use this for year extraction from date strings.
WRONG: SUBSTR(dob, 1, 4) = '1990'
RIGHT: SUBSTR(dob, 0, 4) = '1990'
- Only use keyspaces and fields present in the schema; do not infer array, object, or foreign-key structure unless the schema shows it.
WRONG: UNNEST t.tags AS tag (when `tags` is a plain string in schema)
RIGHT: WHERE t.tags = 'sports'
- Never use CAST(); it is not supported in SQL++.
WRONG: CAST(price AS FLOAT)
RIGHT: TO_NUMBER(price)
- Use the exact field named in the question; do not substitute a related variant.
WRONG: question asks for `revenue`, query uses `total_sales`
RIGHT: query uses `revenue`
- When similar fields exist, prefer the one whose name most literally matches the question; use sample values to distinguish (e.g., `type` vs `types`, `id` vs `uuid`).
WRONG: question asks for "account type", query uses `types` (samples: [1,2,3])
RIGHT: uses `type` (samples: ["savings","checking"])
- Prefer a direct count or pre-aggregated field over computing it from related records when one exists.
WRONG: (SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.id) >= 3
RIGHT: WHERE p.review_count >= 3
- Wrap string fields in TO_NUMBER() before numeric aggregation or ordering.
WRONG: AVG(p.score) when score is stored as "8.5"
RIGHT: AVG(TO_NUMBER(p.score))
- If one collection contains all needed fields and filters, do not join.
WRONG: FROM orders o JOIN orders o2 ON ...
RIGHT: FROM orders o WHERE o.status = 'shipped'
- Use DISTINCT when unique values are requested or when a join could produce duplicates.
WRONG: SELECT c.id FROM customers c JOIN orders o ON c.id = o.customer_id
RIGHT: SELECT DISTINCT c.id ...
- For yes/no questions, return a single existence answer, not matching rows.
WRONG: SELECT e.name FROM employees e WHERE e.dept = 'HR'
RIGHT: SELECT COUNT(*) > 0 FROM employees e WHERE e.dept = 'HR'
- When listing entities with no specified attribute, return the entity identifier.
WRONG: question says "list employees", query returns SELECT e.name
RIGHT: SELECT e.id
- No colon after FROM.
WRONG: FROM: orders o
RIGHT: FROM orders o
- Every alias in a statement must be unique. Couchbase does not allow the same alias to be assigned more than once, even across subqueries or when referencing the same collection.
WRONG: SELECT * FROM orders o WHERE o.id IN (SELECT RAW o.ref_id FROM orders o WHERE ...)
RIGHT: SELECT * FROM orders o WHERE o.id IN (SELECT RAW o2.ref_id FROM orders o2 WHERE ...)
- Match literal types to schema field types; quote string-typed fields even when values look numeric.
WRONG: WHERE zip_code = 10001 (zip_code type is string in the schema)
RIGHT: WHERE zip_code = "10001"
<|im_end|>
<|im_start|>user
Bucket Name: training_bucket
Scope Name: Publishing_House_and_Book_Distribution_scope
Collection Schema:
{}
Natural Language Query:
Help me find all blog posts published on our platform that have received comments from users, where I need to see the full post content including title, author information, publication date, category tags, and view count, along with all the comment details such as commenter name, comment text, timestamp, and any replies to those comments, and also include posts that haven't received any comments yet so I can see which content isn't engaging our audience.
The SQL++ query you generated:
```sql++
-- NOTE: Collections 'blog_posts' and 'comments' do not exist in the specified scope.
-- Without collection schemas, this query cannot be corrected.
SELECT bp.*, c.commenter_name, c.comment_text, c.timestamp AS comment_timestamp, c.replies
FROM `training_bucket`.`Publishing_House_and_Book_Distribution_scope`.blog_posts AS bp
LEFT JOIN `training_bucket`.`Publishing_House_and_Book_Distribution_scope`.comments AS c ON META(bp).id = c.post_id;SELECT p.*, p.`author` AS author_information, p.`published_at` AS publication_date, p.`tags` AS category_tags, p.`views` AS view_count, c.`author` AS commenter_name, c.`content` AS comment_text, c.`created_at` AS comment_timestamp FROM `training_bucket`.`Educational_Learning_Management_Ecosystem_scope`.`discussion_posts` AS p LEFT JOIN `training_bucket`.`Educational_Learning_Management_Ecosystem_scope`.`discussion_posts` AS c ON META(p).`id` = c.`parent_id` WHERE p.`parent_id` IS MISSING OR p.`parent_id` IS NULL;