On 22.04.2011 21:37, Daniel Kraft wrote:
> 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!
Hint: What's the output of "SELECT * FROM `myview`?
/ Carsten
