[jira] [Commented] (IGNITE-5245) MIN function returns NULL if a numeric column of class has NULL values

[Sergey Kalashnikov (JIRA)]

    [ 
https://issues.apache.org/jira/browse/IGNITE-5245?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16021178#comment-16021178
 ] 

Sergey Kalashnikov commented on IGNITE-5245:
--------------------------------------------

I have checked this and found out that Ignite behaviour is actually the same as 
H2.
In case there is only {{null}} values in the field, the result for {{select 
min(value) from test}} is {{null}}.
In case there are some meaningful values along with {{null}} values, the result 
for  {{select min(value) from test}} is minimum value among non-null values.

Below are my tests for both Ignite and H2:

{code:java}
package org.apache.ignite.internal.processors.query.h2;

import java.util.List;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.cache.query.QueryCursor;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.cache.query.annotations.QuerySqlField;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.configuration.IgniteConfiguration;
import org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi;
import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder;
import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest;

/** Test for https://issues.apache.org/jira/browse/IGNITE-5245 */
public class Issue_5245_Test extends GridCommonAbstractTest {
    /** IP finder. */
    private static final TcpDiscoveryVmIpFinder IP_FINDER = new 
TcpDiscoveryVmIpFinder(true);

    /** {@inheritDoc} */
    @Override protected void beforeTest() throws Exception {
        super.beforeTest();

        startGrids(4);
    }

    /** {@inheritDoc} */
    @Override protected void afterTest() throws Exception {
        super.afterTest();

        stopAllGrids();
    }

    /** {@inheritDoc} */
    @Override protected IgniteConfiguration getConfiguration(String gridName) 
throws Exception {
        IgniteConfiguration cfg = super.getConfiguration(gridName);

        TcpDiscoverySpi spi = (TcpDiscoverySpi)cfg.getDiscoverySpi();

        spi.setIpFinder(IP_FINDER);

        CacheConfiguration<?, ?> ccfg = new 
CacheConfiguration<>(DEFAULT_CACHE_NAME);

        ccfg.setIndexedTypes(Integer.class, ValueObj.class);

        cfg.setCacheConfiguration(ccfg);

        if ("client".equals(gridName))
            cfg.setClientMode(true);

        return cfg;
    }

    /** Check min() over null */
    public void testQueryMinMaxNull() throws Exception {
        try (Ignite client = startGrid("client")) {
            IgniteCache<Integer, ValueObj> cache = 
client.cache(DEFAULT_CACHE_NAME);

            cache.put(1, new ValueObj(null));

            SqlFieldsQuery qry = new SqlFieldsQuery("select min(value) from 
ValueObj");

            QueryCursor<List<?>> cursor = cache.query(qry);

            List<List<?>> result = cursor.getAll();

            assertNull(result.get(0).get(0));

            final Short testVal = 1;

            cache.put(2, new ValueObj(testVal));

            cursor = cache.query(qry);

            result = cursor.getAll();

            assertEquals(testVal, result.get(0).get(0));
        }
    }

    /** Value object for test cache */
    public class ValueObj {
        /** */
        @QuerySqlField
        private Short value;

        /** */
        public ValueObj(Short value) {
            this.value = value;
        }

        /** {@inheritDoc} */
        @Override public int hashCode() {
            return value;
        }

        /** {@inheritDoc} */
        @Override public boolean equals(Object o) {
            if (this == o)
                return true;

            if (!(o instanceof ValueObj))
                return false;

            ValueObj other = (ValueObj)o;

            return value == other.value;
        }
    }
}
{code}

H2 Console test:
{code}
drop table test if exists;
create table test(id smallint);
insert into test values (null);
select min(id) from test;
insert into test values (1);
select min(id) from test;
{code}

H2 Console results
{noformat}
MIN(ID)  
null
(1 row, 1 ms)

MIN(ID)  
1
(1 row, 0 ms)
{noformat}


> MIN function returns NULL if a numeric column of class has NULL values
> ----------------------------------------------------------------------
>
>                 Key: IGNITE-5245
>                 URL: https://issues.apache.org/jira/browse/IGNITE-5245
>             Project: Ignite
>          Issue Type: Bug
>          Components: sql
>    Affects Versions: 2.0
>            Reporter: Sergey Kozlov
>            Assignee: Sergey Kalashnikov
>             Fix For: 2.1
>
>
> 1. Use class ({{DefaultTable}}) with the numeric field (e.g. {{Short 
> col_Short_1}})
> 2. Put a few entries where one entry has the field is null
> 3. Run query {{SELECT MIN(col_Short_1) FROM DefaultTable}}
> 4. It returns NULL but for same case h2 returns non-null minimal value



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)