Live CEOing Ep 795: Language Design in the Wolfram Language [Tabular]

Okay. Hello, everyone. Welcome to another Wolfram language design review. This is actually part of what we're planning for version 14.1. It's actually a fairly large project that we've been talking about for a while, but for various reasons we have not been doing that in these live streams. So this time we are doing it in live stream. So let's see. Okay, so look, Join across. You know, I was thinking of the name column wise combine.

Sorry, I was muted. That is in the agenda.

This is.

That's actually, we compartmentalize a small set of updates that will kind of bring up to speed the functionality of Join across and then we can discuss the main. The main item on the agenda.

I'm almost not prepared to talk about Join across because I find it so confusing. Can we start from what I think will actually be the user level functions?

Okay.

I mean, yeah, I think it will be difficult to understand the NRE function without nailing the binary function first, but I don't agree.

I mean. Okay, let's look at the NRA function. Okay.

Okay, so we have. Those were the row notes from last time, but actually Rob has an implementation. So the next section is what didn't make to today's tabular. But this can actually show some stuff about what was floated in the previous meeting.

Okay, so this is automatically noticing the common columns or this is.

Yes, it automatically detects the common. What. What columns are in common and it joins using those common columns.

Okay, that's nice.

All of the common columns.

Well, so look at the tabulars above the common columns in this case are A and B. I mean, I tried.

To keep it simple, but what if there were pairwise? Okay, what if. What if two things had C and the third one didn't? That's still. Okay.

Just.

You just join that. Yeah, well, you're talking about doing that. Yes, not by.

Not automatically.

I see. So the automatic behavior would be only join the things that are in common to all of the specified tabular.

Exactly. And everything, basically everything for this applies to things that are common to all the tabular until we get to the same test section which allows some more differences.

Okay. Okay. So by default what it's doing is it's filling in missings in the places where, you know, it's. It's doing the outer joining thing.

Yes. The default is the outer join, which. Which both you and Christopher said you thought that was the right thing.

I agree. I think it's the right thing to do. I think it's easy to prune this and Hard to un, you know, to unprune it.

Yep.

Sometimes a little weird, but it's quite, you know, makes. Makes sense here. Why. I don't understand why does.

Okay, so. So those tables all have a B column. So when you merge only on A, the B columns are going to be different. If you look a little bit below, I renamed the B's, so you can now see what the B's.

Wait a minute. The B's were not combinable because the bees weren't the same or what? Because their content wasn't the same.

The content wasn't the same. So the join for A, the content. If you look a little bit below, I change the names of B and you'll see how the content is.

Okay, but the error message is not right here. Because if the content of B was the same, you'd still be able to do this join, wouldn't you?

I think it's just the content isn't guaranteed to be the same.

The content is not guaranteed to be the same.

Okay, but true. So what if the content is the same? Why not go ahead and do the join?

That's difficult. That's going to be hard to detect in. In generality, but we might be able to detect it.

I think it would be very weird to do if you're joining on A and it just by coincidence B happens to.

You're right, you're right. Okay, but. But I don't like this message. I mean, for goodness sake.

Oh, no, I know that's an internal thing, Stephen. That's the prototype of a prototype.

This is a prototype of a prototype. And that's something that's missing from the whole tabular stuff is a proper system for error handling and, and handling failures and stuff like that. And that's going to take, you know, some time to develop and put together. So.

So I know in this case we'll just have a message that basically says B is a duplicated. You know, B is a duplicated column. Do you want to join on that column or not? And if you.

Yeah, okay, well, but scroll down to see how you work around it.

Yeah. See this is, this is one workaround is this. So I, I rename the columns so now you can see that the bees have different, different contents.

This is quite.

So here you worked around it by. Just got. Getting rid of collisions by renaming the columns before. Right, right.

I'm doing. Because I'm. I'm trying to, I'm trying to keep it. Right now I'm just trying to do the list syntax and Next is the association syntax where we automatically put the prefixes on.

This is pretty nice, in my opinion. Yeah.

Yeah. Actually, here, Here's a random technical question. When you run something like that, rename columns, is it copying the data?

No, it's just. It's. It's. It's. So. So they get slightly technical. So the tabular column.

Yeah, okay.

Is. Is. It's. It's a ref. Kind of thing, just like an expert, but instead of an expert, that represents a single thing, it represents a whole column. So those are moved around and basically the reference counts change and where the reference changes, but nothing else is copied.

Okay. So the tabular column doesn't, for example, care what it's. The name is not represented in the tab.

The name is independent. The name is interdependent of the column itself.

Right. Because that's in the header metadata and the actual column is effectively a tabular column object. Is that true?

Yes, exactly.

Okay, nice.

I don't know what this horrible thing is here, but anyway.

Well, this. It's just. It's just a replacement rule on the column name.

It's renaming all the columns.

Yeah, I see. Okay. Okay. Okay. Prefixed.

Right.

This is pretty nice.

So. So the idea I chose here, whether it's right or not, and that. That's up to some discussion, is so it chooses the column columns, which are A and B, but it doesn't prefix those.

