List:General Discussion« Previous MessageNext Message »
From:Daniel Kraft Date:April 25 2011 7:33pm
Subject:Re: WHERE does not work on calculated view field - Found word(s)
list error in the Text body
View as plain text  
Hi,

On 04/25/11 20:45, Larry McGhaw wrote:
> My best advice is to not use a custom MySQL function in a view when the parameter to
> that function
> is a column or expression that has the potential to result in NULL because of being
> on the right side
> of a left outer join (or the left side of a right outer join).   This particular set
> of circumstances
> seems to cause MySQL to treat the resulting expression as "unknown" when used in a
> where clause on the view
> itself - such that any use of the expression in the where clause will evaluate to
> unknown/false.
>
> As a workaround - this view for example behaves as expected:
>
> CREATE VIEW `myview2` AS
>        SELECT a.*, IF(b.`Name` IS NULL, '', b.`Name`) AS `TypeName`
>          FROM `mytable` a
>            LEFT JOIN `types` b ON a.`Type` = b.`ID`;

now you mention it, it seems obvious -- but I didn't think about that 
solution before...  But 'inlining' my function this way seems to fix the 
problem also in my real application.

Thanks a lot!

Yours,
Daniel

-- 
http://www.pro-vegan.info/
--
Done:  Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
Thread
WHERE does not work on calculated view fieldDaniel Kraft22 Apr
  • RE: WHERE does not work on calculated view fieldDaevid Vincent22 Apr
    • Re: WHERE does not work on calculated view fieldDaniel Kraft22 Apr
      • RE: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyLarry McGhaw22 Apr
        • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyCarsten Pedersen22 Apr
        • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft24 Apr
          • RE: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyLarry McGhaw25 Apr
            • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft25 Apr
            • RE: WHERE does not work on calculated view field - Foundword(s) list error in the Text bodyhsv26 Apr
              • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft26 Apr
  • Re: WHERE does not work on calculated view fieldCarsten Pedersen22 Apr
    • Re: WHERE does not work on calculated view fieldDaniel Kraft22 Apr
      • Re: WHERE does not work on calculated view fieldars k23 Apr
        • Re: WHERE does not work on calculated view fieldDaniel Kraft23 Apr