Query with select count(*) and group by returns incorrect results (Doc ID 456329.1)

Last updated on FEBRUARY 27, 2016

Applies to:

Oracle TimesTen In-Memory Database - Version 7.0.1.0.0 and later
Information in this document applies to any platform.
Add ***Checked for relevance on 25-Feb-2013***

***Checked for relevance on 25-sep-2014 ***

Symptoms

Query with select count(distinct col) from table returns incorrect and inconsistent results when AI/CI NLS_SORT is being used. For example,

Command> create table teq (type varchar2 (10), src varchar2 (10), ptn varchar2 (10));
Command> insert into teq values ('binary_ci', 'b', 'B');
1 row inserted.
Command> insert into teq values ('binary_ci', 'Bd5eFb', 'bD5EfB');
1 row inserted.
Command> select DISTINCT value from (select type, src as value from teq union all select type, ptn as value from teq) where type='binary_ci';
< b >
< Bd5eFb >
< B >
< bD5EfB >
4 rows found.
Command> alter session set nls_sort=binary_ci;
Command> select DISTINCT value from (select type, src as value from teq union all select type, ptn as value from teq) where type='binary_ci';
< b >
< Bd5eFb >
2 rows found.
Command> select count(DISTINCT value) from (select type, src as value from teq union all select type, ptn as value from teq) where type='binary_ci';
< 4 >                               <----------- Wrong value here
1 row found.



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms