SQLite test output is empty when result column is null

Programming SQLite
1

If the solution query fails, the result column can be set to NULL. When the column is NULL, the output message is NULL.

Our test files use the following to format the test message.

-- Update message for failed tests to give helpful information:
UPDATE tests
SET message = 'Result for "' || tests.moment || '" is "' || actual.result || '", but should be "' || tests.result || '"'
FROM (SELECT moment, result FROM gigasecond) AS actual
WHERE actual.moment = tests.moment AND tests.status = 'fail';

For example, with a NULL column:

sqlite> SELECT 'Result for "' || tests.moment || '" is "' || actual.result || '", but should be "' || tests.result || '"' FROM gigasecond AS actual INNER JOIN
tests WHERE tests.moment = actual.moment LIMIT 1;
| 'Result for "' || tests.moment || '" is "' || actual.result || '", but should be "' || tests.result || '"' |
|------------------------------------------------------------------------------------------------------------|
|                                                                                                            |

Proposal: change NULL to "<NULL>":

UPDATE gigasecond SET result = '<NULL>' WHERE result IS NULL;

If we add something like that to our test files, then the formatting works.

sqlite> SELECT 'Result for "' || tests.moment || '" is "' || actual.result || '", but should be "' || tests.result || '"' FROM gigasecond AS actual INNER JOIN
tests WHERE tests.moment = actual.moment LIMIT 1;
| 'Result for "' || tests.moment || '" is "' || actual.result || '", but should be "' || tests.result || '"' |
|------------------------------------------------------------------------------------------------------------|
| Result for "2011-04-25" is "<NULL>", but should be "2043-01-01T01:46:40"                                   |

@glennj @vaeng Thoughts?

2

One alternative would be to use a conditional IF result IS NULL but it is too late at night for me to figure out that syntax. Pros: doesn’t update the table. Cons: more complex.

3

I don’t understand when this is happening. Is <NULL> a placehodler or some sqlite syntax?

4

"<NULL>" is an arbitrary string that can be used in an output message. “Got <NULL>”.

NULL is a special value which, when concatenated with a string, results in NULL, and results in no message.

5

Ah, I got the issue. So we update to <NULL> for display reasons.

I like it for readability. I am not sure if that would be confusing for “real sql” devs? Is there someone deep into the language who could drop in?

6

Opposed to using … "NULL" over "\<NULL\>" in the error message? Or opposed to using an empty string? Or sticking with an actual NULL value and not displaying anything?

7

I support the proposal, but I would like to hear someones opinion who actually works with SQL, as I am just a hack who made that language work with the Exercism test runner without every using it an a real context :smiley:

8

… you realize that people who “really work with SQL” aren’t doing all the wacky things we do with it in Exercism, right? :smiley: Is the concern that the string "\<NULL\>" would be confusing when the actually value is a NULL value?

9

I prefer having “readable, but obviously not student-created” replacements for any invisible result. <NULL> is perfect here.

PS: Hunting NULL in real life SQL answers is like hunting NPEs in any other language: you welcome any help provided!

10

It’s about 10am currently so … let’s not modify the student’s test results table :slight_smile:

The reporting expression:

...
  SET message =  'Result for "' 
  || tests.moment 
  || '" is "' 
-  || actual.result 
+ || (CASE WHEN actual.result IS NULL THEN "<NULL>" ELSE actual.result END)
  || '", but should be "' 
  || tests.result 
  || '"' FROM gigasecond AS actual 
  INNER JOIN
    tests WHERE tests.moment = actual.moment
    ...
11

See Rewrite test result message by IsaacG · Pull Request #92 · exercism/sqlite · GitHub

12

See my suggested change. This is exactly what COALESCE is for!

2 Likes
13

Well, I’ll be darned. That does make life easier … aside from the bit about having to redo the PR :laughing:

Thanks! I’ll try to push an update in the day or two.

2 Likes