I'm gonna show you something cool that you should probably never ever do. The docs warn very explicitly against doing this. I am going to show you how to do it because I think it's cool and that's maybe enough. But I also think it's helpful for learning. But sometimes you just gotta do something awesome, right?

So what we're gonna do, we are gonna look at something called imposter tables. So if I were to say, SQLite 3 test, open this up and say, let's do an imposter table. This is a way to create a table out of an index so you can directly query the index. It's kind of insane and I don't think it's a good idea. But if you do it like this, you say the index's name Let me just show you.

The index's name is name and it's on the users table. So let's clear this out and say dot imposter name is the name of the index and the such a bad idea that you have to pass in a flag of unsafe testing. So we're gonna add unsafe testing in here. This is the first warning that we get. So now, if I were to say imposter of name on a table name IDX, what it's gonna do is it's gonna create a virtual table for me based on that underlying index.

And it says, writing to an imposter table will corrupt the index. Do not write to this table. You are directly attached to the index b tree now which I have no idea why they give you this power but boy isn't it cool. Select star from name, IDX, it's gonna look ugly. Mode box.

Let's give some space for the big show. There we go. This is exactly what I have been telling you is true. So here here we have a select star from users. So here we have the table.

I'm first. Congratulations to me, double a. I'm first and then Steve, Jennifer, Simon, yadayada all the way down. This is the table. We put an index on name.

What that index looks like is this. You'll notice that the names are in order now. And down there in the leaf node is the row ID. And so this is the pointer that goes back to the main table. So I don't think you should ever use imposture tables.

I don't know why they exist, maybe besides teaching, because mucking around with the index b tree could get it out of sync from the actual, table and could provide just totally nonsensical results. But it's really helpful for teaching because now I can prove to you that, like, this is what the index b tree looks like. It has the name and then it has the row ID, just like we've been talking about. So this is impostor tables. Very, very cool.

You'll you'll notice we got many many warnings not to do this and so I don't recommend doing it. But now you can see for yourself what the b tree looks like and that the row ID is attached to it.