New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for Common Table Expressions #486

Closed
BuBuaBu opened this Issue Aug 27, 2013 · 13 comments

Comments

Projects
None yet
2 participants
@BuBuaBu

BuBuaBu commented Aug 27, 2013

SQL:1999 introduce the 'Common Table Expressions' - The WITH clause.

It would be nice to have this kind of syntax in QueryDSL. In some case It can greatly reduce the sql statement size and also introduce recursive request.

Regarding databases supporting this feature, we've got Oracle, PostgreSQL, MSSQL, H2, HSQLDB
And databases missing this features: MySQL, SQLite, Derby, CUBRID

Is there any plan to support this feature of SQL:1999 ?

SQL Syntax:
WITH
cte1 AS ( SELECT ... FROM table1),
cte2 AS ( SELECT ... FROM table2)
SELECT ...
FROM table3, cte1, cte2
WHERE ...
;

@ghost ghost assigned timowest Aug 27, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Aug 29, 2013

Member

Here are some syntax variants

query.with(
  sq().from(...).list(...).as(alias1),
  sq().from(...).list(...).as(alias2))
.from(table3, alias1, alias2)
.where(...)
.list(...)

and

query.with(alias1, sq().from(...).list(...)).
.with(alias2, sq().from(...).list(...)).
.from(table3, alias1, alias2)
.where(...)
.list(...)

Syntactically I prefer the first one, but the second one would be safer concerning the method signature.

What do you think?

Member

timowest commented Aug 29, 2013

Here are some syntax variants

query.with(
  sq().from(...).list(...).as(alias1),
  sq().from(...).list(...).as(alias2))
.from(table3, alias1, alias2)
.where(...)
.list(...)

and

query.with(alias1, sq().from(...).list(...)).
.with(alias2, sq().from(...).list(...)).
.from(table3, alias1, alias2)
.where(...)
.list(...)

Syntactically I prefer the first one, but the second one would be safer concerning the method signature.

What do you think?

@BuBuaBu

This comment has been minimized.

Show comment
Hide comment
@BuBuaBu

BuBuaBu Aug 29, 2013

In case of a with recursive clause, aliased subquery declared inside the with clause can reference an alias.
That means with an example:

with recursive
alias1 as
(select ... from table1
union all
select ... from alias1
)
select ... from alias1;

So with the second syntax, maybe it is harder to handle this case?

BuBuaBu commented Aug 29, 2013

In case of a with recursive clause, aliased subquery declared inside the with clause can reference an alias.
That means with an example:

with recursive
alias1 as
(select ... from table1
union all
select ... from alias1
)
select ... from alias1;

So with the second syntax, maybe it is harder to handle this case?

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Sep 14, 2013

Member

I tried now the first approach, since the second would have meant that the alias would need to be turned around. Here are some examples

    @Test
    @IncludeIn({HSQLDB, ORACLE, POSTGRES})
    public void With() {
        query().with(employee2, sq().from(employee)
                  .where(employee.firstname.eq("Tom"))
                  .list(Wildcard.all))
               .from(employee, employee2)
               .list(employee.id, employee2.id);
    }

    @Test
    @IncludeIn({HSQLDB, ORACLE, POSTGRES})
    public void With2() {
        QEmployee employee3 = new QEmployee("e3");
        query().with(employee2, sq().from(employee)
                  .where(employee.firstname.eq("Tom"))
                  .list(Wildcard.all))
               .with(employee2, sq().from(employee)
                  .where(employee.firstname.eq("Tom"))
                  .list(Wildcard.all))
               .from(employee, employee2, employee3)
               .list(employee.id, employee2.id, employee3.id);
    }


    @Test
    @IncludeIn({ORACLE, POSTGRES})
    public void With_Recursive() {
        query().withRecursive(employee2, sq().from(employee)
                  .where(employee.firstname.eq("Tom"))
                  .list(Wildcard.all))
               .from(employee, employee2)
               .list(employee.id, employee2.id);
    }
