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 <[email protected]>: > 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/ >
