Let me ask about type coersion.

When you have an int2 column called x, how do you place the conversion
functions when it is being compared to an in4 constant?

	x = int2(500)

	int4(x) = 500

The first is good for indexing, the second is not.  If they are both
variables or both constants, the handling does not matter.


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sat, 8 Aug 1998 02:42:07 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
type coersion
<I changed "x" to "i2" in the example for clarity>

> ... have an int2 column called i2, how do you place the conversion
> functions when it is being compared to an in4 constant?
>         i2 = int2(500)
>         int4(i2) = 500
> The first is good for indexing, the second is not.  If they are both
> variables or both constants, the handling does not matter.

Yes the handling does matter *in general*, since
  while i4 = 500.1
cannot be evaluated as
  while i4 = int4(500.1)
and get the right result.
Even for the types in your example,
  while i2 = 4000000 
should execute correctly, even though we both know that *for this case*
it doesn't make a lot of sense since the constant exceeds the range of
the column.

Actually, there are a lot of mixed-type comparison functions in pg_proc.
For example, there is a int24eq function which would be used for your
query outside of a "where clause". But, read on...

*slaps forehead*

It dawned on me while I was waking up this morning that we'd forgotten a
major bit of info about indexing. The pg_proc routines which are
declared for =, <, >, etc. are _not_ directly used to access indices! It
is of course the pg_am, pg_amop, and pg_amproc tables which are used for
this.

Here are examples from v6.3.2:

regression=> explain select * from tenk1 where unique1 = 3000;
Index Scan on tenk1  (cost=2.05 size=1 width=100)
regression=> explain select * from tenk1 where unique1 = 3000+1;
Seq Scan on tenk1  (cost=512.00 size=1000 width=100)
regression=> explain select * from tenk1 where unique1 = int4(3000.1);
Seq Scan on tenk1  (cost=512.00 size=1000 width=100)

The *only case* I've noticed so far which does better in v6.3.2 than
"v6.4today" (not yet v6.4alpha :) is the one involving OIDs:
regression=> explain select * from tenk1 where oid = 3000;
Index Scan on tenk1  (cost=2.05 size=1 width=100)

And remember that there are a lot of cases which do better in v6.4today
than in earlier versions.

Let's try to figure out how to get constant expressions using indices,
rather than just patching to get mismatched constant values using them.
And even for that I should be able to fix up the "binary compatible"
cases such as OID and int4 without too much trouble. Just need to find
the right spot in the index handling.

However, I should be able to find that by looking for references to the
pg_am* tables in the source code now that my brain is partly unwedged :)
Will do that, and we might still want to figure out how to use Vadim's
new PARAM_EXEC nodes for function calls on constants and constant
expressions (e.g. func1(func2(const1+const2))+const3) in the parser.

                   - Tom

Sat, 08 Aug 1998 13:48:23 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: type coersion (was OR clause status)
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> The *only case* I've noticed so far which does better in v6.3.2 than
> "v6.4today" (not yet v6.4alpha :) is the one involving OIDs:
> regression=> explain select * from tenk1 where oid = 3000;
> Index Scan on tenk1  (cost=2.05 size=1 width=100)

Actually, I'm aware of another one: comparisons using "> constant"
or "< constant" seem more likely to use an index in 6.3.2 than they
do in the sources I have.  I have examples involving both datetime
and int4 columns where "where x = constant" will be implemented by
index scan, but "where x > constant" will not.  Explicit casts of
the righthand side make no difference.  And it works fine in 6.3.2.

I'm a couple of weeks behind the CVS tree, so I was going to wait
until I'd confirmed it with up-to-the-minute sources before complaining.
But if you're proceeding on the assumption that the "oid = integer" case
is the only thing that's broken, you may be misled.

The fact that casting doesn't affect this makes me think it is a
different problem than the must-cast-to-get-an-index-scan cases
we've discussed so far.

			regards, tom lane

Sat, 08 Aug 1998 14:41:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status) 
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
>> ... have an int2 column called i2, how do you place the conversion
>> functions when it is being compared to an in4 constant?
>> i2 = int2(500)
>> int4(i2) = 500
>> The first is good for indexing, the second is not.  If they are both
>> variables or both constants, the handling does not matter.

> Yes the handling does matter *in general*, since
>   while i4 = 500.1
> cannot be evaluated as
>   while i4 = int4(500.1)
> and get the right result.
> Even for the types in your example,
>   while i2 = 4000000 
> should execute correctly, even though we both know that *for this case*
> it doesn't make a lot of sense since the constant exceeds the range of
> the column.

This is all a good point.  I wonder whether it wouldn't help to make the
parser's initial assignment of types to constants depend on how big the
constants are.  In other words, if I write "400" the parser would
implicitly mark this as "int2", whereas if I write "400000" it would be
implicitly marked "int4".  Then, subsequent implicit upward promotion
would cast 400::int2 to 400::int4 if int4 was actually the most
appropriate thing to use *in context*.

It seems to me that this handles all the cases cited correctly:
	where i2 = 400
will get implemented directly as int2 eq int2,
	where i4 = 400
will promote 400::int2 to 400::int4 and then use an int4 eq int4
comparison op (of course we have to address the current failure to
reduce int4(constant) to a constant, but IMHO that has to happen
anyway),
	where i2 = 400000
will get promoted to where int4(i2) = 400000::int4 and executed
correctly (in this case returning no rows, but that's not a reason
not to do it right --- "where i2 < 32768" might be a more compelling
example).  Likewise
	where i4 = 500.1
will be executed with correct semantics as float8(i4) = 500.1::float8
since the parser will know that float8 is a "wider" type than int4.

It'd be nice if the same answer would work for 
	where f4 = 500.1
but I'm not sure that I care to see the parser deciding that "float4
is good enough for this constant".  We could too easily find that in
	where f8 = 500.1
the parser might cast the constant down to float4 and back up to float8
with catastrophic loss of precision.

In the float case, is there some way that a constant might be marked
as "float of unspecified width" (with the actual value held as a float8)
until the type resolution pass is done?  This would need to be
considered "equivalent" to both float4 and float8, so that in
	where f4 = 500.1
the type resolver doesn't decide it must rewrite f4 as float8(f4).
Then at some late stage of the game we resolve the constant to float4
rather than float8 if the context is float4.  I'm not sure how the
details should work, however.  If we *could* make this work it might
be best to handle int2 vs. int4 constants the same way.

			regards, tom lane

Sat, 08 Aug 1998 15:16:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status) 
> > Yes the handling does matter *in general*...
> This is all a good point. I wonder whether it wouldn't help to make 
> the parser's initial assignment of types to constants depend on how 
> big the constants are.

I agree that there is something to fix, including both of the indexing
example cases you are following. I've been seeing this as an opportunity
to fix sub-optimal utilization of indices, and my recent changes to
enhance the type conversion capabilities in the parser just put the
index handling in a harsher light.

If we come near release time, and no one has been able to penetrate the
index handling (to fix cases as simple as "where x = 1 + 1" which have
never worked) the we could fairly easily go back into the parser and
brute-force some non-general workarounds to get the few "worse than
before" cases hacked around.

If we _can_ get the index handling to work more generally, then we have
substantially enhanced the overall capabilities of Postgres.

So far, I haven't seen cases where the parser has tried to do "the wrong
thing", only cases where "the right thing" causes the index handling to
miss the boat...

                       - Tom

Sun, 09 Aug 1998 22:30:23 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: type coersion (was OR clause status)
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> So far, I haven't seen cases where the parser has tried to do "the wrong
> thing", only cases where "the right thing" causes the index handling to
> miss the boat...

I disagree, actually.  In the example

	select ... where i2 = 400;

I claim the parser is doing the wrong thing by representing this as
"where int4(i2) = 400::int4" rather than "where i2 = 400::int2".
It is not really reasonable to expect the optimizer to clean up
after that initial mistake.

			regards, tom lane

