Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[E.Pasma]

Op 21 jul 2013, om 18:06 heeft Simon Slavin het volgende geschreven:


On 21 Jul 2013, at 4:41pm, E.Pasma  wrote:

Is a change in SQLite imaginable such that column expressions are  
not re-evaluated with each reference to the column alias?

...
...  This is partly because the order of evaluation of terms in a  
SELECT is not defined: SQL permits those three values to be  
evaluated in any order. ...


Your suggestion introduces a new requirement on how SQL works that  
it must figure out the "AS" expressions first.  Which might do  
nicely for your example but it would cause delays in other commands.


That is a plausible reason.
Just one more thought with respect to the originally posted query:

select 7 as a, 8 as b, a / b as c;

This might then as well be accepted by sqlite as if it occured in the  
order by clause. 
___

sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Petite Abeille]

On Jul 21, 2013, at 7:47 PM, Igor Tandetnik  wrote:

> This query is in fact perfectly legal. It's OK to refer to column aliases in 
> ORDER BY clause.

Perhaps in SQLite, yes. 

select 1 as a order by 1;
select 1 as a order by a;
select x as a from ( select 1 as x )  order by a;

But this is far from universal.

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Igor Tandetnik]

On 7/21/2013 12:06 PM, Simon Slavin wrote:

One of the problems with this is that it's not standard SQL.  You're not meant 
to be able to refer to column aliases inside the SELECT that defines them.  For 
instance

SELECT yearJoined AS y, ageWhenJoined AS a, (y-a) AS yob FROM members

is not allowed in the SQL standard.  This is partly because the order of 
evaluation of terms in a SELECT is not defined: SQL permits those three values 
to be evaluated in any order.  Now taking a look at your query

select id, (subquery) as c from categories order by c


This query is in fact perfectly legal. It's OK to refer to column 
aliases in ORDER BY clause.

--
Igor Tandetnik

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Luuk]

On 21-07-2013 12:01, E.Pasma wrote:


Op 21 jul 2013, om 11:27 heeft Mikael het volgende geschreven:


Hi Igor,

Ah I just noticed how you wrote your query and it delivers for it indeed.

Here's an arbitrary example verifying its works.

Neat - thanks!

sqlite3 test.sqlite
create table categories (id number);
insert into categories (id) values (5),(10),(15);
create table ot (v number);
insert into ot (v) values (2),(4),(6),(8),(14),(16),(20);

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
)
order by c;

Mikael


Only the execution plan of this query is not optimal:
0|0|0|SCAN TABLE categories (~100 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY





It's not optiomal, but would this be better, or worse?

select id, sum(a) as a, sum(b) as b, 1.0 * sum(a) / sum(b) as c FROM
(
select
 id,
 (case when ot.v>categories.id then ot.v else 0 end) as a,
 (case when ot.vi suspect that this wil depend on the number of rows in both tables, but 
i dont know how that will influence this query ;)



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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Keith Medcalf]

You can also use this form:

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories order by +id
)
order by c;



> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Clemens Ladisch
> Sent: Sunday, 21 July, 2013 07:43
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as
> c; " possible whatsoever?
> 
> RSmith wrote:
> > On 2013/07/21 12:01, E.Pasma wrote:
> >> Only the execution plan of this query is not optimal:
> >> 0|0|0|SCAN TABLE categories (~100 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
> >> 1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
> >> 2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
> >> 3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
> >> 4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
> >> 5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
> >> 6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
> >> 0|0|0|USE TEMP B-TREE FOR ORDER BY
> >
> > Well yes, the plan does not read like one would expect an optimal
> plan
> > to read like - but to the purpose of the original request there is
> no
> > more-optimal a plan, is there?
> 
> SQLite always attempts to flatten such subqueries:
> 
> 
> However, this can be counterproductive when the values in the subquery
> are subquery lookups themselves; storing the values once in a
> temporary
> table might be faster.
> 
> To prevent SQLite from flattening, break one of the rules from the
> link
> above; for example, add an OFFSET clause:
> 
> select id, a, b, 1.0 * a / b as c from
> (
> select
> id,
> (select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
> (select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
> from categories
> limit -1 offset 0
> )
> order by c;
> 
> 1|0|0|SCAN TABLE categories (~100 rows)
> 1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
> 2|0|0|SCAN TABLE ot AS ot1 (~33 rows)
> 1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
> 3|0|0|SCAN TABLE ot AS ot2 (~33 rows)
> 0|0|0|SCAN SUBQUERY 1 (~4294967295 rows)
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Simon Slavin]

On 21 Jul 2013, at 4:41pm, E.Pasma  wrote:

> Is a change in SQLite imaginable such that column expressions are not 
> re-evaluated with each reference to the column alias?
> This could also improve queries that use aliases only in the order by clause, 
> like
>   select id, (subquery) as c from categories order by c;

One of the problems with this is that it's not standard SQL.  You're not meant 
to be able to refer to column aliases inside the SELECT that defines them.  For 
instance

SELECT yearJoined AS y, ageWhenJoined AS a, (y-a) AS yob FROM members

is not allowed in the SQL standard.  This is partly because the order of 
evaluation of terms in a SELECT is not defined: SQL permits those three values 
to be evaluated in any order.  Now taking a look at your query

select id, (subquery) as c from categories order by c

SQL can choose to evaluate the ORDER BY clause first, and only then to evaluate 
the expressions "id" and "c".  But that leaves it ordering by a "c" value which 
it hasn't evaluated yet.

Your suggestion introduces a new requirement on how SQL works that it must 
figure out the "AS" expressions first.  Which might do nicely for your example 
but it would cause delays in other commands.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[E.Pasma]

Op 21 jul 2013, om 15:43 heeft Clemens Ladisch het volgende geschreven:


RSmith wrote:

On 2013/07/21 12:01, E.Pasma wrote:

Only the execution plan of this query is not optimal:
0|0|0|SCAN TABLE categories (~100 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY


Well yes, the plan does not read like one would expect an optimal  
plan

to read like - but to the purpose of the original request there is no
more-optimal a plan, is there?


SQLite always attempts to flatten such subqueries:


However, this can be counterproductive when the values in the subquery
are subquery lookups themselves; storing the values once in a  
temporary

table might be faster.

To prevent SQLite from flattening, break one of the rules from the  
link

above; for example, add an OFFSET clause:

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
limit -1 offset 0
)
order by c;

1|0|0|SCAN TABLE categories (~100 rows)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot1 (~33 rows)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|SCAN SUBQUERY 1 (~4294967295 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY


Regards,
Clemens

Is a change in SQLite imaginable such that column expressions are not  
re-evaluated with each reference to the column alias?
This could also improve queries that use aliases only in the order by  
clause, like

   select id, (subquery) as c from categories order by c;
Sorry if this is beyond the subject of thie thread,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Clemens Ladisch]

RSmith wrote:
> On 2013/07/21 12:01, E.Pasma wrote:
>> Only the execution plan of this query is not optimal:
>> 0|0|0|SCAN TABLE categories (~100 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
>> 1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
>> 2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
>> 3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
>> 4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
>> 5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
>> 6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
>> 0|0|0|USE TEMP B-TREE FOR ORDER BY
>
> Well yes, the plan does not read like one would expect an optimal plan
> to read like - but to the purpose of the original request there is no
> more-optimal a plan, is there?

SQLite always attempts to flatten such subqueries:


However, this can be counterproductive when the values in the subquery
are subquery lookups themselves; storing the values once in a temporary
table might be faster.

To prevent SQLite from flattening, break one of the rules from the link
above; for example, add an OFFSET clause:

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
limit -1 offset 0
)
order by c;

1|0|0|SCAN TABLE categories (~100 rows)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot1 (~33 rows)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|SCAN SUBQUERY 1 (~4294967295 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY


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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[RSmith]

Well yes, the plan does not read like one would expect an optimal plan to read like - but to the purpose of the original request 
there is no more-optimal a plan, is there?.
The entire column used to sort by  is made up on the spot and therefore temp BTrees are needed and all the other quirks, as 
expected. It's as optimal as it gets for this kind of query, unless I'm missing an obvious deficiency.



On 2013/07/21 12:01, E.Pasma wrote:

Only the execution plan of this query is not optimal:
0|0|0|SCAN TABLE categories (~100 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY




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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[E.Pasma]

Op 21 jul 2013, om 11:27 heeft Mikael het volgende geschreven:


Hi Igor,

Ah I just noticed how you wrote your query and it delivers for it  
indeed.


Here's an arbitrary example verifying its works.

Neat - thanks!

sqlite3 test.sqlite
create table categories (id number);
insert into categories (id) values (5),(10),(15);
create table ot (v number);
insert into ot (v) values (2),(4),(6),(8),(14),(16),(20);

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
)
order by c;

Mikael


Only the execution plan of this query is not optimal:
0|0|0|SCAN TABLE categories (~100 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY



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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Mikael]

Hi Igor,

Ah I just noticed how you wrote your query and it delivers for it indeed.

Here's an arbitrary example verifying its works.

Neat - thanks!

sqlite3 test.sqlite
create table categories (id number);
insert into categories (id) values (5),(10),(15);
create table ot (v number);
insert into ot (v) values (2),(4),(6),(8),(14),(16),(20);

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
)
order by c;

Mikael

2013/7/21 Igor Tandetnik 

> On 7/20/2013 7:04 PM, Mikael wrote:
>
>> C is not a present column, but is the result of an expression involving A
>> and B (namely, the expression A / B).
>>
>
> Yes. And this is notable because...?
>
>
>  Can you please take this into consideration and update the query you
>> proposed? :))
>>
>
> In what way do you believe the query as written is deficient?
>
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Igor Tandetnik]

