Re: Weird GROUP BY/TO_CHAR(date) Interaction?
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