Property handling for `select *`

[jexp]

For properties there are some options:

SELECT p.*
FROM products as p

• [x] RETURN p
• [ ] RETURN properties(p) as p
• [ ] RETURN p {.*} as p

All of which have a different connotation that the SQL which would generate as many result columns as there are table columns

If we plan to use the graph schema we could also spell out the properties that we know to exist on the label

• RETURN p.name, p.age

[lukaseder]

Note that in SQL, these two aren't the same either:

• SELECT p.*
• SELECT p (or SELECT ROW(p.*) or equivalent syntaxes)

The first flattens the table columns, the second nests it (only available in ORDBMS, and not necessarily always in the same way).

[jexp]

@lukaseder how would you support the first for now? Like Michael did or leave it off until we have schema support so that we can spell the properties out?

Btw. is there any order defined for column-names in SQL there?

[lukaseder]

The relevant types are org.jooq.Asterisk (unqualified *) and org.jooq.QualifiedAsterisk. They're both accessible via the sum SelectFieldOrAsterisk:

public sealed interface SelectFieldOrAsterisk
extends
    QueryPart
permits
    SelectField,
    Asterisk,
    QualifiedAsterisk
{}

Which is what's being returned by Select::$select. If jOOQ needs to expand the columns, there's a whole algorithm inside of SelectQueryImpl, but I'm not sure if you need that already? You could access it via Select::getSelect, which is a historic method that I regret, but it's public :)

Btw. is there any order defined for column-names in SQL there?

Yes, the * always expands to the logical CREATE TABLE order. Most RDBMS only support ALTER TABLE .. ADD by adding columns to the end of that logical order. Some RDBMS (e.g. Firebird, H2, HSQLDB, MariaDB, MySQL) have means to ADD columns before others or at certain positions. Oracle can do this via a hack (making columns invisible and visible again).

When jOOQ has Meta information and needs to expand the * for whatever reason (e.g. because the BigQuery * EXCEPT (x, y) syntax is used, or two unqualified *, * are combined without the RDBMS supporting this natively), then jOOQ will expand the * in that known order.

[michael-simons]

Kamino closed and cloned this issue to neo4j/neo4j-jdbc