You need to properly collocate your data in order to have correct join results when using partitioned caches (it does not matter whether you join tables within one partitioned cache or join tables across different partitioned caches). Please refer to documentation  and example . Note the usage of AffinityKey class for Person objects in the example.
An alternative for this approach is to use REPLICATED mode for one of the caches in the case if this cache contains relatively small data set (so called star-schema). You can refer to  for further details.
As far as I know, this limitation is planned to be removed in ignite-1.6, however such a distributed join will be significantly slower compared to collocated join. Stay tuned!
Collocation exactly means "to have all the joined entries on the same node". So basically you are right, for cross join it implies having all the data in replicated caches except one partitioned cache.
If you need an ability to run ad-hoc SQL, then you're right and you need to have one PARTITIONED cache and all others should be REPLICATED. However, if you know your SQL queries in advance, usually you can some up with a collocation strategy for multiple PARTITIONED caches.
I believe the community may suggest several options to you if you share your use-case.
I will explore the possibility of adapting the sql based applications to use the ignite as a database.
Therefore, I need to understand what sql can be used as is, and what the limitations and consequences and what you need to completely rewrite or replace with native api calls.
There is only one limitation in the current implementation: you have to make sure that joined entries are collocated and are stored on the same nodes. There are two ways to achieve this:
1. Using affinity . Take a look at query example , it joins Person and Organization types both stored in partitioned caches, but collocated with the help of AffinityKey class.
2. Moving one of the joined parties into a replicated cache. E.g., if you move Organization table in previous example to replicated cache, all organizations will be available on all nodes, therefore you don't have to bother about affinity for persons.
We also have plans to support ad-hoc queries without collocation. But this will always be slower due to potential data reshuffling, so should be used only if both options described above do not work.