Because that's the right thing.

Yeah. So it doesn't prefix them because there's no collision or. No, because they're being right.

They're in common. So what would it be common with it?

Precisely. Okay, now you can get them prefix, as I show below. So here, here you're doing just on column B, and it alone is not prefixed.

Right.

And sorry, what was the syntax we talked about for if you wanted to. To like, suppose there was a column E or something that isn't colliding. Is there a way of getting it so that E doesn't get prefixed or something?

We don't have a syntax for that.

Okay. I think it's kind of an edge case, but. Yeah.

Right. Well, you could.

What.

Once I have the prefix key, the. The extended key thing implemented, so you can just use the last column. You can refer to that E column just by E and it will look it up properly.

No, I understand, but I. I think. Right. It'd be nice to not have to prefix the things. I wonder whether there's any way we can do that.

I could do that. Well, I mean, it would Take. It would take some work, but. But it could definitely be.

But how would you do it with the association syntax? You. You can't, you know, you couldn't have a none arrow because that will collide.

Right.

Well, I think it would just. It would have to just be automatic also, actually.

Sorry, you can only really have one. Oh, I guess. Well, yeah, you can only have one none. It's only safe to have one none.

Why does this have to be an association rather than a list of rules?

I think that we like the automatic. The fact that you couldn't have two.

The list of rules out above. So the list of rules is here, the one we showed. I showed above. So the association is the one that does the prefixing. That's. That was. That was a design Stephen suggested last week.

I think it's pretty good. I think this is pretty.

There's no problem, like, there's no problem in having two tabular having the same prefix if they all have different columns.

You mean different columns? Maybe what is joining over?

Maybe what the correct thing to do then is something like this. Maybe this works. Maybe it doesn't work. I don't know if this works. Does that work? Is that possible or is that not possible?

That would be possible.

A little annoying with the positional base syntax that. It was suggested as well. Yeah.

Which positional syntax? What?

When you need to decide which one wins, you could say the first one, the second one, the third one.

Well, no, no.

Yeah, but you specify that, and now there's not an equivalent part extraction on the first argument.

Well, there would be. It'd be 2, 2 and 21 and so on. But that's annoying.

Yeah, that would be very annoying.

I mean, he stopped three. The third tabular or the two to two.

Yeah, but look, maybe the thing to do is just have it so that, you know, it does more prefixing than it does a lot of prefixing. And then if you really, for some frankly weird reason, want to make sure two things have the same prefix, you rename columns later.

That's what I was going to suggest to you, Christopher.

How do you rename a prefixed column? What is you would do.

So you'd use. You could use a rule like I had above. So you'd do something like rename columns of extended E of, you know, prefix comma, key.

What it would actually be. Yeah, so this would be rename columns of tabular comma. And then it would be extended key.

Yep.

Of, you know, something like beta.

Yep.

Comma.

X, underscore.

Arrow X. Yep.

Or.

Or arrow extended of alpha comma X and. And. Yeah, delayed.

Yeah, but actually, can it just be X?

It can if the extension is not necessary.

Okay, fine, fine. Okay, so that will just flatten out the whole extension there. Needs good documentation. Yeah, but I think it's a. This is pretty nice. And then you could have things where you have an alternatives there and things like this.

Yeah, no, you can have any pattern and the key is that, that, you know, you get the same number of keys out at the end so you don't get any duplicates.

Yep.

So actually for. For rename columns, is there a. If there's a collision, does it just throw an error or does. Is there like some merging function thing?

It doesn't, it doesn't have any merging thing. I think it's. I mean, my. I think the best thing to do is just to have it throw an error. But it could try to get fancier at some point.

Yeah, right.

I mean, I guess, I guess if you wanted to. And any merging behavior it would be better done with, I don't know, transform columns or whatever the current name for it is, or reduce. Whatever. Yeah, yeah, yeah.

Okay, let's continue.

Okay, so see, in the one below, I've specified B with each tabular. So those bees, even though they happen to be the same or you know, the same in. In positions where they match, you get the whole. The whole B with the prefix.

Oh, I see. So it's just two things which are semi duplicate, but they're left in rather than being squashed out.

Right, right.

Yeah. Nice. Okay.

Now here we're doing B and A, but we're squashing A and then not squashing B.

I get it. Little weird, but I think it's understandable.

Yeah.

Okay. Okay. Oh my gosh, this is all going to be incomprehensible.

Yeah. So. So what I did is you had. You had a possibility of required rows and, and not required rows and optional. And. And at least for this prototype, I decided the fundamental thing was. Was the thing that were, you know, you required having the match in it. Basically you require that. You require that row.

I'm sorry, what do you mean the. Okay, what is the question here? The question is right now we got a bunch of missings. We could as well have something where every time there was going to be a missing, you drop the row. So this is really about which rows. I mean, the way to think about it is this is about which rows should be kept.

Yes, you can think about. That's one way to think about It, Yes.

