You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Brian Topping (JIRA)" <ji...@apache.org> on 2012/09/26 20:59:08 UTC

[jira] [Created] (OPENJPA-2268) SQL Generation with query using IN element

Brian Topping created OPENJPA-2268:
--------------------------------------

             Summary: SQL Generation with query using IN element
                 Key: OPENJPA-2268
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2268
             Project: OpenJPA
          Issue Type: Bug
          Components: jdbc, kernel, sql
    Affects Versions: 2.2.0
            Reporter: Brian Topping


Hi all, I've been staring at a problem for the last 12 hours with a JPQL query that appears to be parsing incorrectly.  There are three tables, a permissions table with a n-n relation to a role table, in turn with an n-n relation to a users table.  The intent of the query is to get all permissions for a user by username: {{select p from PersistentPermission p, IN(p.roles) r, IN(r.users) u where u.username = :name}}.  

No errors are generated, but the SQL emitted to the log has not been parsed or transformed at all.  (This may be correct, I've been fortunate enough so far not to have to look at the SQL generated by OpenJPA).  

Debugging into OpenJPA, it appears that the generated query is "select permission p where username='?'", which obviously will not return anything.  

I'm still debugging this (having gotten down to the level of the jflex code), but it's my first time going deep on OpenJPA like this so things are going slow.  I'll post updates here, but if anyone sees this and has ideas of where to look first (no matter how small), it may be a great help.

Thank you1

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] [Updated] (OPENJPA-2268) SQL Generation with query using IN element

Posted by "Brian Topping (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/OPENJPA-2268?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Brian Topping updated OPENJPA-2268:
-----------------------------------

    Description: 
Hi all, I've been staring at a problem for the last 12 hours with a JPQL query that appears to be parsing incorrectly.  There are three tables, a permissions table with a n-n relation to a role table, in turn with an n-n relation to a users table.  These n-n relations are created with default options of the @ManyToMany annotation and I have added them below for completeness.The intent of the query is to get all permissions for a user by username: {{select p from Permission p, IN(p.roles) r, IN(r.users) u where u.username = :name}}.  

