Apache Ignite SQL query result difference

Question

I'm facing an issue with Apache Ignite SQL. Currently I'm using Ignite v2.6 but saw the same issue with previous version as well.

There is a cache defined like this:

    <bean class="org.apache.ignite.configuration.CacheConfiguration">
        <property name="name" value="Tasks"/>
        <property name="cacheMode" value="PARTITIONED" />
        <property name="backups" value="1" />
        <property name="storeKeepBinary" value="true" />
        <property name="atomicityMode" value="TRANSACTIONAL"/> 
        <property name="writeSynchronizationMode" value="FULL_SYNC"/>
        <property name="nodeFilter" ref="TaskNodeFilter" />

        <property name="queryEntities">
            <list>
                <bean class="org.apache.ignite.cache.QueryEntity">
                    <property name="keyType" value="java.util.UUID"/>
                    <property name="valueType" value="Tasks" />
                    <property name="fields">
                        <map>
                            <entry key="id" value="java.util.UUID"/>
                            <entry key="created" value="java.lang.Long"/>
                            <entry key="jobId" value="java.util.UUID"/>
                            <entry key="exception" value="java.lang.Object"/>
                            <entry key="lastAction" value="java.lang.Long"/>
                            <entry key="jobId" value="java.util.UUID"/>
                            <entry key="masterId" value="java.util.UUID"/>
                            <entry key="identifier" value="java.lang.String"/>
                            <entry key="status" value="java.lang.Integer"/>
                            <entry key="nodeId" value="java.util.UUID"/>
                            <entry key="status" value="java.lang.Integer"/>
                            <entry key="progressMax" value="java.lang.Long"/>
                            <entry key="progressAt" value="java.lang.Long"/>
                            <entry key="actionType" value="java.lang.String"/>
                        </map>
                    </property>

                    <property name="indexes">
                        <list>
                            <bean class="org.apache.ignite.cache.QueryIndex">
                                <constructor-arg value="jobId"/>
                            </bean>
                            <bean class="org.apache.ignite.cache.QueryIndex">
                                <constructor-arg value="status"/>
                            </bean>
                            <bean class="org.apache.ignite.cache.QueryIndex">
                                <constructor-arg value="actionType"/>
                            </bean>
                            <bean class="org.apache.ignite.cache.QueryIndex">
                                <constructor-arg value="created"/>
                            </bean>
                            <bean class="org.apache.ignite.cache.QueryIndex">
                                <constructor-arg value="masterId"/>
                            </bean>
                        </list>
                    </property>
                </bean>
            </list>
        </property>
    </bean>

The entry "created" is the timestamp from System.currentTimeMillis(), so in Java it's a long value. So I set this field in cache config to "Java.lang.Long".

Now when I search using SQL I get different results depending on my query but always with the same range:

select COUNT(id) as ANZAHL
FROM "Tasks".Tasks
where created BETWEEN 1543705200000 AND 1545433200000;

returns 0 (wrong!)

select COUNT(id) as ANZAHL
FROM "Tasks".Tasks
where CAST(created AS BIGINT) BETWEEN 1543705200000 AND 1545433200000;

returns 1142 (this is the expected value)

select COUNT(*) as ANZAHL
FROM "Tasks".Tasks
where CAST(created AS BIGINT) BETWEEN 1543705200000 AND 1545433200000;

returns 0 (wrong!)

Am I'm doing something wrong? Why do I need to cast the value to BIGINT if its long value should be enough? Also, why is there a difference between COUNT(id) and COUNT(*)?

Thanks!

I don't see how COUNT(*) could return a smaller value than COUNT(id) if that is the only difference in the query and the underlying data does not change. — Gordon Linoff
Yes, that's the one issue I have. The second is that the query seems to require the CAST to BIGINT if you compare first and second SQL. Btw, I had a typo in the first SQL statement, I just corrected it. — DonTequila
Just to add, the data is not changing between my tests. The results are consistently repeatable with the above statements. — DonTequila

Tukaram Bhosale Tukaram Bhosale · Accepted Answer · 2019-05-06T16:09:35

I also observed count issue but this has nothing to do with casting and it orks by fixing query like below

select COUNT(id) as ANZAHL
FROM "Tasks".Tasks
where created <= 1543705200000 AND created >= 1545433200000;

You dont need to cast the long to BIGINTand can fire above query without casting. Why BETWEEN is not working then?
I tried to get query plan for similar query(mentioned by you) and can see below things in that--
WHERE (__Z0.CREATED>= 1543705200000 ) AND (__Z0.CREATED<= 1545433200000)
I dont thinks that is what you want, its totally reversing the expected condition.

Apache Ignite SQL query result difference

1 Answers