I created a test case in the 'master' codebase and also in the 2.4.0.Final codebase, but I was not able to replicate your error with either of these tests.

Since you didn't provide your node types, I created the following node type definitions (based upon your query and your example data):

<jcr='http://www.jcp.org/jcr/1.0'>
<nt='http://www.jcp.org/jcr/nt/1.0'>
<mix='http://www.jcp.org/jcr/mix/1.0'>

[fincayra.Post] > mix:referenceable
- text (string)
- user (string)

[fincayra.User] > mix:referenceable
- name (string)

I registered these node types, and then used the following code to create and execute the query:

    String sql = "SELECT post.\"jcr:uuid\", post.\"text\", post.\"user\" FROM [fincayra.Post] AS post JOIN [fincayra.User] AS u ON post.\"user\"=u.\"jcr:uuid\"";
    Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2);
    assert query != null;
    QueryResult result = query.execute();

I didn't have any data in my repository, but the error you got sounds like it happens during planning, which is done during the "execute()" method.

One thing to check: make sure you're specifying the JCR_SQL2 language on the "createQuery" method. Using JCR_SQL will not work with this query.

Your query is also quoting identifiers in two different ways. Here's a slight alteration of your original query using JCR-SQL2's idiomatic square brackets and removing identifier quoting where not necessary:

  SELECT post.[jcr:uuid], post.text, post.user 
  FROM [fincayra.Post] AS post 
  JOIN [fincayra.User] AS u 
    ON post.user=u.[jcr:uuid]

Nevertheless, your query is actually not using the JOIN, since you're only selecting properties from the "post" table. The following query would be equivalent (assuming that the "user" property is a REFERENCE):

  SELECT post."jcr:uuid", post."text", post."user" 
  FROM [fincayra.Post] 
  WHERE post."user" IS NOT NULL

Finally, this is unrelated to your error. I noticed in your data that your node types are named "fincayra.User" and "fincayra.Post", with no namespace. The "fincayra" prefix is essentially acting as a namespace, but is not a real JCR namespace. There's technically nothing wrong with doing this, but idiomatic JCR would define a namespace with "fincayra" as the prefix.

I left out a pieace of the query.  What I'm trying to do is find all Posts by a specific user, I should have a where clause in the statement

 

SELECT post.[jcr:uuid], post.text, post.user FROM [fincayra.Post] AS post JOIN [fincayra.User] AS u ON post.user=u.[jcr:uuid] WHERE u.email='test1@innobuilt.com' 

 

That makes the join a lot more useful. But in my tests it still works. :-)

The project I'm working on is trying to stay away from having the developer configure xml files I have to register my definitions dynamically through the API.

That's not a problem, and actually that's why the NodeTypeManager and NamespaceRegistry interfaces are in the JCR API. You can very easily register a namespace using something like:

 javax.jcr.NamespaceRegistry registry = session.getWorkspace().getNamespaceRegistry();
 registry.registerNamespace("fincayra","http://example.com/whatever/url");

This will overwrite any existing registration, but there are methods to check existing registrations, too. BTW, using the NamespaceRegistry will make sure the namespaces are permanently registered, while using the Session-related "registerNamespace" methods will register the namespaces only locally within the session. Other than that, namespace registration is easy.

Then your node type names would be strings like "fincayra:Post" and "fincayra:User".

Could the problem be, that my type's are unstructured?

ModeShape's behavior used to restricte the column names to those explicitly defined on the node type, but that was fixed in 2.4.0.Final as part of MODE-1055. But I just tested with the following CND:

<jcr='http://www.jcp.org/jcr/1.0'>
<nt='http://www.jcp.org/jcr/nt/1.0'>
<mix='http://www.jcp.org/jcr/mix/1.0'>

[fincayra.Post] > nt:unstructured, mix:referenceable

[fincayra.User] > nt:unstructured, mix:referenceable

and I was able to replicate your problem.  Obviously we didn't fully remove that restriction. Would you mind logging an issue?

BTW, with JCR you do not have to choose between structured and unstructured, because it is really a spectrum. For example, you could define your node types to subtype "nt:unstructured" while still adding specific property definitions for the properties you know you will need and will want to have. For example, here is a CND showing this:

<jcr='http://www.jcp.org/jcr/1.0'>
<nt='http://www.jcp.org/jcr/nt/1.0'>
<mix='http://www.jcp.org/jcr/mix/1.0'>

[fincayra.Post] > nt:unstructured, mix:referenceable
- text (string)
- user (string)

[fincayra.User] > nt:unstructured mix:referenceable
- name (string)
- email (string)

With these node type definitions, the "fincayra.Post" node type definition does allow any property and any children (thanks to the "nt:unstructured" supertype), but any use of a "text" property on nodes using this node type would utilize the specific "text" property definition.

Here is the same example, but one that is more idiomatic JCR that uses a custom namespace:

<jcr='http://www.jcp.org/jcr/1.0'>
<nt='http://www.jcp.org/jcr/nt/1.0'>
<mix='http://www.jcp.org/jcr/mix/1.0'>
<fincayra='http://example.com/something'>

[fincayra:Post] > nt:unstructured, mix:referenceable
- fincayra:text (string)
- fincayra:user (string)

[fincayra:User] > nt:unstructured mix:referenceable
- fincayra:name (string)
- fincayra:email (string)

It's also possible to define use the 'nt:unstructured' node type for all your nodes, but then define the Post and User characteristics as mixins:

<jcr='http://www.jcp.org/jcr/1.0'>
<nt='http://www.jcp.org/jcr/nt/1.0'>
<mix='http://www.jcp.org/jcr/mix/1.0'>
<fincayra='http://example.com/something'>

[fincayra:Post] > mix:referenceable mixin
- fincayra:text (string)
- fincayra:user (string)

[fincayra:User] > mix:referenceable mixin
- fincayra:name (string)
- fincayra:email (string)

With these, the primary type of the node would be "nt:unstructured", and you'd add the "fincayra:Post" mixin type to any node you want to treat as a blog post. Mixins work great when you have sets of properties that apply to a "characteristic". For example, both posts and users might nave ratings, so you could define a "fincayra:rateable" mixin that has a "fincayra:stars" long property definition, constrained between 0 and 5 stars:

[fincayra:rateable] mixin
- fincayra:stars (long) < '[0,5]'

You could automatically add this mixin to all user and post nodes, or your application could know that users and posts are both rateable, and add the mixin only to those nodes that you want to set the number of stars. All of this works with queries how you might expect: the "fincayra:rateable" shows up as a table with a single "fincayra:stars" column, and any post that has a rating would show up as a row in this table, while any post node that did not have the mixin would not show up in this table.

You're certainly not doing it wrong now. I'm merely trying to show you some alternatives that may have some advantages in your case.

BTW, does having the properties defined up-front improve query performance?

No, not particularly. The only place they help is when accessing ModeShape through JDBC, because they show up as columns on the tables in the database metadata. Other than that, there isn't really much of a difference performance-wise.