List:General Discussion« Previous MessageNext Message »
From:Carsten Pedersen Date:April 22 2011 8:11pm
Subject:Re: WHERE does not work on calculated view field
View as plain text  
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
Thread
WHERE does not work on calculated view fieldDaniel Kraft22 Apr
  • RE: WHERE does not work on calculated view fieldDaevid Vincent22 Apr
    • Re: WHERE does not work on calculated view fieldDaniel Kraft22 Apr
      • RE: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyLarry McGhaw22 Apr
        • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyCarsten Pedersen22 Apr
        • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft24 Apr
          • RE: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyLarry McGhaw25 Apr
            • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft25 Apr
            • RE: WHERE does not work on calculated view field - Foundword(s) list error in the Text bodyhsv26 Apr
              • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft26 Apr
  • Re: WHERE does not work on calculated view fieldCarsten Pedersen22 Apr
    • Re: WHERE does not work on calculated view fieldDaniel Kraft22 Apr
      • Re: WHERE does not work on calculated view fieldars k23 Apr
        • Re: WHERE does not work on calculated view fieldDaniel Kraft23 Apr