Output field name semantics#

This specification documents how field names in output record batches should be generated based on given user queries. The field name rules apply to DataFusion queries planned from both SQL queries and Dataframe APIs.

Field name rules#

• All bare column field names MUST not contain relation/table qualifier.

  • Both SELECT t1.id, SELECT id and df.select_columns(&["id"]) SHOULD result in field name: id

• All compound column field names MUST contain relation/table qualifier.

  • SELECT foo + bar SHOULD result in field name: table.foo PLUS table.bar

• Function names MUST be converted to lowercase.

  • SELECT AVG(c1) SHOULD result in field name: avg(table.c1)

• Literal string MUST not be wrapped with quotes or double quotes.

  • SELECT 'foo' SHOULD result in field name: foo

• Operator expressions MUST be wrapped with parentheses.

  • SELECT -2 SHOULD result in field name: (- 2)

• Operator and operand MUST be separated by spaces.

  • SELECT 1+2 SHOULD result in field name: (1 + 2)

• Function arguments MUST be separated by a comma , and a space.

  • SELECT f(c1,c2) and df.select(vec![f.udf("f")?.call(vec![col("c1"), col("c2")])]) SHOULD result in field name: f(table.c1, table.c2)

Appendices#

Examples and comparison with other systems#

Data schema for test sample queries:

CREATE TABLE t1 (id INT, a VARCHAR(5));
INSERT INTO t1 (id, a) VALUES (1, 'foo');
INSERT INTO t1 (id, a) VALUES (2, 'bar');

CREATE TABLE t2 (id INT, b VARCHAR(5));
INSERT INTO t2 (id, b) VALUES (1, 'hello');
INSERT INTO t2 (id, b) VALUES (2, 'world');

Projected columns#

Query:

SELECT t1.id, a, t2.id, b
FROM t1
JOIN t2 ON t1.id = t2.id

DataFusion Arrow record batches output:

Spark, MySQL 8 and PostgreSQL 13 output:

SQLite 3 output:

Function transformed columns#

Query:

SELECT ABS(t1.id), abs(-id) FROM t1;

DataFusion Arrow record batches output:

Spark output:

MySQL 8 output:

PostgreSQL 13 output:

SQlite 3 output:

Function with operators#

Query:

SELECT t1.id + ABS(id), ABS(id * t1.id) FROM t1;

DataFusion Arrow record batches output:

Spark output:

MySQL 8 output:

PostgreSQL output:

SQLite output:

Project literals#

Query:

SELECT 1, 2+5, 'foo_bar';

DataFusion Arrow record batches output:

Spark output:

MySQL output:

PostgreSQL output:

SQLite 3 output: