1

I am scheming how to implement a type generator for arbitrary SQL queries.

I found that for any query, the simplest way to get the types of every column is to create a temporary table and then inspect that table. However, this tells nothing about which columns can be NULL.

I am interested to find a solution to identifying which columns can be NULL for any arbitrary query with the goal of generating types for that query.

What’s the way to go about it?

7
  • Is this so you can generate a type for your consuming application, or will this type solely stay within your SQL server for its lifespan?
    – J.D.
    Commented Jan 8, 2021 at 12:38
  • I don't think you can determine this just by looking at the column definitions. Even a column defined as NOT NULL can be NULL in a result as the result of an outer join
    – user1822
    Commented Jan 8, 2021 at 14:06
  • This shows that the query must have internal knowledge of the server code in order to be able to answer such questions - you're tilting at windmills unfortunately!
    – Vérace
    Commented Jan 8, 2021 at 14:27
  • @J.D. for consumption of the application.
    – Gajus
    Commented Jan 8, 2021 at 19:17
  • @Gajus As others have mentioned, what you're trying to accomplish is unfortunately not realistically possible. It's also not something usually done in the database. This is more so something you should be doing in application code (similar to how ORMs work). Regardless, abstractly speaking, NULL or NOT NULL is a state of a data value snapshotted in time. The columns of a result set are always potentially nullable based on the nondeterministic possibilities that can influence a query, such as how a_horse_with_no_name points out. So there is no deterministic way to accurately define if...
    – J.D.
    Commented Jan 8, 2021 at 21:33

1 Answer 1

2

Since SQL (with recursive CTEs) is Turing complete, figuring out if a column in the result could be NULL or not is probably as complex as the halting problem and cannot be computed.

So either run the query and examine the result or restrict yourself to very simple queries (but even then the problem will be non-trivial).

My personal recommendation is to give up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.