And I wonder whether we should just have a function that manipulates rows that, I mean, and then we can have, as an optimization, we can have something. But I mean, if we were just, you know, basically, this is like delete missing. You know, there is a function a bit like delete missing that could just, like we have this syntax here that says which columns to take how seriously. In effect, we could have the same thing for, you know, if there's a. If delete. Okay. So the idea would be we would say something like, not a good name, but I mean, delete rows. Okay. And a tabular. And this would say, Basically. So, I mean, maybe even a default delete rows would be delete anything that has any missing in it.

Sorry, before we, we think time in this design, this says, this requires you to always do the full thing in combined table.

I, I, I'm aware, I'm aware what you don't want to do. I'm aware of this. Okay. Okay. Yeah, Right. I'm just trying to understand how to do this in a nice orthogonal way.

I think the thing we're doing here, though, Stephen, that's a little bit different than just considering a whole tabular is, is we've got, I mean, think of the prefixed things. Okay, we're deleting, you know, so say we're deleting for the inner thing. We're deleting all rows that have an missing in. In any of the three prefixes.

I understand.

Okay. And the inner word. So that's how we get the inner from the outer. But like the, the left one, we're defeating. We're deleting those that come from prefixes, you know, that have prefixes beta and, and chi.

It's not necessarily a prefix. Okay. You're basically deleting if. Look, you're deleting a row that has a certain missing configuration.

Yes, exactly.

Okay, so let's talk about that. Okay. Whether it's prefixed, whether it's whatever doesn't, you know. Go ahead. What are you going to say?

Yeah, what I was going to say is that if you consider the thing you're actually matching on and not everything, which is usually what you want to do here, because you might have a missing in column. Say you have several data columns, and they're not the key, like it's not the missing and matched. It's missing, not available. Right. So those are missings that you want to ignore in general missing that are already.

Well, I assume that we have a certain tag for missing when it's A missing that came from its absent in the tabular.

I, I don't know if that is possible with.

Yes, it is. We just have a. Missing. Has a tag.

I don't know if that is possible with the memory model of tabular.

It, it. It is possible, but it's not, it's not something that's. That's done now and would take quite a bit of work to make it work for the first generation.

Okay so.

So anyway, if you, if you now consider only the things that you're Joining on for three tabulars, there are 14 different ways of doing the missing thing.

Yeah. Okay.

It's basically the power set of the power set.

I know, I understand would be 16.

But you want to ignore the ones that don't have the inner and the empty set.

Just tell me how you would like to specify that. So you've got tabulars and they either have prefixes or they have positions. Right? That's how you specify which tabular you're talking about here. Right?

Yeah.

And in my code I, I did it always with, with positions.

Okay.

Right.

Basically.

Sorry, go ahead.

Yeah, so I was going to say basically the way, the way I, I did this and, and this. Well, you know, maybe inform your discussion or not is I took. I take the, the inner version. So that's. So basically those match at position one. One, one. Okay. And then I extend. So the one in tabular, tabular one is missing two and three. So I add. I add extra, extra cases there with missings in the other columns there. And I do that for each. Each of the indexes.

Can I just understand. Imagine there was a wrapper around the tabulars that appear in column wise combine. Combined tabulars. Right. Let's imagine there was a wrapper row enforced. Not a good name, but let's imagine that in column wise combine.

Yep.

That what we had was something that said row enforced of tabular one. You know, row enforced. I'm just, I'm making this up a little bit for tabular 2. What does that mean? Row enforced means if ever that, that for this thing to show up in the final output, the row has to be present in tabular one. Right. That's what you're really saying. If you. If this is going in the output, only put this in the output. If this row is present for a particular row only put this output thing in the output. If it's present in column blah of tabular blah. Correct.

No, it's not. Only if it's more like. So you have to consider the inner is when all three match, what you're doing is like augment this one with like a rule.

That's not the right way to think about it, I think. Look, just think about it in terms of. Just think about the outer version, okay. And think about what you're doing to prune the outer version to what you want to get. Okay, so I claim that the inner version is a case where every row is required not to have a missing in it. Missing of this type. Right. A missing from joinery.

Yeah, okay, that's right.

So that means in the inner case, every row is being enforced. In every row in every tabular is being enforced. Right. Is non missing.

Okay, so the row enforce means there's no missings generated in that row in the merge. Yes.

Yeah, okay, got it.

The problem is that the outer, the outer and the and the inner are easy. The problem is that if you tag only one or only two of them with this thing, then it's not associative anymore, so you cannot do it.

Let's just walk it through for a second. Okay? Let's think about this idea of missing, not allowed, so to speak. Okay. And so again, I don't have the notation here, but I mean, let's just remind ourselves, what did we do before we had the renames there? We're saying, okay, it's on these two columns. Right?

Right.

And basically what we want to say.

Is.

Either missing isn't allowed in the whole of tabular 1, or missing isn't allowed in a certain column of tabular one.

Steven, can I give you an historical note? Before SQL 94, I think, which introduced left, right, inner router, they were using something like this where they would tag the table instead of that. And that was never standardized because it didn't have the right associativity when you were doing more than two. And so it was confusing and you had to know whether it was.