Sun, 09 Aug 1998 19:06:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status) 
> > > Yes the handling does matter *in general*...
> > This is all a good point. I wonder whether it wouldn't help to make 
> > the parser's initial assignment of types to constants depend on how 
> > big the constants are.
> 
> I agree that there is something to fix, including both of the indexing
> example cases you are following. I've been seeing this as an opportunity
> to fix sub-optimal utilization of indices, and my recent changes to
> enhance the type conversion capabilities in the parser just put the
> index handling in a harsher light.
> 
> If we come near release time, and no one has been able to penetrate the
> index handling (to fix cases as simple as "where x = 1 + 1" which have
> never worked) the we could fairly easily go back into the parser and
> brute-force some non-general workarounds to get the few "worse than
> before" cases hacked around.
> 
> If we _can_ get the index handling to work more generally, then we have
> substantially enhanced the overall capabilities of Postgres.
> 
> So far, I haven't seen cases where the parser has tried to do "the wrong
> thing", only cases where "the right thing" causes the index handling to
> miss the boat...

I have found what is happening in the optimizer, and will explain soon.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sun, 9 Aug 1998 19:33:59 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> If we come near release time, and no one has been able to penetrate the
> index handling (to fix cases as simple as "where x = 1 + 1" which have
> never worked) the we could fairly easily go back into the parser and
> brute-force some non-general workarounds to get the few "worse than
> before" cases hacked around.
> 
> If we _can_ get the index handling to work more generally, then we have
> substantially enhanced the overall capabilities of Postgres.
> 
> So far, I haven't seen cases where the parser has tried to do "the wrong
> thing", only cases where "the right thing" causes the index handling to
> miss the boat...

OK, here is what I have.  I have a table called test, with one
attribute.  I execute:

	select * from test where oid = 3;

and the output plan is:
	
	(
	   { EXPR  
	   :typeOid 0  
	   :opType op 
	   :oper 
	      { OPER  
	      :opno 1137  
	      :opid 0  
	      :opresulttype 16 
	      }
	    
	   :args (
	      { VAR  
	      :varno 1  
	      :varattno -2  
	      :vartype 26  
	      :vartypmod -1  
	      :varlevelsup 0  
	      :varnoold 1  
	      :varoattno -2 
	      }
	    
	      { CONST  
	      :consttype 23  
	      :constlen 4  
	      :constisnull false 
	      :constvalue  4 [  3  0  0  0 ]  
	      :constbyval true
	      }
	   )
	   }
	)

Why does the Var have a type 26(int), and the constant a type of
23(oid)?  Where's the conversion function?
	
Now, the existance of the function doesn't help either, but that is a
different problem:

	test=> explain select * from test where oid = oid(3);
	NOTICE:  QUERY PLAN:
	
	Seq Scan on test  (cost=1936.05 size=4916 width=8)

Is it because there is a int4eqoid() function?  How to use an index on that?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sun, 9 Aug 1998 20:09:57 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> and the output plan is:
>         (
>            { EXPR
>            :typeOid 0
>            :opType op
>            :oper
>               { OPER
>               :opno 1137
>               :opid 0
>               :opresulttype 16
>               }
> 
>            :args (
>               { VAR
>               :varno 1
>               :varattno -2
>               :vartype 26
>               :vartypmod -1
>               :varlevelsup 0
>               :varnoold 1
>               :varoattno -2
>               }
> 
>               { CONST
>               :consttype 23
>               :constlen 4
>               :constisnull false
>               :constvalue  4 [  3  0  0  0 ]
>               :constbyval true
>               }
>            )
>            }
>         )
> 
> Why does the Var have a type 26(int), and the constant a type of
> 23(oid)?  Where's the conversion function? 

A conversion function is not necessary; the operator in the "opno" field
(1137) corresponds to the oid of the entry in pg_operator for "=" with
the correct arguments.

> Now, the existance of the function doesn't help either, but that is a
> different problem:
>         test=> explain select * from test where oid = oid(3);
>         NOTICE:  QUERY PLAN:
>         Seq Scan on test  (cost=1936.05 size=4916 width=8)
> Is it because there is a int4eqoid() function?

Yes. The function is called int4eqoid() (good guess :). And there is
also a function oideqint4(). 

There is a chance that this case would actually work if we just removed
those functions, since (in my test code only) I've made int4 and oid
"binary compatible" so the int4eq or oideq routines would be used
instead. The index support code might actually behave properly then.

> How to use an index on that?

So that is the problem for this case; there is actually a function which
matches the arguments exactly, so it is specified. *And* the same
function is (probably) not mentioned in the index configuration tables
pg_am*. 

However, if we allowed the index support code to look for possible
matches on indices for the non-constant terms, and then look for the
best possible match for conversion routines on other terms, and then did
an "optimizer substitution", we might get better behavior. We would want
code to do the same kind of analysis for constant terms with function
calls and constant expressions too.

I'd be happy to work on the actual substitution code, but still don't
know what the planner does with indices. I'm starting to poke through it
like you are, but am farther behind.

I thought a good start would be to try addressing a case like this, and
allow the planner/indexer/optimizer to substitute "binary compatible"
indices. If we can succeed at that, then we would know what places need
to be touched to do more, like handling function calls and expressions
with constants.

                      - Tom

Mon, 10 Aug 1998 01:52:22 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: type coersion (was OR clause status)
> "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> > So far, I haven't seen cases where the parser has tried to do "the wrong
> > thing", only cases where "the right thing" causes the index handling to
> > miss the boat...
> 
> I disagree, actually.  In the example
> 
> 	select ... where i2 = 400;
> 
> I claim the parser is doing the wrong thing by representing this as
> "where int4(i2) = 400::int4" rather than "where i2 = 400::int2".
> It is not really reasonable to expect the optimizer to clean up
> after that initial mistake.

I don't see that.  If I do:

	select * from test2 where i2 = 3;

I don't see any constants being converted.  The Var is still i2, and the
Const is i4.  This is as it is seen by the optimizer.  It is using
int24eq(opno = 532).  This is valid when doing comparisons in the
executor.

The problem is how do we use indexes for this?  I am still researching
this.


---------------------------------------------------------------------------


(
   { EXPR
   :typeOid 0
   :opType op
   :oper
      { OPER
      :opno 532
      :opid 0
      :opresulttype 16
      }

   :args (
      { VAR
      :varno 1
      :varattno 1
      :vartype 21
      :vartypmod -1
      :varlevelsup 0
      :varnoold 1
      :varoattno 1
      }

       { CONST
      :consttype 23
      :constlen 4
      :constisnull false
      :constvalue  4 [  3  0  0  0 ]
      :constbyval true
      }
   )
   }
)


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sun, 9 Aug 1998 21:57:40 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> > I claim the parser is doing the wrong thing by representing this as
> > "where int4(i2) = 400::int4" rather than "where i2 = 400::int2".
> > It is not really reasonable to expect the optimizer to clean up
> > after that initial mistake.
> I don't see that.

Yup. The parser is behaving as Bruce describes. The new type conversion
stuff isn't the fundamental problem. It's the original features in the
planner when trying to use indices.

> The problem is how do we use indexes for this?  I am still researching
> this.

OK, let me know if I can help look into anything. In the meantime, I'll
keep poking at it a bit...

                         - Tom

Mon, 10 Aug 1998 02:43:48 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: type coersion (was OR clause status)
> > > I claim the parser is doing the wrong thing by representing this as
> > > "where int4(i2) = 400::int4" rather than "where i2 = 400::int2".
> > > It is not really reasonable to expect the optimizer to clean up
> > > after that initial mistake.
> > I don't see that.
> 
> Yup. The parser is behaving as Bruce describes. The new type conversion
> stuff isn't the fundamental problem. It's the original features in the
> planner when trying to use indices.
> 
> > The problem is how do we use indexes for this?  I am still researching
> > this.
> 
> OK, let me know if I can help look into anything. In the meantime, I'll
> keep poking at it a bit...

The optimizer does a loop for each index on every relation:

In match_clause_to_indexkey(), there is code that takes the
operator, in the case of "oid = 3", value 1137, oideqint4:

        if ((rightop && IsA(rightop, Const)) ||
            (rightop && IsA(rightop, Param)))
        {
            restrict_op = ((Oper *) ((Expr *) clause)->oper)->opno;
            isIndexable = (op_class(restrict_op, xclass, index->relam) &&
                            IndexScanableOperand(leftop,
                                                  indexkey,
                                                  rel,
                                                  index));
        }

 
and calls opclass(), which does a lookup in the pg_amop cache, passing
the operator oid (1137), the access method class oid, and the index
access method:

	#0  op_class (opno=1137, opclass=427, amopid=403) at lsyscache.c:58
                      oideqint4    oid_ops   btree_am_oid


and it returns false because there is no access operator for oid_ops and
btree_am_oid that matches oideqint4.

The fundamental problem is that index scans are made to compare columns
all of the same type.  That is how indexes are built and traversed. 
Now, we want to bring in a constant of another type, and have it use an
index.

Sounds like if we add the proper pg_am functions for binary
compatability, the optimizer should then use the proper indexes.

Thomas?

[I am going to bed now.]

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Mon, 10 Aug 1998 00:50:27 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> > > I claim the parser is doing the wrong thing by representing this as
> > > "where int4(i2) = 400::int4" rather than "where i2 = 400::int2".
> > > It is not really reasonable to expect the optimizer to clean up
> > > after that initial mistake.
> > I don't see that.
> 
> Yup. The parser is behaving as Bruce describes. The new type conversion
> stuff isn't the fundamental problem. It's the original features in the
> planner when trying to use indices.
> 
> > The problem is how do we use indexes for this?  I am still researching
> > this.
> 
> OK, let me know if I can help look into anything. In the meantime, I'll
> keep poking at it a bit...

I have also looked at x = oid(3) to see why functions are not being
used.  That same function I mentioned match_clause_to_indexkey clearly
shows it does not support this:

    /*
     * If this is not a join clause, check for clauses of the form:
     * (operator var/func constant) and (operator constant var/func)
     */
    if (!join)
    {
        /*
         * Check for standard s-argable clause
         */
        if ((rightop && IsA(rightop, Const)) ||
            (rightop && IsA(rightop, Param)))
        {
            restrict_op = ((Oper *) ((Expr *) clause)->oper)->opno;
            isIndexable = (op_class(restrict_op, xclass, index->relam) &&
 

They want to use a functional index for oid(), but we are computing it
on a constant.  Some code will have to be added somewhere to handle
this.  If we want it done in the executor, perhaps we can look inside
the function to see if they are all consts, and handle it somehow.

Of course, if a function index does match, we should use that first.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Mon, 10 Aug 1998 01:01:06 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> The fundamental problem is that index scans are made to compare columns
> all of the same type.  That is how indexes are built and traversed. 
> Now, we want to bring in a constant of another type, and have it use an
> index.
> 
> Sounds like if we add the proper pg_am functions for binary
> compatability, the optimizer should then use the proper indexes.

FOLLOWUP:

This may fix our int4/oid problem, but I don't think it addresses the
more common problems like int2/int4.  I can't see how that would work
with just additions to pg_amop.

Yes, we have the int2eqint4 function, but how does that work in an
index.  Actually, it was my understanding that the new conversion code
was going to get rid of the int2eqint4 style functions, and replace them
with conversions.  

Thomas, perhaps a quick summary of the logic behind your parser
conversion changes would help.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Mon, 10 Aug 1998 01:09:10 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> Sounds like if we add the proper pg_am functions for binary
> compatability, the optimizer should then use the proper indexes.
> 

I just tried this and it failed.  Not sure why yet:
	
	test=> explain select * from test where oid = 3;
	NOTICE:  QUERY PLAN:
	
	Seq Scan on test  (cost=1936.05 size=1 width=8)
	
	EXPLAIN
	test=> create index i_test3 on test(oid oidint4ops);
	ERROR:  DefineIndex: oidint4ops class not found
	test=> create index i_test3 on test(oid oidint4_ops);
	vacuum ;
	CREATE
	test=> vacuum ;
	VACUUM
	test=> explain select * from test where oid = 3;
	NOTICE:  QUERY PLAN:
	
	Seq Scan on test  (cost=1936.05 size=1 width=8)
	
	EXPLAIN


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Mon, 10 Aug 1998 09:55:08 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> 	#0  op_class (opno=1137, opclass=427, amopid=403) at lsyscache.c:58
>                       oideqint4    oid_ops   btree_am_oid
> 
> 
> and it returns false because there is no access operator for oid_ops and
> btree_am_oid that matches oideqint4.
> 
> The fundamental problem is that index scans are made to compare columns
> all of the same type.  That is how indexes are built and traversed. 
> Now, we want to bring in a constant of another type, and have it use an
> index.
> 
> Sounds like if we add the proper pg_am functions for binary
> compatability, the optimizer should then use the proper indexes.

I think I have found part of the cause.  We have duplicate type
conversion functions, and the parser is choosing the one that is not in
the access method tables.


---------------------------------------------------------------------------


test=> select * from pg_operator where oid = 1137;
oprname|oprowner|oprprec|oprkind|oprisleft|oprcanhash|oprleft|oprright|oprresult|oprcom|oprnegate|oprlsortop|oprrsortop|oprcode
 |oprrest|oprjoin
-------+--------+-------+-------+---------+----------+-------+--------+---------+------+---------+----------+----------+---------+-------+---------
=      |     139|      0|b      |t        |t         |     26|      23| 
     16|  1136|        0|         0|         0|oideqint4|eqsel 
|eqjoinsel (1 row)


test=> select * from pg_operator where oid = 932;
oprname|oprowner|oprprec|oprkind|oprisleft|oprcanhash|oprleft|oprright|oprresult|oprcom|oprnegate|oprlsortop|oprrsortop|oprcode
 |oprrest |oprjoin
-------+--------+-------+-------+---------+----------+-------+--------+---------+------+---------+----------+----------+---------+--------+------------
=      |     139|      0|b      |t        |f         |    910|     910| 
     16|   932|      935|         0|         0|oidint4eq|intltsel|intltjoinsel (1 row)

t

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Tue, 11 Aug 1998 00:45:42 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> test=> select * from pg_operator where oid = 1137;
> oprname|oprowner|oprprec|oprkind|oprisleft|oprcanhash|oprleft|oprright|oprresult|oprcom|oprnegate|oprlsortop|oprrsortop|oprcode
>  |oprrest|oprjoin
> -------+--------+-------+-------+---------+----------+-------+--------+---------+------+---------+----------+----------+---------+-------+---------
> =      |     139|      0|b      |t        |t         |     26|      23| 
>      16|  1136|        0|         0|         0|oideqint4|eqsel 
> |eqjoinsel (1 row)
> 
> 
> test=> select * from pg_operator where oid = 932;
> oprname|oprowner|oprprec|oprkind|oprisleft|oprcanhash|oprleft|oprright|oprresult|oprcom|oprnegate|oprlsortop|oprrsortop|oprcode
>  |oprrest |oprjoin
> -------+--------+-------+-------+---------+----------+-------+--------+---------+------+---------+----------+----------+---------+--------+------------
> =      |     139|      0|b      |t        |f         |    910|     910| 
>      16|   932|      935|         0|         0|oidint4eq|intltsel|intltjoinsel (1 row)

I was wrong about the above entries.  oidint4eq is for a custom type
that is made up of and oid and an int4.  Strange but true.  Probably
should be removed.  Can't imagine why someone would use this.  Probably
used when we didn't have multi-key indexes or something.

Anyway, here is a query that shows the operators defined for access
methods.
	
	SELECT proname
	FROM pg_operator, pg_amop,pg_proc
	WHERE amopopr = pg_operator.oid AND 
	      RegprocToOid(pg_operator.oprcode) = pg_proc.oid;


Perhaps if i add oid am functions to use the int4 functions, it would
work.  int4int2eq assumes promotion of int2 to int4.  Should work.

proname         
----------------
chareq          
chareq          
nameeq          
nameeq          
int2eq          
int2eq          
int2lt          
int4eq          
int4eq          
int4lt          
texteq          
texteq          
poly_left       
poly_overleft   
poly_overright  
poly_right      
poly_contained  
poly_contain    
poly_same       
poly_overlap    
box_left        
box_left        
box_overleft    
box_overleft    
box_overright   
box_overright   
box_right       
box_right       
box_contained   
box_contained   
box_contain     
box_contain     
box_same        
box_same        
box_overlap     
box_overlap     
int2gt          
int4gt          
int2le          
int4le          
int2ge          
int4ge          
int24eq         
int42eq         
int24lt         
int42lt         
int24gt         
int42gt         
int24le         
int42le         
int24ge         
int42ge         
abstimeeq       
abstimelt       
abstimegt       
abstimele       
abstimege       
oideq           
oideq           
int4lt          
int4gt          
int4le          
int4ge          
float4eq        
float4eq        
float4lt        
float4gt        
float4le        
float4ge        
charlt          
charle          
chargt          
charge          
namelt          
namele          
namegt          
namege          
text_lt         
text_le         
text_gt         
text_ge         
float8eq        
float8eq        
float8lt        
float8le        
float8gt        
float8ge        
oidnamelt       
oidnamele       
oidnameeq       
oidnamege       
oidnamegt       
oidint2lt       
oidint2le       
oidint2eq       
oidint2ge       
oidint2gt       
oidint4lt       
oidint4le       
oidint4eq       
oidint4ge       
oidint4gt       
bpchareq        
bpchareq        
bpcharlt        
bpcharle        
bpchargt        
bpcharge        
varchareq       
varchareq       
varcharlt       
varcharle       
varchargt       
varcharge       
date_eq         
date_eq         
date_lt         
date_le         
date_gt         
date_ge         
time_eq         
time_eq         
time_lt         
time_le         
time_gt         
time_ge         
datetime_eq     
datetime_eq     
datetime_lt     
datetime_le     
datetime_gt     
datetime_ge     
timespan_eq     
timespan_eq     
timespan_lt     
timespan_le     
timespan_gt     
timespan_ge     
circle_left     
circle_overleft 
circle_overright
circle_right    
circle_contained
circle_contain  
circle_same     
circle_overlap  
(146 rows)




-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Tue, 11 Aug 1998 01:08:29 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> I think I have found part of the cause.  We have duplicate type
> conversion functions, and the parser is choosing the one that is not 
> in the access method tables.

I don't think so for this case; I got stuck on this for awhile too. It
seems that "oidint4" is an actual data type, so has an "oidint4eq"
comparison function. The parser/planner/optimizer is finding the correct
functions, which are "oideqint4" and "int4eqoid". 

Don't know what "oidint4" actually does or how it's used. Confusing...

                       - Tom

---------------------------------------------------------------------------
> 
> test=> select * from pg_operator where oid = 1137;
> oprname|oprowner|oprprec|oprkind|oprisleft|oprcanhash|oprleft|oprright|oprresult|oprcom|oprnegate|oprlsortop|oprrsortop|oprcode
>  |oprrest|oprjoin
> -------+--------+-------+-------+---------+----------+-------+--------+---------+------+---------+----------+----------+---------+-------+---------
> =      |     139|      0|b      |t        |t         |     26|      23|
>      16|  1136|        0|         0|         0|oideqint4|eqsel
> |eqjoinsel (1 row)
> 
> test=> select * from pg_operator where oid = 932;
> oprname|oprowner|oprprec|oprkind|oprisleft|oprcanhash|oprleft|oprright|oprresult|oprcom|oprnegate|oprlsortop|oprrsortop|oprcode
>  |oprrest |oprjoin
> -------+--------+-------+-------+---------+----------+-------+--------+---------+------+---------+----------+----------+---------+--------+------------
> =      |     139|      0|b      |t        |f         |    910|     910|
>      16|   932|      935|         0|         0|oidint4eq|intltsel|intltjoinsel (1 row)

Tue, 11 Aug 1998 05:36:21 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: type coersion (was OR clause status)
> > I think I have found part of the cause.  We have duplicate type
> > conversion functions, and the parser is choosing the one that is not 
> > in the access method tables.
> 
> I don't think so for this case; I got stuck on this for awhile too. It
> seems that "oidint4" is an actual data type, so has an "oidint4eq"
> comparison function. The parser/planner/optimizer is finding the correct
> functions, which are "oideqint4" and "int4eqoid". 
> 
> Don't know what "oidint4" actually does or how it's used. Confusing...

OK, new information.  The following query shows the pg_proc names for
oid.*int4:

select pg_operator.oid as pg_operator_oid, pg_proc.proname from pg_proc,
pg_operator where proname ~ 'oid.*int4' and pg_proc.oid =
RegprocToOid(pg_operator.oprcode)

pg_operator_oid|proname
---------------+---------
           1137|oideqint4
            930|oidint4lt
            931|oidint4le
            932|oidint4eq
            933|oidint4ge
            934|oidint4gt
            935|oidint4ne
(7 rows)

The pg_operator_oid is the value stored in pg_amop.amopopr field.  The
optimizer is finding a function that works for the query, in our case
oideqint4, with a pg_operator oid of 1137.

The problem is that there is no pg_am.amopopr for 1137.  What is defined
for oid is this:

/*
 *  nbtree oid_ops
 */

DATA(insert OID = 0 (  403 427 609 1 btreesel btreenpage ));
DATA(insert OID = 0 (  403 427 611 2 btreesel btreenpage ));
DATA(insert OID = 0 (  403 427 607 3 btreesel btreenpage ));
DATA(insert OID = 0 (  403 427 612 4 btreesel btreenpage ));
DATA(insert OID = 0 (  403 427 610 5 btreesel btreenpage ));

For example, the third number in the third line is 607, which is the
amopopr, and is oideq.  We know we can use this for the query because
int4 and oid are identical, but the parser has already chosen the more
appopriate 1137/oideqint4.  If we could add an extra line to this file,
perhaps:

DATA(insert OID = 0 (  403 427 607 3 btreesel btreenpage ));

However, I don't think the access methods allow more than one line in
this way.

The other problem is that there is not a <, >, etc for int4/oid, so a
separate op type can not be created.  In fact, I am not sure it would
work anyway.  I think the operator oid chosen by the backend must
EXACTLY match the oid stored in pg_amop.amopopr for the index to be
used.

That is what match_clause_to_indexkey is doing by calling op_class().

[Update you source trees.  The op_class parameter names were wrong,
called opid instead of opno, so it was very confusing.  Fixed now.]

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Tue, 11 Aug 1998 02:03:46 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> For example, the third number in the third line is 607, which is the
> amopopr, and is oideq.  We know we can use this for the query because
> int4 and oid are identical, but the parser has already chosen the more
> appopriate 1137/oideqint4.  If we could add an extra line to this file,
> perhaps:
> 
> DATA(insert OID = 0 (  403 427 607 3 btreesel btreenpage ));
> 
> However, I don't think the access methods allow more than one line in
> this way.

I just tried adding the extra line, and initdb failed.  I wonder if we
remove the oideqint4, if the parser will go for oideq?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Tue, 11 Aug 1998 02:30:13 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> I just tried adding the extra line, and initdb failed.  I wonder if we
> remove the oideqint4, if the parser will go for oideq?

If you want to try this as an experiment, so we can tell if this is a
possible solution, then great. I still have hopes that we can substitute
other parse trees and strategies within the index utilization routines,
so how about not committing things until we've tried a few options.

match_clause_to_indexkey() seems to be a starting point for what I want
to do. Will let you know how it goes...

                       - Tom

Wed, 12 Aug 1998 02:44:24 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: type coersion (was OR clause status)
> > I just tried adding the extra line, and initdb failed.  I wonder if we
> > remove the oideqint4, if the parser will go for oideq?
> 
> If you want to try this as an experiment, so we can tell if this is a
> possible solution, then great. I still have hopes that we can substitute
> other parse trees and strategies within the index utilization routines,
> so how about not committing things until we've tried a few options.
> 
> match_clause_to_indexkey() seems to be a starting point for what I want
> to do. Will let you know how it goes...

OK.  Check out my other posts on how op_class/pg_amop.amopopr is used to
look up if the current expression operator is a member of the
class(btree), and index opclass(oid_ops).  I could probably code
something so you could put multiple entries in pg_amop.  My issue is
that there are not that many interchangable operators to make that
useful.

Now, if you are thinking of doing some type of constant conversion
there, that may be a bigger win.  The issue is at this point in the
code, there is no guarentee the index will be used.  It is just checking
the index usability.

I think I see where you are going with this.  For queries not involving
indexes(many joins don't), it may be better to leave things for the
executor.

Also, if you can, do a fresh cvs update if you can, because the code is
a little cleaner now in that area.  Particularly, the op_class()
parameters were badly named, causing me confusion.


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Tue, 11 Aug 1998 23:23:20 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> > However, I don't think the access methods allow more than one line in
> > this way.
> 
> I just tried adding the extra line, and initdb failed.  I wonder if we
> remove the oideqint4, if the parser will go for oideq?
> 

One other item.  oid indexes use int4lt(), etc. for their internal
indexing, not oidlt() because they do not exist.


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Wed, 12 Aug 1998 12:41:39 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> > match_clause_to_indexkey() seems to be a starting point for what I 
> > want to do. Will let you know how it goes...
> Check out my other posts on how op_class/pg_amop.amopopr is used to
> look up if the current expression operator is a member of the
> class(btree), and index opclass(oid_ops).  I could probably code
> something so you could put multiple entries in pg_amop.  My issue is
> that there are not that many interchangable operators to make that
> useful.
> Now, if you are thinking of doing some type of constant conversion
> there, that may be a bigger win.  The issue is at this point in the
> code, there is no guarentee the index will be used.  It is just 
> checking the index usability.
> I think I see where you are going with this.  For queries not 
> involving indexes(many joins don't), it may be better to leave things 
> for the executor.

The first thing I want to try is to substitute the operator for types
which are known to be binary-compatible and do not have their own index
defined. The next step would be to substitute an operator _and_ insert
an explicit type conversion using a function call (which may not work
yet for other reasons). Don't know where Vadim's PARAM_EXEC node comes
in, but if it does it might be around here.

> ... if you can, do a fresh cvs update if you can, because the code is
> a little cleaner now in that area.  Particularly, the op_class()
> parameters were badly named, causing me confusion.

OK. Does the current source tree compile now? Haven't had much time to
look at it the last few days, but will start poking at it this evening a
bit.

                         - Tom

Thu, 13 Aug 1998 02:58:03 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: type coersion (was OR clause status)
> The first thing I want to try is to substitute the operator for types
> which are known to be binary-compatible and do not have their own index
> defined. The next step would be to substitute an operator _and_ insert
> an explicit type conversion using a function call (which may not work
> yet for other reasons). Don't know where Vadim's PARAM_EXEC node comes
> in, but if it does it might be around here.
> 
> > ... if you can, do a fresh cvs update if you can, because the code is
> > a little cleaner now in that area.  Particularly, the op_class()
> > parameters were badly named, causing me confusion.
> 
> OK. Does the current source tree compile now? Haven't had much time to
> look at it the last few days, but will start poking at it this evening a
> bit.

Yep.  Only been down for a few hours in the past month, at least for me.
I am working on another huge patch, but it will be tested before
install, of course.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 13 Aug 1998 00:59:54 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> > The first thing I want to try is to substitute the operator for 
> > types which are known to be binary-compatible and do not have their 
> > own index defined.

Got a start on it :)

regression=> explain select * from tenk1 where oid = 3000;
NOTICE:  QUERY PLAN:

Index Scan using tenk1_oid on tenk1  (cost=2.05 size=1 width=148)

EXPLAIN

So far, I've just done the right-hand form (the one with the constant on
the rhs of the expression). The left-hand form should be easy now.

So, just curious: if we stop here, and only match up binary-compatible
built-in types with available indices, then what v6.3.2 features/good
behaviors are still missing?

                       - Tom

The example is from the regression test database with an extra btree
index built on the tenk1 table...

regression=> \d tenk1

Table    = tenk1
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| unique1                          | int4                            
|     4 |
| unique2                          | int4                            
|     4 |
| two                              | int4                            
|     4 |
| four                             | int4                            
|     4 |
| ten                              | int4                            
|     4 |
| twenty                           | int4                            
|     4 |
| hundred                          | int4                            
|     4 |
| thousand                         | int4                            
|     4 |
| twothousand                      | int4                            
|     4 |
| fivethous                        | int4                            
|     4 |
| tenthous                         | int4                            
|     4 |
| odd                              | int4                            
|     4 |
| even                             | int4                            
|     4 |
| stringu1                         | name                            
|    32 |
| stringu2                         | name                            
|    32 |
| string4                          | name                            
|    32 |
+----------------------------------+----------------------------------+-------+
Indices:  tenk1_hundred
          tenk1_oid
          tenk1_unique1
          tenk1_unique2
regression=>

Thu, 13 Aug 1998 14:30:18 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: type coersion (was OR clause status)
> > > The first thing I want to try is to substitute the operator for 
> > > types which are known to be binary-compatible and do not have their 
> > > own index defined.
> 
> Got a start on it :)
> 
> regression=> explain select * from tenk1 where oid = 3000;
> NOTICE:  QUERY PLAN:
> 
> Index Scan using tenk1_oid on tenk1  (cost=2.05 size=1 width=148)
> 
> EXPLAIN
> 
> So far, I've just done the right-hand form (the one with the constant on
> the rhs of the expression). The left-hand form should be easy now.
> 
> So, just curious: if we stop here, and only match up binary-compatible
> built-in types with available indices, then what v6.3.2 features/good
> behaviors are still missing?

Did you put it in the optimizer?

I think binary compatable types converted is going to be the easiest
thing to do for index use.  Not sure how you could try and break it. 
How about character string comparisons using indexes?

> |    32 |
> +----------------------------------+----------------------------------+-------+
> Indices:  tenk1_hundred
>           tenk1_oid
>           tenk1_unique1
>           tenk1_unique2
  ^^^^^^^^^^^^^^^^^^^^^^^^^

How about that new display?


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 13 Aug 1998 11:05:17 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
> > > > The first thing I want to try is to substitute the operator for
> > > > types which are known to be binary-compatible and do not have 
> > > > their own index defined.
> > Got a start on it :)
> > regression=> explain select * from tenk1 where oid = 3000;
> > Index Scan using tenk1_oid on tenk1  (cost=2.05 size=1 width=148)
> > So, just curious: if we stop here, and only match up 
> > binary-compatible built-in types with available indices, then what 
> > v6.3.2 features/good behaviors are still missing?
> Did you put it in the optimizer?

