2018-08-02 23:12 GMT+02:00 R Smith <ryansmit...@gmail.com>:
> On 2018/08/02 10:29 PM, Csányi Pál wrote:
>>
>> Hi,
>>
>> I just want to know why the following SQLite query does not work as I
>> expected?
>>
>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
>> 2017-10-03|3
>> which is not what I am expecting.
>>
>> I am expecting the followings:
>> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
>> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
>> 3. because 2016-10-03 = 2016-10-03 it count 1
>> 4. then add to the result date 2016-10-03 once again 1 year which is
>> 2017-10-03
>> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
>> 6. because 2017-10-03 > 2016-10-03 it does not count 2
>> 7. it should gives the following result:
>> 2016-10-03|1
>>
>> What am I doing wrong here?
>>
>
> When the recursive Query starts up, the first value that it outputs is given
> by the very first part of the query, namely: VALUES('2015-10-03')
> So on the first iteration, it will produce one row like this:
> '2015-10-03'
> regardless of the rest of the Query. This row is pushed into the recursion
> buffer.
>
> After that it then reads a row from the recursion buffer and checks (within
> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
> '2016-11-01', and finds that it definitely IS less, so continues to produce
> the another line of output.
>
> The output created is that date from the buffer (2015-10-03) which is put
> through the given calculation: date(dateD, '+1 year') to give:
> '2016-10-03'
>
> It then continues to push that next row into the recursion buffer and next
> reads again from it and again checks if it (2016-10-03) is <= than
> 2016-11-01, which again it is... so it continues to produce the next output
> row, which after calculation becomes:
> '2017-10-03'
>
> It then continues to push that again into the buffer and again read it and
> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME, it
> isn't... so it stops right there.
>
> So in the end, it has produced 3 output rows namely:
> '2015-10-03'
> '2016-10-03'
> '2017-10-03'
>
> Which is exactly what you've asked for.
>
> Note: The first part of the query will ALWAYS reach the output buffer, even
> if it isn't a recursive query, and the UNION is NOT specified, you will get
> at least the '2015-10-03' value.
> Note: When comparing in the WHERE clause, you do not compare the newly
> calculated value (date(xxx, +1 year)), but indeed you compare the
> before-calculated value, i.e. the previous value in the buffer (the same as
> how your calculation is done on the PREVIOUS value in the buffer to yield
> the new date with.
>
> I hope that helps to make sense.
>
> Cheers,
> Ryan
I think then that that the following SQL query gives to me the desired result:
WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD)-2 FROM dates;
count(dateD)-2 does the math. Right?
--
Best, Pali
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
