On Sun, 13 Sep 2015 10:46:21 +0200
Clemens Ladisch wrote:
> James K. Lowden wrote:
> > select a, r from (
> > SELECT a, random() as r FROM a
> > ) as R
> > WHERE r <> r;
> >
> > will yield zero rows, every time.
>
> $ sqlite3
> SQLite version 3.8.12 2015-09-12
Hello !
Thank you for all feedbacks to this problem !
I originally gave a wrong/difficult example, the original one that other gave
here use the equality operator instead of inequality.
___test-random.sql
create table a(a);
insert into a values (1),(2),(3);
select 'direct select ='
Hello !
Thank you for the good example and explanation !
Cheers !
> Sun Sep 13 2015 2:06:14 am CEST CEST from "James K. Lowden"
> Subject: Re: [sqlite] Third test of json and
>index expressions, now it works
>
> On Wed, 09 Sep 2015 09:56:12 +0200
> "Domingo Alvarez Duarte" wrote:
>
>
James K. Lowden wrote:
> select a, r from (
> SELECT a, random() as r FROM a
> ) as R
> WHERE r <> r;
>
> will yield zero rows, every time.
$ sqlite3
SQLite version 3.8.12 2015-09-12 19:50:58
...
sqlite> create table a(a);
sqlite> insert into a values
On Wed, 09 Sep 2015 09:56:12 +0200
"Domingo Alvarez Duarte" wrote:
> With your knowledge could you give your view about how evaluation of
> calculated/function columns should be done to have correct results.
...
> CREATE TABLE a(b);
...
> SELECT a, random() as r FROM a WHERE r <> r;
Hello !
This fix the example I gave on the other email it was "r <> r" but it's more
easy to see the problem with "r = r".
Nice explanation !
With your knowledge could you give your view about how evaluation of
calculated/function columns should be done to have correct results.
Like
On 9 Sep 2015, at 8:56am, Domingo Alvarez Duarte
wrote:
> SELECT a, random() as r FROM a WHERE r <> r;
This is not valid SQL. You won't find any official SQL standard saying that
'r' can be referred to in the WHERE clause.
You may find implementations of SQL where which will accept that
Hello !
Nice explanation !
With your knowledge could you give your view about how evaluation of
calculated/function columns should be done to have correct results.
Like another example given on other thread:
_
CREATE TABLE a(b);
INSERT INTO a(b) VALUES(1),(2),(3);
On Sat, 5 Sep 2015 09:07:11 -0700
Darko Volaric wrote:
> I'm asking why the SQL standard restricts the use of aliases in this
> way and what the benefit of this restriction is.
Rationales in SQL are hard to come by. The language was promulgated by
a private firm, and the standard evolved under
op 06-09-2015 22:42 schreef Scott Robison op scott at casaderobison.com:
> On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma wrote:
>
>>
>> Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven:
>>
>>>
>>> One, the argument wasn't whether or not column expressions were called
>>>
On Sun, Sep 6, 2015 at 3:47 PM, pasma10 wrote:
> op 06-09-2015 22:42 schreef Scott Robison op scott at casaderobison.com:
>
> > On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma wrote:
> >
> >>
> >> Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven:
> >>
> >>>
> >>> One, the argument
On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma wrote:
>
> Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven:
>
>>
>> One, the argument wasn't whether or not column expressions were called
>> multiple times. My understanding of the debate was that using aliases in
>> place of their
Op 6 sep 2015, om 03:17 heeft Scott Robison het volgende geschreven:
>
> select * from (select a as b from c) where b is something
>
Still column expressions are called mutiple times.
SQLite 3.8.11 2015-07-27 13:49:41
b8e92227a469de677a66da62e4361f099c0b79d0
create table c(a)
;
explain query
On Sun, Sep 6, 2015 at 4:36 AM, E.Pasma wrote:
>
> Op 6 sep 2015, om 03:17 heeft Scott Robison het volgende geschreven:
>
>>
>> select * from (select a as b from c) where b is something
>>
>> Still column expressions are called mutiple times.
>
> SQLite 3.8.11 2015-07-27 13:49:41
Hello !
Sorry about that !
I confused your email with other person that was so negative and defensive
about any critic to sqlite even constructive ones.
I apologize again !
Cheers !
> Sat Sep 05 2015 10:19:55 pm CEST CEST from "R.Smith"
>Subject: Re: [sqlite] Third test of json
On 2015-09-05 08:58 PM, Domingo Alvarez Duarte wrote:
> Hello !
>
> Again your proposition doesn't stand up !
Again?
That was my first post on the subject and it was in reply to Darko whom
asked for a specific reason, so I supplied one. (Which by the way isn't
to say I am 100% convinced by
Hello !
It seems that you mix apples to oranges and try to compare then.
One thing is formal grammar/syntax and the other is
implementation/optimization/execution.
The fact that sqlite does things in one way now do not mean it's the
best/definitive way of implement things and that can be
Hello !
Again your proposition doesn't stand up !
There is no "waste of cpu cycles" for work that is not done, I mean for the
ones that write queries for the machine instead to the humans, they will
continue to have the same results (less parsing steps, besides the parsing
normally accounts
On 2015-09-05 08:07 PM, Darko Volaric wrote:
> So my question remains: why not have this user friendly feature? What are
> the motivations for not having it?
Waste of CPU cycles punishing those users who stick to valid SQL for the
sins of those who like shortcuts.
On 5 Sep 2015, at 7:07pm, Darko Volaric wrote:
> That's not a valid reason since it's trivial for SQLite to transform
> aliases by substituting their names with their definitions. It could be
> handled in the parser code.
And by doing that you would not get optimization, since SQLite would
On Sep 5, 2015 4:10 PM, "Darko Volaric" wrote:
>
> That's not true. SQLite doesn't have to discard that information after
> performing the substitutions. It can use it later for optimizations.
>
> I know that SQLite allows it. I'm not complaining about anything. Have a
> look at my original
Hello !
Although your explanation is interesting it doesn't seems to be correct:
First of all there is no point to to do anything if the whole sql statement
is no correct.
I mean:
- If there is any reference to inexistent columns/functions/tables/views
- If any expression is not
On 5 Sep 2015, at 5:07pm, Darko Volaric wrote:
> That's not what I said, why don't you read/quote the whole sentence:
>
> "Besides being part of the standard (I assume), what's the rationale for
> this restriction?"
>
> I'm asking why the SQL standard restricts the use of aliases in this way
On 5 Sep 2015, at 3:19pm, Darko Volaric wrote:
> Besides being part of the standard (I assume)
You assume incorrectly. In the classic SQL model, aliases to column names are
assigned after the results have been returned. In other words, aliases cannot
be used in the WHERE clause.
Simon.
Hello !
I also prefer a "DRY" approach than be repeating things.
Repetition is the mother of several errors !
Cheers !
> Sat Sep 05 2015 4:19:00 pm CEST CEST from "Darko Volaric"
> Subject: Re: [sqlite] Third test of json and index
>expressions, now it works
>
> Besides being part of
That's not true. SQLite doesn't have to discard that information after
performing the substitutions. It can use it later for optimizations.
I know that SQLite allows it. I'm not complaining about anything. Have a
look at my original question, which is, restated:
What is the rationale behind
Hello again !
There is still some opportunities for constant folding that sqlite is not
using, I'm not saying that is easy to implement.
The same principle could be applied to deterministic functions where all of
it's parameters end up been constants.
_output of "sqlite3 <
Hello !
Thanks for reply !
I still think that is worth having warnings for the already known quirks on
sqlite, probably even show it to stderr when building in debug mode because I
believe at some point people will do some debugging.
Or maybe even better having a pragma "PRAGMA
Hello !
Thanks to point out that plain sql is better to demonstrate a point when
possible !
And after your answer I did another tests and could see that by using views
then we can achieve the use of aliases in a clean way.
This is supposed to be valid, doesn't it ?
__Output of
That's not a valid reason since it's trivial for SQLite to transform
aliases by substituting their names with their definitions. It could be
handled in the parser code.
Meanwhile it's much harder for a human to do the opposite.
So my question remains: why not have this user friendly feature?
Hello !
> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps. I'd do so if I could.
> --
>
> ?
>
>
What about the warning messages to stderr through sqlite3 when opening
databases with
On 5 Sep 2015, at 10:07am, Domingo Alvarez Duarte wrote:
> What about the warning messages to stderr through sqlite3 when opening
> databases with invalid sql constructions, this way we can gradually have less
> and less code written in a non compliant way.
It might be acceptable to put
> > Besides being part of the standard (I assume)
> You assume incorrectly. In the classic SQL model, aliases to column names
> are assigned after the results have been returned. In other words,
> aliases cannot be used in the WHERE clause.
Or group by clause ...
> Simon.
>
On 5 Sep 2015, at 5:35am, Richard Hipp wrote:
> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps. I'd do so if I could.
That's what SQLite4 is for. I hope.
Simon.
That's not what I said, why don't you read/quote the whole sentence:
"Besides being part of the standard (I assume), what's the rationale for
this restriction?"
I'm asking why the SQL standard restricts the use of aliases in this way
and what the benefit of this restriction is.
On Sat, Sep 5,
Besides being part of the standard (I assume), what's the rationale for
this restriction?
It would seem that standard SQL is being willfully less efficient and more
error prone by making the user rewrite expressions.
Isn't this in the same category as manifest typing, where a more liberal
On 9/4/15, Domingo Alvarez Duarte wrote:
>
> Would be nice to sqlite be able to recognize aliases and also do not call
> column expressions multiple times.
>
> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS val
> from json_tbl where val = 'the_value_1';"));
>
This is
Hello again !
I was trying to use the alias in the where clause hopping sqlite would be
smart enough to recognize it and use the index but that doesn't happen.
So I tried again repeating the expression instead of using the alias and now
sqlite recognize the index and run very fast.
Would
38 matches
Mail list logo
