Hi Carsten,
On 04/22/11 22:11, Carsten Pedersen wrote:
> On 22.04.2011 21:37, Daniel Kraft 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!
>
> Hint: What's the output of "SELECT * FROM `myview`?
I get:
mysql> select * from myview;
+----+------+----------+
| ID | Type | TypeName |
+----+------+----------+
| 1 | NULL | |
+----+------+----------+
1 row in set (0.00 sec)
mysql> select *, `TypeName` IS NOT NULL from myview;
+----+------+----------+------------------------+
| ID | Type | TypeName | `TypeName` IS NOT NULL |
+----+------+----------+------------------------+
| 1 | NULL | | 1 |
+----+------+----------+------------------------+
1 row in set (0.00 sec)
Should this tell me something? To me, it looks as expected and fine.
Cheers,
Daniel
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
