Re: [sqlite] Strange problem with using random with a table

[Don V Nielsen]

Does the same thing happen if you select from Psalms, or Thessalonians,
instead of Proverbs?  Sorry. Little religion humor. Too early in the
morning and I haven't had my coffee, yet.

On Fri, Nov 11, 2016 at 4:34 AM, Cecil Westerhof 
wrote:

> 2016-11-11 11:22 GMT+01:00 Clemens Ladisch :
> > Cecil Westerhof wrote:
> >> When I execute the following query:
> >> SELECT *, randomiser * 1000
> >> FROM (SELECT abs(random()) / 1 AS randomiser);
> >>
> >> I get normal results:
> >> 61|61000
> >>
> >> But when I change it to:
> >> SELECT *, randomiser * 1000
> >> FROM (SELECT abs(random()) / 1 AS randomiser
> >>   FROM   proverbs)
> >> LIMIT 5;
> >>
> >> I get very strange results:
> >> 382|606000
> >> 172|148000
> >> 144|592000
> >> 181|136000
> >> 123|469000
> >>
> >> What could be happening here?
> >
> > In the first query, the subquery is computed with a temporary table
> > (actually, a coroutine) before the values of the outer query are
> > computed.
> >
> > The second query is flattened, so it ends up like this:
> >
> >   SELECT abs(random()) / 1,
> >  abs(random()) / 1 * 1000
> >   FROM proverbs
> >   LIMIT 5;
> >
> > "randomiser" is not a table column, it is just an _alias_ for the
> > expression, so this is an allowed transformation.
> >
> > To prevent this, violate one of the rules that the optimizer currently
> > uses to determine whether flattening is possible:
> > 
> > For example, put a LIMIT clause also into the subquery.
>
> I need all the records, so I use:
> SELECT *
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> FROM (
> SELECT abs(random()) / 1 AS randomiser
> FROM   proverbs
> LIMIT  (SELECT COUNT(*) FROM proverbs)
> )
> LIMIT 5;
>
> Not very clean, but it works.
>
> Maybe a good idea to have an option to disable flattening?
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange problem with using random with a table

[Cecil Westerhof]

2016-11-11 11:22 GMT+01:00 Clemens Ladisch :
> Cecil Westerhof wrote:
>> When I execute the following query:
>> SELECT *, randomiser * 1000
>> FROM (SELECT abs(random()) / 1 AS randomiser);
>>
>> I get normal results:
>> 61|61000
>>
>> But when I change it to:
>> SELECT *, randomiser * 1000
>> FROM (SELECT abs(random()) / 1 AS randomiser
>>   FROM   proverbs)
>> LIMIT 5;
>>
>> I get very strange results:
>> 382|606000
>> 172|148000
>> 144|592000
>> 181|136000
>> 123|469000
>>
>> What could be happening here?
>
> In the first query, the subquery is computed with a temporary table
> (actually, a coroutine) before the values of the outer query are
> computed.
>
> The second query is flattened, so it ends up like this:
>
>   SELECT abs(random()) / 1,
>  abs(random()) / 1 * 1000
>   FROM proverbs
>   LIMIT 5;
>
> "randomiser" is not a table column, it is just an _alias_ for the
> expression, so this is an allowed transformation.
>
> To prevent this, violate one of the rules that the optimizer currently
> uses to determine whether flattening is possible:
> 
> For example, put a LIMIT clause also into the subquery.

I need all the records, so I use:
SELECT *
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
FROM (
SELECT abs(random()) / 1 AS randomiser
FROM   proverbs
LIMIT  (SELECT COUNT(*) FROM proverbs)
)
LIMIT 5;

Not very clean, but it works.

Maybe a good idea to have an option to disable flattening?

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange problem with using random with a table

[Cecil Westerhof]

2016-11-11 11:01 GMT+01:00 Cecil Westerhof :
> When I execute the following query:
> SELECT *
> ,  randomiser * 1000
> FROM (
> SELECT abs(random()) / 1 AS randomiser
> );
>
> I get normal results:
> 61|61000
>
>
> But when I change it to:
> SELECT *
> ,  randomiser * 1000
> FROM (
> SELECT abs(random()) / 1 AS randomiser
> FROM   proverbs
> )
> LIMIT 5;
>
> I get very strange results:
> 382|606000
> 172|148000
> 144|592000
> 181|136000
> 123|469000
>
> What could be happening here?
>
> I am using SQLite version 3.8.10.2 on openSUSE Leap 42.1.

It even gets stranger. When I use:
SELECT *
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
,  randomiser * 1000
FROM (
SELECT abs(random()) / 1 AS randomiser
FROM   proverbs
)
LIMIT 5;

I get:
453|75000|90|772000|823000|51000
93|572000|825000|881000|339000|681000
223|213000|726000|456000|688000|772000
565|359000|771000|674000|547000|26000
114|822000|188000|576000|635000|229000

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange problem with using random with a table

[Clemens Ladisch]

Cecil Westerhof wrote:
> When I execute the following query:
> SELECT *, randomiser * 1000
> FROM (SELECT abs(random()) / 1 AS randomiser);
>
> I get normal results:
> 61|61000
>
> But when I change it to:
> SELECT *, randomiser * 1000
> FROM (SELECT abs(random()) / 1 AS randomiser
>   FROM   proverbs)
> LIMIT 5;
>
> I get very strange results:
> 382|606000
> 172|148000
> 144|592000
> 181|136000
> 123|469000
>
> What could be happening here?

In the first query, the subquery is computed with a temporary table
(actually, a coroutine) before the values of the outer query are
computed.

The second query is flattened, so it ends up like this:

  SELECT abs(random()) / 1,
 abs(random()) / 1 * 1000
  FROM proverbs
  LIMIT 5;

"randomiser" is not a table column, it is just an _alias_ for the
expression, so this is an allowed transformation.

To prevent this, violate one of the rules that the optimizer currently
uses to determine whether flattening is possible:

For example, put a LIMIT clause also into the subquery.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange problem with using random with a table

[Cecil Westerhof]

When I execute the following query:
SELECT *
,  randomiser * 1000
FROM (
SELECT abs(random()) / 1 AS randomiser
);

I get normal results:
61|61000


But when I change it to:
SELECT *
,  randomiser * 1000
FROM (
SELECT abs(random()) / 1 AS randomiser
FROM   proverbs
)
LIMIT 5;

I get very strange results:
382|606000
172|148000
144|592000
181|136000
123|469000

What could be happening here?

I am using SQLite version 3.8.10.2 on openSUSE Leap 42.1.

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users