Hello Daniel, 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`; Thanks lm -----Original Message----- From: Daniel Kraft [mailto:d...@domob.eu] Sent: Sunday, April 24, 2011 2:03 PM To: Larry McGhaw Cc: Daevid Vincent; mysql@lists.mysql.com Subject: Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body On 04/22/11 22:41, Larry McGhaw wrote: > It does appear to be some type of bug to me. Hm... do you have an idea how to work around this bug then? Yours, Daniel > > Clearly from the select, the Typename field is not null, as shown here. > > mysql> SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview`; > +----+------+----------+------------------+----------------------+ > | ID | Type | TypeName | TypeName Is NULL | TypeName IS NOT NULL | > +----+------+----------+------------------+----------------------+ > | 1 | NULL | | 0 | 1 | > +----+------+----------+------------------+----------------------+ > 1 row in set (0.00 sec) > > But when referenced in the where clause in any manner, no results are > returned. > > mysql> SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview` where > TYPE > NAME IS NOT NULL; > Empty set (0.00 sec) > > > -----Original Message----- > From: Daniel Kraft [mailto:d...@domob.eu] > Sent: Friday, April 22, 2011 1:05 PM > To: Daevid Vincent > Cc: mysql@lists.mysql.com > Subject: Re: WHERE does not work on calculated view field - Found word(s) > list error in the Text body > > Hi, > > thanks for the fast reply! > > On 04/22/11 21:39, Daevid Vincent wrote: >>> DROP DATABASE `test`; >>> CREATE DATABASE `test`; >>> USE `test`; >>> >>> CREATE TABLE `mytable` >>> (`ID` SERIAL, >>> `Type` INTEGER UNSIGNED NULL, >>> PRIMARY KEY (`ID`)); >>> INSERT INTO `mytable` (`Type`) VALUES (NULL); >>> >>> CREATE TABLE `types` >>> (`ID` SERIAL, >>> `Name` TEXT NOT NULL, >>> PRIMARY KEY (`ID`)); >>> INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B'); >>> >>> DELIMITER | >>> CREATE FUNCTION `EMPTY_STRING` (value TEXT) >>> RETURNS TEXT >>> DETERMINISTIC >>> BEGIN >>> RETURN IF(value IS NULL, '', value); >>> END| >>> DELIMITER ; >>> >>> CREATE VIEW `myview` AS >>> SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName` >>> FROM `mytable` a >>> LEFT JOIN `types` b ON a.`Type` = b.`ID`; >>> >>> SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL; >>> SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL; >>> SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%'; >>> >>> (I tried to simplify my problem as far as possible.) When I run this >>> against MySQL 5.0.24a, I get three times "0" as output from the SELECTs >>> at the end -- shouldn't at least one of them match the single row? >>> (Preferably first and third ones.) >>> >>> What am I doing wrong here? I have no clue what's going on... Thanks a >>> lot! >> >> Try this maybe: >> >> SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NULL; >> SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NOT NULL; >> SELECT COUNT(*) FROM `myview` HAVING `TypeName` LIKE '%'; > > When I try those, I get: > > ERROR 1054 (42S22) at line 35: Unknown column 'TypeName' in 'having clause' > > What would be the difference? (I've never used HAVING before.) > > 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