Luatic

Now this sounds plausible! I must disagree about one thing though: I don't think that blaming it on COBOL and then pretending it is some arcane language behavior only the elders could possibly comprehend when it just isn't is fine. It should be possible to communicate to the general public that something might be amiss with the interpretation of the data without strongly asserting a falsehood. Why throw around COBOL?

@J.D. "Auto-incremental IDs [...] typically are equidistant." - well, only until you DELETE rows.

@J.D. Ah I see, so you're proposing trying out random IDs until a valid one is found? That would indeed work well if the IDs are sufficiently dense.

@J.D. the random number generation will distribute evenly, but then mapping these numbers to IDs using a seek won't distribute evenly (since IDs aren't equidistant), unless there's some clever trick I'm missing.

@J.D. If I understand correctly, the method you propose doesn't guarantee an equal probability distribution; rows will be more likely to be chosen if they are next to a large ID gap.

@RickJames But there only are O(log n) ancestors of a node in a B-Tree?

@LaurenzAlbe Yes, with MVCC there can effectively be multiple variants of the table for different observers. But this should (theoretically at least) be possible to achieve using persistent data structures just as well. After all, B-Tree indexes already work with MVCC; I don't see why the augmentation wouldn't.

@RickJames what sparked this was the use case of selecting a random row, which can be reduced to selecting a random rank in the sorted order from 1 to the count. I am aware that there are various other hacks relying on various assumptions. Also pagination queries. Again there are hacks (esp. if users only ever request the next page). Also counting queries (count in range / count all, the latter of which also can be done using hacks). But overall I just want sorted sets with ranks - sometimes you just want these ranks, think MMORPG (or really anywhere where people compete).

@RickJames There isn't really a "real application"; this is more of a theoretical question (perhaps DBA SE is the wrong place for these?). I had a certain index in mind (augmented B-Trees) which would significantly speed up an entire class of queries (all rank-based queries - find by rank, get rank of, count ranks). Redis' sorted set supports these. I am aware that the augmentation would generally make updates slower (due to requiring updates of all ancestors).

@J.D. very simplified, what I have in mind is a B-Tree which would store an aggregated count of all descendants and itself for each node - "how many rows does this subtree contain" (I suppose in practice you would store these counts in the parents to have them in the same page). Could you elaborate how a "regular" B-Tree index without this augmentation can run the last query in O(log n) time? Note that the offset is by count, not by value of id.

As an example for another query which I'd like to be fast (effectively just an application of the second query): Choosing a random row. And as yet another example, the count of a range by ID (how many rows have ID >= x and ID <= y): SELECT COUNT(*) FROM test WHERE id >= ? and id <= ?. All of these are theoretically possible in O(log n) with an augmented B-Tree index.

There is an index which would make both these queries fast (specifically B-Trees augmented with counts), though this index would of course come at increased (but only by a constant factor) expenses of updates. I'm wondering whether there are any DBMS's which implement such an index. Redis' implementation uses augmented skip lists for this, but that's not feasible for on-disk databases. But if you find another indexing data structure which makes this general class of queries "by rank" fast (and is implemented by any (rel-)DBMS), I'm fine with it too.

I assume by queries you mean SQL queries? Note that concatenating SQL queries isn't an issue per se; it's only an issue if it can break the query (e.g. unescaped, unvalidated user input is concatenated).

)about

Worth noting that the company is a Ltd.

If there would still be some of them, you wouldn't be able to buy them; as said, the state would own those vehicles.