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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