On 7/20/2013 7:04 PM, Mikael wrote:

C is not a present column, but is the result of an expression involving A
and B (namely, the expression A / B).


Yes. And this is notable because...?


Can you please take this into consideration and update the query you
proposed? :))


In what way do you believe the query as written is deficient?
--
Igor Tandetnik

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Mikael]

Hi Igor,

C is not a present column, but is the result of an expression involving A
and B (namely, the expression A / B).

Can you please take this into consideration and update the query you
proposed? :))

Thanks,
Mikael

2013/7/20 Igor Tandetnik 

> On 7/20/2013 9:54 AM, Mikael wrote:
>
>> So again,
>>
>> SELECT
>> id,
>> (SELECT [very complex subselect here, that uses categories.id as input])
>> AS
>> a,
>> (SELECT [another very complex subselect here, that uses categories.id as
>> input]) AS b,
>> a / b AS c
>> FROM categories
>> ORDER BY c;
>>
>
> select id, a, b, a/b as c from (
>
> SELECT
> id,
> (SELECT [very complex subselect here, that uses categories.id as input])
> AS
> a,
> (SELECT [another very complex subselect here, that uses categories.id as
> input]) AS b
> FROM categories
> )
> ORDER BY c;
>
> --
> Igor Tandetnik
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Igor Tandetnik]

On 7/20/2013 3:29 PM, E.Pasma wrote:

Op 20 jul 2013, om 16:13 heeft Igor Tandetnik het volgende geschreven:

select id, a, b, a/b as c from (
SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as
input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b
FROM categories
)
ORDER BY c;


Hi, in my opinion there is nothing funky about this query and I hope my
colleagues agree. However it can be slower than strictly needed. I
experienced that the "very complex subqueries" are evaluated each time
again when referenced from the outer query.


My experience is different. The results of the inner query are saved 
into an ephemeral table, which is then sorted, and iterated over by the 
outer query.

--
Igor Tandetnik

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[E.Pasma]

Op 20 jul 2013, om 16:13 heeft Igor Tandetnik het volgende geschreven:


On 7/20/2013 9:54 AM, Mikael wrote:

So again,

SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as  
input]) AS

a,
(SELECT [another very complex subselect here, that uses  
categories.id as

input]) AS b,
a / b AS c
FROM categories
ORDER BY c;


select id, a, b, a/b as c from (
SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as  
input]) AS

a,
(SELECT [another very complex subselect here, that uses  
categories.id as

input]) AS b
FROM categories
)
ORDER BY c;

--
Igor Tandetnik