It is inside backend/optimizer/path/indxpath.c. I'm using a bit of the
parser support code to help out. It has to be where the backend actually
is checking to see if an index is usable, which is in the optimization
step. Any earlier and we would have to look-ahead at the indices which
seems inappropriate.

> I think binary compatable types converted is going to be the easiest
> thing to do for index use.  Not sure how you could try and break it.
> How about character string comparisons using indexes?

Will try some more tests, but it seems like it will be hard to break
since it only comes into effect with built-in datatypes which are
supposed to be binary compatible.

It would be interesting to try to do constant expressions and function
calls on constants next, though I'm thinking that it isn't required for
v6.4.

Vadim, will the executor know how to use a PARAM_EXEC node in any
context, or will we have to do some coding to get it recognized outside
of subselects? I'll need to figure out how to build one too, I
suppose...

                     - Tom

Fri, 14 Aug 1998 04:24:49 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: type coersion (was OR clause status)
Thomas G. Lockhart wrote:
> 
> > > > > The first thing I want to try is to substitute the operator for
> > > > > types which are known to be binary-compatible and do not have
> > > > > their own index defined.
> > > Got a start on it :)
> > > regression=> explain select * from tenk1 where oid = 3000;
> > > Index Scan using tenk1_oid on tenk1  (cost=2.05 size=1 width=148)
> > > So, just curious: if we stop here, and only match up
> > > binary-compatible built-in types with available indices, then what
> > > v6.3.2 features/good behaviors are still missing?
> > Did you put it in the optimizer?
> 
> It is inside backend/optimizer/path/indxpath.c. I'm using a bit of the
> parser support code to help out. It has to be where the backend actually
> is checking to see if an index is usable, which is in the optimization
> step. Any earlier and we would have to look-ahead at the indices which
> seems inappropriate.
> 
> > I think binary compatable types converted is going to be the easiest
> > thing to do for index use.  Not sure how you could try and break it.
> > How about character string comparisons using indexes?
> 
> Will try some more tests, but it seems like it will be hard to break
> since it only comes into effect with built-in datatypes which are
> supposed to be binary compatible.

