Re: MySQL search query is not executing in Postgres DB - Mailing list pgsql-hackers

I wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> That problem is dead.

> The reason it's dead is that we killed it in 8.3.  I don't want it
> coming back to life, but I think that that will be exactly the outcome
> if we let any implicit casts to text get back into the rules for
> operator/function overloading resolution.

To put some concreteness into what so far has been a pretty hand-wavy
discussion, I experimented with the attached patch.  I'm not sure that
it exactly corresponds to what you proposed, but I think this is the
only place the consideration could be injected without a substantial
amount of code rearrangement.  This results in half a dozen regression
test failures (see second attachment), which mostly consist of
"function/operator does not exist" errors changing to "function/operator
is not unique".  I've not looked into exactly why each one happens ---
possibly the code is now finding multiple string-category matches where
before it found none.  But it definitely illustrates my point that this
would not be without surprises.

Oh, one more thing:

regression=# select lpad(42,8);
ERROR:  failed to find conversion function from integer to text

so this doesn't actually solve the problem you want to solve.
I'm not sure why that's happening, either, but evidently some
additional coercion laxity would required.

            regards, tom lane

diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 2b1a13a..feac9f9 100644
*** a/src/backend/parser/parse_func.c
--- b/src/backend/parser/parse_func.c
*************** func_match_argtypes(int nargs,
*** 555,560 ****
--- 555,578 ----
          }
      }

+     if (ncandidates == 0)
+     {
+         /* try again with assignment rules */
+         for (current_candidate = raw_candidates;
+              current_candidate != NULL;
+              current_candidate = next_candidate)
+         {
+             next_candidate = current_candidate->next;
+             if (can_coerce_type(nargs, input_typeids, current_candidate->args,
+                                 COERCION_ASSIGNMENT))
+             {
+                 current_candidate->next = *candidates;
+                 *candidates = current_candidate;
+                 ncandidates++;
+             }
+         }
+     }
+
      return ncandidates;
  }    /* func_match_argtypes() */

*** /home/tgl/pgsql/src/test/regress/expected/text.out    Tue Jul 12 18:56:58 2011
--- /home/tgl/pgsql/src/test/regress/results/text.out    Wed Aug 29 00:08:45 2012
***************
*** 26,35 ****
  -- As of 8.3 we have removed most implicit casts to text, so that for example
  -- this no longer works:
  select length(42);
! ERROR:  function length(integer) does not exist
  LINE 1: select length(42);
                 ^
! HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  -- But as a special exception for usability's sake, we still allow implicit
  -- casting to text in concatenations, so long as the other input is text or
  -- an unknown literal.  So these work:
--- 26,35 ----
  -- As of 8.3 we have removed most implicit casts to text, so that for example
  -- this no longer works:
  select length(42);
! ERROR:  function length(integer) is not unique
  LINE 1: select length(42);
                 ^
! HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
  -- But as a special exception for usability's sake, we still allow implicit
  -- casting to text in concatenations, so long as the other input is text or
  -- an unknown literal.  So these work:
***************
*** 47,56 ****

  -- but not this:
  select 3 || 4.0;
! ERROR:  operator does not exist: integer || numeric
  LINE 1: select 3 || 4.0;
                   ^
! HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
  /*
   * various string functions
   */
--- 47,56 ----

  -- but not this:
  select 3 || 4.0;
! ERROR:  operator is not unique: integer || numeric
  LINE 1: select 3 || 4.0;
                   ^
! HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
  /*
   * various string functions
   */

======================================================================

*** /home/tgl/pgsql/src/test/regress/expected/errors.out    Thu Jan 26 17:29:22 2012
--- /home/tgl/pgsql/src/test/regress/results/errors.out    Wed Aug 29 00:08:52 2012
***************
*** 126,132 ****
                stype = int4,
                finalfunc = int2um,
                initcond = '0');
! ERROR:  function int2um(integer) does not exist
  -- left out basetype
  create aggregate newcnt1 (sfunc = int4inc,
                stype = int4,
--- 126,132 ----
                stype = int4,
                finalfunc = int2um,
                initcond = '0');
! ERROR:  function int2um(smallint) requires run-time type coercion
  -- left out basetype
  create aggregate newcnt1 (sfunc = int4inc,
                stype = int4,

======================================================================

*** /home/tgl/pgsql/src/test/regress/expected/alter_table.out    Fri Jul 27 17:27:42 2012
--- /home/tgl/pgsql/src/test/regress/results/alter_table.out    Wed Aug 29 00:09:02 2012
***************
*** 1705,1712 ****
  alter table anothertab alter column atcol1 drop default;
  alter table anothertab alter column atcol1 type boolean
          using case when atcol1 % 2 = 0 then true else false end; -- fails
! ERROR:  operator does not exist: boolean <= integer
! HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
  alter table anothertab drop constraint anothertab_chk;
  alter table anothertab drop constraint anothertab_chk; -- fails
  ERROR:  constraint "anothertab_chk" of relation "anothertab" does not exist
--- 1705,1712 ----
  alter table anothertab alter column atcol1 drop default;
  alter table anothertab alter column atcol1 type boolean
          using case when atcol1 % 2 = 0 then true else false end; -- fails
! ERROR:  operator is not unique: boolean <= integer
! HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
  alter table anothertab drop constraint anothertab_chk;
  alter table anothertab drop constraint anothertab_chk; -- fails
  ERROR:  constraint "anothertab_chk" of relation "anothertab" does not exist

======================================================================

*** /home/tgl/pgsql/src/test/regress/expected/rowtypes.out    Mon Jul  9 10:27:28 2012
--- /home/tgl/pgsql/src/test/regress/results/rowtypes.out    Wed Aug 29 00:09:03 2012
***************
*** 348,357 ****
  (0 rows)

  select text(fullname) from fullname;  -- error
! ERROR:  function text(fullname) does not exist
  LINE 1: select text(fullname) from fullname;
                 ^
! HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  select fullname.text from fullname;  -- error
  ERROR:  column fullname.text does not exist
  LINE 1: select fullname.text from fullname;
--- 348,357 ----
  (0 rows)

  select text(fullname) from fullname;  -- error
! ERROR:  function text(fullname) is not unique
  LINE 1: select text(fullname) from fullname;
                 ^
! HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
  select fullname.text from fullname;  -- error
  ERROR:  column fullname.text does not exist
  LINE 1: select fullname.text from fullname;
***************
*** 370,379 ****
  (1 row)

  select text(row('Jim', 'Beam'));  -- error
! ERROR:  function text(record) does not exist
  LINE 1: select text(row('Jim', 'Beam'));
                 ^
! HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  select (row('Jim', 'Beam')).text;  -- error
  ERROR:  could not identify column "text" in record data type
  LINE 1: select (row('Jim', 'Beam')).text;
--- 370,379 ----
  (1 row)

  select text(row('Jim', 'Beam'));  -- error
! ERROR:  function text(record) is not unique
  LINE 1: select text(row('Jim', 'Beam'));
                 ^
! HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
  select (row('Jim', 'Beam')).text;  -- error
  ERROR:  could not identify column "text" in record data type
  LINE 1: select (row('Jim', 'Beam')).text;

======================================================================