cognitive tools

The most important cognitive tool that I need is the one that can help me capture abstract ideas.

Last month, my client's engineer needed to develop an SQL code snippet with the following functionalities:

• Convert a specific type of string into a date. For example, 1900-10-01 should be converted into a date.

• If the conversion fails, return the current date.

Since the client's data warehouse was Oracle and I wasn't very proficient with Oracle SQL, I wrote the following code using DuckDB SQL and then asked a generative AI to help translate it into Oracle SQL.

SELECT
  id,
  coalesce(try_strptime(t, '%Y-%m-%d'), current_date())
FROM
  table_name;

The generative AI translated it into:

SELECT
  id, 
  CASE
    WHEN TO_DATE(t, 'YYYY-MM-DD') IS NOT NULL 
      THEN TO_DATE(t, 'YYYY-MM-DD')
      ELSE SYSDATE
  END AS t,
FROM table_name;

This translation didn't work because DuckDB's try_strptime returns NULL instead of throwing an exception when encountering a string that can't be converted to a date, such as 2024-02-31. However, Oracle's TO_DATE throws an exception in such cases.

I complained to the generative AI, pointing out that the code that throws an exception instead of returning a NULL makes it unable to execute smoothly. The generative AI then proposed another solution: designing a user-defined function (UDF) for the data warehouse.

CREATE OR REPLACE FUNCTION try_strptime(t IN VARCHAR2) RETURN DATE IS
  parsed_date DATE;
BEGIN
  BEGIN
    parsed_date := TO_DATE(t, 'YYYY-MM-DD');
  EXCEPTION
    WHEN OTHERS THEN
      parsed_date := SYSDATE;
  END;
  RETURN parsed_date;
END;

A user-defined function is the last resort I would like to use because it increases extra maintenance costs. 

After looking up on Stack Overflow, I found that we can modify Oracle's TO_DATE to return NULL instead of throwing an exception. In other words, there is a solution that does not require a user-defined function.

TO_DATE(t default NULL on conversion error, 'YYYY-MM-DD')

The principle underlying

This SQL example above manifests the principle, ”Pull Complexity Downwards,” from the book, A Philosophy of Software Design. When Oracle's TO_DATE provides syntax like "default xxx on conversion error," the implementation of the TO_DATE function becomes much more complex. However, this complexity is hidden within Oracle (the lower level), and users hardly feel it. At the same time, what users do experience is, "I no longer need to write a user-defined function for this situation, so it's much simpler" (the upper level).

My reflection: cognitive tools

This year, I read the book A Philosophy of Software Design by John Ousterhout. Since I read that book, I have found myself quoting the content from it 4 times within 5 months. 

This unusually high frequency of my quoting from a book makes me think that what is most different about this book. I believe it is because this book provides me with cognitive tools that help my mind capture abstract ideas, patterns, and clues that I previously felt difficult to identify, analyze, and communicate. 

There is another book written by Peter Drucker gives me a similar feeling: Innovation and Entrepreneurship. 

Both books focus on abstract and tacit ideas. 

• Ousterhout talks about software abstraction.

• Drucker talks about innovation.

Both books provide multiple principles to capture the same idea, and many times looking at the same idea from a different principle is just like looking at the same scene from a different window.

For me, capturing abstract ideas with a multi-principle approach serves as a great cognitive tool.