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:[email protected]]
Sent: Sunday, April 24, 2011 2:03 PM
To: Larry McGhaw
Cc: Daevid Vincent; [email protected]
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:[email protected]]
> Sent: Friday, April 22, 2011 1:05 PM
> To: Daevid Vincent
> Cc: [email protected]
> 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/[email protected]
