Subject: Postquel queries

From: Nabiel Al-Madani <[email-masked]>
Date: Mon, 30 Aug 93 22:36:21 EDT

Hi,

I have two questions concerning Postquel queries. The first is, are there any
floating point binary operators defined in postgres? I typed the following
query:

retrieve (emp.all) where emp.salary > 35000.00\g

where

emp is defined as: emp(ss_no = int4, salary = float4), and postgres responded
by the following message:

Query sent to backend is "retrieve (emp.all) where emp.salary > 35000.00"
NOTICE:Aug 27 04:09:31:there is no operator > for types float4 and float8
NOTICE:Aug 27 04:09:31:You will either have to retype this query using an
NOTICE:Aug 27 04:09:31:explicit cast, or you will have to define the operator
WARN:Aug 27 04:09:31:> for float4 and float8 using DEFINE OPERATOR

if there is none, then how can I explicitly cast the query.

My second question is about the following query:

retrieve (student.all) where student.Id = TA.Id or student.Id = RA.Id\g

where student(Id = int4, name = text)
      TA (Id = int4, name = text)
      RA (Id = int4, name = text)

Postgres responded by the following message:

    Error: No response from the backend, exiting...

I should mention that I tried the same query with an "and" instead of an "or"
in the where clause and the query produced the right results.

I would appreciate any help regarding the above questions. Thanks and best
regards,

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
     Nabeil Al-Madani                 Computer Science & Engineering
[email-masked]          University of South Florida, Tampa
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

---

From: [email-masked] (Paul M. Aoki)
Date: Mon, 30 Aug 93 23:33:15 -0700

general notice.

i can't promise that all bugs reported here will be fixed (and
actually, bugs should be reported to bug-postgres), but i can
promise that from now on i (and only i) will throw away any bug 
reports that do not contain (at an absolute minimum) the version 
of postgres and the operating system/architecture.  someone else
here may very well look at it, but i won't.

Nabiel Al-Madani <[email-masked]> writes:
> floating point binary operators defined in postgres?

yes.

> if there is none, then how can I explicitly cast the query.

from the manual:
   Constants of POSTGRES User-Defined Types
     A constant of an arbitrary type can be entered using the notation:

          "string"::type-name

     In this case the value inside the string is passed to the input conver-
     sion routine for the type called type-name. The result is a constant of
     the indicated type.

hence, 
	retrieve (emp.all) where emp.salary > "35000.00"::float4
should work.. (if it doesn't there's something REALLY wrong.. ^_^;)

avi pfeffer recently cleaned up the typecasting/coercion code so 
that smarter things are done more often and ambiguous cases are 
handled more consistently.  (it was pretty ad hoc before..)

[query with a special kind of 'or' clause]
>     Error: No response from the backend, exiting...

yep, core-dump-o-rama.  gotta hate those existential/free variables.
i *think* the following context diff shows the correct fix, relative to
postgres 4.1.  (save the original in case you have to back it out :-)
however, because of the funky duplicate semantics of postquel, you had 
better change your query to:

	retrieve unique (student.all)
		where student.Id = TA.Id or student.Id = RA.Id\g

unless you want to see tons of duplicates.

*** 1.17	1992/11/23 22:38:29
--- src/backend/planner/path/joinrels.c	1993/08/31 06:00:38
***************
*** 103,113 ****
                                                         get_rel(CAR(other_rels)),
                                                         joininfo),
                                            LispNil);
!                   else
                       temp_node = lispCons((LispValue)init_join_rel(outer_rel,
                                                          get_rel(other_rels),
                                                           joininfo),
                                            LispNil);
                  t_list = nconc(t_list,temp_node);
  
                }
--- 103,115 ----
                                                         get_rel(CAR(other_rels)),
                                                         joininfo),
                                            LispNil);
!                   else if (BushyPlanFlag)
                       temp_node = lispCons((LispValue)init_join_rel(outer_rel,
                                                          get_rel(other_rels),
                                                           joininfo),
                                            LispNil);
+ 		  else
+ 		     temp_node = LispNil;
                  t_list = nconc(t_list,temp_node);
  
                }
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki