Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body

[Daniel Kraft]

On 04/22/11 22:41, Larry McGhaw wrote:
> It does appear to be some type of bug to me.

Hm... do you have an idea how to work around this bug then?

Yours,
Daniel

>
> Clearly from the select, the Typename field is not null, as shown here.
>
> mysql>  SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview`;
> +----+------+----------+------------------+----------------------+
> | ID | Type | TypeName | TypeName Is NULL | TypeName IS NOT NULL |
> +----+------+----------+------------------+----------------------+
> |  1 | NULL |          |                0 |                    1 |
> +----+------+----------+------------------+----------------------+
> 1 row in set (0.00 sec)
>
> But when referenced in the where clause in any manner, no results are returned.
>
> mysql>  SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview` where TYPE
> NAME IS NOT NULL;
> Empty set (0.00 sec)
>
>
> -----Original Message-----
> From: Daniel Kraft [mailto:d@stripped]
> Sent: Friday, April 22, 2011 1:05 PM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: Re: WHERE does not work on calculated view field - Found word(s) list error
> in the Text body
>
> 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