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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
