List:General Discussion« Previous MessageNext Message »
From:ars k Date:April 23 2011 9:33am
Subject:Re: WHERE does not work on calculated view field
View as plain text  
Hi Daniel,
Could you check the 'myview' once again? I think you thought to create the
view as follows:

" CREATE VIEW `myview2` AS  SELECT a.*, EMPTY_STRING(b.`Name`) AS
`TypeName`    FROM `mytable` a      LEFT JOIN `types` b ON *a.ID* *= b.`ID`*;
"

Now your select queries will give results as follows:

mysql> SELECT COUNT(*) FROM `myview2` WHERE `TypeName` LIKE '%';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NOT NULL;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NULL;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Regards,
Vinodh.k
MySQL DBA


On Sat, Apr 23, 2011 at 1:50 AM, Daniel Kraft <d@stripped> wrote:

> Hi Carsten,
>
>
> On 04/22/11 22:11, Carsten Pedersen wrote:
>
>> On 22.04.2011 21:37, Daniel Kraft 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!
>>>
>>
>> Hint: What's the output of "SELECT * FROM `myview`?
>>
>
> I get:
>
> mysql> select * from myview;
> +----+------+----------+
> | ID | Type | TypeName |
> +----+------+----------+
> |  1 | NULL |          |
> +----+------+----------+
> 1 row in set (0.00 sec)
>
> mysql> select *, `TypeName` IS NOT NULL from myview;
> +----+------+----------+------------------------+
> | ID | Type | TypeName | `TypeName` IS NOT NULL |
> +----+------+----------+------------------------+
> |  1 | NULL |          |                      1 |
> +----+------+----------+------------------------+
> 1 row in set (0.00 sec)
>
> Should this tell me something?  To me, it looks as expected and fine.
>
> Cheers,
>
> 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