It does appear to be some type of bug to me. 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=lmcg...@connx.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
