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 Wed, Jul 24, 2019 at 4:48 PM Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> 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.kla...@aklaver.com
>