No errors are generated, but the SQL emitted to the log has not been parsed or transformed at all.  (This may be correct, I've been fortunate enough so far not to have to look at the SQL generated by OpenJPA).  

Debugging into OpenJPA, it appears that the generated query is "select permission p where username='?'", which obviously will not return anything.  

I'm still debugging this (having gotten down to the level of the jflex code), but it's my first time going deep on OpenJPA like this so things are going slow.  I'll post updates here, but if anyone sees this and has ideas of where to look first (no matter how small), it may be a great help.

Thank you1

{code name=Permission.java}
@Entity
public class Permission {
...
    @ManyToMany(mappedBy="permissions",targetEntity=Role.class)
    private Set<Role> roles;
}
{code}
{code name=Role.java}
@Entity
public class Role {
...
    @ManyToMany(mappedBy="users",targetEntity=User.class)
    private Set<User> users;
}
{code}
{code name=User.java}
@Entity
public class User {
...
    @Column
    private String username;
}
{code}

  was:
Hi all, I've been staring at a problem for the last 12 hours with a JPQL query that appears to be parsing incorrectly.  There are three tables, a permissions table with a n-n relation to a role table, in turn with an n-n relation to a users table.  The intent of the query is to get all permissions for a user by username: {{select p from PersistentPermission p, IN(p.roles) r, IN(r.users) u where u.username = :name}}.  

No errors are generated, but the SQL emitted to the log has not been parsed or transformed at all.  (This may be correct, I've been fortunate enough so far not to have to look at the SQL generated by OpenJPA).  

Debugging into OpenJPA, it appears that the generated query is "select permission p where username='?'", which obviously will not return anything.  

I'm still debugging this (having gotten down to the level of the jflex code), but it's my first time going deep on OpenJPA like this so things are going slow.  I'll post updates here, but if anyone sees this and has ideas of where to look first (no matter how small), it may be a great help.

Thank you1

    
> SQL Generation with query using IN element
> ------------------------------------------
>
>                 Key: OPENJPA-2268
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2268
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, kernel, sql
>    Affects Versions: 2.2.0
>            Reporter: Brian Topping
>
> Hi all, I've been staring at a problem for the last 12 hours with a JPQL query that appears to be parsing incorrectly.  There are three tables, a permissions table with a n-n relation to a role table, in turn with an n-n relation to a users table.  These n-n relations are created with default options of the @ManyToMany annotation and I have added them below for completeness.The intent of the query is to get all permissions for a user by username: {{select p from Permission p, IN(p.roles) r, IN(r.users) u where u.username = :name}}.  
> No errors are generated, but the SQL emitted to the log has not been parsed or transformed at all.  (This may be correct, I've been fortunate enough so far not to have to look at the SQL generated by OpenJPA).  
> Debugging into OpenJPA, it appears that the generated query is "select permission p where username='?'", which obviously will not return anything.  
> I'm still debugging this (having gotten down to the level of the jflex code), but it's my first time going deep on OpenJPA like this so things are going slow.  I'll post updates here, but if anyone sees this and has ideas of where to look first (no matter how small), it may be a great help.
> Thank you1
> {code name=Permission.java}
> @Entity
> public class Permission {
> ...
>     @ManyToMany(mappedBy="permissions",targetEntity=Role.class)
>     private Set<Role> roles;
> }
> {code}
> {code name=Role.java}
> @Entity
> public class Role {
> ...
>     @ManyToMany(mappedBy="users",targetEntity=User.class)
>     private Set<User> users;
> }
> {code}
> {code name=User.java}
> @Entity
> public class User {
> ...
>     @Column
>     private String username;
> }
> {code}

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] [Closed] (OPENJPA-2268) SQL Generation with query using IN element

Posted by "Brian Topping (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/OPENJPA-2268?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Brian Topping closed OPENJPA-2268.
----------------------------------

    Resolution: Not A Problem

Meh, this was caused by bad mapping instructions by a colleague that never makes mistakes.  Apologies if anyone spent time reading this!
                
> SQL Generation with query using IN element
> ------------------------------------------
>
>                 Key: OPENJPA-2268
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2268
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, kernel, sql
>    Affects Versions: 2.2.0
>            Reporter: Brian Topping
>
> Hi all, I've been staring at a problem for the last 12 hours with a JPQL query that appears to be parsing incorrectly.  There are three tables, a permissions table with a n-n relation to a role table, in turn with an n-n relation to a users table.  These n-n relations are created with default options of the @ManyToMany annotation and I have added them below for completeness.The intent of the query is to get all permissions for a user by username: {{select p from Permission p, IN(p.roles) r, IN(r.users) u where u.username = :name}}.  
> No errors are generated, but the SQL emitted to the log has not been parsed or transformed at all.  (This may be correct, I've been fortunate enough so far not to have to look at the SQL generated by OpenJPA).  
> Debugging into OpenJPA, it appears that the generated query is "select permission p where username='?'", which obviously will not return anything.  
> I'm still debugging this (having gotten down to the level of the jflex code), but it's my first time going deep on OpenJPA like this so things are going slow.  I'll post updates here, but if anyone sees this and has ideas of where to look first (no matter how small), it may be a great help.
> Thank you1
> {code name=Permission.java}
> @Entity
> public class Permission {
> ...
>     @ManyToMany(mappedBy="permissions",targetEntity=Role.class)
>     private Set<Role> roles;
> }
> {code}
> {code name=Role.java}
> @Entity
> public class Role {
> ...
>     @ManyToMany(mappedBy="users",targetEntity=User.class)
>     private Set<User> users;
> }
> {code}
> {code name=User.java}
> @Entity
> public class User {
> ...
>     @Column
>     private String username;
> }
> {code}

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] [Commented] (OPENJPA-2268) SQL Generation with query using IN element

Posted by "Brian Topping (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/OPENJPA-2268?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13464173#comment-13464173 ] 

Brian Topping commented on OPENJPA-2268:
----------------------------------------

I updated the issue itself with more information about the domain classes involved and mentioned a debugging session but didn't have the exact generated query at hand or it's location.  It is at org.apache.openjpa.kernel.ExpressionStoreQuery.DataStoreExecutor#executeQuery (line 783 in the 2.2.0 source) and the StoreQuery._ctx_query is the same as the JPQL string ("select s from PersistentUser s where s.username = :name"), not what I have above.  Assuming this is correct, where does the actual SQL that would be sent to the database become available?
                
> SQL Generation with query using IN element
> ------------------------------------------
>
>                 Key: OPENJPA-2268
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2268
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, kernel, sql
>    Affects Versions: 2.2.0
>            Reporter: Brian Topping
>
> Hi all, I've been staring at a problem for the last 12 hours with a JPQL query that appears to be parsing incorrectly.  There are three tables, a permissions table with a n-n relation to a role table, in turn with an n-n relation to a users table.  These n-n relations are created with default options of the @ManyToMany annotation and I have added them below for completeness.The intent of the query is to get all permissions for a user by username: {{select p from Permission p, IN(p.roles) r, IN(r.users) u where u.username = :name}}.  
> No errors are generated, but the SQL emitted to the log has not been parsed or transformed at all.  (This may be correct, I've been fortunate enough so far not to have to look at the SQL generated by OpenJPA).  
> Debugging into OpenJPA, it appears that the generated query is "select permission p where username='?'", which obviously will not return anything.  
> I'm still debugging this (having gotten down to the level of the jflex code), but it's my first time going deep on OpenJPA like this so things are going slow.  I'll post updates here, but if anyone sees this and has ideas of where to look first (no matter how small), it may be a great help.
> Thank you1
> {code name=Permission.java}
> @Entity
> public class Permission {
> ...
>     @ManyToMany(mappedBy="permissions",targetEntity=Role.class)
>     private Set<Role> roles;
> }
> {code}
> {code name=Role.java}
> @Entity
> public class Role {
> ...
>     @ManyToMany(mappedBy="users",targetEntity=User.class)
>     private Set<User> users;
> }
> {code}
> {code name=User.java}
> @Entity
> public class User {
> ...
>     @Column
>     private String username;
> }
> {code}

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira