List:General Discussion« Previous MessageNext Message »
From:Daniel Kraft Date:April 22 2011 7:37pm
Subject:WHERE does not work on calculated view field
View as plain text  
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!

Yours,
Daniel

-- 
http://www.pro-vegan.info/
--
Done:  Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
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