I'm suitably chagrined, but I'm going to continue.

So what I'm saying is that we're.

Making not going to work 30 years ago by it's going to work. Yes. And they didn't have symbolic code and they didn't have a bunch of other things and they were probably confused.

I don't.

Keep going. Let's keep going. They had a different set of constraints. Let's keep going. Right. So I think probably the calculus they wanted was one where doing this, you know, the complaint probably was, let's just keep going for a second. By the way, Is Roger here? Can Roger comment on any of this? Roger not here.

Yeah, I was just muted. Yeah, no, I think it's doable.

Okay, let's keep going. I mean, let's just look through this. Okay. What really is this. This is missing. Is not allowed. As you do the join, you are going to drop any column. You know, it's basically the full phrase. It would be drop any column, any row.

Right.

That's missing. Okay. And what you've got to say. So that could be true of a whole tabular. Drop any row that's missing. Right. When you do the join. Okay. Okay. Then the next thing is drop any row that's, you know, drop any row that's missing in a particular column or set of columns. Right? Correct. I mean, that. That's, that's. Drop any row that's missing in any column of the tabular. Is that right?

I mean, it's from the construction, right? It's not. They're not sitting there missing.

No, I. I know, I know, I know, I know, I know, I know. That ends up being.

Well, I mean, one way to look at it is you get the. You get the match. You get the match positions. Okay. Which is, you know, how you started your discussion last week? So in this case, the match positions are 1, 1, 1. So. So in a sense, it's dropping any row that's not. That doesn't. Is not explicitly in the set of match positions for that tabular.

I'm confused. I mean, the basic. This is really clarifying to see the outer one, because. Yeah, what's going to happen is. Let's see if the missing is in a column that was just carried along. Right. I mean, that. Wait a minute. Does that happen? Okay, I'm confused now for a second. Okay, Right. The point is, okay, wh. Where can those missings be? We've got the things that were the, the columns on which the joining was done. Those can't have missings because otherwise they wouldn't be. Or at least at least one of them doesn't have a missing. Correct. Otherwise it just wouldn't be there.

Right.

So the issue is, it could be a column on which you are joining or it could be a column on which you are not joining. Is that true? That could, you know, introduce a missing. Correct.

Yes, both. Both cases can happen.

Okay, so now we have to make a choice. If it is going to introduce a missing, does that mean we want to drop the row or not? Correct. That's. That's the question.

Yes, that's. That. Is that that's basically the question.

Okay, so we want to tag which, you know. You know, another way to put it is in which columns do we allow missings? So I'm saying versus missing introduction. Maybe. I'm sorry, what? Yeah, okay. Do we allow missings to be introduced? Okay. And which columns do we allow missings? And. And remove rows so as to avoid any non allowed missing introductions. Right, that's. That's the strategy. Right, That's.

Yeah, and that's basically how it works.

Okay.

All right. How do we specify in which columns do we allow missings to be introduced? So we could use pattern matching. So we've got two things that specify a column. No, actually, actually by default a column is either a key or an extended key.

That's right.

Okay, so I'm going to make the claim. Okay, so here's, here's what I'm going to claim. Allow missing introduction. Okay. Arrow. And then it's simply a pattern that says or a list or something that says into which columns do we allow missing introduction. Okay, so blank would be outer. And so that's the case of outer. And. Well, I don't know exactly what it is. Something which never matches is the case of nr. Am I correct?

Yes.

Why do we not have something in the pattern matcher that is simply. I never match.

I guess you can just use unique or just.

Really couldn't you use accept of blank?

Yeah, I was thinking about that.

Oh, yeah. Okay. All right. Okay, now let's consider left a left one. Okay.

Yeah.

What that is is allow missing introduction in the right hand, guys. Right.

Yes.

So except of one or something.

Yeah, probably, if that's how we're.

Although are we doing it positionally or.

Well, I don't know how we're doing it. So we need a column.

Well, no, you'd have to use. You want to use the tab. The tag. If we're talking about a pattern, it would be accept extended key of.

Yeah.

Of whatever the name is of the extended key. Your beta comma, blank.

Yeah, except is there a reason this.

Is an option, not an argument?

No, there's no good reason.

In which case this turns back into. It's basically what we had before. But this is the. I forget which we had. The, the sort of. The necessary and the sufficient conditions for joining.

Right.

Yeah.

Right.

And this is just one of those.

Yeah, I kind of like your. I like your missing not allowed or maybe allow missing wrapper around the tables. That's actually a nice. Because.

Because then I'm sure that's. Yeah. I mean, that has the advantage. I. I don't think that's a great idea. Because I think it's easier to say, you know, it's easier to just say this column needs to be entire, so to speak, needs to have no missings in it. That's an easy concept. Like now. Okay, now I'm prepared to listen to Carlo's non associativity thing. What was the non associativity?

The. The thing they did was similar. They tagged the thing that they wanted. They wanted.

Just tell us where the non associativity came in.

