ISDATE working but ISNULL not working (as expected)?

I'm new to CFML so please excuse my ignorance.
I'm experimenting with ISDATE and ISNULL on a simple mysql table which as some NULL dates:
-- ----------------------------
-- Table structure for `temp`
-- ----------------------------
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp` (
  `id` int(11) NOT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of temp
-- ----------------------------
INSERT INTO `temp` VALUES ('1', null);
INSERT INTO `temp` VALUES ('2', '2011-04-13');
INSERT INTO `temp` VALUES ('3', null);
INSERT INTO `temp` VALUES ('4', '2011-04-21');
INSERT INTO `temp` VALUES ('5', null);
INSERT INTO `temp` VALUES ('6', '2011-04-24');
INSERT INTO `temp` VALUES ('7', null);
INSERT INTO `temp` VALUES ('8', '2011-04-28');
INSERT INTO `temp` VALUES ('9', null);

Open in new window

I then run the following in CFML:
<cfquery name="testing">select id, date from temp</cfquery>
<p>Temp table:</p>
<cfoutput query="testing">
#id# IsDate(date)=#IsDate(date)# IsNull(date)=#IsNull(date)#<br />
</cfoutput>

Open in new window

and get the following output:
Temp table:

1 IsDate(date)=NO IsNull(date)=YES
2 IsDate(date)=YES IsNull(date)=YES
3 IsDate(date)=NO IsNull(date)=YES
4 IsDate(date)=YES IsNull(date)=YES
5 IsDate(date)=NO IsNull(date)=YES
6 IsDate(date)=YES IsNull(date)=YES
7 IsDate(date)=NO IsNull(date)=YES
8 IsDate(date)=YES IsNull(date)=YES
9 IsDate(date)=NO IsNull(date)=YES

Open in new window

ISDATE works correctly, giving NO for NULL dates and YES for real dates.
What I do not understand is why ISNULL gives YES for all items -- I was expecting the opposite of ISDATE -- YES for NULL dates and NO for real dates.
What have I missed?
ShanghaiDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you please try to `date` instead of date in the query
0
ShanghaiDAuthor Commented:
select `id`, `date` from `temp`

still gives same result.......
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
<cfquery name="testing">select id, date, IsNull(date) is_null, IsDate(date) is_date from temp</cfquery>
<p>Temp table:</p>
<cfoutput query="testing">
#id# IsDate(date)=#Is_Date# IsNull(date)=#Is_Null#<br />
</cfoutput>

Open in new window

0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

ShanghaiDAuthor Commented:
No,, that throws an error.  Function IsDate is a CFML function, not a MySQL function, so it cannot run in the cfquery tag.  
I have already confirmed that the odd id date values are NULL in MySQL, just to be sure, and running (in MySQL) simply:
SELECT id, date from temp
results in:

1      (null)
2      2011-04-13
3      (null)
4      2011-04-21
5      (null)
6      2011-04-24
7      (null)
8      2011-04-28
9      (null)
(as it should).

0
ShanghaiDAuthor Commented:
I also ran a simple cfdump of the query and got (as expected):
 <cfdump var="#testing#" abort="true">
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I am not a CF specialist, actually (rather mysql)
<cfquery name="testing">select id, date, IsNull(date) is_null from temp</cfquery>
<p>Temp table:</p>
<cfoutput query="testing">
#id# IsDate(date)=#IsDate(date)# IsNull(date)=#Is_Null#<br />
</cfoutput>

Open in new window

0
ShanghaiDAuthor Commented:
Your revised query gives correct output but it uses the MySQL IsNull function (in the query) rather than the CFML ISNULL function in handling the results of the query.

It seems to me that the CFML ISNULL function is treating the "empty string" as something other than a Null, and so is not showing output that I had expected.

I'll do some more research tomorrow at work (it's evening now in Shanghai).

Thanks for you input already.
0
gdemariaCommented:
You have to think of CF's new isNull() function much like  structKeyExists()

It is not testing to see if a database "NULL" value is NULL, coldfusion always handles NULL from the database as an empty string.  isNull() is not testing for empty strings, it is testing for the non-existance of a variable; such as IsDefined() and structkeyexists()

If you want to test if something is empty, the most efficient way is to use len()

<CFIF len(myQuery.value)>
   There is a value
<cfelse>  
   It is empty
</cfif>


Here is more reading...

http://www.bennadel.com/blog/1654-Learning-ColdFusion-9-IsNull-And-Working-With-NULL-Values.htm

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShanghaiDAuthor Commented:
Thank you for your insight. I learn something new about CFML every day!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.