List:General Discussion« Previous MessageNext Message »
From:Larry McGhaw Date:April 25 2011 6:45pm
Subject:RE: WHERE does not work on calculated view field - Found word(s)
list error in the Text body
View as plain text  
Hello Daniel,

My best advice is to not use a custom MySQL function in a view when the parameter to that
function
is a column or expression that has the potential to result in NULL because of being on the
right side 
of a left outer join (or the left side of a right outer join).   This particular set of
circumstances 
seems to cause MySQL to treat the resulting expression as "unknown" when used in a where
clause on the view
itself - such that any use of the expression in the where clause will evaluate to
unknown/false.

As a workaround - this view for example behaves as expected:

CREATE VIEW `myview2` AS
      SELECT a.*, IF(b.`Name` IS NULL, '', b.`Name`) AS `TypeName`
        FROM `mytable` a
          LEFT JOIN `types` b ON a.`Type` = b.`ID`;

Thanks

lm



-----Original Message-----
From: Daniel Kraft [mailto:d@stripped] 
Sent: Sunday, April 24, 2011 2:03 PM
To: Larry McGhaw
Cc: Daevid Vincent; mysql@stripped
Subject: Re: WHERE does not work on calculated view field - Found word(s) list error in
the Text body

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
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