subject:"Re\: WHERE does not work on calculated view field"

RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body

2011-04-26 Thread Hal�sz S�ndor

 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

2011-04-26 Thread Daniel Kraft


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 Thread Larry McGhaw

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

2011-04-25 Thread 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=arch...@jab.org

Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body

2011-04-24 Thread 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

Re: WHERE does not work on calculated view field

2011-04-23 Thread ars k

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...@domob.eu 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=daffodil...@gmail.com

Re: WHERE does not work on calculated view field

2011-04-23 Thread 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 Kraftd...@domob.eu  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=daffodil...@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=arch...@jab.org

RE: WHERE does not work on calculated view field

2011-04-22 Thread 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=arch...@jab.org

Re: WHERE does not work on calculated view field

2011-04-22 Thread 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=arch...@jab.org

Re: WHERE does not work on calculated view field

2011-04-22 Thread 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=arch...@jab.org

Re: WHERE does not work on calculated view field

2011-04-22 Thread 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=arch...@jab.org

RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body

2011-04-22 Thread 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 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

Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body

2011-04-22 Thread 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=arch...@jab.org

RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body

Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body

RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body

Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body

Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body

Re: WHERE does not work on calculated view field

Re: WHERE does not work on calculated view field

RE: WHERE does not work on calculated view field

Re: WHERE does not work on calculated view field

Re: WHERE does not work on calculated view field

Re: WHERE does not work on calculated view field

RE: WHERE does not work on calculated view field - Found word(s) list error in the Text body

Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body

13 matches

Site Navigation

Mail list logo

Footer information