Is Label Encoding with arbitrary numbers ever useful at all?
First, I generally agree that encoding unordered categories as consecutive integers is not a great approach: you are adding a ton of additional relationships that aren't present in the data.
CART
First, let me point out (because I nearly forgot) that there are two main types of decision tree: CART and the Quinlan family.  For the Quinlan family, categorical variables are dealt with by using higher arity splits, so no encoding is needed and this is mostly moot.
Q1, yes, ordinally encoding will be treated by the model as numeric (unless some other parameter controls that, e.g. LightGBM).  But for (most) trees, only the order is actually relevant: the scale is irrelevant, and e.g. the relationship &quot;10 is twice as much as 5&quot; is completely invisible to the tree.
As you point out, one-hot encoding for a CART model can be detrimental, especially when there are many levels in a categorical feature: will the tree ever actually decide to split on one of the dummy variables, if it is only 1 for a small subset of the data?
(Q2)But when you encode ordinally, there will just by chance be some splits that are useful and split many levels in each direction.  (You may even try more than one random ordering of the levels as different features!)
And yes, presumably the best approach is to use an implementation that can take advantage of the raw categoricals, using the average-response trick.  (There's even some debate on how much that helps: some studies have been done, but generally the datasets are synthetic or too small to be representative.)
Other models
In other models, very often one-hot encoding is just fine, and doesn't suffer from the same problem as trees.  If there are too many levels, and especially if some of them are too small, you may consider smoothing techniques to avoid overfitting.  (Q2)I'd be surprised if ordinally encoding is ever worth it for most models, but one would need to consider each model type individually, and probably do some testing.
Names
As for naming, things are a bit muddy, but I don't think this is sklearn's fault.  The &quot;label&quot; in LabelEncoder means it is supposed to be used on the labels, a.k.a. the dependent variable.  And for that usage, there is no debate about whether it's appropriate: sklearn just requires consecutive integer labels for its multiclass classification; it doesn't use the numeric values as though they were mathematically meaningful.
As for OrdinalEncoder, it is meant to be used with input ordering of the categories.  See sklearn Issue#13488 below.  But one could argue that you are encoding the categorical variable in an ordinal way, so even with unordered categories this isn't necessarily a misnomer.
See also
See Issue#13488 for some related discussion.