Re: Weird GROUP BY/TO_CHAR(date) Interaction?

From: L. Carl Pedersen <carl.pedersen_at_dartmouth.edu>
Date: 14 Sep 92 15:30:52 GMT
Message-ID: <carl.pedersen-140992111744_at_kip-sn-49.dartmouth.edu>


I have further isolated the problem described in my previous posting on this subject. It appears to require a view, hence I suspect a kernel problem. I feel hesitant to call it a bug, since I'm not sure what it should be doing. There is no need to refer to my previous message. Here's what you need to reproduce the problem:

$ sqlplus /

SQL*Plus: Version 3.0.11.5.1 - Production on Mon Sep 14 10:09:11 1992

Copyright (c) Oracle Corporation 1979, 1992. All rights reserved.

Connected to:
ORACLE RDBMS V6.0.35.1.0 (6.2), transaction processing option - Production PL/SQL V1.0.34.2.0 - Production

SQL> create table bug1(d char(4));
Table created.
SQL> insert into bug1 values('9203');
1 row created.
SQL> insert into bug1 values('9204');
1 row created.
SQL> insert into bug1 values('9205');
1 row created.
SQL> insert into bug1 values('9205');
1 row created.
SQL> create view bug1v as select to_date(d,'YYMM') d from bug1; View created.

SQL> select d from bug1v;
D


01-MAR-92
01-APR-92
01-MAY-92
01-MAY-92

4 rows selected.

SQL> select to_char(d) from bug1v;
TO_CHAR(D)


01-MAR-92
01-APR-92
01-MAY-92
01-MAY-92

4 rows selected.

SQL> select d from bug1v group by d;
D


01-MAR-92
01-APR-92
01-MAY-92

3 rows selected.

SQL> select to_char(d) from bug1v group by to_char(d);

TO_CHAR(D)


01-APR-92
01-MAR-92
01-MAY-92

3 rows selected.

SQL> select to_char(d) from bug1v group by d; TO_CHAR(D)


01-MAY-92
01-MAY-92
01-MAY-92

3 rows selected.

SQL> The results of the last select are baffling to me. I realize there is no reasons to leave out the to_char in the group by, but why does it matter? It does what I expect if I replace bug1v with a table containing the date values. We're on VMS 5.3. Received on Mon Sep 14 1992 - 17:30:52 CEST

Original text of this message