Okay, so what happens is that the inner one, whenever you have one that is inner, it's projecting, right? So if you do a join, which is like in the current SQL, if you do first left and then inner with something else, the left is canceled out. So you want to do the outer ones last because otherwise they get canceled out. And so they have this syntax where you would put a little star next to the equal, which meant, don't consider this an equality, but consider it that you can add null.

Okay. Okay. They had some crazy thing where they were binarizing.

It's the same as this.

No, it's not the same as this. This is an NRA operation. They were trying to do it with binary operations, which was a mistake.

No, it's. But the comparison is always binary.

What comparison? What? Why?

Because it is like same Q.

Well, in SQL it is.

No same Q.

Same Q is.

We chose same Q is multi arc. We talked about that last week.

Yeah, but it's not really like equals. Equality is a binary operation in mathematics. We just chose one convention for same Q. Right.

You can set it to be whatever you want. Right? I mean, you could set it to be something that doesn't change, treat all the arguments the same. I mean, same Q happens to be commutative, but it doesn't have to be.

Yeah, but what I don't understand is like, if you, if you decide to say just the first one is extendable, so then in what order are you doing this? Because there's. If you look at this as.

There's no question about ordering Carlo. That's the whole point about saying irrelevant. Yeah, that's what's clean about this. This was. This is exactly the type of mistake that I would have expected.

There's a Venn diagram of ways of doing this.

No, there isn't. The way of. Not the. No, there is. If you think about it as a, As a successive binary operation. Because you're just obsessed with Join result.

There's. There is in terms of result that you get out.

No, there isn't.

Yes.

Let me.

Let me just understand. So is the problem. Right. So when you're talking about associativity, Carlo, do you mean like right in SQL, when you do a join, you. You do a join between two things. Right.

But the.

I mean, the syntax here, at least, it looks like you're doing a join between n things. So is the point that you think that sort of secretly inside it's doing successive joins of two things in order to reach N and that there's associativity problems there?

No, what I'm saying is that if you look at the end result, okay. The algebra of missings and where to put them and not put them is a Venn diagram among all the. So if you only have two, you have four possibilities left, which is just the left set. Right, which is just the right set. Inner, which is the intersection, and outer, which is the union. There is. There's 14 possible ways in which you can do the algebra of missing.

Yep, but it doesn't matter. It doesn't matter. Parameterizes all of them.

No, it doesn't.

Yes, it does.

Maybe.

Carlo, can you write an example that shows like, where it wouldn't. Where it would work?

Yeah, I can try, but I think in the meantime I can say slightly differently. I think what Carl is saying about nonsense, and this is also what I noticed, is that the number of different outcomes that can be produced by splitting the multi join into binary joins is larger than what the current spec here would allow you to do. So here you would only be able to do a subset of what is possible with all different ways how you can combine joins.

That may be true, although posit that I don't care because this is what people actually want. Go ahead.

But also, I mean, can't you. Right. Can you not simulate all those by just doing nested this. I mean, this is just. This is joining N objects. But you can. N includes two objects. So can't you just do nested joinings of pairs of objects and get everything that you can do with.

Yeah, yeah, you can.

You can do it, but not the other way around. So that the. So for. To give an example of what I was. What I meant is that if you take two. Two tabulars and do an inner join, you get the intersection of the two things. That's a slice at the center. Then you do with the. With the other one, you do a left join so that you're with that Exact thing with that exact slice of just the first two. That's something that you cannot express in one go here.

I can't think that could possibly be true.

I mean, look, I'm not convinced either.

Look at, look, even, even if it.

Is, think about it this way.

That's, that's why we can't talk about this without first understanding the binary one.

Okay. I think people are confused. I'm sorry, I think it's one of these things where people made up this algebra.

Stephen, there's 50 years of university courses.

I'm, I'm well aware of that. I know, going up to the fifth fundamental form and all these kinds of crazy things.

Okay, I think we'll need the examples. We'll just need to come up with examples.

Okay. What I think.

Wonderful.

I think that, Go ahead.

Well, I was just gonna say, I mean, look, maybe like, let's see an example. But, but otherwise, I mean, otherwise it seems like is the worst case here that you might have to write nested binary column wise combines.

Yeah, I, I mean, I don't, I, I think that the join across we have apart for a couple of things is incomprehensible.

Nobody ever uses it. It's incomprehensible.

But the point is that this thing is something that maybe it's more comprehensible, but it doesn't do everything that the other one does.

Okay, great.

Join across only handles two things at a time anyway, so having to nest, this is no loss from join across.

Okay, can we go through this for a second? Okay, so the issue is, do people like Column wise Combine as a name? I kind of liked it. Certainly part of what I was asking about was presumably this will work for lists of associations and things like this as well.

Well, with a name like Column wise Combine, yes, it could.

Right, good. Okay.

I mean, frankly I, I would have thought the best name would be Join. I mean, obviously Join is taken, but.

Yeah, I think Join is obscure, actually, because I think Join, Join Join has these images of, you know, SQL 101, 201, 301, etc, etc, Etc.

