jcw - Can you elaborate? Ratcl has no null. So the nulls you see in your SQL are implied by using both join1 and join0 together.
TJK - First, elaboration. From a theoretical point of view I don't see any way in ratcl to represent a null. When two sets are joined the result is: A~B, AB, ~AB. A left join produces AB, A~B. In ratcl I can produce "AB" (e.g. [A join1 B]) and "A selected on ~B" (e.g. [A join0 B]) but if there isn't anyway to indicate a null entries then how do I combine the results into one table? If I can't combine the results, then each additional table add to the total join will multiple the number of resulting tables by 2. A further problem will be post processing of the values produced by a join. Suppose I want to do a left join on two tables, sort the results on the first column and display the results in a tktable widget. This problem (which is trivial in sql) would be very difficult in ratcl because records containing nulls and no nulls could be interleaved thus requiring special code to implement record recovery in sorted order.
Second, I think it is a good idea to maintain a certain amount of symmetry between ratcl and sql so an interface to sql can eventually be built between them. This is motivated by the idea I sent you in a prior communication.
Not including the concept of a NULL in ratcl is sort of like not having a zero in a numbering system. You can still do the job but it's a lot harder.
jcw - Well... that's a debate of several decades by now :) - Chris Date has an entirely different opinion on NULL. Just as he forbids duplicate rows in a result.
TJK - I'm a EE not a CS major so my knowledge is mostly experience based and not training. I have a copy of the "An Introduction to Database Systems" by Date, it is my favorite reference on databases. I wasn't aware he disliked NULL's. Certainly nulls in key fields and duplicate records make no sense and should not be allowed but nulls from a join can have a significant impact on the complexity of the software (i.e. make it less complex).
FYI, it is also my opinion that full normalization is usually not the best solution to a database problem and a database design that includes a self joins on a table may be elegant but is to dangerous to be used in a production installation. These opinions usually are not well received ;-)
TJK - I realized I didn't make one point clear in my previous email. I don't believe NULL's should be stored in tables in a database. Which means MetaKit doesn't need to handle NULL's. I'm suggesting that the query engine which does calculations on tables should support NULL's. Unfortunately I suspect this fine distinction isn't possible in the current work because there isn't any real distinction between an external (i.e. stored) table and a table that has been calculated.
I think the only solution will be to add an SQL layer that explicitly separates usage from storage. If such a layer is created then arrays can be populated from lower level metakit/racal calls and nulls can be inferred from missing elements in the array.
jcw - I think I understand what you're aiming at, but I'm not sure I understand all the issues well enough yet. I think it would help if we could somehow get some of these summed up and described there. Not as a position statement but in an effort to find a good way forward. <snip>