List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 22 2011 7:39pm
Subject:RE: WHERE does not work on calculated view field
View as plain text  

> -----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 '%';

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