[Feature request] Support `from-first-syntax` for improved query ergonomics in Table Model

[JackieTien97]

Search before asking

• I searched in the issues and found nothing similar.

Motivation

Currently, IoTDB follows the standard SQL standard where the SELECT clause precedes the FROM clause (e.g., SELECT s1, s2 FROM root.sg.d1). While standard, this syntax introduces a few friction points for developers and data analysts:

1. Poor IDE / CLI Auto-completion: When a user types SELECT , the query editor does not yet know which table the user intends to query. Therefore, it cannot provide intelligent autocomplete suggestions for columns. By the time the user types FROM t1, they have to go back to the SELECT clause to utilize auto-completion.
2. Data Exploration Friction: During quick data exploration, users frequently want to inspect all data in a table. Writing SELECT * FROM t1 is repetitive. DuckDB's approach of simply typing FROM t1 to imply SELECT * is highly efficient.
3. Logical Flow: The FROM-first syntax aligns better with the actual execution logic and human thought process: first, you define the data source (FROM), then you filter it (WHERE), and finally, you choose what to project (SELECT).

Introducing a FROM-first syntax would significantly enhance the developer experience, making CLI interactions and query scripting much more intuitive.

Solution

We propose extending the IoTDB Table SQL dialect to support FROM-first queries without breaking existing standard SQL queries.

The implementation will primarily involve the frontend parser and logical planner:

1. Update Antlr4 Grammar: Modify iotdb-core/relational-grammar/src/main/antlr4/org/apache/iotdb/db/relational/grammar/sql/RelationalSql.g4 to allow statements to begin with the FROM keyword.

   • Example 1: FROM t1 SELECT s1, s2 WHERE time > 0;

   • Example 2: FROM t1 WHERE time > 0; (Omitting the SELECT clause implies SELECT *).

2. AST Transformation in Logical Planner: Update the SQL visitor/planner so that when a FROM-first Abstract Syntax Tree (AST) is detected, it is normalized into the standard internal logical query plan. The underlying execution engine (physical plan, operators) should require zero changes.

3. Ensure Backward Compatibility: This must be an additive feature. The traditional SELECT ... FROM ... syntax must remain fully supported and serve as the default standard.

4. Support for Joins: Ensure the syntax gracefully handles multiple tables (e.g., FROM t1 JOIN t2 ON XXXX SELECT XXXX;).

Example Usage:

-- Current Syntax
SELECT temperature, humidity FROM t1 WHERE time >= 10;

-- Proposed Syntax
FROM t1 SELECT temperature, humidity WHERE time >= 10;

-- Quick Exploration (Implies SELECT *)
FROM t1 LIMIT 10;

Alternatives

No response

Are you willing to submit a PR?

• I'm willing to submit a PR!

[JackieTien97]

The idea is from DuckDB

[Abdelrahman-358]

Hi @JackieTien97 , I would like to work on this issue.