/ All files named "test/window1.test"
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

History for test/window1.test

2020-07-13
15:20
[e52b81ff] part of check-in [e6c2192e] Fix a problem causing queries containing window functions to ignore collation sequences under some circumstances. (check-in: [e6c2192e] user: dan branch: trunk, size: 52300)
2020-06-11
15:47
[9d7f4990] part of check-in [50b4a1c7] Avoid rewriting compound SELECT statements that use a different collation sequence for ORDER BY and record processing a second time if they contain window functions. Fix for [b706351c]. (check-in: [50b4a1c7] user: dan branch: mistake, size: 50908)
2020-06-10
10:58
[486f0e01] part of check-in [094dcfe7] Ensure that the "push-down" optimization does not push constraints down into compound queries if any of the component queries uses window functions. (check-in: [094dcfe7] user: dan branch: trunk, size: 50326)
2020-06-07
20:18
[49f81c25] part of check-in [3c840b4d] AggInfo objects might be referenced even after the sqlite3Select() function that created them has exited. So AggInfo cannot be a stack variable. And it must not be freed until the Parse object is destroyed. (check-in: [3c840b4d] user: drh branch: persist-agginfo, size: 49792)
17:33
[30ec5cc3] part of check-in [7682d8a7] Alternative fix to ticket [c8d3b9f0a750a529]: Prior to deleting or modifying an Expr not that is referenced by an AggInfo, modify the AggInfo to get its own copy of the original Expr. (check-in: [7682d8a7] user: drh branch: persist-agginfo, size: 48785)
00:43
[6df63318] part of check-in [a6c2147c] Extra steps to ensure that queries that involve both window functions and aggregate functions are processed correctly by sqlite3WindowRewrite(). Fix for ticket [e5504e987e419fb0]. (check-in: [a6c2147c] user: drh branch: early-winfunc-rewrite-dev, size: 48440)
2020-06-06
19:35
[aacc5150] part of check-in [8583c348] When determining if the subquery inserted by the window-function rewriter is an aggregate query, aggregate functions that are in parameters to another window function do not count. Fix for ticket [1f6f353b684fc708] (check-in: [8583c348] user: drh branch: early-winfunc-rewrite-dev, size: 48142)
18:34
[e71abf4c] part of check-in [03b32be4] In the query flattener, defer deleting content until after associated Parse object is destroyed, in case some of the deleted expressions have been collected for use by sAggInfo. (check-in: [03b32be4] user: drh branch: early-winfunc-rewrite-dev, size: 47872)
14:44
[bd7894d1] part of check-in [2cddb24e] When rewriting the parse-tree for window functions, ensure that the inserted subqueries have an accurate SF_Aggregate bit set. This change also coincidentally fixes ticket [0899cf62f597d7e7], even thought that was not the issue we were working on at the time. (check-in: [2cddb24e] user: drh branch: early-winfunc-rewrite-dev, size: 47604)
14:29
[2063fc08] part of check-in [c96914ea] Fix an assert() failure that could occur if an ORDER BY expression attached to a compound query contains a subquery that (a) is itself a compound query, (b) uses window functions and (c) has an ORDER BY clause that includes another sub-query. (check-in: [c96914ea] user: dan branch: early-winfunc-rewrite-dev, size: 47152)
2020-06-05
15:26
[e5960fcb] part of check-in [0b42a227] Do parse-tree transformations required for window functions prior to running aggregate function analysis. Fix for ticket [c8d3b9f0a750a529]. (check-in: [0b42a227] user: drh branch: early-winfunc-rewrite-dev, size: 46427)
14:53
[7383659a] part of check-in [2011e832] Ensure that the "push-down" optimization does not push constraints down into compound queries if any of the component queries uses window functions. (check-in: [2011e832] user: dan branch: early-winfunc-rewrite, size: 46137)
2020-05-24
03:38
[502a3e72] part of check-in [ad7bb70a] When rewriting a query for window functions, if the rewrite changes the depth of TK_AGG_FUNCTION nodes, be sure to adjust the Expr.op2 field appropriately. Fix for ticket [7a5279a25c57adf1] (check-in: [ad7bb70a] user: drh branch: trunk, size: 45567)
2020-05-11
10:55
[a3504d44] part of check-in [155e6649] Fix a problem handling constant integer expressions with collation sequences in PARTITION BY clauses. (check-in: [155e6649] user: dan branch: trunk, size: 45214)
2020-04-09
18:51
[b0739f48] part of check-in [d6fda470] Merge recent trunk changes into the apple-osx branch. (check-in: [d6fda470] user: drh branch: apple-osx, size: 43860)
2020-04-03
13:39
[4aa2a388] part of check-in [b6467491] Do not suppress errors when resolving references in an ORDER BY clause belonging to a compound SELECT within a view or trigger within ALTER TABLE. Fix for ticket [a10a14e9b4ba2]. Also, in the event of a semantic error in an aggregate query, early-out the resetAccumulator() function to prevent problems due to incomplete or incorrect initialization of the AggInfo object. Fix for ticket [af4556bb5c285c08]. (check-in: [b6467491] user: drh branch: branch-3.31, size: 41914)
13:19
[ec792f92] part of check-in [4a302b42] In the event of a semantic error in an aggregate query, early-out the resetAccumulator() function to prevent problems due to incomplete or incorrect initialization of the AggInfo object. Fix for ticket [af4556bb5c285c08]. (check-in: [4a302b42] user: drh branch: trunk, size: 43861)
2020-02-29
17:19
[879a1928] part of check-in [1e174ed0] Fix a problem with window functions occuring within sub-selects that are part of an OR term in a WHERE clause of the outer SELECT. (check-in: [1e174ed0] user: dan branch: trunk, size: 43591)
2020-01-15
12:49
[ab9c9b3b] part of check-in [dadedf41] Merge all recent enhancements and fixes from trunk into the apple-osx branch. (check-in: [dadedf41] user: drh branch: apple-osx, size: 41641)
2020-01-09
20:11
[cec56b9a] part of check-in [83dc5567] Fix an assert() in window.c that could fail with some obscure SELECT statements that use window functions. Ticket [678ecf429f8d1a5f] (check-in: [83dc5567] user: dan branch: trunk, size: 41642)
2020-01-03
13:55
[b61afc46] part of check-in [4889cbf8] Ensure that when a Select object is reset a new, empty, SrcList is allocated. (check-in: [4889cbf8] user: dan branch: trunk, size: 41125)
2020-01-01
20:17
[30f38038] part of check-in [ce141732] Ensure that when code for a scalar SELECT featuring window functions is generated more than once by the planner, separate ephemeral tables are opened for each instance. (check-in: [ce141732] user: dan branch: trunk, size: 40648)
2019-12-28
18:25
[cd6e2daf] part of check-in [eeb76f62] Do not attempt to flatten compound sub-queries in a FROM clause into the parent if any component of the sub-query uses a window function. (check-in: [eeb76f62] user: dan branch: trunk, size: 40160)
2019-12-27
20:06
[caf402a9] part of check-in [597896ed] Remove a NEVER() that is no longer true. Fix for [36ffedcb9]. (check-in: [597896ed] user: dan branch: trunk, size: 39783)
19:46
[9b17982e] part of check-in [e3b5fc05] Fix a problem involving window function aliases being referenced from sub-selects. (check-in: [e3b5fc05] user: dan branch: trunk, size: 39121)
18:15
[2a017e76] part of check-in [4e6fbb1a] Add a missing "ifcapable rtree {...}" line to the window1.test test module. (check-in: [4e6fbb1a] user: drh branch: trunk, size: 37736)
16:25
[9f5883b8] part of check-in [99609786] Fix a problem with window functions in aggregate queries that do not have GROUP BY clauses. (check-in: [99609786] user: dan branch: trunk, size: 37702)
15:31
[f091ea2d] part of check-in [45c64d39] Do not mistake constant integers in a PARTITION BY expression for references to ORDER BY style references to values returned by the SELECT statement. (check-in: [45c64d39] user: dan branch: trunk, size: 36738)
08:57
[7619f715] part of check-in [1ffc045d] When determining if an aggregate within a sub-query should be processed as part of the sub-query or an outer query, consider any FILTER clause in the same way as the arguments to the aggregate. (check-in: [1ffc045d] user: dan branch: trunk, size: 35891)
2019-12-25
23:54
[7968f1ba] part of check-in [fa58aad4] When the sqlite3WindowRewrite() routine detects and error, have it convert the SELECT statement into just "SELECT null" so that it does not leave the parse tree in a goofy state that can cause problems with subsequent code before the stack has a chance to unwind and report the error. Ticket [d87336c81c7d0873] (check-in: [fa58aad4] user: drh branch: trunk, size: 35831)
2019-12-23
15:17
[790c9f12] part of check-in [2f179749] Fix a case in which SQLite could fail to identify "x BETWEEN ? AND ?" being true as implying that x is not null. Ticket [dfd66334]. (check-in: [2f179749] user: dan branch: trunk, size: 35537)
14:20
[31b56e53] part of check-in [0b1dbd60] For expressions like (x, y) IN (SELECT ...) where the SELECT uses window-functions, require that all columns on the LHS be indexed before an index can be used. Fix for [d9ed4ebe]. (check-in: [0b1dbd60] user: dan branch: trunk, size: 35436)
2019-12-20
15:35
[29efd3d6] part of check-in [3cc2b570] When computing dependencies on WHERE-clause terms, be sure to take into account the FILTER clause of aggregate functions. Problem reported by Manuel Rigger. (check-in: [3cc2b570] user: drh branch: trunk, size: 35216)
2019-12-18
09:17
[6db2c38a] part of check-in [519864da] Add a test case for fix in check-in [1ca0bd982ab1183b] (check-in: [519864da] user: drh branch: trunk, size: 34693)
2019-12-07
13:42
[b3567fde] part of check-in [26d991f2] Correctly deal with multi-row VALUES clauses that contain window functions. (check-in: [26d991f2] user: drh branch: trunk, size: 34223)
2019-12-04
01:42
[a73b1d41] part of check-in [92893b79] Fix a double-free that could occur when a component of a compound SELECT with an ORDER BY clause uses named window definitions. (check-in: [92893b79] user: dan branch: trunk, size: 33932)
2019-11-23
15:10
[027053b7] part of check-in [57070c68] Avoid evaluating PARTITION BY or ORDER BY expressions multiple times for window function queries that use multiple window functions with the same window-definition. (check-in: [57070c68] user: dan branch: trunk, size: 33193)
2019-11-22
10:14
[60ba11fb] part of check-in [bcdd66c1] Fix a crash that could occur if a sub-select that uses both DISTINCT and window functions also used an ORDER BY that is the same as its select list. (check-in: [bcdd66c1] user: dan branch: trunk, size: 32892)
2019-11-16
11:33
[e88f674b] part of check-in [0adb273f] Fix a potential NULL pointer dereference on a RENAME TABLE that references a VIEW with a logic error in a window function in the ORDER BY clause. (check-in: [0adb273f] user: drh branch: trunk, size: 32600)
2019-10-10
15:17
[453bb9dc] part of check-in [ccba7202] Be sure to rewrite column references inside FILTER clauses and window frame definitions when flattening queries. Fix for [1079ad19]. (check-in: [ccba7202] user: dan branch: trunk, size: 32287)
2019-07-19
01:25
[9f42ddb0] part of check-in [ce6bf395] Fix a new problem in the BETWEEN operator when applied to a window function. The problem was introduced yesterday by check-in [7ef7b23cbb1b9ace]. This cherry-pick also needed to remove an assert from sqliteExprDelete(). (check-in: [ce6bf395] user: drh branch: branch-3.29, size: 31180)
01:11
[376a7c9c] part of check-in [47e23064] Fix a new problem in the BETWEEN operator when applied to a window function. The problem was introduced yesterday by check-in [7ef7b23cbb1b9ace]. (check-in: [47e23064] user: drh branch: trunk, size: 31303)
2019-07-13
17:54
[82202e13] part of check-in [8c80e81d] Fix a typo in test file window1.test. (check-in: [8c80e81d] user: dan branch: trunk, size: 30585)
17:45
[18ec9bbd] part of check-in [871796bb] Fix error handling for the case where a window function is passed the wrong number of arguments. (check-in: [871796bb] user: dan branch: trunk, size: 30583)
2019-04-03
16:27
[8d453bfa] part of check-in [39225cc7] Fix a couple of problems with "RANGE BETWEEN <expr> PRECEDING AND <expr> PRECEDING" frames. (check-in: [39225cc7] user: dan branch: trunk, size: 30462)
2019-04-01
18:43
[71bb9bfb] part of check-in [b36813d6] Add a test case to cover a missed VDBE branch generated by window.c. (check-in: [b36813d6] user: dan branch: trunk, size: 28357)
2019-03-22
13:56
[7484bf9a] part of check-in [9aca86f9] Fix a problem with a window function in a correlated sub-query where at least one reference to the outer SELECT appears in a FROM clause sub-select. (check-in: [9aca86f9] user: dan branch: window-functions, size: 27970)
2019-03-21
13:51
[c4ff89d6] part of check-in [c7b33618] Remove assert() statements based on the counter-factual proposition that 0 is not a valid cursor number. (check-in: [c7b33618] user: dan branch: window-functions, size: 27191)
2019-03-19
17:45
[c7c274d5] part of check-in [e195948a] Add tests to ensure that the window functions implementation is not generating code for unnecessary sorts. (check-in: [e195948a] user: dan branch: window-functions, size: 27077)
11:56
[320d8812] part of check-in [c02f77b1] Revert the OP_MustBeInt opcode implementation on this branch so that it again matches trunk. The extra functionality is no longer required. (check-in: [c02f77b1] user: dan branch: window-functions, size: 25632)
06:40
[1e011872] part of check-in [a6d61dfd] Ensure that all the new window-function keywords can still be used as SQL identifiers. (check-in: [a6d61dfd] user: dan branch: window-functions, size: 24793)
2019-03-16
20:29
[6c7bbed9] part of check-in [cebe09e1] Fix problems with RANGE windows and string, blob and NULL values. (check-in: [cebe09e1] user: dan branch: window-functions, size: 24358)
2019-03-13
17:31
[d690a34f] part of check-in [0b904517] Merge latest trunk changes into this branch. (check-in: [0b904517] user: dan branch: window-functions, size: 22011)
2019-03-12
09:49
[53db0413] part of check-in [0524aaaf] Fix a crash that could occur if the RHS of an IN expression is a correlated sub-query that refers to the outer query from within a window frame definition only. (check-in: [0524aaaf] user: dan branch: trunk, size: 18997)
2019-03-04
21:08
[5d9b3cfc] part of check-in [9b4d561f] Merge trunk changes into this branch. (check-in: [9b4d561f] user: dan branch: window-functions, size: 21300)
2019-02-22
19:24
[d141eba0] part of check-in [579b66ea] Internally, remove all references to a Window object that belongs to an expression in an ORDER BY clause if that expression is converted to an alias of a result-set expression. Fix for [4feb3159c6]. (check-in: [579b66ea] user: dan branch: trunk, size: 18286)
2019-02-16
17:27
[4d98700c] part of check-in [c155125f] Add support for chaining of WINDOW definitions. (check-in: [c155125f] user: dan branch: window-functions, size: 20829)
2019-01-26
16:34
[8910b6d1] part of check-in [d536be69] Roll back the transaction if a write statement fails with OE_Abort but there is no open statement transaction. (check-in: [d536be69] user: dan branch: rollback-abort, size: 18782)
2019-01-23
16:59
[2798c824] part of check-in [0387cb3a] Fix problems with sub-selects in WINDOW definitions. Also rename-column operations when the column being renamed appears in a WINDOW definition that is part of a VIEW or TRIGGER. (check-in: [0387cb3a] user: dan branch: trunk, size: 17815)
2019-01-01
18:00
[9003bb75] part of check-in [5c188361] Ensure that when a new cursor is opened by OP_OpenDup, any existing cursor with the same id opened by a previous OP_OpenDup is closed first. (check-in: [5c188361] user: dan branch: trunk, size: 17311)
2018-12-31
16:36
[148552f6] part of check-in [4678cb10] Fix the OP_OpenEphemeral opcode in the bytecode engine so that if it is called a second or subsequent time, it merely clears the existing table rather than creating a new one. Proposed fix for ticket [d0866b26f83e9c55e30de0821f5d]. (check-in: [4678cb10] user: drh branch: trunk, size: 17199)
2018-12-22
15:46
[a96a80bd] part of check-in [ad70f03c] Remove unnecessary whitespace from testcase result strings. (check-in: [ad70f03c] user: drh branch: trunk, size: 16107)
2018-12-06
19:56
[1003e19b] part of check-in [a9a3b532] New test case that still hits an assertion fault, just to prove that the previous checkin merely made the problem more obscure and did not completely fix it. (check-in: [a9a3b532] user: drh branch: ticket-f09fcd17810f, size: 16108)
19:15
[d55c41a0] part of check-in [f1b18d44] Simplify the query flattener so that it does not duplicate the WHERE clause of subquery that is being incorporated into the outer query - copies it directly. This is more efficient. And it also fixes the specific test case show for ticket [f09fcd17810f65f71789525] but it does not resolve the more general problem that sqlite3ExprDup() does not correctly duplicate expressions that contain subqueries with window functions. (check-in: [f1b18d44] user: drh branch: ticket-f09fcd17810f, size: 15619)
2018-10-23
13:48
[02e481ac] part of check-in [059ff53a] Fix a problem with using window functions in compound (UNION, INTERSECT etc.) queries. (check-in: [059ff53a] user: dan branch: trunk, size: 15324)
2018-09-17
15:19
[474bef1a] part of check-in [c6c9585f] Disable the ORDER BY LIMIT optimization in queries using window functions. This fixes a problem that was introduced by check-in [206720129ed2fa8875a286] which attempted to fix ticket [9936b2fa443fec03ff25f9]. This changes is a fix for the follow-in tocket [510cde277783b5fb5de628]. (check-in: [c6c9585f] user: drh branch: trunk, size: 14338)
2018-06-25
11:42
[7cb26007] part of check-in [e954145a] Fix another problem that could cause a crash when a window function was used in a view. (check-in: [e954145a] user: dan branch: exp-window-functions, size: 13582)
2018-06-23
19:29
[0e0867fd] part of check-in [3e23cfc8] Fix problems with using window-functions in correlated sub-queries. (check-in: [3e23cfc8] user: dan branch: exp-window-functions, size: 12931)
16:26
[a28ea121] part of check-in [c1abd2dd] Fix a problem with using LIMIT in window-function queries. (check-in: [c1abd2dd] user: dan branch: exp-window-functions, size: 11400)
07:59
[ebb246c8] part of check-in [11d73339] Fix a problem with using a window-function SELECT as a FROM clause sub-query in some circumstances. (check-in: [11d73339] user: dan branch: exp-window-functions, size: 10548)
2018-06-22
20:51
[6ec7d892] part of check-in [5f04b016] Omit all window-function related code when building with SQLITE_OMIT_WINDOWFUNC. (check-in: [5f04b016] user: dan branch: exp-window-functions, size: 9683)
2018-06-19
18:11
[c4c138b1] part of check-in [f4b1b6f8] Fix a problem caused by a sub-query containing a window function in the FROM clause of a query that itself uses a window function. (check-in: [f4b1b6f8] user: dan branch: exp-window-functions, size: 9634)
17:13
[8908f000] part of check-in [801074ce] Fix a problem with using min() or max() as a window function. (check-in: [801074ce] user: dan branch: exp-window-functions, size: 9166)
2018-06-18
20:34
[f6624f97] part of check-in [943bccd2] Fix problems with using window functions in CREATE VIEW statements. (check-in: [943bccd2] user: dan branch: exp-window-functions, size: 8135)
16:55
[af17e847] part of check-in [da03fb43] Add new API function sqlite3_create_window_function(), for creating new aggregate window functions. (check-in: [da03fb43] user: dan branch: exp-window-functions, size: 7186)
2018-06-15
20:46
[3398c541] part of check-in [ac251f72] Add extra OOM test. (check-in: [ac251f72] user: dan branch: exp-window-functions, size: 6988)
16:10
[fb076d92] part of check-in [3839fb18] Fix a bug in the lead() and lag() window functions causing them to fail when used in queries featuring multiple window functions. (check-in: [3839fb18] user: dan branch: exp-window-functions, size: 6903)
2018-06-14
19:06
[68607fd4] part of check-in [43eb1e75] Fix problem with window functions min() and max() when used with a PARTITION clause and a frame starting point other than "UNBOUNDED PRECEDING". (check-in: [43eb1e75] user: dan branch: exp-window-functions, size: 6678)
2018-06-11
18:16
[94c626fe] part of check-in [b6d9c7ed] Fix handling of window frames containing negative number of rows. e.g. "ROWS x PRECEDING AND y PRECEDING" where (x<y). (check-in: [b6d9c7ed] user: dan branch: exp-window-functions, size: 6563)
2018-06-09
17:43
[6c648f3f] part of check-in [ceaf798e] Add support for FILTER clause on aggregate window functions. (check-in: [ceaf798e] user: dan branch: exp-window-functions, size: 5431)
2018-06-08
16:11
[4561399c] part of check-in [236cb75b] Do not flatten sub-queries that contain window functions. (check-in: [236cb75b] user: dan branch: exp-window-functions, size: 5252)
2018-06-04
18:55
[905fe084] part of check-in [3f093f60] Add implementation of window function ntile(). (check-in: [3f093f60] user: dan branch: exp-window-functions, size: 4739)
2018-05-19
14:15
[57053377] part of check-in [19c2e4b2] Fix minor problems on this branch. (check-in: [19c2e4b2] user: dan branch: exp-window-functions, size: 4273)
2018-05-17
19:24
[5278e157] part of check-in [c9f0f140] Evaluate multiple window functions in a single pass if they use the same window definition. Add xValue callbacks for other built-in aggregate functions. (check-in: [c9f0f140] user: dan branch: exp-window-functions, size: 4016)
14:26
[c088fff1] part of check-in [35af0b75] Handle multiple window-functions in a single query. (check-in: [35af0b75] user: dan branch: exp-window-functions, size: 3520)
2018-05-16
20:58
Added: [d1766b0c] part of check-in [3781e520] Start of experimental implementation of SQL window functions. Does not yet work. (check-in: [3781e520] user: dan branch: exp-window-functions, size: 3000)