Hi Gary!

First, never start a new, unrelated question by hitting reply (you
replied to Rick Roens question about changing field type). I chose to
reply to Dmitrys answer (he started a new thread) to avoid messing up
threading for those that use it.

Secondly, one thing Aage told me when I started working with him and
InterBase (it was before Firebird was born), was that it was a bad idea
to specify plans explicitly. Rather, I was told to hint the optimizer.

I'd recommend you to modify your query like this (alternatively use +''
rather than +0 if the field is a char or varchar field:

select coalesce( mc.catagory, 'Other') catagory, c.menucode, i.menuitem,
i.uniquelink, i.price, n.note, c.vegetarian, c.kosher, c.halal,
c.health, c.kids
from menucode c
inner join menuitems i on c.link+0 = i.link and i.deleted = 'N'
left outer join menunotes n on n.menucodelink = c.link
and n.deleted = 'N'
left outer join meal_catagory mc on c.mealcatagory = mc.link
and mc.deleted = 'N'
where c.available = 'Y' and c.restlink = 'BEN0000059'
and c.deleted = 'N'
order by 1, c.menucode, i.menuitem, i.price

I'd be very surprised if the optimizer doesn't produce the plan you want
with this strong a hint.

HTH,
Set

Gary Benade wrote:

> Last night I decided to start testing version 2 RC4 SuperServer on my
> development webserver running winXP sp2, apache 2.0.52 and PHP5.1.4.
> After installation and restart I manually replaced the instance of
> GDS32.DLL that lives in the PHP5 directory, the only other copy of the
> lib other than the one in system32. When I tested a website it seemed
> to be working OK except that some of the manual plans I had specified
> caused errors now.
>
> select coalesce( mc.catagory, 'Other') catagory, c.menucode,
> i.menuitem, i.uniquelink, i.price, n.note, c.vegetarian, c.kosher,
> c.halal, c.health, c.kids
> from menucode c
> inner join menuitems i on c.link = i.link and i.deleted = 'N'
> left outer join meal_catagory mc on c.mealcatagory = mc.link
> and mc.deleted = 'N'
> left outer join menunotes n on n.menucodelink = c.link
> and n.deleted = 'N'
> where c.available = 'Y' and c.restlink = 'BEN0000059'
> and c.deleted = 'N'
> PLAN SORT (JOIN (JOIN (C INDEX (I_MENUCODE_RESTLINK),I
> INDEX (MENUITEMS_MENUCODELINK)),N INDEX (RDB$PRIMARY24)))
> order by 1, c.menucode, i.menuitem, i.price
>
> ibase_errmsg: table MEAL_CATAGORY is not referenced in plan
>
> This works in 1.5.3 and is probably something I am doing wrong but it
> does mean that the switch to v2 is going to be a lesson in standards
> compliance, not necesarilly a bad thing.