WHERE does not work on calculated view field
WHERE does not work on calculated view field
am 22.04.2011 21:37:14 von Daniel Kraft
Hi all,
I'm by no means a (My)SQL expert and just getting started working with
VIEWs and stored procedures, and now I'm puzzled by this behaviour:
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!
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=gcdmg-mysql-2@m.gmane.org
RE: WHERE does not work on calculated view field
am 22.04.2011 21:39:42 von Daevid Vincent
> -----Original Message-----
> From: Daniel Kraft [mailto:d@domob.eu]
> Sent: Friday, April 22, 2011 12:37 PM
> To: mysql@lists.mysql.com
> Subject: WHERE does not work on calculated view field
>
> Hi all,
>
> I'm by no means a (My)SQL expert and just getting started working with
> VIEWs and stored procedures, and now I'm puzzled by this behaviour:
>
> 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 '%';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: WHERE does not work on calculated view field
am 22.04.2011 22:05:13 von Daniel Kraft
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=gcdmg-mysql-2@m.gmane.org
Re: WHERE does not work on calculated view field
am 22.04.2011 22:11:36 von Carsten Pedersen
On 22.04.2011 21:37, Daniel Kraft wrote:
> Hi all,
>
> I'm by no means a (My)SQL expert and just getting started working with
> VIEWs and stored procedures, and now I'm puzzled by this behaviour:
>
> 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`?
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: WHERE does not work on calculated view field
am 22.04.2011 22:20:20 von Daniel Kraft
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=gcdmg-mysql-2@m.gmane.org
RE: WHERE does not work on calculated view field - Found word(s)list error in the Text body
am 22.04.2011 22:41:01 von Larry McGhaw
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 retur=
ned.
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]=20
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) l=
ist 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` =3D 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
--=20
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dlmcghaw@connx.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
Re: WHERE does not work on calculated view field - Found word(s)list error in the Text body
am 22.04.2011 22:52:21 von Carsten Pedersen
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=gcdmg-mysql-2@m.gmane.org
Re: WHERE does not work on calculated view field
am 23.04.2011 11:33:04 von ars k
--20cf3071cc004ee86104a192a9d0
Content-Type: text/plain; charset=ISO-8859-1
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 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=daffodils4u@gmail.com
>
>
--20cf3071cc004ee86104a192a9d0--
Re: WHERE does not work on calculated view field
am 23.04.2011 11:41:16 von Daniel Kraft
Hi,
thanks for the answer!
On 04/23/11 11:33, ars k wrote:
> 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`*;
> "
Hm, no, I don't think so -- what I want to achieve is to link the
Type-field (as index) in mytable to the corresponding entry in types, so
that I can get the name of this type (and other data in my real
application of course).
What you propose compares the ID of entries in mytable (customers, say)
to IDs of types, which doesn't make much sense to me. Or did I get this
wrong? (As I said, I'm more of less learning-by-doing and no expert!)
Yours,
Daniel
>
> 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 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=daffodils4u@gmail.com
>>
>>
>
--
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=gcdmg-mysql-2@m.gmane.org
Re: WHERE does not work on calculated view field - Found word(s)list error in the Text body
am 24.04.2011 23:03:22 von 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=gcdmg-mysql-2@m.gmane.org
RE: WHERE does not work on calculated view field - Found word(s)list error in the Text body
am 25.04.2011 20:45:12 von Larry McGhaw
Hello Daniel,
My best advice is to not use a custom MySQL function in a view when the par=
ameter to that function
is a column or expression that has the potential to result in NULL because =
of being on the right side=20
of a left outer join (or the left side of a right outer join). This parti=
cular set of circumstances=20
seems to cause MySQL to treat the resulting expression as "unknown" when us=
ed in a where clause on the view
itself - such that any use of the expression in the where clause will evalu=
ate 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` =3D b.`ID`;
Thanks
lm
-----Original Message-----
From: Daniel Kraft [mailto:d@domob.eu]=20
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) l=
ist 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 ret=
urned.
>
> mysql> SELECT *, TypeName Is NULL, TypeName IS NOT NULL FROM `myview` wh=
ere 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` =3D 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 claus=
e'
>
> What would be the difference? (I've never used HAVING before.)
>
> Yours,
> Daniel
>
--=20
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=3Dgcdmg-mysql-2@m.gmane.o rg
Re: WHERE does not work on calculated view field - Found word(s)list error in the Text body
am 25.04.2011 21:33:53 von Daniel Kraft
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=gcdmg-mysql-2@m.gmane.org
RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body
am 26.04.2011 05:32:18 von (Halász Sándor) hsv
>>>> 2011/04/25 18:45 +0000, 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=gcdmg-mysql-2@m.gmane.org
Re: WHERE does not work on calculated view field - Found word(s)list error in the Text body
am 26.04.2011 19:03:23 von Daniel Kraft
On 04/26/11 05:32, Halász Sándor wrote:
>>>>> 2011/04/25 18:45 +0000, 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=gcdmg-mysql-2@m.gmane.org