Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body
On 04/26/11 05:32, Halász Sándor wrote: 2011/04/25 18:45 +, Larry McGhaw 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`; Well, for this construct IF(b.`Name` IS NULL, '', b.`Name`) there is a special function IFNULL: IFNULL(b.`Name`, '') It has the same special type-treatment that IF has. Wow, thanks! I wasn't aware of it, but this looks helpful in my case (at least simplifying those expressions)! 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
RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body
2011/04/25 18:45 +, Larry McGhaw 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`; Well, for this construct IF(b.`Name` IS NULL, '', b.`Name`) there is a special function IFNULL: IFNULL(b.`Name`, '') It has the same special type-treatment that IF has. I quite often use it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body
Hi, On 04/25/11 20:45, Larry McGhaw wrote: 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`; now you mention it, it seems obvious -- but I didn't think about that solution before... But 'inlining' my function this way seems to fix the problem also in my real application. Thanks a lot! 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
RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body
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...@domob.eu] Sent: Sunday, April 24, 2011 2:03 PM To: Larry McGhaw Cc: Daevid Vincent; mysql@lists.mysql.com 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...@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
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...@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
Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body
On 22.04.2011 22:41, Larry McGhaw wrote: It does appear to be some type of bug to me. I agree. I was thrown by Daniels "first and third" comment, which I guess should read "second and third" I reproduced the behavior in 5.1.53-community on Windows. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body
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...@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=lmcg...@connx.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org