List:General Discussion« Previous MessageNext Message »
From:Larry McGhaw Date:April 22 2011 8:41pm
Subject:RE: WHERE does not work on calculated view field - Found word(s)
list error in the Text body
View as plain text  
It does appear to be some type of bug to me.

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

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

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