> -----Original Message----- > From: Daniel Kraft [mailto:d...@domob.eu] > Sent: Friday, April 22, 2011 12:37 PM > To: mysql@lists.mysql.com > Subject: WHERE does not work on calculated view field > > Hi all, > > I'm by no means a (My)SQL expert and just getting started working with > VIEWs and stored procedures, and now I'm puzzled by this behaviour: > > 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 '%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
