EBean generates SQL with syntax error (at the "order by" clause)

618 views
Skip to first unread message

Gyuro

unread,
Jul 10, 2014, 7:19:25 AM7/10/14
to eb...@googlegroups.com
Hello Everyone!

I've encountered a kinda strange problem, and I would like to ask for Your help. I received this exception, and I was not able to find solution:

javax.persistence.PersistenceException: java.util.concurrent.ExecutionException: javax.persistence.PersistenceException: Query threw SQLException:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{pp_table}value limit 40' at line 1 
Bind values:[12345678-1234-1234-1234-12345678] 
Query was:
select distinct t0.id c0, t0.amount c1 from ps_view t0 join pp_table u1 on u1.id = t0.id  join pp_table_to_ww_table u2z_ on u2z_.pp_id = u1.id  join ww_table u2 on u2.id = u2z_.ww_id  where (1 = 1 and u2.id = ? )  order by ${pp_table}value limit 40 

        at com.avaje.ebeaninternal.server.query.LimitOffsetPage.getList(LimitOffsetPage.java:64)
        at com.avaje.ebeaninternal.server.query.LimitOffsetPagingQuery.get(LimitOffsetPagingQuery.java:111)
        at com.avaje.ebeaninternal.server.query.LimitOffsetList.get(LimitOffsetList.java:49)
        ...

So technically EBean generates an invalid sql, and I am almost clueless why.
I'm using EBean 3.2.1 with MySQL database (MySQL-Java connector version 5.1.26).

My entities are looking like this:
@Entity
@Table(name = "ps_view")
public class PsViewAggregate extends BaseEntity {
...
@OneToOne
@JoinColumn(name = "id", referencedColumnName = "id")
private PP pp;
...
//Getters + Setters//
}

@Entity
@Table(name = "pp_table")
public class PP {
...
@Id
@Column(name = "id")
private UUID id;
...
@JoinTable(name = "pp_table_to_ww_table",
          joinColumns = {@JoinColumn(name = "pp_id", referencedColumnName = "id")},
          inverseJoinColumns = {@JoinColumn(name = "ww_id", referencedColumnName = "id")})
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<WW> wwList = new ArrayList<>();
...
//Getters + Setters//
}

@Entity
@Table(name = "ww_table")
public class WW {
...
@Id
@Column(name = "id")
private UUID id;
...
//Getters + Setters//
}


I have a Query object (created by the UI), and the following sql code can be generated from it:
select t0.id c0, t0.amount c1 from ps_view t0 where 1 = 1 limit 40;

If I add my where Expression to the Query object like this:
...
PP pp = ...;
WW ww = ...;
String propertyName = PsViewAggregate_.get(PP_.wwList).toString(); // = "pp.wwList"
...
Expression expression = Expr.and(
Expr.raw("1 = 1"),
Expr.eq(propertyName, ww)
);
...
query.where(expression);
Then the following, perfecly working sql will be generated:
select distinct t0.id c0, t0.amount c1 from ps_view t0 join pp_table u1 on u1.id = t0.id  join pp_table_to_ww_table u2z_ on u2z_.pp_id = u1.id  join ww_table u2 on u2.id = u2z_.ww_id  where (1 = 1 and u2.id = ? )  limit 40; --bind(12345678-1234-1234-1234-12345678)


Unfortunately if I add an OrderBy object to the Query like this:
...
OrderBy<PsViewAggregate> orderby = new OrderBy<>("pp_table.value")
...
query.setOrderBy(orderBy);
...
Then the already seen sql code with syntax error will be generated:
select distinct t0.id c0, t0.amount c1 from ps_view t0 join pp_table u1 on u1.id = t0.id  join pp_table_to_ww_table u2z_ on u2z_.pp_id = u1.id  join ww_table u2 on u2.id = u2z_.ww_id  where (1 = 1 and u2.id = ? )  order by ${pp_table}value limit 40;


The "${pp_table}" string should be converted to "u1." string, but it does not occur for some reason.
As you can see, PP connects with a @ManyToMany connection to WW, and I assume that this could be the core of the problem.
Does anyone has some idea, what could I do to solve this problem?

Thank You for the help!

Daryl Stultz