Member

timowest commented Sep 14, 2013

I tried now the first approach, since the second would have meant that the alias would need to be turned around. Here are some examples

    @Test
    @IncludeIn({HSQLDB, ORACLE, POSTGRES})
    public void With() {
        query().with(employee2, sq().from(employee)
                  .where(employee.firstname.eq("Tom"))
                  .list(Wildcard.all))
               .from(employee, employee2)
               .list(employee.id, employee2.id);
    }

    @Test
    @IncludeIn({HSQLDB, ORACLE, POSTGRES})
    public void With2() {
        QEmployee employee3 = new QEmployee("e3");
        query().with(employee2, sq().from(employee)
                  .where(employee.firstname.eq("Tom"))
                  .list(Wildcard.all))
               .with(employee2, sq().from(employee)
                  .where(employee.firstname.eq("Tom"))
                  .list(Wildcard.all))
               .from(employee, employee2, employee3)
               .list(employee.id, employee2.id, employee3.id);
    }


    @Test
    @IncludeIn({ORACLE, POSTGRES})
    public void With_Recursive() {
        query().withRecursive(employee2, sq().from(employee)
                  .where(employee.firstname.eq("Tom"))
                  .list(Wildcard.all))
               .from(employee, employee2)
               .list(employee.id, employee2.id);
    }
@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Sep 14, 2013

Member

Common table support is available in the latest snapshot from here https://oss.sonatype.org/content/repositories/snapshots/

Member

timowest commented Sep 14, 2013

Common table support is available in the latest snapshot from here https://oss.sonatype.org/content/repositories/snapshots/

@BuBuaBu

This comment has been minimized.

Show comment
Hide comment
@BuBuaBu

BuBuaBu Sep 16, 2013

Hello,

I made some quick test on Oracle only, and there are few issues.

  • The sql produce look like "with alias (select ...) ...", but should be
    "with alias as (select ...) ..."
  • The withRecursive() produce "with recursive ...", but should be "with ..."
  • The withRecursive() produce "with recursive alias (select a from ...)
    ...", but should be "with alias (a) as (select a from ...) ..."

I managed to quick fix the first two issues by overriding some templates in
OracleTemplates.

Sorry for not giving more details, I've got no time now. I will perform
more tests by the end of the week.

Vivien

BuBuaBu commented Sep 16, 2013

Hello,

I made some quick test on Oracle only, and there are few issues.

  • The sql produce look like "with alias (select ...) ...", but should be
    "with alias as (select ...) ..."
  • The withRecursive() produce "with recursive ...", but should be "with ..."
  • The withRecursive() produce "with recursive alias (select a from ...)
    ...", but should be "with alias (a) as (select a from ...) ..."

I managed to quick fix the first two issues by overriding some templates in
OracleTemplates.

Sorry for not giving more details, I've got no time now. I will perform
more tests by the end of the week.

Vivien

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Sep 16, 2013

Member

Which version of Oracle DB are you using?

Member

timowest commented Sep 16, 2013

Which version of Oracle DB are you using?

timowest added a commit that referenced this issue Sep 22, 2013

@BuBuaBu

This comment has been minimized.

Show comment
Hide comment
@BuBuaBu

BuBuaBu Sep 24, 2013

Hello,

I'm using Oracle 11.2.0.3

Anyway, I got some difficulties to write a With recursive clause (with latest sources)

The target is this SQL query:
with sub (id, firstname, superior_id) as
(
select id, firstname, superior_id from employee where firstname like 'Mike'
union all
select employee.id, employee.firstname, employee.superior_id from sub, employee where employee.superior_id = sub.id
)
select * from sub;

I tried something like this:

SQLQuery query = new SQLQuery(getConnection(), new Configuration(new OracleTemplates()));
QEmployee e = new QEmployee("e");
PathBuilder sub = new PathBuilder(Tuple.class, "sub");