Maybe, but I, I, I think, I mean, maybe I'm weird, but like, I, I, when I hear Join, I just think of like, I mean, kind of this operation of like I have a bunch of, I'm trying to find where the rows match.

Yeah, but let's combine. Okay, so then we've got this whole syntax of which, so normally the second argument of column wise combine is a specification of or we're not putting in the second argument, we're putting it in like this, which columns we want to combine. Is that right? Is that what we're doing?

Yes.

Okay, And which is a good thing because then we can localize that in tab one, arrow A inside here. Correct?

Y. Yeah.

Okay, so then the second argument potentially could be the columns to require being entirely.

Or.

Or the other way around. I don't know which way around is the right thing.

Yeah. Again, when I was implementing it, I wasn't. I got myself confused as to which way it should be. So.

Okay, okay. So it's either require entire, require entire, or it's. Allowed missings, allow holes.

Yeah.

And I ended up doing require entire.

Okay. So the default would be require no entire, require nothing to be entire.

Yeah, so that's, that's that. So I had a. An empty list there. Okay.

Okay. So otherwise, basically the way to think about it is what's the thing that has to be there? In other words, if the person has no name, they do not appear, for example. Or something. If what. What's a real example of something where you're joining and you don't want the thing for which that. Okay, I think require entire is the right one.

Okay.

Right. So.

And I did it by index versus pattern, but we could do it by pattern instead. That would be.

Let's think about that for a second. Let's think. Think for a second. Every column has a name, Right. Why don't we do it by a mirror of what we did here? So in other words, is this going to work? So it's either a pattern name, a column name pattern, Or a global column name pattern, or. I'm trying to think here. Is there a way of getting something which is. Which mirrors the list that we had here? See, we have a list here of tabulars.

That's. That's. That to me, that was the index. The thing is, the thing is, it's. It's either. So, so if you do that exam. Well, you can't do it. But with, with the. The B associed with each tabular, where you get. And I have an example above where it's done, you'll see that the B. The B has missing elements too, in the same positions as the A's and the C's in the first one, for example.

Yeah. Okay.

Okay. So. So they all come in groups of. Of missing. So. So the missings are not. They're not by column, they're by. By input tabular.

Are they?

Yes.

I mean, what about always?

Always.

But, but look, look, Listen, listen. The issue is, does a row survive? Okay, yeah. If the row has, is missing in column A and column A is a required. Is required to be non missing that row.

But it's not, it's not column A because it, it, it's, it's like I, I'm merging, you know, Right. I'm merging my, my three tabulars, which numbers tabular.

Because all, all the. So when, when you introduce a missing, all the columns for that tabular are missing. It's you. It just doesn't appear. Sometimes when you merge the, you merge the columns into a single column, but the one associated with that tabular actually does have missings.

What about. Wait a minute, there, there's not a situation here in which there are two column Bs and some of their elements disagree. As soon as the elements disagree, you are making a new row. Is that correct? There's no scenario. Oh, that's the question of the same test.

If you're joining on them.

That's.

Yeah, that is the question of the same test.

Yep.

Right.

And if you look, if you look at the same test section, you'll see I sort of have a discussion of how we. So what we do the implementation, it actually does it a little backwards to the way. So it starts from the inner, which are the match positions, then it adds missings, and then to get these particular things, we take them away.

Okay, so just let me understand here. I mean, I'm not totally wild about. Okay, so let's say column wise combine. So one possibility is a wrapper of these things, but all the alternative is tab one, tab two, tab three. And we say two.

Yeah.

And that means that everything in tabular two never allow any holes in the columns coming from tabular two. Is that correct?

You mean of the columns that you're joining on, but.

Yes, yes, exactly.

Never allow holes in tab two columns.

Yep.

Okay, so none here is outer.

Is none the same as empty list? Yeah.

Or empty list. Or none.

Yeah.

And then 1, 2, 3 is the, is the inner.

Yeah.

I wonder if we could allow blank as well. Sure.

I mean, you could.

Although frankly, I think that patterns aren't as useful here because they're always going.

To be like, yeah, 1, 2, 3.

Yeah, yeah, yeah. And then of course, if you're giving your tabulars and an association on the left, then, you know, you can give them the names instead of the indices.

This seems reasonable. We should try it.

Okay, well, that's, that's, that's what, that's what I've implemented I. I haven't quite done the names yet. Thank Christopher.

But.

But yes, I intend to. Yeah.

Actually, does anyone remember? So, I mean, last time I remember we talked about having two arguments that was sort of like the necessary and sufficient ones.

I don't remember why did not make sense out of that. I. I tried and I just. So I finally came down to what we just discussed.

I like very much this idea of, you know, the default is. It's very outer and then you're pruning it down by requiring certain, you know, by requiring columns from a given tabular to be entire. Okay, let's just have a quick look at same test here.

So I'm being a little pedantic here and explaining what I'm doing because it's. I find it confusing. It took convince myself.

It'S because the same test is not the right thing, I think here.

