Order By gives different result

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Order By gives different result

Stephen Chrzanowski
I think I may have found a query issue.  I haven't checked the SQLite docs
to see if this is something that is specified as an order of operations, or
if this is a check that has been overlooked.

The database SQL code is here: https://pastebin.com/raw/FukX4qEB

select [main].[StateLabels].[LabelText] as
[FromState],[StateLabels1].[LabelText] as [ToState]
from [main].[StateLabels]
  inner join [main].[StateTables] on [main].[StateLabels].[LabelID] =
[main].[StateTables].[FromState]
  inner join [main].[StateLabels] [StateLabels1] on
[StateLabels1].[LabelID] = [main].[StateTables].[ToState]
order by [FromState],[ToState]

I get an ordered list that I'd expect.  However, if I change the order by
portion to

order by upper([FromState]), upper([ToState])

I get an undefined order (Actually, it looks like it is ordering based on
the StateLabels.LabelID -- And this is the clue)

But then, if I go and change the order by to look like:

order by upper([main].[StateLabels].[LabelText]),
upper([StateLabels1].[LabelText])

I get the original order mentioned.

It seems like the query analyzer is picking a real field name versus the
one I defined with "As".  My bad design, sure, I'll take credit for that,
but FWIW, I would have expected an error to be thrown because I've got two
types of fields named FromState and ToState.  One being I've defined by
using "As" and the other by an actual field name in StateTables.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Order By gives different result

Dan Kennedy-4
On 08/21/2017 11:51 PM, Stephen Chrzanowski wrote:
> I think I may have found a query issue.  I haven't checked the SQLite docs
> to see if this is something that is specified as an order of operations, or
> if this is a check that has been overlooked.

I think using a column alias in a complex expression within an ORDER BY
is non-standard SQL. An extension that was accidentally implemented and
is now maintained for backward compatibility. In SQL Server you can use
a column alias as a standalone ORDER BY term but not as part of a
complex expression.

More here:

   http://sqlite.org/src/info/f617ea3125e9c

https://stackoverflow.com/questions/25763920/why-cant-i-refer-to-a-column-alias-in-the-order-by-using-case

Dan.






>
> The database SQL code is here: https://pastebin.com/raw/FukX4qEB
>
> select [main].[StateLabels].[LabelText] as
> [FromState],[StateLabels1].[LabelText] as [ToState]
> from [main].[StateLabels]
>    inner join [main].[StateTables] on [main].[StateLabels].[LabelID] =
> [main].[StateTables].[FromState]
>    inner join [main].[StateLabels] [StateLabels1] on
> [StateLabels1].[LabelID] = [main].[StateTables].[ToState]
> order by [FromState],[ToState]
>
> I get an ordered list that I'd expect.  However, if I change the order by
> portion to
>
> order by upper([FromState]), upper([ToState])
>
> I get an undefined order (Actually, it looks like it is ordering based on
> the StateLabels.LabelID -- And this is the clue)
>
> But then, if I go and change the order by to look like:
>
> order by upper([main].[StateLabels].[LabelText]),
> upper([StateLabels1].[LabelText])
>
> I get the original order mentioned.
>
> It seems like the query analyzer is picking a real field name versus the
> one I defined with "As".  My bad design, sure, I'll take credit for that,
> but FWIW, I would have expected an error to be thrown because I've got two
> types of fields named FromState and ToState.  One being I've defined by
> using "As" and the other by an actual field name in StateTables.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users