SQLSubQuery subQuery = new SQLSubQuery();

query.withRecursive(sub,
new SQLSubQuery().from(subQuery.unionAll(
new SQLSubQuery().from(e).where(e.firstname.eq("Mike")).unique(e.id, e.firstname, e.superiorId),
new SQLSubQuery().from(e, sub).where(e.superiorId.eq(sub.get(e.id))).unique(e.id, e.firstname, e.superiorId)
)
).list(sub.get(e.id), sub.get(e.firstname), sub.get(e.superiorId)));
query.from(e, sub);

But this is not working, I may have to many SubQuery level with this syntax.
I think that my problem is more related to writing the union clause rather than the with one.

Could you help me to find the good syntax?

Vivien

Le 16 sept. 2013 à 18:41, Timo Westkämper notifications@github.com a écrit :

Which version of Oracle DB are you using?


Reply to this email directly or view it on GitHub.

BuBuaBu commented Sep 24, 2013

Hello,

I'm using Oracle 11.2.0.3

Anyway, I got some difficulties to write a With recursive clause (with latest sources)

The target is this SQL query:
with sub (id, firstname, superior_id) as
(
select id, firstname, superior_id from employee where firstname like 'Mike'
union all
select employee.id, employee.firstname, employee.superior_id from sub, employee where employee.superior_id = sub.id
)
select * from sub;

I tried something like this:

SQLQuery query = new SQLQuery(getConnection(), new Configuration(new OracleTemplates()));
QEmployee e = new QEmployee("e");
PathBuilder sub = new PathBuilder(Tuple.class, "sub");

SQLSubQuery subQuery = new SQLSubQuery();

query.withRecursive(sub,
new SQLSubQuery().from(subQuery.unionAll(
new SQLSubQuery().from(e).where(e.firstname.eq("Mike")).unique(e.id, e.firstname, e.superiorId),
new SQLSubQuery().from(e, sub).where(e.superiorId.eq(sub.get(e.id))).unique(e.id, e.firstname, e.superiorId)
)
).list(sub.get(e.id), sub.get(e.firstname), sub.get(e.superiorId)));
query.from(e, sub);

But this is not working, I may have to many SubQuery level with this syntax.
I think that my problem is more related to writing the union clause rather than the with one.

Could you help me to find the good syntax?

Vivien

Le 16 sept. 2013 à 18:41, Timo Westkämper notifications@github.com a écrit :

Which version of Oracle DB are you using?


Reply to this email directly or view it on GitHub.

timowest added a commit that referenced this issue Sep 24, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Sep 24, 2013

Member

I added a test which constructs a similar query. I also modified AbstractSQLQuery. For Oracle with and withRecursive are equivalent.

Member

timowest commented Sep 24, 2013

I added a test which constructs a similar query. I also modified AbstractSQLQuery. For Oracle with and withRecursive are equivalent.

@BuBuaBu

This comment has been minimized.

Show comment
Hide comment
@BuBuaBu

BuBuaBu Sep 24, 2013

There are still some issue.

On Postgres, we must use a with recursive "ERROR: relation "sub" does not exist
Detail: There is a WITH item named "sub", but it cannot be referenced from this part of the query.
Hint: Use WITH RECURSIVE, or re-order the WITH items to remove forward references."

Could you also add the method
public Q withRecursive(Path alias, Expression query) ?

On Oracle (11.2) It's still not working: "ORA-32039: recursive WITH clause must have column alias list"

Yes, on Oracle with and with recursive are equivalent, but for your test case you should use a with recursive so that it could be the same for all databases.

Vivien

Le 24 sept. 2013 à 21:03, Timo Westkämper notifications@github.com a écrit :

I added a test which constructs a similar query. I also modified AbstractSQLQuery. For Oracle with and withRecursive are equivalent.


Reply to this email directly or view it on GitHub.

BuBuaBu commented Sep 24, 2013

There are still some issue.

On Postgres, we must use a with recursive "ERROR: relation "sub" does not exist
Detail: There is a WITH item named "sub", but it cannot be referenced from this part of the query.
Hint: Use WITH RECURSIVE, or re-order the WITH items to remove forward references."

Could you also add the method
public Q withRecursive(Path alias, Expression query) ?

On Oracle (11.2) It's still not working: "ORA-32039: recursive WITH clause must have column alias list"

Yes, on Oracle with and with recursive are equivalent, but for your test case you should use a with recursive so that it could be the same for all databases.

Vivien

Le 24 sept. 2013 à 21:03, Timo Westkämper notifications@github.com a écrit :

I added a test which constructs a similar query. I also modified AbstractSQLQuery. For Oracle with and withRecursive are equivalent.


Reply to this email directly or view it on GitHub.

timowest added a commit that referenced this issue Sep 24, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Sep 24, 2013

Member

I updated the test and added support for column lists.

Member

timowest commented Sep 24, 2013

I updated the test and added support for column lists.

@BuBuaBu

This comment has been minimized.

Show comment
Hide comment
@BuBuaBu

BuBuaBu Sep 24, 2013

Nice, I now get my query working on both Oracle and Postgress.

I saw that you also introduce the WithBuilder class.

Why not make it extends EntityPathBase / PathBuilder ?
So that we could write thing like:

WithBuilder sub = query.withRecursive(String alias, Path…columns)
withBuilder.as(new SQLSubQuery().unionAll
(
new SQLSubQuery().from(e).where(e.firstname.eq("Mike")).list(e.id, e.firstname, e.superiorId),
new SQLSubQuery().from(e, sub).where(e.superiorId.eq(sub.get(e.id))).list(e.id, e.firstname, e.superiorId)
));
query.from(sub);

without the need to use an extra PathBuilder.
What do you think?

This will also a little bit more safe, because in that case we could raise an exception if we try to use a column not declared in the withBuilder.

Le 24 sept. 2013 à 22:06, Timo Westkämper notifications@github.com a écrit :

I updated the test and added support for column lists.


Reply to this email directly or view it on GitHub.

BuBuaBu commented Sep 24, 2013

Nice, I now get my query working on both Oracle and Postgress.

I saw that you also introduce the WithBuilder class.

Why not make it extends EntityPathBase / PathBuilder ?
So that we could write thing like:

WithBuilder sub = query.withRecursive(String alias, Path…columns)
withBuilder.as(new SQLSubQuery().unionAll
(
new SQLSubQuery().from(e).where(e.firstname.eq("Mike")).list(e.id, e.firstname, e.superiorId),
new SQLSubQuery().from(e, sub).where(e.superiorId.eq(sub.get(e.id))).list(e.id, e.firstname, e.superiorId)
));
query.from(sub);

without the need to use an extra PathBuilder.
What do you think?

This will also a little bit more safe, because in that case we could raise an exception if we try to use a column not declared in the withBuilder.

Le 24 sept. 2013 à 22:06, Timo Westkämper notifications@github.com a écrit :

I updated the test and added support for column lists.


Reply to this email directly or view it on GitHub.

timowest added a commit that referenced this issue Sep 24, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Sep 25, 2013

Member

I see WithBuilder more as part of the query grammar. It's main purpose is to provide the as method between with and the rest of the query.

Normal usage would be

query.withRecursive(...).as(
        ...
    )
    .from(...)
    .where(...)
    .list(...);

Or with the column declaration just with/withRecursive and then back to the query.

Member

timowest commented Sep 25, 2013

I see WithBuilder more as part of the query grammar. It's main purpose is to provide the as method between with and the rest of the query.

Normal usage would be

query.withRecursive(...).as(
        ...
    )
    .from(...)
    .where(...)
    .list(...);

Or with the column declaration just with/withRecursive and then back to the query.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Oct 20, 2013

Member

Released in 3.2.4

Member

timowest commented Oct 20, 2013

Released in 3.2.4

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment