The following bug has been logged on the website:
Bug reference: 17168
Logged by: Alfonso Vicente
Email address: alfonsovicente@gmail.com
PostgreSQL version: 13.2
Operating system: Red Hat Enterprise Linux 8
Description:
This is a minimal example to reproduce the error, the "ara" tuple is
incorrectly sorted between "a r" and "a s"
test=> with s1 as (select 'a r' c), s2 as (select 'ara' c), s3 as (select 'a
s' c) select c from (select c from s1 union all select c from s2 union all
select c from s3) as foo order by 1;
c
-----
a r
ara
a s
(3 rows)
It appears to be related with spaces, here are two examples that shows the
same problem with the "ara" and "aja" strings
Example 1
------------------
test=> with s1 as (select 'a r' c), s2 as (select 'ara' c), s3 as (select 'a
q' c) select c from (select c from s1 union all select c from s2 union all
select c from s3) as foo order by 1;
c
-----
a q
a r
ara
(3 rows)
test=> with s1 as (select 'a r' c), s2 as (select 'ara' c), s3 as (select 'a
r' c) select c from (select c from s1 union all select c from s2 union all
select c from s3) as foo order by 1;
c
-----
a r
a r
ara
(3 rows)
test=> with s1 as (select 'a r' c), s2 as (select 'ara' c), s3 as (select 'a
s' c) select c from (select c from s1 union all select c from s2 union all
select c from s3) as foo order by 1;
c
-----
a r
ara
a s
(3 rows)
Example 2
------------------
test=> with s1 as (select 'a j' c), s2 as (select 'aja' c), s3 as (select 'a
i' c) select c from (select c from s1 union all select c from s2 union all
select c from s3) as foo order by 1;
c
-----
a i
a j
aja
(3 rows)
test=> with s1 as (select 'a j' c), s2 as (select 'aja' c), s3 as (select 'a
j' c) select c from (select c from s1 union all select c from s2 union all
select c from s3) as foo order by 1;
c
-----
a j
a j
aja
(3 rows)
test=> with s1 as (select 'a j' c), s2 as (select 'aja' c), s3 as (select 'a
k' c) select c from (select c from s1 union all select c from s2 union all
select c from s3) as foo order by 1;
c
-----
a j
aja
a k
(3 rows)