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
Ryan, thank you very much for the explanation! Then, how can I implement steps from 1 to 7 in SQLite language? -- Best, Pali _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users