unread,
Jul 10, 2014, 8:03:24 AM7/10/14
to eb...@googlegroups.com
On Thu, Jul 10, 2014 at 7:19 AM, Gyuro <darth...@gmail.com> wrote:

Unfortunately if I add an OrderBy object to the Query like this:
...
OrderBy<PsViewAggregate> orderby = new OrderBy<>("pp_table.value")
...
query.setOrderBy(orderBy);
...
Then the already seen sql code with syntax error will be generated:
select distinct t0.id c0, t0.amount c1 from ps_view t0 join pp_table u1 on u1.id = t0.id  join pp_table_to_ww_table u2z_ on u2z_.pp_id = u1.id  join ww_table u2 on u2.id = u2z_.ww_id  where (1 = 1 and u2.id = ? )  order by ${pp_table}value limit 40;


The "${pp_table}" string should be converted to "u1." string, but it does not occur for some reason.

It's a little hard for me to see the full picture since you don't include the base query generated by the UI and I don't see a "value" field in PP. What's going on is that you are passing a "raw sql" expression to the OrderBy constructor. Ebean tries to parse it out and replace model paths with the table aliases such as the "u1" you are expecting. Since "pp_table" doesn't match a property of the root object you are querying, it just passes the expression through as raw sql. You should not be specifying the table name to order by but the model path. I can't tell you what the path should be because I can't see the whole query and object model. It might be "pp.value".

Also you should not initialize wwList, let Ebean initialize it and you'll get a BeanList that will be aware of adds and removes.

/Daryl

Gyuro

unread,
Jul 10, 2014, 11:34:55 AM7/10/14
to eb...@googlegroups.com
Thank You for the quick answer Daryl.

Here is the PP entity with the missed out fields:
@Entity
@Table(name = "pp_table")
public class PP {
...
@Id
@Column(name = "id")
private UUID id;

@Basic(optional = false)
@Column(name = "name", length = 100, nullable = true)
private String name;
@Basic(optional = false)
@Column(name = "value", length = 100, nullable = false)
private String value;
@JoinTable(name = "pp_table_to_ww_table",
          joinColumns = {@JoinColumn(name = "pp_id", referencedColumnName = "id")},
          inverseJoinColumns = {@JoinColumn(name = "ww_id", referencedColumnName = "id")})
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<WW> wwList = new ArrayList<>();
...
//Getters + Setters//
}


The base query is generated somehow like this:
...
String column01 = PsViewAggregate_.pp.toString(); //="pp"
String column02 = PsViewAggregate_.pp.get(PP_.value).toString(); //="pp.value"
String column03 = PsViewAggregate_.pp.get(PP_.name).toString(); //="pp.name"
String columns = column01 + ", " + column02 + ", " + column03; //="pp, pp.value, pp.name"
...
Query<PsViewAggregate> query = Ebean.find(PsViewAggregate.class);
...
query.select(columns);
...

The order by clause has been generated from meta object too, but at the end it is converted into String:
...
String orderByStatement = PsViewAggregate_.get(PP_.value).toString(); //="pp_table.value"
...
OrderBy<PsViewAggregate> orderby = new OrderBy<>(orderByStatement);
...
query.setOrderBy(orderBy);
...



Also you should not initialize wwList, let Ebean initialize it and you'll get a BeanList that will be aware of adds and removes.

Thank You for this advice, I will use it further on :) 

Daryl Stultz

unread,
Jul 10, 2014, 11:58:52 AM7/10/14
to eb...@googlegroups.com
On Thu, Jul 10, 2014 at 11:34 AM, Gyuro <darth...@gmail.com> wrote:
Thank You for the quick answer Daryl.

Here is the PP entity with the missed out fields:
@Entity
@Table(name = "pp_table")
public class PP {
...
@Id
@Column(name = "id")
private UUID id;

@Basic(optional = false)
@Column(name = "name", length = 100, nullable = true)
private String name;
@Basic(optional = false)
@Column(name = "value", length = 100, nullable = false)
private String value;
@JoinTable(name = "pp_table_to_ww_table",
          joinColumns = {@JoinColumn(name = "pp_id", referencedColumnName = "id")},
          inverseJoinColumns = {@JoinColumn(name = "ww_id", referencedColumnName = "id")})
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<WW> wwList = new ArrayList<>();
...
//Getters + Setters//
}


