Message-Id: <9307080900.AA01662@postgres.Berkeley.EDU>
From: Bart Maessen <Bart.Maessen@fel.tno.nl>
Subject: sorting on attributes of user defined types
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
To: postgres@postgres.berkeley.edu
Date: Thu, 8 Jul 93 11:04:57 MET DST
Cc: bart.maessen@tnofel.fel.tno.nl

Dear Sirs,
 
Sorting a table on an attribute of a user defined type is not
always done correctly.
 
Suppose we have the following table:
  lll (id=int4,transmittal=int4,kind=rec_kind4,facc=text,facc_descr=text,qid=tex
t,rest=text)
  rec_kind4 is the user defined type.
 
The following query will result in a correctly sorted table:
  retrieve (a.all) from a in lll sort by id,transmittal,qid,facc
 
However the following query will result in an incorrectly sorted
table:
  retrieve (a.all) from a in lll sort by id,transmittal,facc,qid
The table is not correctly sorted for the qid attribute.
 
 
regards,
 
Bart Maessen
e-mail: bart.maessen@fel.tno.nl
 
The following section describes how to repeat the bug.
 
The following lines are C code for the new defined type "rec_kind4".
 
C code: typedef enum {line_fea4,point_fea4,area_fea4}
C code: rec_kind4;
C code:
C code: long *rec_kind4_in(str)
C code: char *str;
C code: {
C code:   char *tmp_str;
C code:   long *result;
C code:
C code:   tmp_str = strip(str);
C code:   result = (long *) palloc (sizeof(long));
C code:   if (0 == strcmp(tmp_str,"line_fea"))
C code:   {
C code:     *result =  line_fea4;
C code:   }
C code:   else if (0 == strcmp(tmp_str,"point_fea"))
C code:   {
C code:     *result =  point_fea4;
C code:   }
C code:   else if (0 == strcmp(tmp_str,"area_fea"))
C code:   {
C code:     *result =  area_fea4;
C code:   }
C code:   else
C code:   {
C code:     elog(WARN,"unknown rec_kind4");
C code:   };
C code:   pfree(tmp_str);
C code:   return result;
C code: };
C code:
C code: char *rec_kind4_out(mdk)
C code: long *mdk;
C code: {
C code:   char *out_str;
C code:
C code:   out_str = (char *) palloc (100);
C code:   *out_str = '\0';
C code:   switch (*mdk)
C code:   {
C code:     case line_fea4:
C code:       strcpy(out_str, "line_fea");
C code:       break;
C code:     case point_fea4:
C code:       strcpy(out_str, "point_fea");
C code:       break;
C code:     case area_fea4:
C code:       strcpy(out_str, "area_fea");
C code:       break;
C code:     default:
C code:       elog(WARN,"unknown rec_kind4");
C code:       break;
C code:   };
C code:   return out_str;
C code: };
C code:
C code: long Equal2rk4rk4(mdk1,mdk2)
C code: long *mdk1,*mdk2;
C code: {
C code:   return (*mdk1 == *mdk2);
C code: };
C code:
C code: long NotEqual2rk4rk4(mdk1,mdk2)
C code: long *mdk1,*mdk2;
C code: {
C code:   return (!(*mdk1 == *mdk2));
C code: };
C code:
C code: long Small2rk4rk4 (mdk1,mdk2)
C code: long *mdk1,*mdk2;
C code: {
C code:   return (!(*mdk1 < *mdk2));
C code: };
 
compile this code into pg_enumeration_types.o
 
The following script loads the new type into the database:
 
script: #! /bin/sh
script:
script: # this file is responsible for the loading of the enumeration
script: # types
script:
script: #DB indicates the database where rec_kind4 is to be defined
script: DB=${1:-geo}
script: #DIGESTHOME defines the directory where pg_enumeration_types.o is locate
d
script: DIGESTHOME=${2:-/tmp}
script:
script: set -v
script:
script: # definition of enumeration_kind rec_kind4
script: monitor -c "define function rec_kind4_in (language = \"c\",returntype=re
c_kind4)
script:   arg is (char16)
script:   as \"$DIGESTHOME/pg_enumeration_types.o\"" $DB
script:
script: monitor -c "define function rec_kind4_out (language = \"c\",returntype=c
har16)
script:   arg is (rec_kind4)
script:   as \"$DIGESTHOME/pg_enumeration_types.o\"" $DB
script:
script: monitor -c "define type rec_kind4 (internallength = 4,
script:   input = rec_kind4_in,output=rec_kind4_out)" $DB
script:
script: monitor -c "define function Equal2rk4rk4 ( language = \"c\",
script:   returntype = bool)
script:   arg is (rec_kind4,rec_kind4)
script:   as \"$DIGESTHOME/pg_enumeration_types.o\"" $DB
script:
script: monitor -c "define function NotEqual2rk4rk4 ( language = \"c\",
script:   returntype = bool)
script:   arg is (rec_kind4,rec_kind4)
script:   as \"$DIGESTHOME/pg_enumeration_types.o\"" $DB
script:
script: monitor -c "define function Small2rk4rk4 ( language = \"c\",
script:   returntype = bool)
script:   arg is (rec_kind4,rec_kind4)
script:   as \"$DIGESTHOME/pg_enumeration_types.o\"" $DB
script:
script: monitor -c "define operator = (arg1 = rec_kind4,arg2 = rec_kind4,
script:   procedure = Equal2rk4rk4)" $DB
script:
script: monitor -c "define operator != (arg1 = rec_kind4,arg2 = rec_kind4,
script:   procedure = NotEqual2rk4rk4)" $DB
script:
script: monitor -c "define operator < (arg1 = rec_kind4,arg2 = rec_kind4,
script:   procedure = Small2rk4rk4)" $DB
 
Run the previous script with correctly defined DB and DIGESTHOME.
 
Use the monitor to create the previous mentioned table lll.
Copy the following contents into lll:
1	1	point_fea	AL020	kdkjsk	LEN	kdflksjkkls
1	1	point_fea	AL020	jjklkj	WID	kkjljkldjlf
1	1	point_fea	AL020	jkjljk	HGT	kljlkjkljlk
1	1	point_fea	AL020	jkjj	DMR	kjkk
B
1	1	point_fea	AL020	kkjlk	DMS	kjkll
1	1	area_fea	AL020	kkjlk	DMS	kjkll
1	1	line_fea	AL020	kkjlk	DMS	kjkll
 
Executing the two previous mentioned queries will show (not) correctly
ordered tables.