> -----Original Message-----
> From: Daniel Kraft [mailto:d@stripped]
> Sent: Friday, April 22, 2011 12:37 PM
> To: mysql@stripped
> 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 '%';