Hi, in my opinion there is nothing funky about this query and I hope  
my colleagues agree. However it can be slower than strictly needed. I  
experienced that the "very complex subqueries" are evaluated each time  
again when referenced from the outer query. That is three times here:

- select a, b
- select a / b as c
- order by c
I like to make myself more clear in a seperate mail and work out a  
reproducible case because I hope this could be improved in the  
optimizer.

Thanks, EPasma (sqlite version 3.7.15)

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Michael Black]

Is this some mental exercise? Why can't you do this in the calling code
rather than some funky SQL select?
Or add a custom function?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mikael
Sent: Saturday, July 20, 2013 8:54 AM
To: General Discussion of SQLite Database; luu...@gmail.com
Subject: Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; "
possible whatsoever?

Ah I realize now I didn't write it out in the example, but by thing and
thing2 I just allegorically wanted to represent a *very complex* subselect,
so here we go more clearly:

Inlining this subselect's SQL expression in the "A / B" part would make it
need to execute once more, which would make it take double the time, which
would be really long.

So again,

SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b,
a / b AS c
FROM categories
ORDER BY c;


any way to do it whatsoever?

Thanks :))
Mikael


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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Igor Tandetnik]

On 7/20/2013 9:54 AM, Mikael wrote:

So again,

SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b,
a / b AS c
FROM categories
ORDER BY c;


select id, a, b, a/b as c from (
SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b
FROM categories
)
ORDER BY c;

--
Igor Tandetnik

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Mikael]

Ah I realize now I didn't write it out in the example, but by thing and
thing2 I just allegorically wanted to represent a *very complex* subselect,
so here we go more clearly:

Inlining this subselect's SQL expression in the "A / B" part would make it
need to execute once more, which would make it take double the time, which
would be really long.

So again,

SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b,
a / b AS c
FROM categories
ORDER BY c;


any way to do it whatsoever?

Thanks :))
Mikael

2013/7/20 Luuk 

>
> SELECT
>   id,
>   othertable.thing as a,
>   othertable2.thing as b,
>   othertable.thing/othertable2.**thing as c
> FROM categories
> INNER jOIN othertable ON othertable.something = categories.id
> INNER JOIN othertable2 ON othertable2.something2 = categories.id
> ORDER BY c;
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Luuk]

On 20-07-2013 15:26, Mikael wrote:


SELECT
id,
(SELECT thing FROM othertable WHERE othertable.something = categories.id)
AS a,
(SELECT thing2 FROM othertable2 WHERE othertable2.something2 = categories.id)
AS b,
a / b AS c
FROM categories ORDER BY c;


Is there any way whatsoever to do this?



SELECT
  id,
  othertable.thing as a,
  othertable2.thing as b,
  othertable.thing/othertable2.thing as c
FROM categories
INNER jOIN othertable ON othertable.something = categories.id
INNER JOIN othertable2 ON othertable2.something2 = categories.id
ORDER BY c;

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Mikael]

Hi Clemens!

Hmm. Let's see how this fits into the bigger picture:

I have a table "categories". I'm doing a select for each of its id:s i.e:

SELECT id FROM categories;


Now, what I want to do is that I want to do two separate subselects that
use categories.id for the respective row the categories select is at, as
input.

So something like

SELECT
id,
(SELECT thing FROM othertable WHERE othertable.something = categories.id)
AS a,
(SELECT thing2 FROM othertable2 WHERE othertable2.something2 = categories.id)
AS b,
a / b AS c
FROM categories ORDER BY c;


Is there any way whatsoever to do this?

Thanks,
Mikael

2013/7/20 Clemens Ladisch 

> Mikael wrote:
> > Is anything like "select 7 as a, 8 as b, a / b as c;" possible?
>
> Not directy, but you could use a subquery:
>
>   SELECT *, a / b AS c FROM (SELECT 7 AS a, 8 AS b);
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Clemens Ladisch]

Mikael wrote:
> Is anything like "select 7 as a, 8 as b, a / b as c;" possible?

Not directy, but you could use a subquery:

  SELECT *, a / b AS c FROM (SELECT 7 AS a, 8 AS b);


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