You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Skollur <Sk...@idbny.com> on 2018/12/12 22:37:10 UTC
Apache Ignite Join
I have single node with REPLICATE mode. Record counts in database and ignite
is matching when query the number of records in each table. However when it
comes to join, there is a mismatch in some cases. I am using JAVA code
version(Not Spring Version).
Example:
Table A has code as below :-
HashMap<String, String> aliases = new HashMap<>();
aliases.put("id", "ID");
aliases.put("accountType", "Account_Type");
aliases.put("subCategory", "Sub_Category");
qryEntity.setAliases(aliases);
ArrayList<QueryIndex> indexes = new ArrayList<>();
QueryIndex index = new QueryIndex();
index.setName("NonClustered_Index_LC");
index.setIndexType(QueryIndexType.SORTED);
LinkedHashMap<String, Boolean> indFlds = new LinkedHashMap<>();
indFlds.put("id", true);
indFlds.put("accountType", true);
indFlds.put("subCategory", true);
index.setFields(indFlds);
indexes.add(index);
qryEntity.setIndexes(indexes);
Table B has below :-
HashMap<String, String> aliases = new HashMap<>();
aliases.put("id", "ID");
aliases.put("accountType", "Account_Type");
aliases.put("subCategory", "Sub_Category");
qryEntity.setAliases(aliases);
ArrayList<QueryIndex> indexes = new ArrayList<>();
QueryIndex index = new QueryIndex();
index.setName("NonClustered_Index_LC");
index.setIndexType(QueryIndexType.SORTED);
LinkedHashMap<String, Boolean> indFlds = new LinkedHashMap<>();
indFlds.put("accountNumber", true);
indFlds.put("accountType", true);
indFlds.put("assetClassAllocation", true);
index.setFields(indFlds);
indexes.add(index);
qryEntity.setIndexes(indexes);
When below query is executed with JOIN executed count returns correct and
results in both SQL and IGNITE is correct i.e 20.
SELECT
count(*)
FROM
TABLEA WAS
INNER JOIN TABLEB CSS ON
WAS.accountType = CSS.accountType
----------------------------------------------------------
But when joined with one additional parameter as below, I get different
results different from database (i.e database has 127 and ignite shows 66).
SELECT
count(*)
FROM
TABLEA WAS
INNER JOIN TABLEB CSS ON
WAS.accountType = CSS.accountType
AND was.assetClassAllocation = css.subCategory.
Any suggestion? Note I have the data in index.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Apache Ignite Join
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I don't really understand why you add extra condition to join and get more
records, not fewer.
Can you analyze those 66 records, try to find something in common. Then do
the same for records that did not show up, try to understand how they are
different from the previous ones.
Regards,
--
Ilya Kasnacheev
чт, 13 дек. 2018 г. в 01:37, Skollur <Sk...@idbny.com>:
> I have single node with REPLICATE mode. Record counts in database and
> ignite
> is matching when query the number of records in each table. However when it
> comes to join, there is a mismatch in some cases. I am using JAVA code
> version(Not Spring Version).
>
> Example:
>
> Table A has code as below :-
> HashMap<String, String> aliases = new HashMap<>();
> aliases.put("id", "ID");
> aliases.put("accountType", "Account_Type");
> aliases.put("subCategory", "Sub_Category");
> qryEntity.setAliases(aliases);
> ArrayList<QueryIndex> indexes = new ArrayList<>();
> QueryIndex index = new QueryIndex();
> index.setName("NonClustered_Index_LC");
> index.setIndexType(QueryIndexType.SORTED);
> LinkedHashMap<String, Boolean> indFlds = new LinkedHashMap<>();
> indFlds.put("id", true);
> indFlds.put("accountType", true);
> indFlds.put("subCategory", true);
> index.setFields(indFlds);
> indexes.add(index);
> qryEntity.setIndexes(indexes);
>
> Table B has below :-
>
> HashMap<String, String> aliases = new HashMap<>();
> aliases.put("id", "ID");
> aliases.put("accountType", "Account_Type");
> aliases.put("subCategory", "Sub_Category");
> qryEntity.setAliases(aliases);
> ArrayList<QueryIndex> indexes = new ArrayList<>();
> QueryIndex index = new QueryIndex();
> index.setName("NonClustered_Index_LC");
> index.setIndexType(QueryIndexType.SORTED);
> LinkedHashMap<String, Boolean> indFlds = new LinkedHashMap<>();
> indFlds.put("accountNumber", true);
> indFlds.put("accountType", true);
> indFlds.put("assetClassAllocation", true);
> index.setFields(indFlds);
> indexes.add(index);
> qryEntity.setIndexes(indexes);
>
>
> When below query is executed with JOIN executed count returns correct and
> results in both SQL and IGNITE is correct i.e 20.
>
> SELECT
> count(*)
> FROM
> TABLEA WAS
> INNER JOIN TABLEB CSS ON
> WAS.accountType = CSS.accountType
> ----------------------------------------------------------
> But when joined with one additional parameter as below, I get different
> results different from database (i.e database has 127 and ignite shows 66).
>
> SELECT
> count(*)
> FROM
> TABLEA WAS
> INNER JOIN TABLEB CSS ON
> WAS.accountType = CSS.accountType
> AND was.assetClassAllocation = css.subCategory.
>
> Any suggestion? Note I have the data in index.
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>