Indeed, you are right, I do my test in pure sql and via ruby ActiveRecord, and I must had been confused,
the behaviour is correct in sql, it must have been a cache thing in ActiveRecord that prevented the reordering.
But meanwhile, I tested on our whole CI, and it took twice the normal time with updates to shuffle DB :(
For the union, I speak about production code like this:
"select count(*) from (#{directory_doctors_query_sql} union all #{profiles_query_sql}) as doctors"
In the to_sql, we cannot detect that we will be injected into a union.
So I cannot blindly add the random in the to_sql method.
On 7/24/19 1:45 AM, Cyril Champier wrote:
> Thanks for your answers.
> Unfortunately the update trick only seems to work under certain conditions.
>
> I do this to shuffle my patients table:
> UPDATE "patients"
> SET "updated_at" = NOW()
> WHERE "patients"."id" = (SELECT "patients"."id" FROM "patients" ORDER BY
> random() LIMIT 1)
>
> Then indeed, this query returns different order:
> SELECT *
> FROM "patients"
>
> But this one (because it use an index?) always returns values in the
> same order:
> SELECT "id"
> FROM "patients"
Hmm, I don't see that:
test=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
a | integer | | not null |
b | character varying | | |
Indexes:
"t1_pkey" PRIMARY KEY, btree (a)
test=# select * from t1;
a | b
---+---------
2 | cat
3 | fish
1 | dogfish
(3 rows)
test=# select a from t1;
a
---
2
3
1
(3 rows)
Are you sure there is nothing going on between the first and second
queries e.g. ROLLBACK?
>
>
>
> And for the other suggestion, I cannot blindly add 'ORDER BY random()'
> to every select,
> because of the incompatibility with distinct and union, and the way we
> use our orm.
>
Are you talking about the production or test queries above?
--
Adrian Klaver
adrian.klaver@aklaver.com
