Re: When is Like different to =

["Ben Trewern" <ben.trewern@xxxxxxxxxxxxxxxxxx>]

After some more digging I found there was an index:

CREATE INDEX job_list_status_idx
  ON job_list
  USING btree
  (status(event_no));

I had previously created.  I must have changed the function from IMMUTABLE 
to STABLE after creating the index or I assume I wouldn't have been able to 
create the index.  When I dropped the index Like and = started working 
correctly.

BTW should there be check so an error is thrown if I try to change a 
function used in an index from IMMUTABLE to STABLE?

Ben

"Ben Trewern" <ben.trewern@xxxxxxxxxxxxxxxxxx> wrote in message 
news:dbr6fj$10p3$1@xxxxxxxxxxxxxxx
> All,
>
> I've a query:
>
> SELECT
>    c.code, c.pc_no, jl.event_no, jl.order_number, jl.location, s.status, 
> cs.commercial_status
> FROM
>    ((codes as c JOIN job_list as jl ON c.id = jl.code_id) JOIN status as s 
> ON jl.event_no = s.event_no)
>        JOIN commercial_status AS cs on jl.event_no = cs.event_no
> WHERE
>    (status = 'Job Allocated') AND (code_id = 39);
>
> Where codes and job_list are tables and status is a view:
>
> CREATE VIEW status AS
> SELECT job_list.event_no, status(job_list.event_no) AS status
>   FROM job_list
>   JOIN user_codes ON job_list.code_id = user_codes.code_id
>  WHERE user_codes.user_name::name = "current_user"();
>
> CREATE FUNCTION status(int4)
>  RETURNS text AS
> $BODY$SELECT
> CASE
>            WHEN job_cancelled IS NOT NULL THEN 'Cancelled'::text
>            WHEN job_sheet_received IS NOT NULL THEN 'Job Sheet 
> Received'::text
>            WHEN works_complete IS NOT NULL THEN 'Works Complete'::text
>            WHEN awaiting_action_id IS NOT NULL THEN 'Awaiting 
> Action'::text
>            WHEN attend_date IS NOT NULL THEN 'Job Attended'::text
>            WHEN issued_to IS NOT NULL THEN 'Job Allocated'::text
>            ELSE 'Not Allocated'::text
> END
> FROM
> job_list
> WHERE
> event_no = $1$BODY$
>  LANGUAGE 'sql' STABLE;
>
> The above query should return one row from my current database but does 
> not. If I change the where clause from (status = 'Job Allocated') AND 
> (code_id = 39)
> to
> (status LIKE 'Job Allocated') AND (code_id = 39)
> it does return the row.
>
> What am I missing?
>
> Regards,
>
> Ben
> 



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match