But the same test is being applied to all. Corresponding to all elements of corresponding columns.

Yeah, it's. I show exactly how it works with this is. With that outer below. And this, this stuff will actually work in. And this is just plain, you know, plain Wolfram language stuff. So hold on.

I mean, if there are three things and which are candidates for same test.

Yep.

It's always, as Carlo has been emphasizing, there is always a binariness to. If they're all the same, we're done. Okay. If they're not all the same, it could still be the case that two of them are the same, but one of them isn't.

Yes. I have an example of that below.

The more common case is like you have something like employees in offices and they are compared by the office code. And then you want to take employees and customers and those are joined on something completely different.

Yeah, I have bad examples.

So that's the case where it's not ternary really. It's two different things and you can't write.

So you're saying that you're joining on. You're doing one join between three tables and you're joining on employee ID and on customer id.

No, no, no, no, no. I'm joining on office code, which is a foreign key for. For employees and on. On customer. No, sorry, sales representative. That is a foreign key for the customer. So each customer has a sales representative and each employee works out of an office. So if you want to do a triple join between offices, employees and customers, you're going to do it on. They don't have three things in common. They have two and two.

Right. Okay.

There's no way to represent it. With this syntax?

Well, I mean, you can with two joins, but. Right. I mean, with a single join, can you not. I mean, so what would you do? You'd say, I'm joining on, I'm joining on. So from the offices thing, I'm joining on office ID on the. For. For employees, I'm joining on both office ID and on this customer represented customer id and on customers, I'm joining on. Or I'm enjoying employee id and then on customers, I'm. I'm doing, you know, sales representative or something like that.

Yeah, and, and the simple and for the same test. Yeah, yeah, and. But then you have different lengths, so if you don't specify the same test, it doesn't work. And, and so the same, the same.

Test just takes an argument for each tabular, so.

No, but what I'm saying is that you have something where you have to specify the two outer ones have two plugs, it's like valence bonds. And this, the middle one has to have two. And I don't know if you support that where they have different lengths.

Can you just write that in the. I mean, can't you just write that like in the same test you just say, you know, hash 1 of. Of, you know, office ID equals hash 2 of. Of employee office, and then hash 2 of customer, you know, of employee ID equals hash 3 of sales representative.

Yeah, okay, you do that. But then what I was trying to propose before is that all of this is something that currently, with the meta information we have about this stuff, you don't even need to say on what you're joining because it's obvious from the fact that they're foreign keys. And so those could be.

Well, we don't have foreign keys in that. Do we have any way of specifying something as a foreign key?

Right now we don't have them yet, but we might in the future. And so what I'm saying is that the syntax for the simple join across if you have foreign keys is like two, two argument join across with no same test function. Otherwise two rules that you have to add and that's it.

Okay, well then maybe if we, if we add a whole foreign key mechanism, then we can have same tests be deduced in some brilliant way. But I mean, if we don't, you know, that we can always add that.

It'S not the same test. Like the moment where you start using a function you've kind of lost because you have to produce the full outer product. Unless you do some black magic on the function and figure out that it's actually doing like just the quantities because algorithms.

Well, okay, that, that I'm somewhat sympathetic to because I'm. I'm sort of skeptical of black magic functions. But I mean, maybe, I mean, two, two things. I mean, one thing is, is, you know, it's right when we require function input, it is always a bit restrictive because anything can be a function. But also lists are probably not functions, for example, so there are still a few other things that you could imagine putting there, like, you know, structures with lists that, that wouldn't conflict with having a function syntax too.

Yeah, but it shouldn't be an option like same test. It should really be a. That's what we were proposing for like the current join across other than rules and lists of rules and lists also add a function there.

It's.

It's non. Like, it doesn't cause any problem, it doesn't conflict with anything. And then you can do more what.

Doesn'T cause any trouble.

What like if you look at the current syntax for join across, the third argument could be a function. It doesn't conflict with anything.

Yeah, and it's not so.

And it's like, it's kind of. It's not an option. It's like an important thing.

There is another difference here, unless I'm misunderstanding. If you have a function, same test, which takes three arguments, like three tableaus. And then Christopher, like you proposed in that example of Carlo, where we join as an end clause, like office, office code, double equals employee, office code, and then employee sales representative equals customers, whatever, it's the end close. So if we are doing, for example, full outer joins, then that thing will basically cut out more things that we like than we like. Because if you do two binaries, you may want to allow certain missings in there because it's. It's not going to be. My point is ternary function with an end clause is not equivalent to two joins because of the possibility of incorporating mistics.

Fine. Well, I mean, I guess. Okay. I mean, at this point I'm not entirely sure what's being advocated for because it sort of sounds like, you know, there's some kinds of. There's some kinds of drawings you can do with this same test syntax. Maybe. I'm not entirely sure I understand, but maybe there are joins that you can really only do in this binary and not any way, but then you can distribute them binary.

Christopher.

Yeah, that's true.

