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...@domob.eu]
Sent: Friday, April 22, 2011 1:05 PM
To: Daevid Vincent
Cc: mysql@lists.mysql.com
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=arch...@jab.org