Ok, so did you try 

OrderBy<PsViewAggregate> orderby = new OrderBy<>("pp.value")

?

/Daryl

Gyuro

unread,
Jul 11, 2014, 4:10:09 AM7/11/14
to eb...@googlegroups.com
 Ok, so did you try 

OrderBy<PsViewAggregate> orderby = new OrderBy<>("pp.value")

?

Yes, I tried it, but it still does not work, so I've tried another thing too.
Actually, I have renamed "pp_table" at the database to pp (+ I have the orderBy above) and now the generated query looks like this: 

select distinct t0.id c0, t0.amount c1 from ps_view t0 join pp_table u1 on u1.id = t0.id  join pp_table_to_ww_table u2z_ on u2z_.pp_id = u1.id  join ww_table u2 on u2.id = u2z_.ww_id  where (1 = 1 andu2.id = ? )  order by ${pp}value limit 40;

Thank You for your help again!

Gyuro

unread,
Jul 15, 2014, 11:01:22 AM7/15/14
to eb...@googlegroups.com
Hi,

After some debugging, I have located the source of my problem, but I have no clue how to solve it.

Actually the SqlTreeAlias.aliasMap is empty and the SqlTreeAlias.manyWhereAliasMap contains the '"product" -> u1' alias.
When the CQueryPredicates.parseTableAlias method parses the Where clause, it uses the SqlTreeAlias.parseWhere method, and the alias is found, so it is ok.
But when CQueryPredicates.parseTableAlias method parses the OrderBy clause, it uses the SqlTreeAlias.parse method, which not looks for aliases at the SqlTreeAlias.manyWhereAliasMap (it only parses SqlTreeAlias.aliasMap), so it does not find the correct u1 alias.

I'm not sure if it's a bug or nor, but I don't see why the parser does not looks for aliases at the manyWhereAliasMap while triing to find aliases for the orderBy clause.
Does anyone have some idea why is this happening and how can this be solved?

Thank You for your help!

Rob Bygrave

unread,
Jul 15, 2014, 11:32:05 PM7/15/14
to ebean@googlegroups
Daryl's suggestion of using "pp.value" would have worked with the model at that time. You need to post your query and your model again.  Previously you were incorrectly trying to use the table name rather than the logical property name and it is not clear you got that part correct (so you need to show your query and model again).

>> Actually the SqlTreeAlias.aliasMap is empty ... so it does not find the correct u1 alias.

No that is all correct.  The u1 alias is NOT used for the order by properties but only used for 'where predicates on *toMany properties.  So nice work but incorrect guessing there.


>> why the parser does not looks for aliases at the manyWhereAliasMap while triing to find aliases for the orderBy clause.

Because the predicate joins for *toMany properties are not the same joins as those used for fetching or order by clauses (hence the u1 alias is not to be used for the order by clause).



Cheers, Rob.


--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Gyuro

unread,
Jul 16, 2014, 7:49:59 AM7/16/14
to eb...@googlegroups.com
Thank You for your help Rob!

These are the entities:
@Entity
@Table(name = "ps_view")
public class PsViewAggregate {

@OneToOne
@JoinColumn(name = "id", referencedColumnName = "id")
private PP pp;

@Basic(optional = false)
    @Column(name = "amount")
    private Integer amount;
...
//Getters + Setters//
}

@Entity
@Table(name = "ww")
public class WW {

@Id
@Column(name = "id")
private UUID id;

    @Basic(optional = false)
    @Column(name = "name", unique = true)
    private String name;
...
//Getters + Setters//
}

@Entity
@Table(name = "pp")
public class PP {

@Id
@Column(name = "id")
private UUID id;

@Basic(optional = false)
@Column(name = "name", length = 100, nullable = true)
private String name;

    @Column(name = "points_01")
    private Integer points01;

    @Column(name = "points_02")
    private Integer points02;

@Basic(optional = false)
@Column(name = "value", length = 100, nullable = false)
private String value;

@JoinTable(name = "pp_to_ww",
          joinColumns = {@JoinColumn(name = "pp_id", referencedColumnName = "id")},
          inverseJoinColumns = {@JoinColumn(name = "ww_id", referencedColumnName = "id")})
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<WW> wwList;
...
//Getters + Setters//
}

