<|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: bird_training_bucket
Scope Name: restaurant

Collection Schema:
{"`bird_training_bucket`.`restaurant`.`location`": {"Flavor": "", "properties": {"id_restaurant": {"samples": [1534], "type": "number"}, "city": {"samples": [["berkeley"]], "type": "string"}, "street_name": {"samples": [["addison st"]], "type": "string"}, "street_num": {"samples": [[427]], "type": "number"}}, "type": "object"}, "`bird_training_bucket`.`restaurant`.`generalinfo`": {"Flavor": "", "properties": {"city": {"samples": ["emeryville"], "type": "string"}, "review": {"samples": [2], "type": "number"}, "label": {"samples": ["doucet's lounge"], "type": "string"}, "id_restaurant": {"samples": [196], "type": "number"}, "food_type": {"samples": ["american"], "type": "string"}}, "type": "object"}, "`bird_training_bucket`.`restaurant`.`geographic`": {"Flavor": "", "properties": {"city": {"samples": ["charlotte"], "type": "string"}, "region": {"samples": ["bay area"], "type": "string"}, "county": {"samples": ["alameda county"], "type": "string"}}, "type": "object"}}

Natural Language Query:
Show the identifying street address details for places that serve alcohol (a “bar”), located in Oakland, with a review score of 2.7.
<|im_end|>
<|im_start|>assistant

```sql++
SELECT T2.`street_num` FROM `bird_training_bucket`.`restaurant`.`generalinfo` AS T1 INNER JOIN `bird_training_bucket`.`restaurant`.`location` AS T2 ON T1.`id_restaurant` = T2.`id_restaurant` WHERE T1.`review` = 2.7 AND T2.`city` = 'oakland' AND T1.`food_type` = 'bar'