If we can get the extension to the current join across that we have proposed. Otherwise we end up in a weird feature matrix where Some things can be done only with join across, and some other things can only be done with combined columns. And join across doesn't support functions, but combined columns doesn't support weird left right combinations. And so you can't do some things because they're not.

Hold on, hold on. Just. We've got to wrap here for a second. But just that the issue with same test is there is, you know, an arbitrary function, as you say, requires us to enumerate the outer cases and then prune them.

Okay. Yeah.

What are some realistic functions? Like, you know, case independent, ignore case, for instance, is an example of something. For strings will be an example of something. I mean, what is the realistic thing you want to do? You've got, for example, names, and you say, you know, you want one of them to be the canonical. When you have a same test, what do you actually want to put in the results? Let's say you've got names and they've got different uppercase and lowercase things. Okay. There is a canonical one that you'll want to put in the final result if you can. So you've got this. These combinations and, you know, you. Well, hold on. There's no reason to do a same test. Now, wait a second. You don't have to generate the outer, you just have to generate. Well, okay.

I mean, I think you do in general have to generate the outer. Unless you do black magic on the function.

Yeah, okay, but the outer isn't the same outer that we were talking about before, because what it is is given a column given two columns in unknown orders, we're trying to figure out what matches up in these two columns.

Yeah, it's worse than generating the outer. It's generating the cross product. Right, okay.

So we're trying to figure out of these two things, and presumably there's some nice hashing and so on that can be done here. And, you know, we're trying to figure out a permutation from one of these to another. And with three, we're trying to figure out a fairly complicated threading of what matches with what.

Yeah, it's not a permutation, but yeah, yeah, okay.

All right. But we, we've got these things and the question is, well, what happens if one of them is uppercase, one of them is lowercase, what are we actually going to put in the result?

We talked about this last time, I think we just said like the left one or something.

But is that the right thing now that we are thinking about? So look, I mean.

That'S one of the syntax Things I want to discuss is in the case where we have a same test function, we may want to include, we may want to not want not to ally those columns and include the prefix version of the columns and then somebody can figure out how to merge them from those results.

Yeah. In general it doesn't have to be equality, like equality where you. It's kind of a simple case. You could have less than in there. That's, that's a terrible case because he generates a much larger number of matches. The problem is that it's really hard to do it ternary. It's difficult to resolve.

I don't think there's anything about. No, it's already with binary. It's non trivial because you've got to decide which one is the winner. And I'm sure SQL has some default, like it's the leftmost one that's the winner.

No, no, in SQL you do, you do prefixing. It's fine. Yeah, but the question is not like you keep them both always by default. So, so that's fine. But the question is really that in a number of times what you want to express is that as I said before, you're doing, say you're joining chemical with elements and then you're joining elements with isotopes. Isotopes and chemicals don't talk to each other, they don't have anything in common. And so it's really difficult to express it as a ternary function because you don't know whether to put and. Or, or it's really two different comparisons.

I'm still somewhat confused.

Yeah, I mean, I think, I mean, I think. Go ahead, Leonid.

Yeah, I just wanted to say quickly that I think what Carl is saying, what I was trying also to say previously is that if you have a ternary function and you have a model that you first do the complete outer cross product and then you filter with a ternary function, then there are cases where two binary joins will produce a set of missings and non missings that completely cannot be reproduced by a ternary function regardless of which operator you put there or, or, and, or some combination.

Right. But I guess. Well, I mean, two things. I mean, the first thing is just. Again, I, I just, I'm not sure it's a big issue if there's something that really can't, you know, there's some fairly obscure case that really can't be represented as an nary thing. You can always just do multiple binary ones.

Yep.

But also, I'm not sure the binary ones.

Christopher. Which we, which is what I Wanted to start with like.

Well, that's, that's, that's a different.

We accept different changes.

If we accept it might still be forever about call and combine. Just do join across.

I mean, look, it might be, it might look, it's still useful to have something that's NRA if it's right. You know, 80% of the time. Even if 20% of the time you're doing something really complicated and then you need to do multiple binary ones. Right. It's still nice to be able to do NRE ones that are mostly right. But, but, but also, I, I'm not sure. I mean, I'm not even sure it's. It's true that you can't represent all those with. You know, if you can specify arbitrary and ors, I think you can basically spec. You can basically specify anything you would do, can you not if you.

All right, Christopher, if you specify all the missings like, you know, you probably can, but then I guess.

No, no, no.

Okay, listen, we have to wrap.

We will continue.

Okay, I'll send you an email, Christopher.

Okay, fine. But, but maybe, maybe one.

One final thing would be maybe it sounds like might end up making sense to. You know, instead of having same test, maybe we could have some. Something that communicates the same information, but in a way that isn't just a function that's more structured.

I don't think it's going to work. I think it's going to need a canonicalization thing. It's going to. To need to say which is the canonical member, et cetera, et cetera, et cetera.

Right. But it'll probably communicate ultimately the same information. But, but it can be, you know, implemented much.

May not be because I think it needs one more piece of information which is. Which is the canonical.

Fine.

Right. Okay. See you guys soon.

Okay, bye. Bye.