/ Check-in [a9a3b532]
Login

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

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ticket-f09fcd17810f
Files: files | file ages | folders
SHA3-256: a9a3b532643b5f106509bb29c3e6bc9d41ec5b2da5a0cd4067898f376eb626a2
User & Date: drh 2018-12-06 19:56:20
Context
2018-12-06
22:04
Fix the sqlite3ExprDup() routine so that it makes complete duplications of subqueries containing window functions. check-in: 94017454 user: drh tags: ticket-f09fcd17810f
19:56
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 tags: ticket-f09fcd17810f
19:15
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 tags: ticket-f09fcd17810f
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/window1.test.

   592    592       INTERSECT 
   593    593     SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
   594    594   } {
   595    595   }
   596    596   
   597    597   # 2018-12-06
   598    598   # https://www.sqlite.org/src/info/f09fcd17810f65f7
   599         -# Assertion fault when window functions are used
          599  +# Assertion fault when window functions are used.
          600  +#
          601  +# Root cause is the query flattener invoking sqlite3ExprDup() on
          602  +# expressions that contain subqueries with window functions.  The
          603  +# sqlite3ExprDup() routine is not making correctly initializing
          604  +# Select.pWin field of the subqueries.
   600    605   #
   601    606   sqlite3 db :memory:
   602    607   do_execsql_test 14.0 {
   603    608     SELECT * FROM(
   604    609       SELECT * FROM (SELECT 1 AS c) WHERE c IN (
   605    610           SELECT (row_number() OVER()) FROM (VALUES (0))
   606    611       )
   607    612     );
   608    613   } {1}
          614  +do_execsql_test 14.1 {
          615  +  CREATE TABLE t1(x); INSERT INTO t1(x) VALUES(12345);
          616  +  CREATE TABLE t2(c); INSERT INTO t2(c) VALUES(1);
          617  +  SELECT y, y+1, y+2 FROM (
          618  +    SELECT c IN (
          619  +      SELECT (row_number() OVER()) FROM t1
          620  +    ) AS y FROM t2
          621  +  );
          622  +} {1 2 3}
   609    623   
   610    624   finish_test