SQL SERVER – A Puzzle – Fun with NULL – Fix Error 8117

During my 8 years of career, I have been involved in many interviews. Quite often, I act as the  interview. If I am the interviewer, I ask many questions – from easy questions to difficult ones. When I am the interviewee, I frequently get an opportunity to ask the interviewer some questions back. Regardless of the my capacity in attending the interview, I always make it a point to ask the interviewer at least one question. Let’s learn how to fix Error 8117.

What is NULL?

It’s always fun to ask this question during interviews, because in every interview, I get a different answer. NULL is often confused with false, absence of value or infinite value. Honestly, NULL is a very interesting subject as it bases its behavior in the server settings. There are a few properties of NULL that are universal, but the knowledge about these properties is not known in a universal sense.

Let us run this simple puzzle. Run the following T-SQL script:

SELECT SUM(data)
FROM (SELECT NULL AS data) t

It will return the following error:

Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

SQL SERVER - A Puzzle - Fun with NULL - Fix Error 8117 8117error

Now the error makes it very clear that NULL is invalid for sum Operator. Frequently enough, I have shown this simple query to many folks whom I came across. I asked them if they could modify the subquery and return the result as NULL. Here is what I expected:

SQL SERVER - A Puzzle - Fun with NULL - Fix Error 8117 8117error1

Even though this is a very simple looking query, so far I’ve got the correct answer from only 10% of the people to whom I have asked this question. It was common for me to receive this kind of answer – convert the NULL to some data type. However, doing so usually returns the value as 0 or the integer they passed.

SELECT SUM(data)
FROM (SELECT ISNULL(NULL,0) AS data) t

SQL SERVER - A Puzzle - Fun with NULL - Fix Error 8117 8117error2

I usually see many people modifying the outer query to get desired NULL results, but that is not allowed in this simple puzzle. This small puzzle made me wonder how many people have a clear understanding about NULL.

Well, here is the answer to my simple puzzle. Just CAST NULL AS INT and it will return the final result as NULL:

SELECT SUM(data)
FROM (SELECT CAST(NULL AS INT) AS data) t

SQL SERVER - A Puzzle - Fun with NULL - Fix Error 8117 8117error3

Now that you know the answer, don’t you think it was very simple indeed? This blog post is especially dedicated to my friend Madhivanan who has written an excellent blog post about NULL. I am confident that after reading the blog post from Madhivanan, you will have no confusion regarding NULL in the future.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL NULL, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Saturday Fun Puzzle with SQL Server DATETIME2 and CAST
Next Post
SQL SERVER – A Puzzle – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value

Related Posts

12 Comments. Leave new

  • select sum(data)
    from (select 1+ null as data)t

    Reply
  • I Did it like this

    SELECT SUM(Data) FROM (SELECT NULL+1 AS Data ) t

    it worked..Whata play with null;sir

    –Kamesh

    Reply
  • Thank you Pinal for this post

    Reply
  • Casting null as some datatype, I didn’t knew that.

    unable to solve this puzzle first answer that came to my mind was this ;)

    SELECT SUM(data)
    FROM (SELECT 1 as data where 1=2) t

    After reading the correct answer I wont use this. Thanks Sir.

    Reply
    • Marek Golunski
      May 26, 2012 12:51 am

      Hehe :)
      I had the same idea: subquery should have 0 rows, I wrote:

      SELECT SUM(data)
      FROM (SELECT CASE WHEN 0>1 THEN 1 END AS data) AS t

      Reply
  • There are some good stoies from my ANSI X3H2 committtee days, The conflict is that SQL is strongly typed and NULL offically has no type. This is why we prefer CAST(NULL AS ) to get an explicit NULL in a column.

    Reply
  • SELECT SUM(data)
    FROM (SELECT cast(NULL as decimal) AS data) t

    Reply
  • Sudhanshu Verma
    May 25, 2012 4:50 pm

    null is noting but its roll is very valuable in sql.

    thanks

    Reply
  • select sum(data)
    from (select nullif(0, 0) as data) t

    Reply
  • select (10-NULL), the result is NULL ,
    Can any one explain how this happens

    Reply
    • Any arithmetic operation done on NULL is always NULL. Read this to know more about NUL Lhttp://beyondrelational.com/modules/2/blogs/70/posts/14865/null-null-null-and-nothing-but-null.aspx

      Reply

Leave a Reply