Re: WHERE does not work on calculated view field

[Daniel Kraft]

Hi,

thanks for the fast reply!

On 04/22/11 21:39, Daevid Vincent 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!

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

When I try those, I get:

ERROR 1054 (42S22) at line 35: Unknown column 'TypeName' in 'having clause'

What would be the difference?  (I've never used HAVING before.)

Yours,
Daniel

--
http://www.pro-vegan.info/
--
Done:  Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org