Hi,
I see another wired thing with count query.
Query fired was - SELECT count(*) as COUNT FROM Person p join table(joinId
VARCHAR(50) = ?) i on p.id = i.joinId
inParameter.add("1");
inParameter.add("2");
inParameter.add("3");
inParameter.add("4");
inParameter.add("5");
inParameter.add("6");
inParameter.add("7");
inParameter.add("8");
PreparedStatement statement = conn.prepareStatement(sql);
statement.setObject(1, inParameter.toArray());
ResultSet rs = statement.executeQuery();
while (rs.next()) {
System.out.println("Count - "+ rs.getLong("COUNT"));
}
expected result - *Count - 8*
Actual results as - (in 3 node cluster)
*Count - 1*
*Count - 2*
*Count - 5*
it looks like count query is not aggregation of all counts of each node.
count of each node is returned.
Is this expected behavior ? Could you please point me to the documentation ?
Thanks
On 4 January 2017 at 17:21, Sergi Vladykin <[email protected]> wrote:
>
> I am not clear how joined rows produce cartesian product.
>>
>
> I guess you have to learn SQL then:
>
> https://en.wikipedia.org/wiki/Join_(SQL)
> https://en.wikipedia.org/wiki/Cartesian_product
>
>
>>
>> Can you please correct the query ? thanks.
>>
>>
> Just use NOT IN, <> will not use index anyways.
>
> Sergi
>
>
>
>> On 4 January 2017 at 17:06, Sergi Vladykin <[email protected]>
>> wrote:
>>
>>> Your query is wrong:
>>>
>>> Joined rows will produce a cartesian product of all the row pairs, thus
>>> you will have a row Person[100, 'Name100'] that correctly passed the first
>>> join and paired with n['Name200'] in the second join. Since 'Name100' <>
>>> 'Name200', it will be correctly returned in the result set.
>>>
>>> Sergi
>>>
>>> 2017-01-04 14:06 GMT+03:00 Anil <[email protected]>:
>>>
>>>> HI,
>>>>
>>>> As per ignite documentation, IN operator does not use index and has to
>>>> be use as *join table.*
>>>>
>>>> Hoping this holds good for* NOT IN *operator as well.
>>>>
>>>> String sql = "SELECT p.* from PERSON_CACHE.PERSON p join table(joinId
>>>> varchar(50) = ?) i on i.joinId = p.personId join table(joinId varchar(50) =
>>>> ?) n on n.joinId <> p.name";
>>>> PreparedStatement statement = conn.prepareStatement(sql);
>>>> statement.setFetchSize(100);
>>>> statement.setObject(1, new String[]{"100", "200", "300"});
>>>> statement.setObject(2, new String[]{"Name100", "Name200"});
>>>>
>>>>
>>>> Expected results are Name300
>>>> Actual output :
>>>> Name100
>>>> Name200
>>>> Name300
>>>> Name300
>>>>
>>>> Did you see any issue with the query ? please help.
>>>>
>>>> thanks.
>>>>
>>>
>>>
>>
>
