outer join fails with error - solaris only - Mailing list pgsql-general

The following query gives an error when run on solaris 2.7, but it works
fine with an equivalent configuration on rh 6.2.

    select tr.transition_key,
           tr.transition_name
    from   wf_transition_info tr
             left outer join
           wf_context_assignments ca
             on (tr.context_key = ca.context_key and
                  tr.transition_key = ca.transition_key)
    where  tr.workflow_key = 'expenses_wf'
      and  tr.context_key = 'default'
      and  tr.trigger_type = 'user'
      and  tr.assignment_callback is null;

 .
 .
 .

DROP
CREATE
psql:tst.sql:210: ERROR:  RIGHT JOIN is only supported with mergejoinable join conditions
acspg=# select version();
                             version
-----------------------------------------------------------------
 PostgreSQL 7.1 on sparc-sun-solaris2.7, compiled by GCC 2.7.2.2
(1 row)

acspg=#


The following code can be used to reproduce the error:

drop table wf_workflows;
create table wf_workflows (
  workflow_key          varchar(100)
                        constraint wf_workflows_pk
                        primary key,
  description           text
);



drop table wf_contexts;
create table wf_contexts (
  context_key            varchar(100)
            constraint wf_context_pk
            primary key,
  context_name        varchar(100)
            constraint wf_contexts_context_name_nn
                not null
            constraint wf_contexts_context_name_un
            unique
);


drop table wf_transitions;
create table wf_transitions (
  transition_key        varchar(100),
  transition_name       varchar(100)
                        constraint wf_transition_name_nn
                        not null,
  workflow_key          varchar(100)
                        constraint wf_transition_workflow_fk
                        references wf_workflows(workflow_key)
            on delete cascade,
  -- purely for UI purposes
  sort_order            integer
                        constraint wf_transition_order_ck
                        check (sort_order > 0),
  trigger_type          varchar(40)
            constraint wf_transition_trigger_type_ck
            check (trigger_type in
                          ('','automatic','user','message','time')),
  constraint wf_transition_pk
  primary key (workflow_key, transition_key),
  constraint wf_trans_wf_key_trans_name_un
  unique (workflow_key, transition_name)
);

drop table wf_context_transition_info;
create table wf_context_transition_info (
  context_key                  varchar(100)
                  constraint wf_context_trans_context_fk
                  references wf_contexts,
  workflow_key                varchar(100)
                  constraint wf_context_trans_workflow_fk
                  references wf_workflows,
  transition_key              varchar(100),
  /* information for the transition in the context */
  /* The integer of minutes this task is estimated to take */
  estimated_minutes        integer,
  /*
   * Will be called when the transition is enabled/fired.
   * signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2)
   */
  enable_callback             varchar(100),
  enable_custom_arg           text,
  fire_callback               varchar(100),
  fire_custom_arg             text,
  /*
   * Must insert rows into the wf_task_assignments table.
   * Will be called when the transition becomes enabled
   * signature: (task_id in integer, custom_arg in varchar)
   */
  assignment_callback         varchar(100),
  assignment_custom_arg       text,
  /*
   * Must return the date that the timed transition should fire
   * Will be called when the transition is enabled
   * signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2) return date
   */
  time_callback               varchar(100),
  time_custom_arg             text,
  /*
   * Returns the deadline for this task.
   * Will be called when the transition becomes enabled
   * Signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2) return date
   */
  deadline_callback           varchar(100),
  deadline_custom_arg         text,
  /* The name of an attribute that holds the deadline */
  deadline_attribute_name          varchar(100),
  /*
   * Must return the date that the user's hold on the task times out.
   * called when the user starts the task.
   * signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2) return date
   */
  hold_timeout_callback       varchar(100),
  hold_timeout_custom_arg     text,
  /*
   * Notification callback
   * Will be called when a notification is sent i.e., when a transition is enabled,
   * or assignment changes.
   * signature: (task_id     in integer,
   *             custom_arg     in varchar,
   *             party_to     in integer,
   *             party_from     in out integer,
   *             subject     in out varchar,
   *             body         in out varchar)
   */
  notification_callback        varchar(100),
  notification_custom_arg    text,
  /*
   * Unassigned callback
   * Will be called whenever a task becomes unassigned
   * Signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2)
   */
  unassigned_callback       varchar(100),
  unassigned_custom_arg     text,
  /* name of the privilege we should check before allowing access
   * to task information.
   */
  access_privilege          text,
  /* table constraints */
  constraint wf_context_trans_trans_fk
  foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key)
  on delete cascade,
  constraint wf_context_transition_pk
  primary key (context_key, workflow_key, transition_key)
);

drop table wf_context_assignments;
create table wf_context_assignments (
  context_key                  varchar(100)
                  constraint wf_context_assign_context_fk
                  references wf_contexts(context_key)
                on delete cascade,
  workflow_key                varchar(100)
                  constraint wf_context_assign_workflow_fk
                  references wf_workflows(workflow_key)
                on delete cascade,
  transition_key              varchar(100),
  party_id            integer,
  /* table constraints */
  constraint wf_context_assign_trans_fk
  foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key)
  on delete cascade,
  constraint wf_context_assign_pk
  primary key (context_key, workflow_key, transition_key, party_id)
);


drop view wf_transition_contexts;

create view wf_transition_contexts as
select t.transition_key,
       t.transition_name,
       t.workflow_key,
       t.sort_order,
       t.trigger_type,
       c.context_key,
       c.context_name
from   wf_transitions t, wf_contexts c;


drop view wf_transition_info;
create view wf_transition_info as
select t.transition_key,
       t.transition_name,
       t.workflow_key,
       t.sort_order,
       t.trigger_type,
       t.context_key,
       ct.estimated_minutes,
       ct.enable_callback,
       ct.enable_custom_arg,
       ct.fire_callback,
       ct.fire_custom_arg,
       ct.assignment_callback,
       ct.assignment_custom_arg,
       ct.time_callback,
       ct.time_custom_arg,
       ct.deadline_callback,
       ct.deadline_custom_arg,
       ct.deadline_attribute_name,
       ct.hold_timeout_callback,
       ct.hold_timeout_custom_arg,
       ct.notification_callback,
       ct.notification_custom_arg,
       ct.unassigned_callback,
       ct.unassigned_custom_arg,
       ct.access_privilege
from   wf_transition_contexts t LEFT OUTER JOIN wf_context_transition_info ct
on    (ct.workflow_key = t.workflow_key and
       ct.transition_key = t.transition_key and
       ct.context_key = t.context_key);

Regards,

Dan Wickstrom