The creator script of ps_view:
CREATE VIEW ps_view AS
SELECT p.id AS id, (p.points_01 + p.points_02) AS amount, FROM pp p GROUP BY p.value;

The query has been generated like this:
PP pp = ...;
WW ww = ...;
...
String columns = "pp, pp.value, pp.name, amount";
...
Expression where = Expr.and(
Expr.raw("1 = 1"),
Expr.eq("pp.wwList", ww)
);
...
OrderBy<PsViewAggregate> orderBy = new OrderBy<>("pp.value");
...
Query<PsViewAggregate> query = Ebean.find(PsViewAggregate.class);
query.select(columns);
query.where(where);
query.setOrderBy(orderBy);

This sql has been generated from the query:
select distinct t0.id c0, t0.amount c1 from ps_view t0 join pp u1 on u1.id = t0.id  join pp_to_ww u2z_ on u2z_.pp_id = u1.id  join ww u2 on u2.id = u2z_.ww_id  where (1 = 1 and u2.id = ? )  order by ${pp}value limit 40; --bind(12345678-1234-1234-1234-12345678)


The strange thing is that if the WHERE expression looks like this:
Expression where = Expr.and(
Expr.raw("1 = 1"),
Expr.ilike("pp.name", '%42%')
);

Then the correct sql has been generated:
select t0.id c0, t0.amount c1 from ps_view t0 left outer join pp t1 on t1.id = t0.id  where (1 = 1 and lower(t1.name) like ? )  order by t1.value
limit 40; --bind(%42%)

This time, SqlTreeAlias.manyWhereAliasMap is empty, and all of the aliases are stored at SqlTreeAlias.aliasMap.
It seems that my problem only occurs when I'm trying to use a @ManyToMany field at the WHERE expression.
But I was not able to figure out what's missing.

Thank You for the help!
Gyuro





Rob Bygrave

unread,
Jul 16, 2014, 8:03:22 AM7/16/14
to ebean@googlegroups

>> String columns = "pp, pp.value, pp.name, amount";

Note that you can't have pp.value or pp.name in here (in the select()) or more accurately those are being ignored, instead you should be doing a query.fetch("pp","value, name") instead.  That is why they are not included in your SQL select clause.


In terms of the orderby can you try removing: 

query.setOrderBy(orderBy);

... and replacing it with:

query.orderBy("pp.value");



Thanks, Rob.


Gyuro

unread,
Jul 16, 2014, 8:20:30 AM7/16/14
to eb...@googlegroups.com
I have tried to use "query.orderBy("pp.value");" as you suggested, but most unfortunately the same sql has been generated as before.

Thank you for your help!
Gyuro

Rob Bygrave

unread,
Jul 16, 2014, 8:54:30 AM7/16/14
to ebean@googlegroups
One thing that stands out is that the @OneToOne PsViewAggregate -> PP relationship is unidirectional  (no mappedBy attribute, no relationship from PP back to PsViewAggregate).

It is also looks odd/uncommon to me that the table name, class name and property name are all "pp" ... I haven't seen that before.

Seems like this is bug though.


Cheers, Rob.


--

Gyuro

unread,
Jul 17, 2014, 4:22:47 AM7/17/14
to eb...@googlegroups.com
Thank you for the help Rob!
I will try to find a workaround to solve the problem.

+ Thank you for creating EBean! It's a great thing. Keep up the good work!

Br,
Gyuro

Rob Bygrave

unread,
Jul 17, 2014, 8:39:32 AM7/17/14
to ebean@googlegroups
No problem. I'll look to see if I can produce a test case to reproduce the issue sometime this weekend.

Cheers, Rob.


--

Rob Bygrave

unread,
Jul 21, 2014, 5:50:44 AM7/21/14
to ebean@googlegroups

Gyuro

unread,
Jul 22, 2014, 8:42:04 AM7/22/14
to eb...@googlegroups.com
Thank you for the quick solution Rob!
You are a hero!
Reply all
Reply to author
Forward
0 new messages