Why SELECT DISTINCT returns twice the same value?

Question

I have a table in Oracle, which has DATE column. I can observe that SELECT DISTINCT returns duplicates:

select distinct myDate from myTable;

myDate
----------------------------
2009-09-05 00:00:00          
2009-09-05 00:00:00

But:

select distinct to_char(myDate, 'DD-MM-YYYY HH24:MI:SS') from myTable;

TO_CHAR(myDate,'DD-MM-YYYYHH24:MI:SS')
------------------------------------------------
05-09-2009 00:00:00

And next queries, which can tell more:

select count(*) from myTable;

  COUNT(*)
----------
        12 

select myDate, count(*) from myTable group by myDate;

myDate                        COUNT(*)
---------------------------- ----------
2009-09-05 00:00:00                   6 
2009-09-05 00:00:00                   6 


select count(*) from myTable where myDate='2009-09-05';

  COUNT(*)
----------
         6

What can be the reason of such behaviour? As far as I know there is no more information stored in DATE column which can make the difference (no time zone, no miliseconds).

EDIT: table DDL

Unfortunately I can't post whole DDL. Definition of the column is:

myDate DATE NOT NULL

What can be important, table is partitioned using this column:

PARTITION BY RANGE (myDate)
(
PARTITION P_19991231 VALUES LESS THAN 
    (TO_DATE('1999-12-31 00:00:00',
           'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

There is more partitions in the table.

And also ENABLE ROW MOVEMENT is ON.

Why don't you change the NLS format for SQL*Plus so it displays the date ***and*** the time? — , Jun 05 '13 at 13:08
@a_horse_with_no_name I changed it and edited my question. But it shows nothing (at least for me). — psur, Jun 05 '13 at 13:14
Maybe a long-shot, but might you have BCE dates, incorrectly entered (presumably) as -2009? You can see those by using `SYYYY` instead of `YYYY`. If you use `YYYY`, explicitly or in your NLS settings, the two would look the same when displayed. — Alex Poole, Jun 05 '13 at 13:21
@AlexPoole Thanks, I've checked it, but it's not the case... — psur, Jun 05 '13 at 13:24
That's a shame, I had a [SQL Fiddle](http://www.sqlfiddle.com/#!4/8b9cd/4) to demonstrate *8-) Can you `dump()` the date values to see what they show - only other thing I can think of is some kind of internal corruption. — Alex Poole, Jun 05 '13 at 13:28
@AlexPoole Thank you, I didn't know `dump()` function. I used it and it showed the same for all records: `Typ=12 Len=7: 120,109,9,5,1,1,1`. So it has to be some internal error and I can reproduce it. I think that it can be connected with partitioning (this column is partitioning key). — psur, Jun 06 '13 at 06:48
Is it showing that for 12 records or for 6? How many records do you think there really are? Do you have a partition around that date? Not that it really matters, if that is the only value it's showing then you probably need to raise a service request with Oracle to see if this is a bug in your particular version. — Alex Poole, Jun 06 '13 at 07:59
@AlexPoole The problem was in partitions. I made an exchange and did mistake in it. I will add answer with details in a while. — psur, Jun 06 '13 at 08:14

score 0 · Answer 1 · answered Jun 05 '13 at 12:48

0

A precision problem: after the seconds, you have milliseconds. You have in your table 2 rows with dates in the same second, but not with the same milliseconds. When you execute a SELECT DISTINCT against the dates, they are distinct, so you have 2 rows. But using the to_char function, you round the dates to the seconds and you have only one row returned.

To group by dates, you must be sure that the date is exactly the same.

answered Jun 05 '13 at 12:48

eternay

3,754
2
29
27

1

Are you sure that `DATE` type has miliseconds? As far as I know it end on the seconds: http://stackoverflow.com/questions/9180014/using-oracle-to-date-function-for-date-string-with-milliseconds – psur Jun 05 '13 at 12:53
Try to execute this: `select distinct to_char(myDate, 'DD-MM-YYYY HH24:MI:SS.FF3') from myTable;`. FF indicates the fractions of seconds. – eternay Jun 05 '13 at 12:58
2

@eternay: the `FFx` is only valid for `TIMESTAMP` columns. A `DATE` does not have milliseconds, only seconds. – Jun 05 '13 at 13:07
@a_horse_with_no_name, you are right. I was thinking with timestamp columns. – eternay Jun 05 '13 at 13:10

score 0 · Accepted Answer · answered Jun 06 '13 at 08:20

0

Problem was in partitioning. I made few EXCHANGE PARTITIONS operations without validation and as a result few records was placed on incorrect partition (not in its range). In such situation comparisons and SELECT DISTINCT didn't work correct.

answered Jun 06 '13 at 08:20

psur

4,400
26
36

Why SELECT DISTINCT returns twice the same value?

2 Answers2