Why ORDER BY Can Sort a Value Differently Than What You See

Jim Knicely

Sometimes a query result looks wrong at first glance… until you realize YugabyteDB is doing exactly what the type system told it to do.

This tip was inspired by a fun internal puzzle from my colleague Dan Farrell, Senior Pre-Sales Engineer, who brought some old-school ORDBMS magic to YugabyteDB.

At first, the output looked confusing:

  • ● the numbers shown in the result set didn’t appear sorted

  • ● but the query was using ORDER BY

So what gives?

🔍 Key Insight
You can design a type so that YugabyteDB sorts by an internal normalized value, while your query displays a different, user-facing value.

That means:

  • The value you see is not always the value being used for sorting.

And that is exactly what makes this demo so cool.

The Puzzle First

Here is the kind of result that started the conversation.

				
					SELECT id, (dist).val
FROM t1
ORDER BY dist;

And the output looked something like this:

				
					.                 id                  |  val
--------------------------------------+-------
 9b4af1d5-079c-48c9-9df5-dcaf4fc36d39 |  3.28
 c2e07358-a3ad-4d01-84e2-5274399ccaa4 |  1.00
 b5e8bed4-0784-451f-9405-28eb5037bba7 |  8.20
 989010c0-566f-4e32-bc54-015e4e982263 |  2.50
 5ca91417-5182-4c1b-b1c2-8fc2a14f51aa | 10.00
 be53c3cf-8925-48ac-87c3-041f1546b777 |  3.10
 e723327c-3ce5-4017-976f-8a2a081b4c0e | 16.40
 a3ef0abd-0c3f-47d0-bcfb-46f2d1429463 |  5.00
 e7ee0d24-c6b6-4ce3-9134-5159f4f2d3bf | 20.00
 f10eb7ba-cb93-4a73-987a-ab61cd0f2b60 |  6.10

At first glance, the result looks confusing because the visible numeric values are clearly not in ascending order.

So what is happening?

What’s Actually Happening

The key is this:

  • ● The query displays: (dist).val

  • ● But the query sorts by: dist

Those are not the same thing.

In this demo, dist is a custom composite type with this structure:

				
					(sort_key, val, unit)

Where:

  • ● sort_key = normalized value (used internally for ordering)

  • ● val = the original value (what you display)

  • ● unit = metadata (feet, meters, etc.)

Composite types in YugabyteDB (PostgreSQL) are compared left-to-right by attribute order, so the first field effectively becomes the sort key.

This:

				
					ORDER BY dist

is effectively:

				
					ORDER BY (dist).sort_key, (dist).val, (dist).unit

So the database is sorting correctly… just not by the value you chose to display.

💡 Why It Looks “Wrong”
You are displaying (dist).val, but sorting by (dist).sort_key. The mismatch between display and sort key creates the illusion of incorrect ordering.

ORDBMS Throwback

Dan explained this as a callback to the old ORDBMS world.

The “O” is for object.

The idea was that you could extend the database type system with richer behavior. For example:

  • ● define a type like feet or meters

  • ● allow inserts using those values naturally

  • ● let the database perform automatic conversion

  • ● make expressions, predicates, and sort behavior all work automatically

So instead of treating values as dumb numbers, the database treats them as values with meaning.

That is exactly what made the demo so interesting.

A Minimal Demo Pattern

Below is a simplified example showing the idea. It is not meant to be a full production-grade unit system, but it illustrates the mechanics clearly.

1) Create a composite type

				
					CREATE TYPE distance_t AS (
  sort_key numeric,
  val      numeric,
  unit     text
);

Here:

  • ● sort_key is hidden from casual display unless you select it

  • ● val is the number you want people to see

  • ● unit is optional but makes the demo clearer

2) Create constructor functions (feels like a real type system)

These functions normalize values automatically.

				
					CREATE OR REPLACE FUNCTION meters(v numeric)
RETURNS distance_t
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT ROW(v, v, 'm')::distance_t
$$;

CREATE OR REPLACE FUNCTION feet(v numeric)
RETURNS distance_t
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT ROW(v * 0.3048, v, 'ft')::distance_t
$$;

Now:

  • ● meters(10) → (10.0, 10, 'm')

  • ● feet(32) → (9.75, 32, 'ft')

So the internal sort key differs from the visible value.

3) Add a cast helper (optional, but very ORDBMS-style)

				
					CREATE OR REPLACE FUNCTION numeric_to_distance(v numeric)
RETURNS distance_t
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT meters(v)
$$;

CREATE CAST (numeric AS distance_t)
WITH FUNCTION numeric_to_distance(numeric)
AS IMPLICIT;

Now a plain number automatically becomes meters.

🔥 ORDBMS Throwback
This is exactly the kind of behavior ORDBMS systems were designed for… values carry meaning, and the database enforces consistent behavior automatically.

4) Create a table

				
					CREATE TABLE t1 (
  id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dist distance_t
);

5) Insert sample data

				
					INSERT INTO t1 (dist) VALUES
  (feet(10.76)),   -- ~3.28 m
  (meters(1.00)),  -- 1.00 m
  (feet(26.90)),   -- ~8.20 m
  (feet(8.20)),    -- ~2.50 m
  (meters(10.00)), -- 10.00 m
  (feet(10.17)),   -- ~3.10 m
  (feet(53.81)),   -- ~16.40 m
  (feet(16.40)),   -- ~5.00 m
  (meters(20.00)), -- 20.00 m
  (feet(20.01));   -- ~6.10 m

6) Reveal the hidden truth

				
					SELECT
  id,
  (dist).sort_key AS internal_sort_value,
  (dist).val      AS displayed_value,
  (dist).unit
FROM t1
ORDER BY dist;

Sample outout:

				
					.                 id                  | internal_sort_value | displayed_value | unit
--------------------------------------+---------------------+-----------------+------
 975459a8-d1d9-491f-b25b-284b74192940 |                1.00 |            1.00 | m
 4847a42d-db0a-433f-a08e-48f4cdf65576 |            2.499360 |            8.20 | ft
 cd24eed3-f3ee-4732-9160-0ec73a1ff64f |            3.099816 |           10.17 | ft
 b29713fd-a265-4ae4-85b4-38d8bc52aca3 |            3.279648 |           10.76 | ft
 67541e3d-8b27-4053-b60c-2ed373f4e964 |            4.998720 |           16.40 | ft
 d0903496-cdae-4569-8447-40833812ab93 |            6.099048 |           20.01 | ft
 de76cd4c-1efc-47ad-97fa-5725e82af376 |            8.199120 |           26.90 | ft
 ad31c8ba-2d3f-4c4e-adfa-736412605c5c |               10.00 |           10.00 | m
 dd40c71f-7286-42bc-9f43-cf434fa6e772 |           16.401288 |           53.81 | ft
 d8561656-3d63-4a52-99de-b86139df7fbd |               20.00 |           20.00 | m

Now the behavior becomes obvious:

  • ● rows are sorted by sort_key

  • ● but you may choose to display val

6) Now reproduce the “weird” sort

This is the fun part:

				
					SELECT id, (dist).val
FROM t1
ORDER BY dist;

Example output:

				
					.                 id                  |  val
--------------------------------------+-------
 975459a8-d1d9-491f-b25b-284b74192940 |  1.00
 4847a42d-db0a-433f-a08e-48f4cdf65576 |  8.20
 cd24eed3-f3ee-4732-9160-0ec73a1ff64f | 10.17
 b29713fd-a265-4ae4-85b4-38d8bc52aca3 | 10.76
 67541e3d-8b27-4053-b60c-2ed373f4e964 | 16.40
 d0903496-cdae-4569-8447-40833812ab93 | 20.01
 de76cd4c-1efc-47ad-97fa-5725e82af376 | 26.90
 ad31c8ba-2d3f-4c4e-adfa-736412605c5c | 10.00
 dd40c71f-7286-42bc-9f43-cf434fa6e772 | 53.81
 d8561656-3d63-4a52-99de-b86139df7fbd | 20.00

Now the numbers look out of order… just like Dan’s example.

8) Compare with raw numeric sort

				
					SELECT id, (dist).val
FROM t1
ORDER BY (dist).val;

Example output:

				
					.                 id                  |  val
--------------------------------------+-------
 975459a8-d1d9-491f-b25b-284b74192940 |  1.00
 4847a42d-db0a-433f-a08e-48f4cdf65576 |  8.20
 ad31c8ba-2d3f-4c4e-adfa-736412605c5c | 10.00
 cd24eed3-f3ee-4732-9160-0ec73a1ff64f | 10.17
 b29713fd-a265-4ae4-85b4-38d8bc52aca3 | 10.76
 67541e3d-8b27-4053-b60c-2ed373f4e964 | 16.40
 d8561656-3d63-4a52-99de-b86139df7fbd | 20.00
 d0903496-cdae-4569-8447-40833812ab93 | 20.01
 de76cd4c-1efc-47ad-97fa-5725e82af376 | 26.90
 dd40c71f-7286-42bc-9f43-cf434fa6e772 | 53.81

Now the values sort exactly as expected numerically.

The Single Most Important Distinction

Expression What it sorts by
ORDER BY (dist).val Visible numeric value
ORDER BY dist Internal normalized sort key

The Aha Moment

You’re not sorting the data… you’re sorting the behavior of the type.

Why This Matters

This demo is fun, but it also highlights something real and important:

  • YugabyteDB inherits PostgreSQL-style extensibility, which means values do not have to be limited to built-in scalar behavior.

With the right definitions, custom types can participate in:

  • ● comparisons

  • ● casts

  • ● expressions

  • ● predicates

  • ● ordering

That means you can model domain-aware values such as:

  • ● units of measure

  • ● currencies

  • ● encoded business objects

  • ● custom ranking systems

  • ● structured comparison semantics

So the database is not just storing a number.

It can understand how that value should behave.

✅ Practical Takeaway
If one sort order comes from a raw component and another comes from the full custom type, do not assume YugabyteDB is broken. First ask whether the type itself defines comparison behavior that changes the semantics of the sort.

What Ever Happened to ORDBMS?

Dan also gave a great historical summary.

These ideas were exciting because they let you push more intelligence into the database layer.

But they also ran into predictable issues:

Challenge Why it mattered
Proprietary implementations Many systems implemented this in platform-specific ways
Scaling limitations Rich behavior in the database could become expensive and complex
Too much business logic in the data tier Teams often preferred to keep application behavior out of the database

So the grand ORDBMS vision mostly faded.

But the underlying idea is still fascinating.

Why This Is Still Cool in YugabyteDB

That is what makes Dan’s example so interesting.

You get a taste of that old ORDBMS power, but inside a modern distributed SQL system.

YugabyteDB combines:

  • ● PostgreSQL extensibility

  • ● distributed scale

  • ● familiar SQL semantics

  • ● the ability to define richer type behavior when it actually adds value

That does not mean every application should build fancy custom types.

But it does mean the platform is flexible enough to support some very elegant ideas.

👏 Credit
Big credit to Dan Farrell for the original puzzle and for showing that this old-school ORDBMS-style idea can still be demonstrated in YugabyteDB today.

Have Fun!