OK, I've just committed to the source tree changes for looking for
binary-compatible indices when either the left- or right-hand side of
the restriction clause is a constant.

I've #ifndef'd them so we can disable it if necessary. But, all of the
regression tests pass, except for the select_view test, which has been
core dumping for weeks. Anyone else seeing that, or is it just me? :(

                    - Tom

Sat, 15 Aug 1998 01:32:44 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: type coersion (was OR clause status)
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> ... all of the
> regression tests pass, except for the select_view test, which has been
> core dumping for weeks. Anyone else seeing that, or is it just me? :(

I rebuilt the system from current sources today, and ran the regression
tests for the first time in a long time.  select_views works fine for
me, but there are several other tests that look badly broken:
SELECT ... ORDER BY upper(c) is misordering the results in select_implicit,
GROUP BY on a datetime is not working right in select_having, and the
random test is failing because it "can't look up operator 713".

I'm on HP-UX 9.03, PA-RISC 1.1, gcc 2.7.2.2 if that helps.

			regards, tom lane


*** expected/select_implicit.out	Sat Aug 15 11:56:03 1998
--- results/select_implicit.out	Sat Aug 15 13:44:16 1998
***************
*** 213,226 ****
  QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
  a
  -
- 1
  2
  3
  4
  5
  6
- 7
  8
  9
  0
  (10 rows)
--- 213,226 ----
  QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
  a
  -
  2
+ 1
  3
  4
  5
  6
  8
+ 7
  9
  0
  (10 rows)

----------------------

*** expected/select_having.out	Wed Jul  8 10:29:09 1998
--- results/select_having.out	Sat Aug 15 13:44:16 1998
***************
*** 2,12 ****
    GROUP BY d1 HAVING count(*) > 1;
  d1                          |count
  ----------------------------+-----
! Thu Jun 13 00:00:00 1957 PDT|    2
! Mon Feb 10 09:32:01 1997 PST|    3
! Mon Feb 10 17:32:01 1997 PST|   13
  Sun Feb 16 17:32:01 1997 PST|    2
  Sat Mar 01 17:32:01 1997 PST|    2
! invalid                     |    2
! (6 rows)
  
--- 2,13 ----
    GROUP BY d1 HAVING count(*) > 1;
  d1                          |count
  ----------------------------+-----
! Thu Jun 13 00:00:00 1957 PST|    2
! Mon Feb 10 17:32:01 1997 PST|    4
! Mon Feb 10 09:32:01 1997 PST|    2
! Mon Feb 10 17:32:01 1997 PST|    2
! Mon Feb 10 17:32:01 1997 PST|    7
  Sun Feb 16 17:32:01 1997 PST|    2
  Sat Mar 01 17:32:01 1997 PST|    2
! (7 rows)
  

----------------------

*** expected/random.out	Tue Apr 29 10:23:40 1997
--- results/random.out	Sat Aug 15 13:44:19 1998
***************
*** 5,18 ****
  (1 row)
  
  QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
! count
! -----
!    92
! (1 row)
  
  QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
! count
! -----
!    98
! (1 row)
  
--- 5,12 ----
  (1 row)
  
  QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
! ERROR:  can't look up operator 713
  
  QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
! ERROR:  can't look up operator 713
  

----------------------

Sat, 15 Aug 1998 14:18:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Regression test status (was type coersion)
> > ... all of the
> > regression tests pass, except for the select_view test, which has 
> > been core dumping for weeks. Anyone else seeing that?
> I ... ran the regression
> tests for the first time in a long time.  select_views works fine for
> me, but there are several other tests that look badly broken:
> I'm on HP-UX 9.03, PA-RISC 1.1, gcc 2.7.2.2 if that helps.

Hmm. I'm on Linux-libc5, i686, gcc 2.7.2.1, and select_views is still
core dumping:

QUERY: SELECT * FROM toyemp WHERE name = 'sharon';
pqReadData() -- backend closed the channel unexpectedly.

> --- results/select_implicit.out Sat Aug 15 13:44:16 1998
> --- results/select_having.out   Sat Aug 15 13:44:16 1998

These two pass on my machine. Your select_having result looks similar to
the sorting results back when the in-memory/on-disk sorting routines
were broken; some identical values are not getting grouped together
while others are.

> --- results/random.out  Sat Aug 15 13:44:19 1998
>   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> ! ERROR:  can't look up operator 713

Ah. I am getting failures on this too but didn't actually look at why,
just assuming that the randomizer was doing better nowadays. This is
probably related to something I, or possibly Bruce or David, had done.
If you change the query to

  SELECT count(*) FROM onek where oidrand(onek.oid, 10) = TRUE;

then it works. I'll look at it. Darn, I had just stripped out the
debugging stuff in the code before submitting it, since leaving it
hanging around was annoying Bruce (with good reason btw).

                     - Tom

Sun, 16 Aug 1998 04:36:55 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Regression test status (was type coersion)
> > --- results/random.out  Sat Aug 15 13:44:19 1998
> >   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> > ! ERROR:  can't look up operator 713
> Ah. I am getting failures on this too but didn't actually look at why,
> just assuming that the randomizer was doing better nowadays. This is
> probably related to something I, or possibly Bruce or David, had done.

OK, I just committed patches which fixes this problem. The index
strategy code used to just check for an available operator, but I had
changed it to try an alternate strategy by looking for the same operator
with different types. But the same code gets executed even if the
restriction clause is just a function returning a boolean, with no
operator involved at all.

I now allow the operator name lookup to return a null pointer if an
alternate does not exist, and then the code stops looking for
alternatives.

So now the "random" test still fails, but in a good way with the
expected results...

                      - Tom

Sun, 16 Aug 1998 05:53:16 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Regression test status (was type coersion)


Tom Lane wrote:

> I rebuilt the system from current sources today, and ran the regression
> tests for the first time in a long time.  select_views works fine for
> me, but there are several other tests that look badly broken:
> SELECT ... ORDER BY upper(c) is misordering the results in select_implicit,
> GROUP BY
>                         regards, tom lane
>
> *** expected/select_implicit.out        Sat Aug 15 11:56:03 1998
> --- results/select_implicit.out Sat Aug 15 13:44:16 1998
> ***************
> *** 213,226 ****
>   QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
>   a
>   -
> - 1
>   2
>   3
>   4
>   5
>   6
> - 7
>   8
>   9
>   0
>   (10 rows)
> --- 213,226 ----
>   QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
>   a
>   -
>   2
> + 1
>   3
>   4
>   5
>   6
>   8
> + 7
>   9
>   0
>   (10 rows)
>


Interesting.   I do not recall my exact data set in the regression, but I
believe both results are correct.   In some sense, on your machine
upper('CCCC') and upper('cccc') are sorting in a different order then my
machine.    I realize that internally they are actually ordinally tied.   But I
thought they should still produce a predictable, uniform, result set.  Either,
there is a bug or I need more reliable test data.   I will verify this when I
get home.


Mon, 17 Aug 1998 09:46:29 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Regression test status (was type coersion)
Thomas G. Lockhart wrote:
> 
> It is inside backend/optimizer/path/indxpath.c. I'm using a bit of the
> parser support code to help out. It has to be where the backend actually
> is checking to see if an index is usable, which is in the optimization
> step. Any earlier and we would have to look-ahead at the indices which
> seems inappropriate.

Just let me note that function calls on constants is problem not 
only for indices using. Call lower() for each tuple in
WHERE a = lower('bbb') is always bad - lower() eats memory...

> > I think binary compatable types converted is going to be the easiest
> > thing to do for index use.  Not sure how you could try and break it.
> > How about character string comparisons using indexes?

Parser could use type_in()/type_out() funcs to do type
coersion...

> 
> Will try some more tests, but it seems like it will be hard to break
> since it only comes into effect with built-in datatypes which are
> supposed to be binary compatible.
> 
> It would be interesting to try to do constant expressions and function
> calls on constants next, though I'm thinking that it isn't required for
> v6.4.
> 
> Vadim, will the executor know how to use a PARAM_EXEC node in any
> context, or will we have to do some coding to get it recognized outside
> of subselects? I'll need to figure out how to build one too, I
> suppose...

I'm not sure... But imho, PARAM_EXEC could be usefull for 
now() etc funcs - for non-variant funcs I would suggest
just evaluate them in parser...

Vadim

Tue, 18 Aug 1998 12:13:06 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: type coersion (was OR clause status)
> Just let me note that function calls on constants is problem not
> only for indices using. Call lower() for each tuple in
> WHERE a = lower('bbb') is always bad - lower() eats memory...

Sure, so we want to evaluate as a constant. I've found a place in the
code where a function gets evaluated immediately; don't remember where
but it looked like an interesting possibility.

> > > How about character string comparisons using indexes?
> Parser could use type_in()/type_out() funcs to do type
> coersion...

That is what it used to try to do. But I don't think that generalizes
very well. For example, at the moment floating point numbers without
fractional parts are printed without a decimal point or trailing digits,
but we could decide to format them with a ".0" at the end. Then they
couldn't be converted to an integer...

> > Vadim, will the executor know how to use a PARAM_EXEC node in any
> > context, or will we have to do some coding to get it recognized 
> > outside of subselects? I'll need to figure out how to build one too, 
> > I suppose...
> I'm not sure... But imho, PARAM_EXEC could be usefull for
> now() etc funcs - for non-variant funcs I would suggest
> just evaluate them in parser...

But it could be used for every function called with constants, right? If
it works for everything, why bother with other special cases?

                     - Tom

Tue, 18 Aug 1998 05:22:17 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: type coersion (was OR clause status)
> OK, I've just committed to the source tree changes for looking for
> binary-compatible indices when either the left- or right-hand side of
> the restriction clause is a constant.
> 
> I've #ifndef'd them so we can disable it if necessary. But, all of the
> regression tests pass, except for the select_view test, which has been
> core dumping for weeks. Anyone else seeing that, or is it just me? :(

Is this fixed?  Are there any open problems with the regress tests?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sun, 23 Aug 1998 18:12:09 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: type coersion (was OR clause status)
Do any of these problems still exist?

> "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> > ... all of the
> > regression tests pass, except for the select_view test, which has been
> > core dumping for weeks. Anyone else seeing that, or is it just me? :(
> 
> I rebuilt the system from current sources today, and ran the regression
> tests for the first time in a long time.  select_views works fine for
> me, but there are several other tests that look badly broken:
> SELECT ... ORDER BY upper(c) is misordering the results in select_implicit,
> GROUP BY on a datetime is not working right in select_having, and the
> random test is failing because it "can't look up operator 713".
> 
> I'm on HP-UX 9.03, PA-RISC 1.1, gcc 2.7.2.2 if that helps.
> 
> 			regards, tom lane
> 
> 
> *** expected/select_implicit.out	Sat Aug 15 11:56:03 1998
> --- results/select_implicit.out	Sat Aug 15 13:44:16 1998
> ***************
> *** 213,226 ****
>   QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
>   a
>   -
> - 1
>   2
>   3
>   4
>   5
>   6
> - 7
>   8
>   9
>   0
>   (10 rows)
> --- 213,226 ----
>   QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
>   a
>   -
>   2
> + 1
>   3
>   4
>   5
>   6
>   8
> + 7
>   9
>   0
>   (10 rows)
> 
> ----------------------
> 
> *** expected/select_having.out	Wed Jul  8 10:29:09 1998
> --- results/select_having.out	Sat Aug 15 13:44:16 1998
> ***************
> *** 2,12 ****
>     GROUP BY d1 HAVING count(*) > 1;
>   d1                          |count
>   ----------------------------+-----
> ! Thu Jun 13 00:00:00 1957 PDT|    2
> ! Mon Feb 10 09:32:01 1997 PST|    3
> ! Mon Feb 10 17:32:01 1997 PST|   13
>   Sun Feb 16 17:32:01 1997 PST|    2
>   Sat Mar 01 17:32:01 1997 PST|    2
> ! invalid                     |    2
> ! (6 rows)
>   
> --- 2,13 ----
>     GROUP BY d1 HAVING count(*) > 1;
>   d1                          |count
>   ----------------------------+-----
> ! Thu Jun 13 00:00:00 1957 PST|    2
> ! Mon Feb 10 17:32:01 1997 PST|    4
> ! Mon Feb 10 09:32:01 1997 PST|    2
> ! Mon Feb 10 17:32:01 1997 PST|    2
> ! Mon Feb 10 17:32:01 1997 PST|    7
>   Sun Feb 16 17:32:01 1997 PST|    2
>   Sat Mar 01 17:32:01 1997 PST|    2
> ! (7 rows)
>   
> 
> ----------------------
> 
> *** expected/random.out	Tue Apr 29 10:23:40 1997
> --- results/random.out	Sat Aug 15 13:44:19 1998
> ***************
> *** 5,18 ****
>   (1 row)
>   
>   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> ! count
> ! -----
> !    92
> ! (1 row)
>   
>   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> ! count
> ! -----
> !    98
> ! (1 row)
>   
> --- 5,12 ----
>   (1 row)
>   
>   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> ! ERROR:  can't look up operator 713
>   
>   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> ! ERROR:  can't look up operator 713
>   
> 
> ----------------------
> 
> 


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sun, 23 Aug 1998 18:16:12 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Regression test status (was type coersion)
> then it works. I'll look at it. Darn, I had just stripped out the
> debugging stuff in the code before submitting it, since leaving it
> hanging around was annoying Bruce (with good reason btw).

I have an idea on this.  If we change #ifdef PARSEDEBUG to something
else, I think we can keep it.  If in an include file we say:

#ifdef PARSEDEBUG
#define ParseDebug(x)	x
#else	
#define ParseDebug(x)
#endif

we can then do

	x=3;
	y=4;
	ParseDebug(printf("test));

and it will look good, and we can always enable it.  What do you think? 
I can do it if you want?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sun, 23 Aug 1998 18:20:15 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Regression test status (was type coersion)
Tom,

I was never able to reproduce the ORDER BY anomaly on my Linux box.   I will try
to reproduce it on my AIX box.  I have some porting issues I need to get through
first.

In the mean time, if you still see a problem, could you run the query as:
     SELECT a, upper(c) FROM test_missing_target ORDER BY upper;
to eliminate the junkfilter as a culprit.

Bruce Momjian wrote:

> Do any of these problems still exist?
>
> > "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> > > ... all of the
> > > regression tests pass, except for the select_view test, which has been
> > > core dumping for weeks. Anyone else seeing that, or is it just me? :(
> >
> > I rebuilt the system from current sources today, and ran the regression
> > tests for the first time in a long time.  select_views works fine for
> > me, but there are several other tests that look badly broken:
> > SELECT ... ORDER BY upper(c) is misordering the results in select_implicit,
> > GROUP BY on a datetime is not working right in select_having, and the
> > random test is failing because it "can't look up operator 713".
> >
> > I'm on HP-UX 9.03, PA-RISC 1.1, gcc 2.7.2.2 if that helps.
> >
> >                       regards, tom lane
> >
> >
> > *** expected/select_implicit.out      Sat Aug 15 11:56:03 1998
> > --- results/select_implicit.out       Sat Aug 15 13:44:16 1998
> > ***************
> > *** 213,226 ****
> >   QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
> >   a
> >   -
> > - 1
> >   2
> >   3
> >   4
> >   5
> >   6
> > - 7
> >   8
> >   9
> >   0
> >   (10 rows)
> > --- 213,226 ----
> >   QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
> >   a
> >   -
> >   2
> > + 1
> >   3
> >   4
> >   5
> >   6
> >   8
> > + 7
> >   9
> >   0
> >   (10 rows)
> >
> > ----------------------
> >
> > *** expected/select_having.out        Wed Jul  8 10:29:09 1998
> > --- results/select_having.out Sat Aug 15 13:44:16 1998
> > ***************
> > *** 2,12 ****
> >     GROUP BY d1 HAVING count(*) > 1;
> >   d1                          |count
> >   ----------------------------+-----
> > ! Thu Jun 13 00:00:00 1957 PDT|    2
> > ! Mon Feb 10 09:32:01 1997 PST|    3
> > ! Mon Feb 10 17:32:01 1997 PST|   13
> >   Sun Feb 16 17:32:01 1997 PST|    2
> >   Sat Mar 01 17:32:01 1997 PST|    2
> > ! invalid                     |    2
> > ! (6 rows)
> >
> > --- 2,13 ----
> >     GROUP BY d1 HAVING count(*) > 1;
> >   d1                          |count
> >   ----------------------------+-----
> > ! Thu Jun 13 00:00:00 1957 PST|    2
> > ! Mon Feb 10 17:32:01 1997 PST|    4
> > ! Mon Feb 10 09:32:01 1997 PST|    2
> > ! Mon Feb 10 17:32:01 1997 PST|    2
> > ! Mon Feb 10 17:32:01 1997 PST|    7
> >   Sun Feb 16 17:32:01 1997 PST|    2
> >   Sat Mar 01 17:32:01 1997 PST|    2
> > ! (7 rows)
> >
> >
> > ----------------------
> >
> > *** expected/random.out       Tue Apr 29 10:23:40 1997
> > --- results/random.out        Sat Aug 15 13:44:19 1998
> > ***************
> > *** 5,18 ****
> >   (1 row)
> >
> >   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> > ! count
> > ! -----
> > !    92
> > ! (1 row)
> >
> >   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> > ! count
> > ! -----
> > !    98
> > ! (1 row)
> >
> > --- 5,12 ----
> >   (1 row)
> >
> >   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> > ! ERROR:  can't look up operator 713
> >
> >   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> > ! ERROR:  can't look up operator 713
> >
> >
> > ----------------------
> >
> >
>
> --
> Bruce Momjian                          |  830 Blythe Avenue
> maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
>   +  If your life is a hard drive,     |  (610) 353-9879(w)
>   +  Christ can be your backup.        |  (610) 853-3000(h)




Mon, 24 Aug 1998 08:38:51 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Regression test status (was type coersion)
I check  two these of these problems.    Read ahead  --->

Bruce Momjian wrote:

> Do any of these problems still exist?
>
> > "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> > > ... all of the
> > > regression tests pass, except for the select_view test, which has been
> > > core dumping for weeks. Anyone else seeing that, or is it just me? :(
> >
> > I rebuilt the system from current sources today, and ran the regression
> > tests for the first time in a long time.  select_views works fine for
> > me, but there are several other tests that look badly broken:
> > SELECT ... ORDER BY upper(c) is misordering the results in select_implicit,
> > GROUP BY on a datetime is not working right in select_having, and the
> > random test is failing because it "can't look up operator 713".
> >
> > I'm on HP-UX 9.03, PA-RISC 1.1, gcc 2.7.2.2 if that helps.
> >
> >                       regards, tom lane
> >
> >
> > *** expected/select_implicit.out      Sat Aug 15 11:56:03 1998
> > --- results/select_implicit.out       Sat Aug 15 13:44:16 1998
> > ***************
> > *** 213,226 ****
> >   QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
> >   a
> >   -
> > - 1
> >   2
> >   3
> >   4
> >   5
> >   6
> > - 7
> >   8
> >   9
> >   0
> >   (10 rows)
> > --- 213,226 ----
> >   QUERY: SELECT a FROM test_missing_target ORDER BY upper(c);
> >   a
> >   -
> >   2
> > + 1
> >   3
> >   4
> >   5
> >   6
> >   8
> > + 7
> >   9
> >   0
> >   (10 rows)
> >
> > ----------------------
> >

Both sort orders are correct.     I seems that different machines are resolving
equivalent strings differently.    I got the same result as Tom on an RS/6000
box.   I could be an big vs little endian, signed vs unsigned chars issue.  In
any case, the actual sort order is indeterminate.  Therefore I will submit a new
regression test with reliable test data.

> > *** expected/select_having.out        Wed Jul  8 10:29:09 1998
> > --- results/select_having.out Sat Aug 15 13:44:16 1998
> > ***************
> > *** 2,12 ****
> >     GROUP BY d1 HAVING count(*) > 1;
> >   d1                          |count
> >   ----------------------------+-----
> > ! Thu Jun 13 00:00:00 1957 PDT|    2
> > ! Mon Feb 10 09:32:01 1997 PST|    3
> > ! Mon Feb 10 17:32:01 1997 PST|   13
> >   Sun Feb 16 17:32:01 1997 PST|    2
> >   Sat Mar 01 17:32:01 1997 PST|    2
> > ! invalid                     |    2
> > ! (6 rows)
> >
> > --- 2,13 ----
> >     GROUP BY d1 HAVING count(*) > 1;
> >   d1                          |count
> >   ----------------------------+-----
> > ! Thu Jun 13 00:00:00 1957 PST|    2
> > ! Mon Feb 10 17:32:01 1997 PST|    4
> > ! Mon Feb 10 09:32:01 1997 PST|    2
> > ! Mon Feb 10 17:32:01 1997 PST|    2
> > ! Mon Feb 10 17:32:01 1997 PST|    7
> >   Sun Feb 16 17:32:01 1997 PST|    2
> >   Sat Mar 01 17:32:01 1997 PST|    2
> > ! (7 rows)
> >
> >
> > ----------------------
> >

These results are also correct.  Somewhat.    I do not know much about datatime
porting issues, but if  I do a:
    SELECT d1 FROM  DATETIME_TBL
I get time reported to the 1/100 of a second.    If  GROUP BY d1 the hundredths
are not shown.   Thus, the counts and groupings are correct.   Its just not
showing the hundredths portion.

> > *** expected/random.out       Tue Apr 29 10:23:40 1997
> > --- results/random.out        Sat Aug 15 13:44:19 1998
> > ***************
> > *** 5,18 ****
> >   (1 row)
> >
> >   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> > ! count
> > ! -----
> > !    92
> > ! (1 row)
> >
> >   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> > ! count
> > ! -----
> > !    98
> > ! (1 row)
> >
> > --- 5,12 ----
> >   (1 row)
> >
> >   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> > ! ERROR:  can't look up operator 713
> >
> >   QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
> > ! ERROR:  can't look up operator 713
> >
> >

Don't know about this one.


Wed, 26 Aug 1998 13:45:02 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Regression test status (was type coersion)
David Hartwig <daveh@insightdist.com> writes:
> Both sort orders are correct.  I seems that different machines are
> resolving equivalent strings differently.  I got the same result as
> Tom on an RS/6000 box.  I could be an big vs little endian, signed vs
> unsigned chars issue.  In any case, the actual sort order is
> indeterminate.  Therefore I will submit a new regression test with
> reliable test data.

OK.

> These results are also correct.  Somewhat.  I do not know much about
> datatime porting issues, but if I do a:
>     SELECT d1 FROM  DATETIME_TBL
> I get time reported to the 1/100 of a second.  If GROUP BY d1 the
> hundredths are not shown.  Thus, the counts and groupings are correct.
> Its just not showing the hundredths portion.

The issue here is why you (or whoever it was prepared the regression
test) got different results from me...

>>>> QUERY: SELECT count(*) FROM onek where oidrand(onek.oid, 10);
>>>> ! ERROR:  can't look up operator 713
>
> Don't know about this one.

That bug should be fixed in the current sources, I believe.

			regards, tom lane

Wed, 26 Aug 1998 14:25:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Regression test status (was type coersion) 


Tom Lane wrote:

> > These results are also correct.  Somewhat.  I do not know much about
> > datatime porting issues, but if I do a:
> >     SELECT d1 FROM  DATETIME_TBL
> > I get time reported to the 1/100 of a second.  If GROUP BY d1 the
> > hundredths are not shown.  Thus, the counts and groupings are correct.
> > Its just not showing the hundredths portion.
>
> The issue here is why you (or whoever it was prepared the regression
> test) got different results from me...
>

I do get the same results - on my RS/6000.   And I did not prepare the
test.    But, to be more precise:

1. The failure does not indicate any problems w/ the HAVING clause.

2.  This test will never work if there is a machine dependence on the
ability to resolve datatime down to the 1/100 second.

3.   There appears to be something strange going on in the way GROUPed BY
datetime columns do not display the 1/100 portion of seconds.   Is this a
known problem?

Unless someone objects, I will submit a  test suit  to, at least remove the
false reading on select_having.


Wed, 26 Aug 1998 15:04:09 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Regression test status (was type coersion)