Index: VERSION ================================================================== --- VERSION +++ VERSION @@ -1,1 +1,1 @@ -3.29.0 +3.30.0 Index: configure ================================================================== --- configure +++ configure @@ -1,8 +1,8 @@ #! /bin/sh # Guess values for system-dependent variables and create Makefiles. -# Generated by GNU Autoconf 2.69 for sqlite 3.29.0. +# Generated by GNU Autoconf 2.69 for sqlite 3.30.0. # # # Copyright (C) 1992-1996, 1998-2012 Free Software Foundation, Inc. # # @@ -724,12 +724,12 @@ MAKEFLAGS= # Identity of this package. PACKAGE_NAME='sqlite' PACKAGE_TARNAME='sqlite' -PACKAGE_VERSION='3.29.0' -PACKAGE_STRING='sqlite 3.29.0' +PACKAGE_VERSION='3.30.0' +PACKAGE_STRING='sqlite 3.30.0' PACKAGE_BUGREPORT='' PACKAGE_URL='' # Factoring default headers for most tests. ac_includes_default="\ @@ -1464,11 +1464,11 @@ # if test "$ac_init_help" = "long"; then # Omit some internal or obsolete options to make the list less imposing. # This message is too long to be a string in the A/UX 3.1 sh. cat <<_ACEOF -\`configure' configures sqlite 3.29.0 to adapt to many kinds of systems. +\`configure' configures sqlite 3.30.0 to adapt to many kinds of systems. Usage: $0 [OPTION]... [VAR=VALUE]... To assign environment variables (e.g., CC, CFLAGS...), specify them as VAR=VALUE. See below for descriptions of some of the useful variables. @@ -1529,11 +1529,11 @@ _ACEOF fi if test -n "$ac_init_help"; then case $ac_init_help in - short | recursive ) echo "Configuration of sqlite 3.29.0:";; + short | recursive ) echo "Configuration of sqlite 3.30.0:";; esac cat <<\_ACEOF Optional Features: --disable-option-checking ignore unrecognized --enable/--with options @@ -1655,11 +1655,11 @@ fi test -n "$ac_init_help" && exit $ac_status if $ac_init_version; then cat <<\_ACEOF -sqlite configure 3.29.0 +sqlite configure 3.30.0 generated by GNU Autoconf 2.69 Copyright (C) 2012 Free Software Foundation, Inc. This configure script is free software; the Free Software Foundation gives unlimited permission to copy, distribute and modify it. @@ -2074,11 +2074,11 @@ } # ac_fn_c_check_header_mongrel cat >config.log <<_ACEOF This file contains any messages produced by compilers while running configure, to aid debugging if configure makes a mistake. -It was created by sqlite $as_me 3.29.0, which was +It was created by sqlite $as_me 3.30.0, which was generated by GNU Autoconf 2.69. Invocation command line was $ $0 $@ _ACEOF @@ -12230,11 +12230,11 @@ cat >>$CONFIG_STATUS <<\_ACEOF || ac_write_fail=1 # Save the log message, to keep $0 and so on meaningful, and to # report actual input values of CONFIG_FILES etc. instead of their # values after options handling. ac_log=" -This file was extended by sqlite $as_me 3.29.0, which was +This file was extended by sqlite $as_me 3.30.0, which was generated by GNU Autoconf 2.69. Invocation command line was CONFIG_FILES = $CONFIG_FILES CONFIG_HEADERS = $CONFIG_HEADERS CONFIG_LINKS = $CONFIG_LINKS @@ -12296,11 +12296,11 @@ _ACEOF cat >>$CONFIG_STATUS <<_ACEOF || ac_write_fail=1 ac_cs_config="`$as_echo "$ac_configure_args" | sed 's/^ //; s/[\\""\`\$]/\\\\&/g'`" ac_cs_version="\\ -sqlite config.status 3.29.0 +sqlite config.status 3.30.0 configured by $0, generated by GNU Autoconf 2.69, with options \\"\$ac_cs_config\\" Copyright (C) 2012 Free Software Foundation, Inc. This config.status script is free software; the Free Software Foundation Index: ext/fts5/fts5_index.c ================================================================== --- ext/fts5/fts5_index.c +++ ext/fts5/fts5_index.c @@ -688,10 +688,11 @@ sqlite3_free(pRet); pRet = 0; }else{ /* TODO1: Fix this */ pRet->p[nByte] = 0x00; + pRet->p[nByte+1] = 0x00; pRet->szLeaf = fts5GetU16(&pRet->p[2]); } } p->rc = rc; p->nRead++; Index: ext/fts5/test/fts5corrupt3.test ================================================================== --- ext/fts5/test/fts5corrupt3.test +++ ext/fts5/test/fts5corrupt3.test @@ -9158,9 +9158,216 @@ do_catchsql_test 62.1 { WITH c(x) AS (VALUES(false) UNION ALL SELECT x+1 FROM c WHERE x<72) INSERT INTO t1(a) SELECT randomblob(2829) FROM c; } {0 {}} + +#--------------------------------------------------------------------------- +do_test 63.0 { + sqlite3 db {} + db deserialize [decode_hexdb { +.open --hexdb +| size 24576 pagesize 4096 filename crash-8230e6c3b368f5.db +| page 1 offset 0 +| 0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3. +| 16: 10 00 01 01 00 40 20 20 00 00 00 00 00 00 00 00 .....@ ........ +| 96: 00 00 00 00 0d 00 00 00 06 0e 0f 00 0f aa 0f 53 ...............S +| 112: 0e e8 0e 8b 0e 33 0e 0f 00 00 00 00 00 00 00 00 .....3.......... +| 3584: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 22 ................ +| 3600: 06 06 17 11 11 01 31 74 61 62 7c 65 62 63 62 62 ......1tab|ebcbb +| 3616: 06 43 52 45 41 54 45 20 54 41 42 4c 45 20 62 62 .CREATE TABLE bb +| 3632: 28 61 29 56 05 06 17 1f 1f 01 7d 74 61 62 6c 65 (a)V.......table +| 3648: 74 31 5f 63 6f 6e 66 69 67 74 31 5f 63 6f 6e 66 t1_configt1_conf +| 3664: 69 67 05 43 52 45 41 54 45 20 54 41 42 4c 45 20 ig.CREATE TABLE +| 3680: 27 74 31 5f 63 6f 6e 66 69 67 27 28 6b 20 50 52 't1_config'(k PR +| 3696: 49 4d 41 52 59 20 4b 45 59 2c 20 76 29 20 57 49 IMARY KEY, v) WI +| 3712: 54 48 4f 55 54 20 52 4f 57 49 44 5b 04 07 17 21 THOUT ROWID[...! +| 3728: 21 01 81 01 74 61 62 6c 65 74 31 5f 64 6f 63 73 !...tablet1_docs +| 3744: 69 7a 65 74 31 5f 64 6f 63 73 69 7a 65 04 43 52 izet1_docsize.CR +| 3760: 45 41 54 45 20 54 41 42 4c 45 20 27 74 31 5f 9d EATE TABLE 't1_. +| 3776: 6f 63 73 69 7a 65 27 28 69 64 20 49 4e 54 45 47 ocsize'(id INTEG +| 3792: 45 52 20 50 52 49 4d 41 52 59 20 4b 45 59 2c 20 ER PRIMARY KEY, +| 3808: 73 7a 20 42 4c 4f 42 29 69 03 07 17 19 19 01 81 sz BLOB)i....... +| 3824: 2d 74 61 62 6c 65 74 31 5f 69 64 78 74 31 5f 69 -tablet1_idxt1_i +| 3840: 64 78 03 43 52 45 41 54 45 20 54 41 42 4c 45 20 dx.CREATE TABLE +| 3856: 27 74 31 5f 69 64 78 27 28 73 65 67 69 64 2c 20 't1_idx'(segid, +| 3872: 74 65 72 6d 2c 20 70 67 6e 6f 2c 20 50 52 49 4d term, pgno, PRIM +| 3888: 41 52 59 20 4b 45 59 28 73 65 67 69 64 2c 20 74 ARY KEY(segid, t +| 3904: 65 72 6d 29 29 20 57 49 54 48 4f 55 54 20 52 4f erm)) WITHOUT RO +| 3920: 57 49 44 55 02 07 17 1b 1b 01 81 01 74 61 62 6c WIDU........tabl +| 3936: 65 64 31 5f 64 61 74 61 74 31 5f 64 61 74 61 02 ed1_datat1_data. +| 3952: 43 52 45 41 54 45 20 54 41 42 4c 45 20 27 74 31 CREATE TABLE 't1 +| 3968: 5f 64 61 74 61 27 28 69 64 20 49 4e 54 45 47 45 _data'(id INTEGE +| 3984: 52 20 50 52 49 4d 41 52 59 20 4b 45 59 2c 20 62 R PRIMARY KEY, b +| 4000: 6c 6f 63 6b 20 42 4c 4f 42 29 54 01 07 17 10 11 lock BLOB)T..... +| 4016: 08 81 15 74 61 62 6c 65 74 31 74 31 43 52 45 41 ...tablet1t1CREA +| 4032: 54 45 20 56 49 52 54 55 41 4c 20 54 41 42 4c 45 TE VIRTUAL TABLE +| 4048: 20 74 31 20 55 53 49 4e 47 20 66 74 73 35 28 61 t1 USING fts5(a +| 4064: 2c 62 2c 70 72 65 66 69 78 3d 22 31 2c 32 2c 33 ,b,prefix=.1,2,3 +| 4080: 2c 34 22 2c 20 63 6f 6e 74 65 6e 74 3d 22 22 29 ,4., content=..) +| page 2 offset 4096 +| 0: 0d 0b 6a 00 37 09 4c 02 0f e7 09 4c 0f c6 0f a4 ..j.7.L....L.... +| 16: 0f 88 0f 6d 0f 4b 0f 2c 0f 0e 0e ec 0e cd 0e ad ...m.K.,........ +| 32: 0e 8e 0e 6c 0e 4b 0e 29 0e 08 0d e6 0d c4 0d b5 ...l.K.)........ +| 48: 0d 97 0d 76 0d 54 0d 31 0d 15 0c f3 0c d3 0c b5 ...v.T.1........ +| 64: 0c 95 0c 73 0c 54 0c 32 0c 10 0b ee 0b cc 0b b0 ...s.T.2........ +| 80: 0b 8d 0b 7e 0b 48 0b 2e 0b 0b 0a ef 0a cc 0a ad ...~.H.......... +| 96: 0a 8c 0a 6d 0a 4d 0a 2b 0a 0c 09 ec 09 ca 09 a8 ...m.M.+........ +| 112: 09 86 09 63 0f f1 00 00 00 00 00 00 00 00 00 00 ...c............ +| 2368: 00 00 00 00 00 00 00 00 00 00 00 00 15 0a 03 00 ................ +| 2384: 30 00 00 00 01 01 03 35 00 03 01 01 12 02 01 12 0......5........ +| 2400: 03 01 11 1c 8c 80 80 80 80 10 03 00 3e 00 00 00 ............>... +| 2416: 17 01 05 05 34 74 61 62 6c 03 02 03 01 04 77 68 ....4tabl.....wh +| 2432: 65 72 03 02 06 09 1b 8c 80 80 80 80 0f 03 00 3c er.............< +| 2448: 00 00 00 16 05 34 66 74 73 34 03 02 02 01 04 6e .....4fts4.....n +| 2464: 75 6d 62 03 06 01 04 09 1b 8c 80 80 80 80 0e 03 umb............. +| 2480: 00 3c 00 00 00 16 04 33 74 68 65 13 06 01 01 04 .<.....3the..... +| 2496: 01 03 77 68 65 03 02 04 04 0a 1b 8c 80 80 80 80 ..whe........... +| 2512: 0d 03 00 3c 00 00 00 16 04 33 6e 75 6d 03 06 01 ...<.....3num... +| 2528: 01 05 01 03 75 61 62 03 02 03 04 0a 19 8c 80 80 ....uab......... +| 2544: 80 80 0c 03 00 38 00 00 00 14 03 32 ec 68 03 02 .....8.....2.h.. +| 2560: 04 00 04 33 66 74 73 03 02 02 04 07 18 8c 80 80 ...3fts......... +| 2576: 80 80 0b 03 00 36 00 00 00 13 03 32 74 61 03 02 .....6.....2ta.. +| 2592: 03 02 01 68 03 06 01 01 04 04 17 1b 8c 80 80 80 ...h............ +| 2608: 80 0a 03 00 3c 00 00 00 16 03 32 6e 75 03 06 01 ....<.....2nu... +| 2624: 01 05 01 02 6f 66 03 06 01 01 06 04 09 19 8c 80 ....of.......... +| 2640: 80 80 80 09 03 00 38 00 00 00 14 03 32 66 74 03 ......8.....2ft. +| 2656: 02 02 01 02 69 73 03 06 01 01 03 04 07 18 8c 80 ....is.......... +| 2672: 80 80 80 08 03 00 36 00 00 00 13 02 31 74 03 08 ......6.....1t.. +| 2688: 03 01 01 04 01 01 77 03 02 04 04 09 1a 8c 80 80 ......w......... +| 2704: 80 80 07 03 00 3a 00 00 00 15 02 31 6e 03 08 01 .....:.....1n... +| 2720: 01 02 05 01 01 6f 03 06 01 01 06 04 09 18 8c 80 .....o.......... +| 2736: 80 80 80 06 03 00 36 00 00 00 13 04 02 31 66 03 ......6......1f. +| 2752: 02 02 01 01 69 03 06 01 01 03 05 06 1c 8c 80 80 ....i........... +| 2768: 80 80 05 03 00 3e 00 00 00 17 04 30 74 68 65 03 .....>.....0the. +| 2784: 06 01 01 04 01 05 77 68 65 72 65 03 02 04 0a 15 ......where..... +| 2800: 8c 80 80 80 80 04 03 00 30 00 00 00 11 01 01 06 ........0....... +| 2816: 06 30 74 61 62 6c 65 03 02 03 07 1c 8c 80 80 80 .0table......... +| 2832: 80 03 03 00 3e 00 00 00 17 07 30 6e 75 6d 62 65 ....>.....0numbe +| 2848: 72 03 06 01 01 05 01 02 6f 66 03 06 04 0d 13 8c r.......of...... +| 2864: 80 80 80 80 02 03 00 2c 00 00 00 0f 01 01 03 02 .......,........ +| 2880: 30 6e 03 06 01 01 02 07 1b 8c 80 80 80 80 01 03 0n.............. +| 2896: 00 3c 00 00 00 16 08 30 66 74 73 34 61 75 78 03 .<.....0fts4aux. +| 2912: 02 02 01 02 69 73 03 06 04 0c 00 00 00 14 2a 00 ....is........*. +| 2928: 00 00 01 01 02 24 00 02 01 01 12 02 01 12 08 88 .....$.......... +| 2944: 80 80 80 80 12 03 00 16 00 00 00 05 02 1c 88 80 ................ +| 2960: 80 80 80 11 03 00 3e 00 00 00 17 05 34 72 6f 77 ......>.....4row +| 2976: 73 02 06 01 01 05 01 04 74 68 65 72 02 02 04 0b s.......ther.... +| 2992: 15 88 80 80 80 80 10 03 00 30 00 00 00 11 02 01 .........0...... +| 3008: 01 07 05 34 62 65 74 77 02 02 04 08 1b 88 80 80 ...4betw........ +| 3024: 80 80 0f 03 00 3c 00 00 00 16 04 04 33 72 6f 77 .....<......3row +| 3040: 02 06 01 01 05 01 03 74 68 64 02 08 05 0a 1b 88 .......thd...... +| 3056: 80 80 80 80 0e 03 00 3c 00 00 00 16 01 01 02 04 .......<........ +| 3072: 33 61 72 65 02 02 03 01 03 62 65 74 02 02 07 08 3are.....bet.... +| 3088: 1b 88 80 80 80 80 0d 03 00 3c 00 00 00 16 03 32 .........<.....2 +| 3104: 74 68 02 08 02 01 01 07 00 04 33 61 6e 64 02 06 th........3and.. +| 3120: 04 0a 1b 88 80 80 80 80 0c 03 00 3c 00 00 00 16 ...........<.... +| 3136: 03 32 69 6e 02 06 01 01 06 01 02 72 6f 02 06 01 .2in.......ro... +| 3152: 01 43 04 09 18 88 80 80 80 80 0b 03 00 36 00 00 .C...........6.. +| 3168: 00 13 02 03 32 61 72 02 02 03 01 02 62 65 02 02 ....2ar.....be.. +| 3184: 04 05 07 1b 88 80 80 80 80 0a 03 00 3c 00 00 00 ............<... +| 3200: 16 02 31 74 02 08 02 01 01 07 00 03 32 61 6e 02 ..1t........2an. +| 3216: 06 01 01 04 09 19 88 80 80 80 80 09 03 00 38 00 ..............8. +| 3232: 00 00 14 02 31 6e 02 06 01 01 03 01 01 72 02 06 ....1n.......r.. +| 3248: 01 01 05 04 08 17 88 80 80 80 80 08 03 00 34 00 ..............4. +| 3264: 00 00 12 02 31 62 02 02 04 01 01 69 02 06 01 01 ....1b.....i.... +| 3280: 06 04 06 19 88 80 80 80 80 07 03 00 38 00 00 00 ............8... +| 3296: 14 04 02 31 32 02 02 05 01 01 61 02 08 03 01 01 ...12.....a..... +| 3312: 02 05 06 1b 88 80 80 80 80 06 03 00 3c 00 00 00 ............<... +| 3328: 16 06 30 74 68 65 72 65 02 02 01 00 02 30 21 02 ..0there.....0!. +| 3344: 06 01 01 04 0a 15 88 80 80 80 80 05 03 00 30 00 ..............0. +| 3360: 00 00 11 01 01 05 04 30 74 68 65 02 06 01 01 07 .......0the..... +| 3376: 07 1c 88 80 80 80 80 04 03 00 3e 00 00 00 17 01 ..........>..... +| 3392: 01 06 02 30 6e 02 06 01 01 03 01 04 72 6f 77 73 ...0n.......rows +| 3408: 02 06 07 08 1b 88 80 80 80 80 03 03 00 3c 00 51 .............<.Q +| 3424: 00 16 08 30 62 65 74 77 65 65 6e 02 02 04 01 02 ...0between..... +| 3440: 69 6e 02 06 04 0c 1a 88 80 80 80 80 02 03 00 3a in.............: +| 3456: 00 00 00 15 04 30 61 6e 64 02 06 01 01 02 02 02 .....0and....... +| 3472: 72 65 02 02 03 04 0a 17 88 80 80 80 80 01 03 00 re.............. +| 3488: 34 00 00 00 12 02 30 31 02 06 01 01 04 01 01 32 4.....01.......2 +| 3504: 02 02 05 04 08 08 84 80 80 80 80 12 03 00 16 00 ................ +| 3520: 00 00 05 04 1b 84 80 80 80 80 11 03 00 3c 00 00 .............<.. +| 3536: 00 16 05 34 74 51 62 6c 01 06 01 01 05 02 03 65 ...4tQbl.......e +| 3552: 72 6d 01 02 04 0b 1b 84 80 80 80 80 10 03 00 3c rm.............< +| 3568: 00 00 00 16 05 34 65 17 63 68 01 02 03 01 04 70 .....4e.ch.....p +| 3584: 72 65 73 01 02 05 04 09 1a 84 80 80 80 80 0f 03 res............. +| 3600: 00 3a 00 00 00 15 04 33 74 65 72 01 02 04 02 02 .:.....3ter..... +| 3616: 68 65 01 06 01 01 03 04 08 1b 84 80 80 80 80 0e he.............. +| 3632: 03 00 3c 00 00 00 16 04 33 70 72 65 01 02 05 01 ..<.....3pre.... +| 3648: 03 74 61 62 01 06 01 01 05 04 08 1a 84 80 80 80 .tab............ +| 3664: 80 0d 03 00 3a 00 00 00 15 04 33 66 6f 72 01 03 ....:.....3for.. +| 3680: 02 02 02 74 73 01 06 01 01 04 04 08 1b 84 80 80 ...ts........... +| 3696: 80 80 0c 03 00 3c 00 00 00 16 03 32 74 68 01 06 .....<.....2th.. +| 3712: 01 01 03 00 04 33 65 61 63 01 02 03 04 09 18 84 .....3eac....... +| 3728: 80 80 80 80 0b 03 00 36 00 00 00 13 03 32 74 61 .......6.....2ta +| 3744: 01 06 01 01 05 02 01 65 01 02 04 04 09 19 84 80 .......e........ +| 3760: 80 80 80 0a 03 00 38 00 00 00 14 03 32 69 6e 01 ......8.....2in. +| 3776: 06 01 01 02 01 02 70 72 01 02 05 04 09 18 84 80 ......pr........ +| 3792: 80 80 80 09 03 00 36 00 00 00 13 03 32 66 6f 01 ......6.....2fo. +| 3808: 02 02 02 01 74 01 06 01 01 04 04 07 1b 84 80 80 ....t........... +| 3824: 80 80 08 03 00 3c 00 00 00 16 02 31 74 01 0a 04 .....<.....1t... +| 3840: 01 01 03 04 00 03 32 65 61 01 02 03 04 0a 17 84 ......2ea....... +| 3856: 80 80 80 80 07 03 00 34 00 00 00 12 02 31 69 01 .......4.....1i. +| 3872: 06 01 01 02 01 01 70 01 02 05 04 08 18 84 80 80 ......p......... +| 3888: 80 80 06 03 00 36 00 00 00 12 02 31 65 01 02 02 .....6.....1e... +| 3904: 01 01 66 01 08 02 01 01 04 04 06 1b 84 80 80 80 ..f............. +| 3920: 80 05 03 00 3c 00 00 00 16 05 30 74 65 72 6d 01 ....<.....0term. +| 3936: 02 04 02 02 68 65 01 06 01 01 03 04 09 14 84 80 ....he.......... +| 3952: 80 80 80 04 03 00 2e 00 00 00 10 06 30 74 61 62 ............0tab +| 3968: 6c 65 01 06 01 01 05 04 15 84 80 80 80 80 03 03 le.............. +| 3984: 00 30 00 00 00 11 02 08 30 70 72 65 73 65 6e 74 .0......0present +| 4000: 01 02 05 05 1b 84 80 80 80 80 02 03 00 3c 00 00 .............<.. +| 4016: 00 16 04 30 66 74 73 01 06 01 01 04 01 02 69 6e ...0fts.......in +| 4032: 01 06 01 01 04 0a 1a 84 80 80 80 80 01 03 00 3a ...............: +| 4048: 00 00 00 15 05 30 65 61 63 68 01 02 03 01 03 66 .....0each.....f +| 4064: 6f 72 01 02 01 f4 09 06 01 03 00 12 03 0b 0f 00 or.............. +| 4080: 00 08 8c 80 80 80 80 11 03 00 16 00 00 00 05 04 ................ +| page 3 offset 8192 +| 0: 0a 00 00 00 32 0e 4f 00 0f fa 0f f1 0f e9 0f e1 ....2.O......... +| 16: 0f d8 0f d1 0f c9 0f c1 0f b9 0f b1 0f a9 0f a0 ................ +| 32: 0f 98 0f 90 0f 87 0f 80 0f 78 0f 71 0f 68 0f 5f .........x.q.h._ +| 48: 0f 56 0f 4d 0f 41 0f 38 0f 2f 0f 26 0f 1d 0f 13 .V.M.A.8./.&.... +| 64: 0f 0a 0f 01 0e f7 0e ee 0e e6 0e dd 0e d6 0e cd ................ +| 80: 0e c3 0e ba 0e b0 0e a8 0e 9f 0e 00 00 00 00 00 ................ +| 3648: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 08 ................ +| 3664: 04 01 10 01 03 34 74 20 07 04 01 0e 01 03 34 1e .....4t ......4. +| 3680: 09 04 01 12 01 03 33 74 68 1c 08 04 01 10 01 03 ......3th....... +| 3696: 33 6e 1a 08 04 01 10 01 03 32 77 18 08 04 01 10 3n.......2w..... +| 3712: 01 03 32 74 16 08 04 01 10 01 03 32 6e 14 07 04 ..2t.......2n... +| 3728: 01 0e 01 03 32 12 08 04 01 10 01 03 31 74 10 07 ....2.......1t.. +| 3744: f4 01 10 01 03 31 6e 0e 07 04 01 0e 01 03 31 0c .....1n.......1. +| 3760: 09 04 01 12 01 03 30 74 68 0a 08 04 01 10 01 03 ......0th....... +| 3776: 30 74 08 09 04 01 12 01 03 30 6e 75 06 08 04 01 0t.......0nu.... +| 3792: 10 01 03 30 6e 04 06 04 01 0c 01 03 02 08 04 01 ...0n........... +| 3808: 10 01 02 34 73 22 07 04 01 0e 01 02 34 20 08 04 ...4s.......4 .. +| 3824: 01 10 01 02 33 72 1e 09 04 01 12 01 02 33 61 72 ....3r.......3ar +| 3840: 1c 08 04 01 10 01 02 32 74 1a 08 04 01 10 01 02 .......2t....... +| 3856: 32 69 18 09 04 01 12 01 02 32 61 72 16 08 04 01 2i.......2ar.... +| 3872: 10 01 02 31 74 14 08 04 01 10 01 02 31 6e 12 08 ...1t.......1n.. +| 3888: 04 01 10 01 02 31 62 10 08 04 01 10 01 02 31 32 .....1b.......12 +| 3904: 0e 0b 04 01 16 01 02 30 74 00 00 00 00 00 00 00 .......0t....... +| page 4 offset 12288 +| 4064: 00 00 00 00 00 00 00 00 00 00 00 05 02 03 00 10 ................ +| 4080: 03 05 05 02 03 00 10 04 06 05 01 03 00 10 04 04 ................ +| page 5 offset 16384 +| 0: 0a 00 00 00 02 0f eb 00 0f eb 0f f4 00 00 00 00 ................ +| 4064: 00 00 00 00 00 00 00 00 00 00 00 08 03 15 01 70 ...............p +| 4080: 67 73 7a 08 0b 03 1b 01 76 65 72 73 69 6f 6e 04 gsz.....version. +| end crash-8230e6c3b368f5.db +}]} {} + +do_catchsql_test 63.1 { + SELECT * FROM t1 WHERE b MATCH 'thead*thead*theSt*'; +} {1 {database disk image is malformed}} + +do_catchsql_test 63.2 { + INSERT INTO t1(t1) VALUES('optimize'); +} {0 {}} + +breakpoint +do_catchsql_test 63.3 { + SELECT * FROM t1 WHERE b MATCH 'thead*thead*theSt*'; +} {0 {}} sqlite3_fts5_may_be_corrupt 0 finish_test Index: ext/misc/fossildelta.c ================================================================== --- ext/misc/fossildelta.c +++ ext/misc/fossildelta.c @@ -34,10 +34,11 @@ #include #include #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 +#ifndef SQLITE_AMALGAMATION /* ** The "u32" type must be an unsigned 32-bit integer. Adjust this */ typedef unsigned int u32; @@ -44,10 +45,12 @@ /* ** Must be a 16-bit value */ typedef short int s16; typedef unsigned short int u16; + +#endif /* SQLITE_AMALGAMATION */ /* ** The width of a hash window in bytes. The algorithm only works if this ** is a power of 2. ADDED ext/rbu/rbuexpr.test Index: ext/rbu/rbuexpr.test ================================================================== --- ext/rbu/rbuexpr.test +++ ext/rbu/rbuexpr.test @@ -0,0 +1,93 @@ +# 2014 August 30 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +source [file join [file dirname [info script]] rbu_common.tcl] +set ::testprefix rbuexpr + +db close +sqlite3_shutdown +sqlite3_config_uri 1 + +sqlite3 db test.db + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b, c PRIMARY KEY); + CREATE INDEX i1 ON t1(a, null, b+1); + CREATE INDEX i2 ON t1(a+1, b+1, c+1); + + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(4, 5, 6); + INSERT INTO t1 VALUES(7, 8, 9); + INSERT INTO t1 VALUES(10, 11, 12); + + PRAGMA integrity_check; +} {ok} + +forcedelete rbu.db +sqlite3 db2 rbu.db +do_execsql_test -db db2 1.1 { + CREATE TABLE data_t1(a, b, c, rbu_control); + INSERT INTO data_t1 VALUES(13, 14, 15, 0); + INSERT INTO data_t1 VALUES(NULL, NULL, 6, 1); + INSERT INTO data_t1 VALUES(NULL, 'three', 3, '.x.'); +} +db2 close +db close + +do_test 1.2 { + run_rbu test.db rbu.db +} {SQLITE_DONE} + +sqlite3 db test.db + +do_execsql_test 1.3 { + SELECT * FROM t1 WHERE a=4; +} + +integrity_check 1.4 + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 2.0 { + CREATE TABLE t1(c1, c2, c3, i INTEGER PRIMARY KEY); + INSERT INTO t1 VALUES('one', 'one', 'one', 1); + INSERT INTO t1 VALUES('two', 'two', 'two', 2); + INSERT INTO t1 VALUES('three', 'three', 'three', 3); + INSERT INTO t1 VALUES('four', 'four', 'four', 4); + + CREATE INDEX i1 ON t1( substr(c1, 1, 2) ); + CREATE INDEX i2 ON t1( c1 || c2 || c3 ); + CREATE INDEX i3 ON t1( length(c1) + length(c2) - 1, c3||i ); +} + +forcedelete rbu.db +sqlite3 db2 rbu.db +do_execsql_test -db db2 2.1 { + CREATE TABLE data_t1(c1, c2, c3, i, rbu_control); + INSERT INTO data_t1 VALUES(NULL, NULL, NULL, 2, 1); + INSERT INTO data_t1 VALUES('thirty', NULL, NULL, 3, 'xx..'); + INSERT INTO data_t1 VALUES('five', 'five', 'five', 5, 0); +} +db2 close + +db close + +do_test 2.2 { + run_rbu test.db rbu.db +} {SQLITE_DONE} + +sqlite3 db test.db +integrity_check 2.3 + +finish_test + Index: ext/rbu/rbupartial.test ================================================================== --- ext/rbu/rbupartial.test +++ ext/rbu/rbupartial.test @@ -38,10 +38,19 @@ CREATE INDEX i1c ON t1(%C%); CREATE INDEX i1c2 ON t1(%C%) WHERE %C% IS NULL; CREATE INDEX i1c3 ON t1(%C%) WHERE %C% IS NOT NULL; CREATE INDEX i1c4 ON t1(%C%) WHERE %D% < 'd'; + CREATE INDEX i1c5 ON t1( + %C% -- for (c = ... expressions + ) WHERE %D% < 'd'; + CREATE INDEX i1c6 ON t1( + %C% /* Again, for (c=... expr */, %D% + ) WHERE %D% < 'd'; + + CREATE INDEX i1c7 ON t1( + %C% /* As before, for (c=... "expr */) WHERE %D% < 'd'; } do_execsql_test $tn.1.1 { INSERT INTO t1 VALUES(0, NULL, NULL, 'a'); INSERT INTO t1 VALUES(1, 2, 3, 'b'); Index: ext/rbu/sqlite3rbu.c ================================================================== --- ext/rbu/sqlite3rbu.c +++ ext/rbu/sqlite3rbu.c @@ -180,10 +180,11 @@ "CREATE TABLE IF NOT EXISTS %s.rbu_state(k INTEGER PRIMARY KEY, v)" typedef struct RbuFrame RbuFrame; typedef struct RbuObjIter RbuObjIter; typedef struct RbuState RbuState; +typedef struct RbuSpan RbuSpan; typedef struct rbu_vfs rbu_vfs; typedef struct rbu_file rbu_file; typedef struct RbuUpdateStmt RbuUpdateStmt; #if !defined(SQLITE_AMALGAMATION) @@ -223,10 +224,15 @@ struct RbuUpdateStmt { char *zMask; /* Copy of update mask used with pUpdate */ sqlite3_stmt *pUpdate; /* Last update statement (or NULL) */ RbuUpdateStmt *pNext; }; + +struct RbuSpan { + const char *zSpan; + int nSpan; +}; /* ** An iterator of this type is used to iterate through all objects in ** the target database that require updating. For each such table, the ** iterator visits, in order: @@ -273,10 +279,13 @@ int nCol; /* Number of columns in current object */ sqlite3_stmt *pSelect; /* Source data */ sqlite3_stmt *pInsert; /* Statement for INSERT operations */ sqlite3_stmt *pDelete; /* Statement for DELETE ops */ sqlite3_stmt *pTmpInsert; /* Insert into rbu_tmp_$zDataTbl */ + int nIdxCol; + RbuSpan *aIdxCol; + char *zIdxSql; /* Last UPDATE used (for PK b-tree updates only), or NULL. */ RbuUpdateStmt *pRbuUpdate; }; @@ -807,17 +816,22 @@ RbuUpdateStmt *pTmp = pUp->pNext; sqlite3_finalize(pUp->pUpdate); sqlite3_free(pUp); pUp = pTmp; } + sqlite3_free(pIter->aIdxCol); + sqlite3_free(pIter->zIdxSql); pIter->pSelect = 0; pIter->pInsert = 0; pIter->pDelete = 0; pIter->pRbuUpdate = 0; pIter->pTmpInsert = 0; pIter->nCol = 0; + pIter->nIdxCol = 0; + pIter->aIdxCol = 0; + pIter->zIdxSql = 0; } /* ** Clean up any resources allocated as part of the iterator object passed ** as the only argument. @@ -1087,18 +1101,19 @@ ** if the allocation succeeds, (*pRc) is left unchanged. */ static char *rbuStrndup(const char *zStr, int *pRc){ char *zRet = 0; - assert( *pRc==SQLITE_OK ); - if( zStr ){ - size_t nCopy = strlen(zStr) + 1; - zRet = (char*)sqlite3_malloc64(nCopy); - if( zRet ){ - memcpy(zRet, zStr, nCopy); - }else{ - *pRc = SQLITE_NOMEM; + if( *pRc==SQLITE_OK ){ + if( zStr ){ + size_t nCopy = strlen(zStr) + 1; + zRet = (char*)sqlite3_malloc64(nCopy); + if( zRet ){ + memcpy(zRet, zStr, nCopy); + }else{ + *pRc = SQLITE_NOMEM; + } } } return zRet; } @@ -1266,10 +1281,13 @@ sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", zIdx) ); while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){ int iCid = sqlite3_column_int(pXInfo, 1); if( iCid>=0 ) pIter->abIndexed[iCid] = 1; + if( iCid==-2 ){ + memset(pIter->abIndexed, 0x01, sizeof(u8)*pIter->nTblCol); + } } rbuFinalize(p, pXInfo); bIndex = 1; pIter->nIndex++; } @@ -1680,30 +1698,38 @@ int bDesc = sqlite3_column_int(pXInfo, 3); const char *zCollate = (const char*)sqlite3_column_text(pXInfo, 4); const char *zCol; const char *zType; - if( iCid<0 ){ - /* An integer primary key. If the table has an explicit IPK, use - ** its name. Otherwise, use "rbu_rowid". */ - if( pIter->eType==RBU_PK_IPK ){ - int i; - for(i=0; pIter->abTblPk[i]==0; i++); - assert( inTblCol ); - zCol = pIter->azTblCol[i]; - }else if( rbuIsVacuum(p) ){ - zCol = "_rowid_"; + if( iCid==-2 ){ + int iSeq = sqlite3_column_int(pXInfo, 0); + zRet = sqlite3_mprintf("%z%s(%.*s) COLLATE %Q", zRet, zCom, + pIter->aIdxCol[iSeq].nSpan, pIter->aIdxCol[iSeq].zSpan, zCollate + ); + zType = ""; + }else { + if( iCid<0 ){ + /* An integer primary key. If the table has an explicit IPK, use + ** its name. Otherwise, use "rbu_rowid". */ + if( pIter->eType==RBU_PK_IPK ){ + int i; + for(i=0; pIter->abTblPk[i]==0; i++); + assert( inTblCol ); + zCol = pIter->azTblCol[i]; + }else if( rbuIsVacuum(p) ){ + zCol = "_rowid_"; + }else{ + zCol = "rbu_rowid"; + } + zType = "INTEGER"; }else{ - zCol = "rbu_rowid"; + zCol = pIter->azTblCol[iCid]; + zType = pIter->azTblType[iCid]; } - zType = "INTEGER"; - }else{ - zCol = pIter->azTblCol[iCid]; - zType = pIter->azTblType[iCid]; + zRet = sqlite3_mprintf("%z%s\"%w\" COLLATE %Q", zRet, zCom,zCol,zCollate); } - zRet = sqlite3_mprintf("%z%s\"%w\" COLLATE %Q", zRet, zCom, zCol, zCollate); if( pIter->bUnique==0 || sqlite3_column_int(pXInfo, 5) ){ const char *zOrder = (bDesc ? " DESC" : ""); zImpPK = sqlite3_mprintf("%z%s\"rbu_imp_%d%w\"%s", zImpPK, zCom, nBind, zCol, zOrder ); @@ -2179,34 +2205,65 @@ static char *rbuObjIterGetIndexWhere(sqlite3rbu *p, RbuObjIter *pIter){ sqlite3_stmt *pStmt = 0; int rc = p->rc; char *zRet = 0; + assert( pIter->zIdxSql==0 && pIter->nIdxCol==0 && pIter->aIdxCol==0 ); + if( rc==SQLITE_OK ){ rc = prepareAndCollectError(p->dbMain, &pStmt, &p->zErrmsg, "SELECT trim(sql) FROM sqlite_master WHERE type='index' AND name=?" ); } if( rc==SQLITE_OK ){ int rc2; rc = sqlite3_bind_text(pStmt, 1, pIter->zIdx, -1, SQLITE_STATIC); if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ - const char *zSql = (const char*)sqlite3_column_text(pStmt, 0); + char *zSql = (char*)sqlite3_column_text(pStmt, 0); + if( zSql ){ + pIter->zIdxSql = zSql = rbuStrndup(zSql, &rc); + } if( zSql ){ int nParen = 0; /* Number of open parenthesis */ int i; + int iIdxCol = 0; + int nIdxAlloc = 0; for(i=0; zSql[i]; i++){ char c = zSql[i]; + + /* If necessary, grow the pIter->aIdxCol[] array */ + if( iIdxCol==nIdxAlloc ){ + RbuSpan *aIdxCol = (RbuSpan*)sqlite3_realloc( + pIter->aIdxCol, (nIdxAlloc+16)*sizeof(RbuSpan) + ); + if( aIdxCol==0 ){ + rc = SQLITE_NOMEM; + break; + } + pIter->aIdxCol = aIdxCol; + nIdxAlloc += 16; + } + if( c=='(' ){ + if( nParen==0 ){ + assert( iIdxCol==0 ); + pIter->aIdxCol[0].zSpan = &zSql[i+1]; + } nParen++; } else if( c==')' ){ nParen--; if( nParen==0 ){ + int nSpan = &zSql[i] - pIter->aIdxCol[iIdxCol].zSpan; + pIter->aIdxCol[iIdxCol++].nSpan = nSpan; i++; break; } + }else if( c==',' && nParen==1 ){ + int nSpan = &zSql[i] - pIter->aIdxCol[iIdxCol].zSpan; + pIter->aIdxCol[iIdxCol++].nSpan = nSpan; + pIter->aIdxCol[iIdxCol].zSpan = &zSql[i+1]; }else if( c=='"' || c=='\'' || c=='`' ){ for(i++; 1; i++){ if( zSql[i]==c ){ if( zSql[i+1]!=c ) break; i++; @@ -2214,15 +2271,23 @@ } }else if( c=='[' ){ for(i++; 1; i++){ if( zSql[i]==']' ) break; } + }else if( c=='-' && zSql[i+1]=='-' ){ + for(i=i+2; zSql[i] && zSql[i]!='\n'; i++); + if( zSql[i]=='\0' ) break; + }else if( c=='/' && zSql[i+1]=='*' ){ + for(i=i+2; zSql[i] && (zSql[i]!='*' || zSql[i+1]!='/'); i++); + if( zSql[i]=='\0' ) break; + i++; } } if( zSql[i] ){ zRet = rbuStrndup(&zSql[i], &rc); } + pIter->nIdxCol = iIdxCol; } } rc2 = sqlite3_finalize(pStmt); if( rc==SQLITE_OK ) rc = rc2; @@ -2263,15 +2328,15 @@ char *zBind = 0; char *zPart = 0; int nBind = 0; assert( pIter->eType!=RBU_PK_VTAB ); + zPart = rbuObjIterGetIndexWhere(p, pIter); zCollist = rbuObjIterGetIndexCols( p, pIter, &zImposterCols, &zImposterPK, &zWhere, &nBind ); zBind = rbuObjIterGetBindlist(p, nBind); - zPart = rbuObjIterGetIndexWhere(p, pIter); /* Create the imposter table used to write to this index. */ sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 0, 1); sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 1,tnum); rbuMPrintfExec(p, p->dbMain, Index: ext/rtree/rtree.c ================================================================== --- ext/rtree/rtree.c +++ ext/rtree/rtree.c @@ -1562,17 +1562,18 @@ int eInt; RtreeSearchPoint x; eInt = pRtree->eCoordType==RTREE_COORD_INT32; while( (p = rtreeSearchPointFirst(pCur))!=0 && p->iLevel>0 ){ + u8 *pCellData; pNode = rtreeNodeOfFirstSearchPoint(pCur, &rc); if( rc ) return rc; nCell = NCELL(pNode); assert( nCell<200 ); + pCellData = pNode->zData + (4+pRtree->nBytesPerCell*p->iCell); while( p->iCellzData + (4+pRtree->nBytesPerCell*p->iCell); eWithin = FULLY_WITHIN; for(ii=0; iiaConstraint + ii; if( pConstraint->op>=RTREE_MATCH ){ rc = rtreeCallbackConstraint(pConstraint, eInt, pCellData, p, @@ -1581,17 +1582,27 @@ }else if( p->iLevel==1 ){ rtreeLeafConstraint(pConstraint, eInt, pCellData, &eWithin); }else{ rtreeNonleafConstraint(pConstraint, eInt, pCellData, &eWithin); } - if( eWithin==NOT_WITHIN ) break; + if( eWithin==NOT_WITHIN ){ + p->iCell++; + pCellData += pRtree->nBytesPerCell; + break; + } } - p->iCell++; if( eWithin==NOT_WITHIN ) continue; + p->iCell++; x.iLevel = p->iLevel - 1; if( x.iLevel ){ x.id = readInt64(pCellData); + for(ii=0; iinPoint; ii++){ + if( pCur->aPoint[ii].id==x.id ){ + RTREE_IS_CORRUPT(pRtree); + return SQLITE_CORRUPT_VTAB; + } + } x.iCell = 0; }else{ x.id = p->id; x.iCell = p->iCell - 1; } Index: src/backup.c ================================================================== --- src/backup.c +++ src/backup.c @@ -617,12 +617,14 @@ if( p->pDestDb ){ p->pSrc->nBackup--; } if( p->isAttached ){ pp = sqlite3PagerBackupPtr(sqlite3BtreePager(p->pSrc)); + assert( pp!=0 ); while( *pp!=p ){ pp = &(*pp)->pNext; + assert( pp!=0 ); } *pp = p->pNext; } /* If a transaction is still open on the Btree, roll it back. */ Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -6662,16 +6662,11 @@ assert( MX_CELL(pPage->pBt)<=10921 ); assert( pPage->nCell<=MX_CELL(pPage->pBt) || CORRUPT_DB ); assert( pPage->nOverflow<=ArraySize(pPage->apOvfl) ); assert( ArraySize(pPage->apOvfl)==ArraySize(pPage->aiOvfl) ); assert( sqlite3_mutex_held(pPage->pBt->mutex) ); - /* The cell should normally be sized correctly. However, when moving a - ** malformed cell from a leaf page to an interior page, if the cell size - ** wanted to be less than 4 but got rounded up to 4 on the leaf, then size - ** might be less than 8 (leaf-size + pointer) on the interior node. Hence - ** the term after the || in the following assert(). */ - assert( sz==pPage->xCellSize(pPage, pCell) || (sz==8 && iChild>0) ); + assert( sz==pPage->xCellSize(pPage, pCell) || CORRUPT_DB ); assert( pPage->nFree>=0 ); if( pPage->nOverflow || sz+2>pPage->nFree ){ if( pTemp ){ memcpy(pTemp, pCell, sz); pCell = pTemp; @@ -7960,10 +7955,12 @@ nNew>=5 ? apNew[4]->pgno : 0, nNew>=5 ? szNew[4] : 0, nNew>=5 ? cntNew[4] - cntNew[3] - !leafData : 0 )); assert( sqlite3PagerIswriteable(pParent->pDbPage) ); + assert( nNew>=1 && nNew<=ArraySize(apNew) ); + assert( apNew[nNew-1]!=0 ); put4byte(pRight, apNew[nNew-1]->pgno); /* If the sibling pages are not leaves, ensure that the right-child pointer ** of the right-most new sibling page is set to the value that was ** originally in the same field of the right-most old sibling page. */ @@ -8305,15 +8302,17 @@ VVA_ONLY( int balance_quick_called = 0 ); VVA_ONLY( int balance_deeper_called = 0 ); do { - int iPage = pCur->iPage; + int iPage; MemPage *pPage = pCur->pPage; if( NEVER(pPage->nFree<0) && btreeComputeFreeSpace(pPage) ) break; - if( iPage==0 ){ + if( pPage->nOverflow==0 && pPage->nFree<=nMin ){ + break; + }else if( (iPage = pCur->iPage)==0 ){ if( pPage->nOverflow ){ /* The root page of the b-tree is overfull. In this case call the ** balance_deeper() function to create a new child for the root-page ** and copy the current contents of the root-page to it. The ** next iteration of the do-loop will balance the child page. @@ -8330,12 +8329,10 @@ assert( pCur->pPage->nOverflow ); } }else{ break; } - }else if( pPage->nOverflow==0 && pPage->nFree<=nMin ){ - break; }else{ MemPage * const pParent = pCur->apPage[iPage-1]; int const iIdx = pCur->aiIdx[iPage-1]; rc = sqlite3PagerWrite(pParent->pDbPage); @@ -8727,11 +8724,16 @@ ** ** This optimization cannot be used on an autovacuum database if the ** new entry uses overflow pages, as the insertCell() call below is ** necessary to add the PTRMAP_OVERFLOW1 pointer-map entry. */ assert( rc==SQLITE_OK ); /* clearCell never fails when nLocal==nPayload */ - if( oldCell+szNew > pPage->aDataEnd ) return SQLITE_CORRUPT_BKPT; + if( oldCell < pPage->aData+pPage->hdrOffset+10 ){ + return SQLITE_CORRUPT_BKPT; + } + if( oldCell+szNew > pPage->aDataEnd ){ + return SQLITE_CORRUPT_BKPT; + } memcpy(oldCell, newCell, szNew); return SQLITE_OK; } dropCell(pPage, idx, info.nSize, &rc); if( rc ) goto end_insert; Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -1884,10 +1884,11 @@ */ static void convertToWithoutRowidTable(Parse *pParse, Table *pTab){ Index *pIdx; Index *pPk; int nPk; + int nExtra; int i, j; sqlite3 *db = pParse->db; Vdbe *v = pParse->pVdbe; /* Mark every PRIMARY KEY column as NOT NULL (except for imposter tables) @@ -1926,10 +1927,11 @@ pTab->iPKey = -1; sqlite3CreateIndex(pParse, 0, 0, 0, pList, pTab->keyConf, 0, 0, 0, 0, SQLITE_IDXTYPE_PRIMARYKEY); if( db->mallocFailed || pParse->nErr ) return; pPk = sqlite3PrimaryKeyIndex(pTab); + assert( pPk->nKeyCol==1 ); }else{ pPk = sqlite3PrimaryKeyIndex(pTab); assert( pPk!=0 ); /* @@ -1940,19 +1942,21 @@ for(i=j=1; inKeyCol; i++){ if( isDupColumn(pPk, j, pPk, i) ){ pPk->nColumn--; }else{ testcase( hasColumn(pPk->aiColumn, j, pPk->aiColumn[i]) ); + pPk->azColl[j] = pPk->azColl[i]; + pPk->aSortOrder[j] = pPk->aSortOrder[i]; pPk->aiColumn[j++] = pPk->aiColumn[i]; } } pPk->nKeyCol = j; } assert( pPk!=0 ); pPk->isCovering = 1; if( !db->init.imposterTable ) pPk->uniqNotNull = 1; - nPk = pPk->nKeyCol; + nPk = pPk->nColumn = pPk->nKeyCol; /* Bypass the creation of the PRIMARY KEY btree and the sqlite_master ** table entry. This is only required if currently generating VDBE ** code for a CREATE TABLE (not when parsing one as part of reading ** a database schema). */ @@ -1998,25 +2002,25 @@ assert( pIdx->nColumn>=j ); } /* Add all table columns to the PRIMARY KEY index */ - if( nPknCol ){ - if( resizeIndexObject(db, pPk, pTab->nCol) ) return; - for(i=0, j=nPk; inCol; i++){ - if( !hasColumn(pPk->aiColumn, j, i) ){ - assert( jnColumn ); - pPk->aiColumn[j] = i; - pPk->azColl[j] = sqlite3StrBINARY; - j++; - } + nExtra = 0; + for(i=0; inCol; i++){ + if( !hasColumn(pPk->aiColumn, nPk, i) ) nExtra++; + } + if( resizeIndexObject(db, pPk, nPk+nExtra) ) return; + for(i=0, j=nPk; inCol; i++){ + if( !hasColumn(pPk->aiColumn, j, i) ){ + assert( jnColumn ); + pPk->aiColumn[j] = i; + pPk->azColl[j] = sqlite3StrBINARY; + j++; } - assert( pPk->nColumn==j ); - assert( pTab->nCol==j ); - }else{ - pPk->nColumn = pTab->nCol; } + assert( pPk->nColumn==j ); + assert( pTab->nCol<=j ); recomputeColumnsNotIndexed(pPk); } #ifndef SQLITE_OMIT_VIRTUALTABLE /* @@ -3683,10 +3687,11 @@ sqlite3VdbeAddOp3(v, OP_CreateBtree, iDb, iMem, BTREE_BLOBKEY); /* Gather the complete text of the CREATE INDEX statement into ** the zStmt variable */ + assert( pName!=0 || pStart==0 ); if( pStart ){ int n = (int)(pParse->sLastToken.z - pName->z) + pParse->sLastToken.n; if( pName->z[n-1]==';' ) n--; /* A named index with an explicit CREATE INDEX statement */ zStmt = sqlite3MPrintf(db, "CREATE%s INDEX %.*s", Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -1038,19 +1038,22 @@ if( !ExprHasProperty(p, (EP_TokenOnly|EP_Leaf)) ){ /* The Expr.x union is never used at the same time as Expr.pRight */ assert( p->x.pList==0 || p->pRight==0 ); if( p->pLeft && p->op!=TK_SELECT_COLUMN ) sqlite3ExprDeleteNN(db, p->pLeft); if( p->pRight ){ + assert( !ExprHasProperty(p, EP_WinFunc) ); sqlite3ExprDeleteNN(db, p->pRight); }else if( ExprHasProperty(p, EP_xIsSelect) ){ + assert( !ExprHasProperty(p, EP_WinFunc) ); sqlite3SelectDelete(db, p->x.pSelect); }else{ sqlite3ExprListDelete(db, p->x.pList); - } - if( ExprHasProperty(p, EP_WinFunc) ){ - assert( p->op==TK_FUNCTION ); - sqlite3WindowDelete(db, p->y.pWin); +#ifndef SQLITE_OMIT_WINDOWFUNC + if( ExprHasProperty(p, EP_WinFunc) ){ + sqlite3WindowDelete(db, p->y.pWin); + } +#endif } } if( ExprHasProperty(p, EP_MemToken) ) sqlite3DbFree(db, p->u.zToken); if( !ExprHasProperty(p, EP_Static) ){ sqlite3DbFreeNN(db, p); @@ -1081,20 +1084,10 @@ if( ExprHasProperty(p, EP_TokenOnly) ) return EXPR_TOKENONLYSIZE; if( ExprHasProperty(p, EP_Reduced) ) return EXPR_REDUCEDSIZE; return EXPR_FULLSIZE; } -/* -** Copy the complete content of an Expr node, taking care not to read -** past the end of the structure for a reduced-size version of the source -** Expr. -*/ -static void exprNodeCopy(Expr *pDest, Expr *pSrc){ - memset(pDest, 0, sizeof(Expr)); - memcpy(pDest, pSrc, exprStructSize(pSrc)); -} - /* ** The dupedExpr*Size() routines each return the number of bytes required ** to store a copy of an expression or expression tree. They differ in ** how much of the tree is measured. ** @@ -1330,14 +1323,22 @@ ** gatherSelectWindowsCallback() are used to scan all the expressions ** an a newly duplicated SELECT statement and gather all of the Window ** objects found there, assembling them onto the linked list at Select->pWin. */ static int gatherSelectWindowsCallback(Walker *pWalker, Expr *pExpr){ - if( pExpr->op==TK_FUNCTION && pExpr->y.pWin!=0 ){ - assert( ExprHasProperty(pExpr, EP_WinFunc) ); - pExpr->y.pWin->pNextWin = pWalker->u.pSelect->pWin; - pWalker->u.pSelect->pWin = pExpr->y.pWin; + if( pExpr->op==TK_FUNCTION && ExprHasProperty(pExpr, EP_WinFunc) ){ + Select *pSelect = pWalker->u.pSelect; + Window *pWin = pExpr->y.pWin; + assert( pWin ); + assert( IsWindowFunc(pExpr) ); + assert( pWin->ppThis==0 ); + if( pSelect->pWin ){ + pSelect->pWin->ppThis = &pWin->pNextWin; + } + pWin->pNextWin = pSelect->pWin; + pWin->ppThis = &pSelect->pWin; + pSelect->pWin = pWin; } return WRC_Continue; } static int gatherSelectWindowsSelectCallback(Walker *pWalker, Select *p){ return p==pWalker->u.pSelect ? WRC_Continue : WRC_Prune; @@ -4042,25 +4043,31 @@ ExprList *pEList; /* List of WHEN terms */ struct ExprList_item *aListelem; /* Array of WHEN terms */ Expr opCompare; /* The X==Ei expression */ Expr *pX; /* The X expression */ Expr *pTest = 0; /* X==Ei (form A) or just Ei (form B) */ + Expr *pDel = 0; + sqlite3 *db = pParse->db; assert( !ExprHasProperty(pExpr, EP_xIsSelect) && pExpr->x.pList ); assert(pExpr->x.pList->nExpr > 0); pEList = pExpr->x.pList; aListelem = pEList->a; nExpr = pEList->nExpr; endLabel = sqlite3VdbeMakeLabel(pParse); if( (pX = pExpr->pLeft)!=0 ){ - exprNodeCopy(&tempX, pX); + pDel = sqlite3ExprDup(db, pX, 0); + if( db->mallocFailed ){ + sqlite3ExprDelete(db, pDel); + break; + } testcase( pX->op==TK_COLUMN ); - exprToRegister(&tempX, exprCodeVector(pParse, &tempX, ®Free1)); + exprToRegister(pDel, exprCodeVector(pParse, pDel, ®Free1)); testcase( regFree1==0 ); memset(&opCompare, 0, sizeof(opCompare)); opCompare.op = TK_EQ; - opCompare.pLeft = &tempX; + opCompare.pLeft = pDel; pTest = &opCompare; /* Ticket b351d95f9cd5ef17e9d9dbae18f5ca8611190001: ** The value in regFree1 might get SCopy-ed into the file result. ** So make sure that the regFree1 register is not reused for other ** purposes and possibly overwritten. */ @@ -4084,10 +4091,11 @@ if( (nExpr&1)!=0 ){ sqlite3ExprCode(pParse, pEList->a[nExpr-1].pExpr, target); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, target); } + sqlite3ExprDelete(db, pDel); sqlite3VdbeResolveLabel(v, endLabel); break; } #ifndef SQLITE_OMIT_TRIGGER case TK_RAISE: { @@ -4365,44 +4373,48 @@ Expr *pExpr, /* The BETWEEN expression */ int dest, /* Jump destination or storage location */ void (*xJump)(Parse*,Expr*,int,int), /* Action to take */ int jumpIfNull /* Take the jump if the BETWEEN is NULL */ ){ - Expr exprAnd; /* The AND operator in x>=y AND x<=z */ + Expr exprAnd; /* The AND operator in x>=y AND x<=z */ Expr compLeft; /* The x>=y term */ Expr compRight; /* The x<=z term */ - Expr exprX; /* The x subexpression */ int regFree1 = 0; /* Temporary use register */ + Expr *pDel = 0; + sqlite3 *db = pParse->db; memset(&compLeft, 0, sizeof(Expr)); memset(&compRight, 0, sizeof(Expr)); memset(&exprAnd, 0, sizeof(Expr)); assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); - exprNodeCopy(&exprX, pExpr->pLeft); - exprAnd.op = TK_AND; - exprAnd.pLeft = &compLeft; - exprAnd.pRight = &compRight; - compLeft.op = TK_GE; - compLeft.pLeft = &exprX; - compLeft.pRight = pExpr->x.pList->a[0].pExpr; - compRight.op = TK_LE; - compRight.pLeft = &exprX; - compRight.pRight = pExpr->x.pList->a[1].pExpr; - exprToRegister(&exprX, exprCodeVector(pParse, &exprX, ®Free1)); - if( xJump ){ - xJump(pParse, &exprAnd, dest, jumpIfNull); - }else{ - /* Mark the expression is being from the ON or USING clause of a join - ** so that the sqlite3ExprCodeTarget() routine will not attempt to move - ** it into the Parse.pConstExpr list. We should use a new bit for this, - ** for clarity, but we are out of bits in the Expr.flags field so we - ** have to reuse the EP_FromJoin bit. Bummer. */ - exprX.flags |= EP_FromJoin; - sqlite3ExprCodeTarget(pParse, &exprAnd, dest); - } - sqlite3ReleaseTempReg(pParse, regFree1); + pDel = sqlite3ExprDup(db, pExpr->pLeft, 0); + if( db->mallocFailed==0 ){ + exprAnd.op = TK_AND; + exprAnd.pLeft = &compLeft; + exprAnd.pRight = &compRight; + compLeft.op = TK_GE; + compLeft.pLeft = pDel; + compLeft.pRight = pExpr->x.pList->a[0].pExpr; + compRight.op = TK_LE; + compRight.pLeft = pDel; + compRight.pRight = pExpr->x.pList->a[1].pExpr; + exprToRegister(pDel, exprCodeVector(pParse, pDel, ®Free1)); + if( xJump ){ + xJump(pParse, &exprAnd, dest, jumpIfNull); + }else{ + /* Mark the expression is being from the ON or USING clause of a join + ** so that the sqlite3ExprCodeTarget() routine will not attempt to move + ** it into the Parse.pConstExpr list. We should use a new bit for this, + ** for clarity, but we are out of bits in the Expr.flags field so we + ** have to reuse the EP_FromJoin bit. Bummer. */ + pDel->flags |= EP_FromJoin; + sqlite3ExprCodeTarget(pParse, &exprAnd, dest); + } + sqlite3ReleaseTempReg(pParse, regFree1); + } + sqlite3ExprDelete(db, pDel); /* Ensure adequate test coverage */ testcase( xJump==sqlite3ExprIfTrue && jumpIfNull==0 && regFree1==0 ); testcase( xJump==sqlite3ExprIfTrue && jumpIfNull==0 && regFree1!=0 ); testcase( xJump==sqlite3ExprIfTrue && jumpIfNull!=0 && regFree1==0 ); @@ -4837,24 +4849,21 @@ return 1; } return 2; } if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){ - if( pA->op==TK_FUNCTION ){ + if( pA->op==TK_FUNCTION || pA->op==TK_AGG_FUNCTION ){ if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2; #ifndef SQLITE_OMIT_WINDOWFUNC - /* Justification for the assert(): - ** window functions have p->op==TK_FUNCTION but aggregate functions - ** have p->op==TK_AGG_FUNCTION. So any comparison between an aggregate - ** function and a window function should have failed before reaching - ** this point. And, it is not possible to have a window function and - ** a scalar function with the same name and number of arguments. So - ** if we reach this point, either A and B both window functions or - ** neither are a window functions. */ - assert( ExprHasProperty(pA,EP_WinFunc)==ExprHasProperty(pB,EP_WinFunc) ); + assert( pA->op==pB->op ); + if( ExprHasProperty(pA,EP_WinFunc)!=ExprHasProperty(pB,EP_WinFunc) ){ + return 2; + } if( ExprHasProperty(pA,EP_WinFunc) ){ - if( sqlite3WindowCompare(pParse,pA->y.pWin,pB->y.pWin)!=0 ) return 2; + if( sqlite3WindowCompare(pParse, pA->y.pWin, pB->y.pWin, 1)!=0 ){ + return 2; + } } #endif }else if( pA->op==TK_NULL ){ return 0; }else if( pA->op==TK_COLLATE ){ Index: src/fkey.c ================================================================== --- src/fkey.c +++ src/fkey.c @@ -1330,10 +1330,11 @@ if( db->mallocFailed==1 ){ fkTriggerDelete(db, pTrigger); return 0; } assert( pStep!=0 ); + assert( pTrigger!=0 ); switch( action ){ case OE_Restrict: pStep->op = TK_SELECT; break; Index: src/os_unix.c ================================================================== --- src/os_unix.c +++ src/os_unix.c @@ -519,17 +519,18 @@ #define osLstat ((int(*)(const char*,struct stat*))aSyscall[27].pCurrent) #if defined(__linux__) && defined(SQLITE_ENABLE_BATCH_ATOMIC_WRITE) # ifdef __ANDROID__ { "ioctl", (sqlite3_syscall_ptr)(int(*)(int, int, ...))ioctl, 0 }, +#define osIoctl ((int(*)(int,int,...))aSyscall[28].pCurrent) # else { "ioctl", (sqlite3_syscall_ptr)ioctl, 0 }, +#define osIoctl ((int(*)(int,unsigned long,...))aSyscall[28].pCurrent) # endif #else { "ioctl", (sqlite3_syscall_ptr)0, 0 }, #endif -#define osIoctl ((int(*)(int,int,...))aSyscall[28].pCurrent) }; /* End of the overrideable system calls */ /* @@ -7601,11 +7602,11 @@ } default: { assert( 0 ); /* The call assures that only valid opcodes are sent */ } } - /*NOTREACHED*/ + /*NOTREACHED*/ assert(0); return SQLITE_ERROR; } /* ** Within this division (the proxying locking implementation) the procedures Index: src/os_win.c ================================================================== --- src/os_win.c +++ src/os_win.c @@ -4213,10 +4213,11 @@ if( !pShm ){ rc = winOpenSharedMemory(pDbFd); if( rc!=SQLITE_OK ) return rc; pShm = pDbFd->pShm; + assert( pShm!=0 ); } pShmNode = pShm->pShmNode; sqlite3_mutex_enter(pShmNode->mutex); if( pShmNode->isUnlocked ){ @@ -4515,10 +4516,11 @@ osGetCurrentProcessId(), pFd, sqlite3ErrName(rc))); return rc; } } if( pFd->mmapSize >= iOff+nAmt ){ + assert( pFd->pMapRegion!=0 ); *pp = &((u8 *)pFd->pMapRegion)[iOff]; pFd->nFetchOut++; } } #endif Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -456,10 +456,11 @@ ** For a compound SELECT statement, make sure p->pPrior->pNext==p for ** all elements in the list. And make sure list length does not exceed ** SQLITE_LIMIT_COMPOUND_SELECT. */ static void parserDoubleLinkSelect(Parse *pParse, Select *p){ + assert( p!=0 ); if( p->pPrior ){ Select *pNext = 0, *pLoop; int mxSelect, cnt = 0; for(pLoop=p; pLoop; pNext=pLoop, pLoop=pLoop->pPrior, cnt++){ pLoop->pNext = pNext; @@ -1042,15 +1043,15 @@ expr(A) ::= id(X) LP STAR RP. { A = sqlite3ExprFunction(pParse, 0, &X, 0); } %ifndef SQLITE_OMIT_WINDOWFUNC -expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP over_clause(Z). { +expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP filter_over(Z). { A = sqlite3ExprFunction(pParse, Y, &X, D); sqlite3WindowAttach(pParse, A, Z); } -expr(A) ::= id(X) LP STAR RP over_clause(Z). { +expr(A) ::= id(X) LP STAR RP filter_over(Z). { A = sqlite3ExprFunction(pParse, 0, &X, 0); sqlite3WindowAttach(pParse, A, Z); } %endif @@ -1655,12 +1656,18 @@ %destructor frame_opt {sqlite3WindowDelete(pParse->db, $$);} %type part_opt {ExprList*} %destructor part_opt {sqlite3ExprListDelete(pParse->db, $$);} -%type filter_opt {Expr*} -%destructor filter_opt {sqlite3ExprDelete(pParse->db, $$);} +%type filter_clause {Expr*} +%destructor filter_clause {sqlite3ExprDelete(pParse->db, $$);} + +%type over_clause {Window*} +%destructor over_clause {sqlite3WindowDelete(pParse->db, $$);} + +%type filter_over {Window*} +%destructor filter_over {sqlite3WindowDelete(pParse->db, $$);} %type range_or_rows {int} %type frame_bound {struct FrameBound} %destructor frame_bound {sqlite3ExprDelete(pParse->db, $$.pExpr);} @@ -1722,29 +1729,39 @@ %type window_clause {Window*} %destructor window_clause {sqlite3WindowListDelete(pParse->db, $$);} window_clause(A) ::= WINDOW windowdefn_list(B). { A = B; } -%type over_clause {Window*} -%destructor over_clause {sqlite3WindowDelete(pParse->db, $$);} -over_clause(A) ::= filter_opt(W) OVER LP window(Z) RP. { +filter_over(A) ::= filter_clause(F) over_clause(O). { + O->pFilter = F; + A = O; +} +filter_over(A) ::= over_clause(O). { + A = O; +} +filter_over(A) ::= filter_clause(F). { + A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); + if( A ){ + A->eFrmType = TK_FILTER; + A->pFilter = F; + }else{ + sqlite3ExprDelete(pParse->db, F); + } +} + +over_clause(A) ::= OVER LP window(Z) RP. { A = Z; assert( A!=0 ); - A->pFilter = W; } -over_clause(A) ::= filter_opt(W) OVER nm(Z). { +over_clause(A) ::= OVER nm(Z). { A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); if( A ){ A->zName = sqlite3DbStrNDup(pParse->db, Z.z, Z.n); - A->pFilter = W; - }else{ - sqlite3ExprDelete(pParse->db, W); } } -filter_opt(A) ::= . { A = 0; } -filter_opt(A) ::= FILTER LP WHERE expr(X) RP. { A = X; } +filter_clause(A) ::= FILTER LP WHERE expr(X) RP. { A = X; } %endif /* SQLITE_OMIT_WINDOWFUNC */ /* ** The code generator needs some extra TK_ token values for tokens that ** are synthesized and do not actually appear in the grammar: Index: src/pcache.c ================================================================== --- src/pcache.c +++ src/pcache.c @@ -260,10 +260,11 @@ if( sqlite3GlobalConfig.pcache2.xInit==0 ){ /* IMPLEMENTATION-OF: R-26801-64137 If the xInit() method is NULL, then the ** built-in default page cache is used instead of the application defined ** page cache. */ sqlite3PCacheSetDefault(); + assert( sqlite3GlobalConfig.pcache2.xInit!=0 ); } return sqlite3GlobalConfig.pcache2.xInit(sqlite3GlobalConfig.pcache2.pArg); } void sqlite3PcacheShutdown(void){ if( sqlite3GlobalConfig.pcache2.xShutdown ){ Index: src/pcache1.c ================================================================== --- src/pcache1.c +++ src/pcache1.c @@ -422,10 +422,11 @@ PgHdr1 *p = 0; void *pPg; assert( sqlite3_mutex_held(pCache->pGroup->mutex) ); if( pCache->pFree || (pCache->nPage==0 && pcache1InitBulk(pCache)) ){ + assert( pCache->pFree!=0 ); p = pCache->pFree; pCache->pFree = p->pNext; p->pNext = 0; }else{ #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -1161,10 +1161,19 @@ case PragTyp_INDEX_INFO: if( zRight ){ Index *pIdx; Table *pTab; pIdx = sqlite3FindIndex(db, zRight, zDb); + if( pIdx==0 ){ + /* If there is no index named zRight, check to see if there is a + ** WITHOUT ROWID table named zRight, and if there is, show the + ** structure of the PRIMARY KEY index for that table. */ + pTab = sqlite3LocateTable(pParse, LOCATE_NOERR, zRight, zDb); + if( pTab && !HasRowid(pTab) ){ + pIdx = sqlite3PrimaryKeyIndex(pTab); + } + } if( pIdx ){ int iIdxDb = sqlite3SchemaToIndex(db, pIdx->pSchema); int i; int mx; if( pPragma->iArg ){ Index: src/prepare.c ================================================================== --- src/prepare.c +++ src/prepare.c @@ -726,11 +726,14 @@ *pzTail = sParse.zTail; } rc = sParse.rc; #ifndef SQLITE_OMIT_EXPLAIN - if( rc==SQLITE_OK && sParse.pVdbe && sParse.explain ){ + /* Justification for the ALWAYS(): The only way for rc to be SQLITE_OK and + ** sParse.pVdbe to be NULL is if the input SQL is an empty string, but in + ** that case, sParse.explain will be false. */ + if( sParse.explain && rc==SQLITE_OK && ALWAYS(sParse.pVdbe) ){ static const char * const azColName[] = { "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment", "id", "parent", "notused", "detail" }; int iFirst, mx; @@ -751,12 +754,12 @@ #endif if( db->init.busy==0 ){ sqlite3VdbeSetSql(sParse.pVdbe, zSql, (int)(sParse.zTail-zSql), prepFlags); } - if( sParse.pVdbe && (rc!=SQLITE_OK || db->mallocFailed) ){ - sqlite3VdbeFinalize(sParse.pVdbe); + if( rc!=SQLITE_OK || db->mallocFailed ){ + if( sParse.pVdbe ) sqlite3VdbeFinalize(sParse.pVdbe); assert(!(*ppStmt)); }else{ *ppStmt = (sqlite3_stmt*)sParse.pVdbe; } Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -747,11 +747,13 @@ int nId; /* Number of characters in function name */ const char *zId; /* The function name. */ FuncDef *pDef; /* Information about the function */ u8 enc = ENC(pParse->db); /* The database encoding */ int savedAllowFlags = (pNC->ncFlags & (NC_AllowAgg | NC_AllowWin)); - +#ifndef SQLITE_OMIT_WINDOWFUNC + Window *pWin = (IsWindowFunc(pExpr) ? pExpr->y.pWin : 0); +#endif assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); zId = pExpr->u.zToken; nId = sqlite3Strlen30(zId); pDef = sqlite3FindFunction(pParse->db, zId, n, enc, 0); if( pDef==0 ){ @@ -828,22 +830,22 @@ #ifndef SQLITE_OMIT_WINDOWFUNC assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX) || (pDef->xValue==0 && pDef->xInverse==0) || (pDef->xValue && pDef->xInverse && pDef->xSFunc && pDef->xFinalize) ); - if( pDef && pDef->xValue==0 && ExprHasProperty(pExpr, EP_WinFunc) ){ + if( pDef && pDef->xValue==0 && pWin ){ sqlite3ErrorMsg(pParse, "%.*s() may not be used as a window function", nId, zId ); pNC->nErr++; }else if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) - || (is_agg && (pDef->funcFlags&SQLITE_FUNC_WINDOW) && !pExpr->y.pWin) - || (is_agg && pExpr->y.pWin && (pNC->ncFlags & NC_AllowWin)==0) + || (is_agg && (pDef->funcFlags&SQLITE_FUNC_WINDOW) && !pWin) + || (is_agg && pWin && (pNC->ncFlags & NC_AllowWin)==0) ){ const char *zType; - if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || pExpr->y.pWin ){ + if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || pWin ){ zType = "window"; }else{ zType = "aggregate"; } sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId); @@ -867,45 +869,69 @@ }else if( wrong_num_args ){ sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()", nId, zId); pNC->nErr++; } +#ifndef SQLITE_OMIT_WINDOWFUNC + else if( is_agg==0 && ExprHasProperty(pExpr, EP_WinFunc) ){ + sqlite3ErrorMsg(pParse, + "FILTER may not be used with non-aggregate %.*s()", + nId, zId + ); + pNC->nErr++; + } +#endif if( is_agg ){ /* Window functions may not be arguments of aggregate functions. ** Or arguments of other window functions. But aggregate functions ** may be arguments for window functions. */ #ifndef SQLITE_OMIT_WINDOWFUNC - pNC->ncFlags &= ~(NC_AllowWin | (!pExpr->y.pWin ? NC_AllowAgg : 0)); + pNC->ncFlags &= ~(NC_AllowWin | (!pWin ? NC_AllowAgg : 0)); #else pNC->ncFlags &= ~NC_AllowAgg; #endif } } +#ifndef SQLITE_OMIT_WINDOWFUNC + else if( ExprHasProperty(pExpr, EP_WinFunc) ){ + is_agg = 1; + } +#endif sqlite3WalkExprList(pWalker, pList); if( is_agg ){ #ifndef SQLITE_OMIT_WINDOWFUNC - if( pExpr->y.pWin ){ + if( pWin ){ Select *pSel = pNC->pWinSelect; + assert( pWin==pExpr->y.pWin ); if( IN_RENAME_OBJECT==0 ){ - sqlite3WindowUpdate(pParse, pSel->pWinDefn, pExpr->y.pWin, pDef); + sqlite3WindowUpdate(pParse, pSel->pWinDefn, pWin, pDef); } - sqlite3WalkExprList(pWalker, pExpr->y.pWin->pPartition); - sqlite3WalkExprList(pWalker, pExpr->y.pWin->pOrderBy); - sqlite3WalkExpr(pWalker, pExpr->y.pWin->pFilter); + sqlite3WalkExprList(pWalker, pWin->pPartition); + sqlite3WalkExprList(pWalker, pWin->pOrderBy); + sqlite3WalkExpr(pWalker, pWin->pFilter); if( 0==pSel->pWin - || 0==sqlite3WindowCompare(pParse, pSel->pWin, pExpr->y.pWin) + || 0==sqlite3WindowCompare(pParse, pSel->pWin, pWin, 0) ){ - pExpr->y.pWin->pNextWin = pSel->pWin; - pSel->pWin = pExpr->y.pWin; + pWin->pNextWin = pSel->pWin; + if( pSel->pWin ){ + pSel->pWin->ppThis = &pWin->pNextWin; + } + pSel->pWin = pWin; + pWin->ppThis = &pSel->pWin; } pNC->ncFlags |= NC_HasWin; }else #endif /* SQLITE_OMIT_WINDOWFUNC */ { NameContext *pNC2 = pNC; pExpr->op = TK_AGG_FUNCTION; pExpr->op2 = 0; +#ifndef SQLITE_OMIT_WINDOWFUNC + if( ExprHasProperty(pExpr, EP_WinFunc) ){ + sqlite3WalkExpr(pWalker, pExpr->y.pWin->pFilter); + } +#endif while( pNC2 && !sqlite3FunctionUsesThisSrc(pExpr, pNC2->pSrcList) ){ pExpr->op2++; pNC2 = pNC2->pNext; } assert( pDef!=0 ); @@ -1272,39 +1298,36 @@ return 0; } #ifndef SQLITE_OMIT_WINDOWFUNC /* -** Walker callback for resolveRemoveWindows(). +** Walker callback for windowRemoveExprFromSelect(). */ static int resolveRemoveWindowsCb(Walker *pWalker, Expr *pExpr){ if( ExprHasProperty(pExpr, EP_WinFunc) ){ - Window **pp; - for(pp=&pWalker->u.pSelect->pWin; *pp; pp=&(*pp)->pNextWin){ - if( *pp==pExpr->y.pWin ){ - *pp = (*pp)->pNextWin; - break; - } - } + Window *pWin = pExpr->y.pWin; + sqlite3WindowUnlinkFromSelect(pWin); } return WRC_Continue; } /* ** Remove any Window objects owned by the expression pExpr from the ** Select.pWin list of Select object pSelect. */ -static void resolveRemoveWindows(Select *pSelect, Expr *pExpr){ - Walker sWalker; - memset(&sWalker, 0, sizeof(Walker)); - sWalker.xExprCallback = resolveRemoveWindowsCb; - sWalker.u.pSelect = pSelect; - sqlite3WalkExpr(&sWalker, pExpr); +static void windowRemoveExprFromSelect(Select *pSelect, Expr *pExpr){ + if( pSelect->pWin ){ + Walker sWalker; + memset(&sWalker, 0, sizeof(Walker)); + sWalker.xExprCallback = resolveRemoveWindowsCb; + sWalker.u.pSelect = pSelect; + sqlite3WalkExpr(&sWalker, pExpr); + } } #else -# define resolveRemoveWindows(x,y) -#endif +# define windowRemoveExprFromSelect(a, b) +#endif /* SQLITE_OMIT_WINDOWFUNC */ /* ** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect. ** The Name context of the SELECT statement is pNC. zType is either ** "ORDER" or "GROUP" depending on which type of clause pOrderBy is. @@ -1371,11 +1394,11 @@ for(j=0; jpEList->nExpr; j++){ if( sqlite3ExprCompare(0, pE, pSelect->pEList->a[j].pExpr, -1)==0 ){ /* Since this expresion is being changed into a reference ** to an identical expression in the result set, remove all Window ** objects belonging to the expression from the Select.pWin list. */ - resolveRemoveWindows(pSelect, pE); + windowRemoveExprFromSelect(pSelect, pE); pItem->u.x.iOrderByCol = j+1; } } } return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType); Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -100,10 +100,11 @@ if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){ sqlite3WindowListDelete(db, p->pWinDefn); } #endif if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith); + assert( p->pWin==0 ); if( bFree ) sqlite3DbFreeNN(db, p); p = pPrior; bFree = 1; } } @@ -3947,10 +3948,11 @@ ** elements we are now copying in. */ for(pParent=p; pParent; pParent=pParent->pPrior, pSub=pSub->pPrior){ int nSubSrc; u8 jointype = 0; + assert( pSub!=0 ); pSubSrc = pSub->pSrc; /* FROM clause of subquery */ nSubSrc = pSubSrc->nSrc; /* Number of terms in subquery FROM clause */ pSrc = pParent->pSrc; /* FROM clause of the outer query */ if( pSrc ){ @@ -4401,11 +4403,14 @@ ExprList *pOrderBy; u8 sortOrder; assert( *ppMinMax==0 ); assert( pFunc->op==TK_AGG_FUNCTION ); - if( pEList==0 || pEList->nExpr!=1 ) return eRet; + assert( !IsWindowFunc(pFunc) ); + if( pEList==0 || pEList->nExpr!=1 || ExprHasProperty(pFunc, EP_WinFunc) ){ + return eRet; + } zFunc = pFunc->u.zToken; if( sqlite3StrICmp(zFunc, "min")==0 ){ eRet = WHERE_ORDERBY_MIN; sortOrder = SQLITE_SO_ASC; }else if( sqlite3StrICmp(zFunc, "max")==0 ){ @@ -4448,11 +4453,11 @@ if( IsVirtual(pTab) ) return 0; if( pExpr->op!=TK_AGG_FUNCTION ) return 0; if( NEVER(pAggInfo->nFunc==0) ) return 0; if( (pAggInfo->aFunc[0].pFunc->funcFlags&SQLITE_FUNC_COUNT)==0 ) return 0; - if( pExpr->flags&EP_Distinct ) return 0; + if( ExprHasProperty(pExpr, EP_Distinct|EP_WinFunc) ) return 0; return pTab; } /* @@ -5328,20 +5333,28 @@ int nArg; int addrNext = 0; int regAgg; ExprList *pList = pF->pExpr->x.pList; assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) ); + assert( !IsWindowFunc(pF->pExpr) ); + if( ExprHasProperty(pF->pExpr, EP_WinFunc) ){ + Expr *pFilter = pF->pExpr->y.pWin->pFilter; + addrNext = sqlite3VdbeMakeLabel(pParse); + sqlite3ExprIfFalse(pParse, pFilter, addrNext, SQLITE_JUMPIFNULL); + } if( pList ){ nArg = pList->nExpr; regAgg = sqlite3GetTempRange(pParse, nArg); sqlite3ExprCodeExprList(pParse, pList, regAgg, 0, SQLITE_ECEL_DUP); }else{ nArg = 0; regAgg = 0; } if( pF->iDistinct>=0 ){ - addrNext = sqlite3VdbeMakeLabel(pParse); + if( addrNext==0 ){ + addrNext = sqlite3VdbeMakeLabel(pParse); + } testcase( nArg==0 ); /* Error condition */ testcase( nArg>1 ); /* Also an error */ codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg); } if( pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ @@ -5854,12 +5867,19 @@ /* The code for a subquery should only be generated once, though it is ** technically harmless for it to be generated multiple times. The ** following assert() will detect if something changes to cause ** the same subquery to be coded multiple times, as a signal to the - ** developers to try to optimize the situation. */ - assert( pItem->addrFillSub==0 ); + ** developers to try to optimize the situation. + ** + ** Update 2019-07-24: + ** See ticket https://sqlite.org/src/tktview/c52b09c7f38903b1311cec40. + ** The dbsqlfuzz fuzzer found a case where the same subquery gets + ** coded twice. So this assert() now becomes a testcase(). It should + ** be very rare, though. + */ + testcase( pItem->addrFillSub!=0 ); /* Increment Parse.nHeight by the height of the largest expression ** tree referred to by this, the parent select. The child select ** may contain expression trees of at most ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit @@ -5929,11 +5949,11 @@ int topAddr; int onceAddr = 0; int retAddr; struct SrcList_item *pPrior; - assert( pItem->addrFillSub==0 ); + testcase( pItem->addrFillSub==0 ); /* Ticket c52b09c7f38903b1311 */ pItem->regReturn = ++pParse->nMem; topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn); pItem->addrFillSub = topAddr+1; if( pItem->fg.isCorrelated==0 ){ /* If the subquery is not correlated and if we are not inside of @@ -6220,13 +6240,20 @@ minMaxFlag = minMaxQuery(db, sAggInfo.aFunc[0].pExpr, &pMinMaxOrderBy); }else{ minMaxFlag = WHERE_ORDERBY_NORMAL; } for(i=0; ix.pList); + sqlite3ExprAnalyzeAggList(&sNC, pExpr->x.pList); +#ifndef SQLITE_OMIT_WINDOWFUNC + assert( !IsWindowFunc(pExpr) ); + if( ExprHasProperty(pExpr, EP_WinFunc) ){ + sqlite3ExprAnalyzeAggregates(&sNC, pExpr->y.pWin->pFilter); + } +#endif sNC.ncFlags &= ~NC_InAggFunc; } sAggInfo.mxReg = pParse->nMem; if( db->mallocFailed ) goto select_end; #if SELECTTRACE_ENABLED Index: src/shell.c.in ================================================================== --- src/shell.c.in +++ src/shell.c.in @@ -1255,16 +1255,16 @@ sqlite3_result_error(context, "edit() cannot open temp file", -1); goto edit_func_end; } sz = sqlite3_value_bytes(argv[0]); if( bBin ){ - x = fwrite(sqlite3_value_blob(argv[0]), 1, sz, f); + x = fwrite(sqlite3_value_blob(argv[0]), 1, (size_t)sz, f); }else{ const char *z = (const char*)sqlite3_value_text(argv[0]); /* Remember whether or not the value originally contained \r\n */ if( z && strstr(z,"\r\n")!=0 ) hasCRNL = 1; - x = fwrite(sqlite3_value_text(argv[0]), 1, sz, f); + x = fwrite(sqlite3_value_text(argv[0]), 1, (size_t)sz, f); } fclose(f); f = 0; if( x!=sz ){ sqlite3_result_error(context, "edit() could not write the whole file", -1); @@ -1293,11 +1293,11 @@ p = sqlite3_malloc64( sz+(bBin==0) ); if( p==0 ){ sqlite3_result_error_nomem(context); goto edit_func_end; } - x = fread(p, 1, sz, f); + x = fread(p, 1, (size_t)sz, f); fclose(f); f = 0; if( x!=sz ){ sqlite3_result_error(context, "could not read back the whole file", -1); goto edit_func_end; @@ -3896,10 +3896,12 @@ nLine++; if( fgets(zLine, sizeof(zLine), in)==0 ) goto readHexDb_error; rc = sscanf(zLine, "| size %d pagesize %d", &n, &pgsz); if( rc!=2 ) goto readHexDb_error; if( n<0 ) goto readHexDb_error; + if( pgsz<512 || pgsz>65536 || (pgsz&(pgsz-1))!=0 ) goto readHexDb_error; + n = (n+pgsz-1)&~(pgsz-1); /* Round n up to the next multiple of pgsz */ a = sqlite3_malloc( n ? n : 1 ); if( a==0 ){ utf8_printf(stderr, "Out of memory!\n"); goto readHexDb_error; } @@ -6636,11 +6638,11 @@ }else{ pTab->azlCol[0] = shellMPrintf(&rc, ""); } i = 1; shellPreparePrintf(dbtmp, &rc, &pStmt, - "SELECT %Q || group_concat(name, ', ') " + "SELECT %Q || group_concat(quote(name), ', ') " " FILTER (WHERE cid!=%d) OVER (ORDER BY %s cid) " "FROM pragma_table_info(%Q)", bIntkey ? ", " : "", pTab->iPk, bIntkey ? "" : "(CASE WHEN pk=0 THEN 1000000 ELSE pk END), ", zName @@ -6828,10 +6830,11 @@ } shellExecPrintf(pState->db, &rc, /* Attach an in-memory database named 'recovery'. Create an indexed ** cache of the sqlite_dbptr virtual table. */ + "PRAGMA writable_schema = on;" "ATTACH %Q AS recovery;" "DROP TABLE IF EXISTS recovery.dbptr;" "DROP TABLE IF EXISTS recovery.freelist;" "DROP TABLE IF EXISTS recovery.map;" "DROP TABLE IF EXISTS recovery.schema;" @@ -6857,10 +6860,25 @@ " FROM freelist WHERE n>=0" ")" "REPLACE INTO recovery.freelist SELECT freepgno FROM freelist;" ); } + + /* If this is an auto-vacuum database, add all pointer-map pages to + ** the freelist table. Do this regardless of whether or not + ** --freelist-corrupt was specified. */ + shellExec(pState->db, &rc, + "WITH ptrmap(pgno) AS (" + " SELECT 2 WHERE shell_int32(" + " (SELECT data FROM sqlite_dbpage WHERE pgno=1), 13" + " )" + " UNION ALL " + " SELECT pgno+1+(SELECT page_size FROM pragma_page_size)/5 AS pp " + " FROM ptrmap WHERE pp<=(SELECT page_count FROM pragma_page_count)" + ")" + "REPLACE INTO recovery.freelist SELECT pgno FROM ptrmap" + ); shellExec(pState->db, &rc, "CREATE TABLE recovery.dbptr(" " pgno, child, PRIMARY KEY(child, pgno)" ") WITHOUT ROWID;" @@ -6963,10 +6981,11 @@ shellPrepare(pState->db, &rc, "SELECT pgno FROM recovery.map WHERE root=?", &pPages ); shellPrepare(pState->db, &rc, "SELECT max(field), group_concat(shell_escape_crnl(quote(value)), ', ')" + ", min(field) " "FROM sqlite_dbdata WHERE pgno = ? AND field != ?" "GROUP BY cell", &pCells ); /* Loop through each root page. */ @@ -6981,10 +7000,11 @@ int bIntkey = sqlite3_column_int(pLoop, 1); int nCol = sqlite3_column_int(pLoop, 2); int bNoop = 0; RecoverTable *pTab; + assert( bIntkey==0 || bIntkey==1 ); pTab = recoverFindTable(pState, &rc, iRoot, bIntkey, nCol, &bNoop); if( bNoop || rc ) continue; if( pTab==0 ){ if( pOrphan==0 ){ pOrphan = recoverOrphanTable(pState, &rc, zLostAndFound, nOrphan); @@ -7002,22 +7022,32 @@ while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pPages) ){ int iPgno = sqlite3_column_int(pPages, 0); sqlite3_bind_int(pCells, 1, iPgno); while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pCells) ){ int nField = sqlite3_column_int(pCells, 0); + int iMin = sqlite3_column_int(pCells, 2); const char *zVal = (const char*)sqlite3_column_text(pCells, 1); + + RecoverTable *pTab2 = pTab; + if( pTab!=pOrphan && (iMin<0)!=bIntkey ){ + if( pOrphan==0 ){ + pOrphan = recoverOrphanTable(pState, &rc, zLostAndFound, nOrphan); + } + pTab2 = pOrphan; + if( pTab2==0 ) break; + } nField = nField+1; - if( pTab==pOrphan ){ + if( pTab2==pOrphan ){ raw_printf(pState->out, "INSERT INTO %s VALUES(%d, %d, %d, %s%s%s);\n", - pTab->zQuoted, iRoot, iPgno, nField, - bIntkey ? "" : "NULL, ", zVal, pTab->azlCol[nField] + pTab2->zQuoted, iRoot, iPgno, nField, + iMin<0 ? "" : "NULL, ", zVal, pTab2->azlCol[nField] ); }else{ raw_printf(pState->out, "INSERT INTO %s(%s) VALUES( %s );\n", - pTab->zQuoted, pTab->azlCol[nField], zVal + pTab2->zQuoted, pTab2->azlCol[nField], zVal ); } } shellReset(&rc, pCells); } @@ -8632,20 +8662,20 @@ sqlite3_snprintf(sizeof(zScNum), zScNum, "%d", ++iSchema); appendText(&sSelect, zDiv, 0); zDiv = " UNION ALL "; appendText(&sSelect, "SELECT shell_add_schema(sql,", 0); if( sqlite3_stricmp(zDb, "main")!=0 ){ - appendText(&sSelect, zDb, '"'); + appendText(&sSelect, zDb, '\''); }else{ appendText(&sSelect, "NULL", 0); } appendText(&sSelect, ",name) AS sql, type, tbl_name, name, rowid,", 0); appendText(&sSelect, zScNum, 0); appendText(&sSelect, " AS snum, ", 0); appendText(&sSelect, zDb, '\''); appendText(&sSelect, " AS sname FROM ", 0); - appendText(&sSelect, zDb, '"'); + appendText(&sSelect, zDb, quoteChar(zDb)); appendText(&sSelect, ".sqlite_master", 0); } sqlite3_finalize(pStmt); #ifdef SQLITE_INTROSPECTION_PRAGMAS if( zName ){ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -210,19 +210,19 @@ ** Ticket #3860: The llvm-gcc-4.2 compiler from Apple chokes on ** the ((void*)&((char*)0)[X]) construct. But MSVC chokes on ((void*)(X)). ** So we have to define the macros in different ways depending on the ** compiler. */ -#if defined(__PTRDIFF_TYPE__) /* This case should work for GCC */ +#if defined(HAVE_STDINT_H) /* Use this case if we have ANSI headers */ +# define SQLITE_INT_TO_PTR(X) ((void*)(intptr_t)(X)) +# define SQLITE_PTR_TO_INT(X) ((int)(intptr_t)(X)) +#elif defined(__PTRDIFF_TYPE__) /* This case should work for GCC */ # define SQLITE_INT_TO_PTR(X) ((void*)(__PTRDIFF_TYPE__)(X)) # define SQLITE_PTR_TO_INT(X) ((int)(__PTRDIFF_TYPE__)(X)) #elif !defined(__GNUC__) /* Works for compilers other than LLVM */ # define SQLITE_INT_TO_PTR(X) ((void*)&((char*)0)[X]) # define SQLITE_PTR_TO_INT(X) ((int)(((char*)X)-(char*)0)) -#elif defined(HAVE_STDINT_H) /* Use this case if we have ANSI headers */ -# define SQLITE_INT_TO_PTR(X) ((void*)(intptr_t)(X)) -# define SQLITE_PTR_TO_INT(X) ((int)(intptr_t)(X)) #else /* Generates a warning - but it always works */ # define SQLITE_INT_TO_PTR(X) ((void*)(X)) # define SQLITE_PTR_TO_INT(X) ((int)(X)) #endif @@ -2509,11 +2509,11 @@ ** TK_AGG_FUNCTION: nesting depth */ AggInfo *pAggInfo; /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */ union { Table *pTab; /* TK_COLUMN: Table containing column. Can be NULL ** for a column of an index on an expression */ - Window *pWin; /* TK_FUNCTION: Window definition for the func */ + Window *pWin; /* EP_WinFunc: Window/Filter defn for a function */ struct { /* TK_IN, TK_SELECT, and TK_EXISTS */ int iAddr; /* Subroutine entry address */ int regReturn; /* Register used to hold return address */ } sub; } y; @@ -2596,10 +2596,18 @@ /* ** Flags passed to the sqlite3ExprDup() function. See the header comment ** above sqlite3ExprDup() for details. */ #define EXPRDUP_REDUCE 0x0001 /* Used reduced-size Expr nodes */ + +/* +** True if the expression passed as an argument was a function with +** an OVER() clause (a window function). +*/ +#define IsWindowFunc(p) ( \ + ExprHasProperty((p), EP_WinFunc) && p->y.pWin->eFrmType!=TK_FILTER \ +) /* ** A list of expressions. Each expression may optionally have a ** name. An expr/name combination can be used in several ways, such ** as the list of "expr AS ID" fields following a "SELECT" or in the @@ -3574,14 +3582,15 @@ u8 bLine[100]; /* Draw vertical in column i if bLine[i] is true */ }; #endif /* SQLITE_DEBUG */ /* -** This object is used in various ways, all related to window functions +** This object is used in various ways, most (but not all) related to window +** functions. ** ** (1) A single instance of this structure is attached to the -** the Expr.pWin field for each window function in an expression tree. +** the Expr.y.pWin field for each window function in an expression tree. ** This object holds the information contained in the OVER clause, ** plus additional fields used during code generation. ** ** (2) All window functions in a single SELECT form a linked-list ** attached to Select.pWin. The Window.pFunc and Window.pExpr @@ -3588,10 +3597,14 @@ ** fields point back to the expression that is the window function. ** ** (3) The terms of the WINDOW clause of a SELECT are instances of this ** object on a linked list attached to Select.pWinDefn. ** +** (4) For an aggregate function with a FILTER clause, an instance +** of this object is stored in Expr.y.pWin with eFrmType set to +** TK_FILTER. In this case the only field used is Window.pFilter. +** ** The uses (1) and (2) are really the same Window object that just happens ** to be accessible in two different ways. Use case (3) are separate objects. */ struct Window { char *zName; /* Name of window (may be NULL) */ @@ -3603,10 +3616,11 @@ u8 eEnd; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ u8 bImplicitFrame; /* True if frame was implicitly specified */ u8 eExclude; /* TK_NO, TK_CURRENT, TK_TIES, TK_GROUP, or 0 */ Expr *pStart; /* Expression for " PRECEDING" */ Expr *pEnd; /* Expression for " FOLLOWING" */ + Window **ppThis; /* Pointer to this object in Select.pWin list */ Window *pNextWin; /* Next window function belonging to this SELECT */ Expr *pFilter; /* The FILTER expression */ FuncDef *pFunc; /* The function */ int iEphCsr; /* Partition buffer or Peer buffer */ int regAccum; @@ -3622,14 +3636,15 @@ int regEndRowid; }; #ifndef SQLITE_OMIT_WINDOWFUNC void sqlite3WindowDelete(sqlite3*, Window*); +void sqlite3WindowUnlinkFromSelect(Window*); void sqlite3WindowListDelete(sqlite3 *db, Window *p); Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8); void sqlite3WindowAttach(Parse*, Expr*, Window*); -int sqlite3WindowCompare(Parse*, Window*, Window*); +int sqlite3WindowCompare(Parse*, Window*, Window*, int); void sqlite3WindowCodeInit(Parse*, Window*); void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int); int sqlite3WindowRewrite(Parse*, Select*); int sqlite3ExpandSubquery(Parse*, struct SrcList_item*); void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*); Index: src/test1.c ================================================================== --- src/test1.c +++ src/test1.c @@ -7738,10 +7738,15 @@ while( zIn[i]==' ' || zIn[i]=='\t' ){ i++; } if( a==0 ){ int pgsz; rc = sscanf(zIn+i, "| size %d pagesize %d", &n, &pgsz); if( rc!=2 ) continue; + if( pgsz<512 || pgsz>65536 || (pgsz&(pgsz-1))!=0 ){ + Tcl_AppendResult(interp, "bad 'pagesize' field", (void*)0); + return TCL_ERROR; + } + n = (n+pgsz-1)&~(pgsz-1); /* Round n up to the next multiple of pgsz */ if( n<512 ){ Tcl_AppendResult(interp, "bad 'size' field", (void*)0); return TCL_ERROR; } a = malloc( n ); Index: src/trigger.c ================================================================== --- src/trigger.c +++ src/trigger.c @@ -359,10 +359,11 @@ if( db->init.busy ){ Trigger *pLink = pTrig; Hash *pHash = &db->aDb[iDb].pSchema->trigHash; assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); + assert( pLink!=0 ); pTrig = sqlite3HashInsert(pHash, zName, pTrig); if( pTrig ){ sqlite3OomFault(db); }else if( pLink->pSchema==pLink->pTabSchema ){ Table *pTab; Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -2925,38 +2925,124 @@ } } #endif /* Loop through the elements that will make up the record to figure - ** out how much space is required for the new record. + ** out how much space is required for the new record. After this loop, + ** the Mem.uTemp field of each term should hold the serial-type that will + ** be used for that term in the generated record: + ** + ** Mem.uTemp value type + ** --------------- --------------- + ** 0 NULL + ** 1 1-byte signed integer + ** 2 2-byte signed integer + ** 3 3-byte signed integer + ** 4 4-byte signed integer + ** 5 6-byte signed integer + ** 6 8-byte signed integer + ** 7 IEEE float + ** 8 Integer constant 0 + ** 9 Integer constant 1 + ** 10,11 reserved for expansion + ** N>=12 and even BLOB + ** N>=13 and odd text + ** + ** The following additional values are computed: + ** nHdr Number of bytes needed for the record header + ** nData Number of bytes of data space needed for the record + ** nZero Zero bytes at the end of the record */ pRec = pLast; do{ assert( memIsValid(pRec) ); - serial_type = sqlite3VdbeSerialType(pRec, file_format, &len); - if( pRec->flags & MEM_Zero ){ - if( serial_type==0 ){ + if( pRec->flags & MEM_Null ){ + if( pRec->flags & MEM_Zero ){ /* Values with MEM_Null and MEM_Zero are created by xColumn virtual ** table methods that never invoke sqlite3_result_xxxxx() while ** computing an unchanging column value in an UPDATE statement. ** Give such values a special internal-use-only serial-type of 10 ** so that they can be passed through to xUpdate and have ** a true sqlite3_value_nochange(). */ assert( pOp->p5==OPFLAG_NOCHNG_MAGIC || CORRUPT_DB ); - serial_type = 10; - }else if( nData ){ - if( sqlite3VdbeMemExpandBlob(pRec) ) goto no_mem; + pRec->uTemp = 10; + }else{ + pRec->uTemp = 0; + } + nHdr++; + }else if( pRec->flags & (MEM_Int|MEM_IntReal) ){ + /* Figure out whether to use 1, 2, 4, 6 or 8 bytes. */ + i64 i = pRec->u.i; + u64 u; + testcase( pRec->flags & MEM_Int ); + testcase( pRec->flags & MEM_IntReal ); + if( i<0 ){ + u = ~i; + }else{ + u = i; + } + nHdr++; + testcase( u==127 ); testcase( u==128 ); + testcase( u==32767 ); testcase( u==32768 ); + testcase( u==8388607 ); testcase( u==8388608 ); + testcase( u==2147483647 ); testcase( u==2147483648 ); + testcase( u==140737488355327LL ); testcase( u==140737488355328LL ); + if( u<=127 ){ + if( (i&1)==i && file_format>=4 ){ + pRec->uTemp = 8+(u32)u; + }else{ + nData++; + pRec->uTemp = 1; + } + }else if( u<=32767 ){ + nData += 2; + pRec->uTemp = 2; + }else if( u<=8388607 ){ + nData += 3; + pRec->uTemp = 3; + }else if( u<=2147483647 ){ + nData += 4; + pRec->uTemp = 4; + }else if( u<=140737488355327LL ){ + nData += 6; + pRec->uTemp = 5; }else{ - nZero += pRec->u.nZero; - len -= pRec->u.nZero; + nData += 8; + if( pRec->flags & MEM_IntReal ){ + /* If the value is IntReal and is going to take up 8 bytes to store + ** as an integer, then we might as well make it an 8-byte floating + ** point value */ + pRec->u.r = (double)pRec->u.i; + pRec->flags &= ~MEM_IntReal; + pRec->flags |= MEM_Real; + pRec->uTemp = 7; + }else{ + pRec->uTemp = 6; + } } + }else if( pRec->flags & MEM_Real ){ + nHdr++; + nData += 8; + pRec->uTemp = 7; + }else{ + assert( db->mallocFailed || pRec->flags&(MEM_Str|MEM_Blob) ); + assert( pRec->n>=0 ); + len = (u32)pRec->n; + serial_type = (len*2) + 12 + ((pRec->flags & MEM_Str)!=0); + if( pRec->flags & MEM_Zero ){ + serial_type += pRec->u.nZero*2; + if( nData ){ + if( sqlite3VdbeMemExpandBlob(pRec) ) goto no_mem; + len += pRec->u.nZero; + }else{ + nZero += pRec->u.nZero; + } + } + nData += len; + nHdr += sqlite3VarintLen(serial_type); + pRec->uTemp = serial_type; } - nData += len; - testcase( serial_type==127 ); - testcase( serial_type==128 ); - nHdr += serial_type<=127 ? 1 : sqlite3VarintLen(serial_type); - pRec->uTemp = serial_type; if( pRec==pData0 ) break; pRec--; }while(1); /* EVIDENCE-OF: R-22564-11647 The header begins with a single varint @@ -3288,11 +3374,11 @@ "cannot commit - no transaction is active")); rc = SQLITE_ERROR; goto abort_due_to_error; } - break; + /*NOTREACHED*/ assert(0); } /* Opcode: Transaction P1 P2 P3 P4 P5 ** ** Begin a transaction on database P1 if a transaction is not already @@ -4044,12 +4130,12 @@ /* If the P3 value could not be converted into an integer without ** loss of information, then special processing is required... */ if( (pIn3->flags & (MEM_Int|MEM_IntReal))==0 ){ if( (pIn3->flags & MEM_Real)==0 ){ if( (pIn3->flags & MEM_Null) || oc>=OP_SeekGE ){ - VdbeBranchTaken(1,2); goto jump_to_p2; - break; + VdbeBranchTaken(1,2); + goto jump_to_p2; }else{ rc = sqlite3BtreeLast(pC->uc.pCursor, &res); if( rc!=SQLITE_OK ) goto abort_due_to_error; goto seek_not_found; } Index: src/vdbeInt.h ================================================================== --- src/vdbeInt.h +++ src/vdbeInt.h @@ -484,11 +484,13 @@ void sqliteVdbePopStack(Vdbe*,int); int sqlite3VdbeCursorMoveto(VdbeCursor**, int*); int sqlite3VdbeCursorRestore(VdbeCursor*); u32 sqlite3VdbeSerialTypeLen(u32); u8 sqlite3VdbeOneByteSerialTypeLen(u8); +#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 u32 sqlite3VdbeSerialType(Mem*, int, u32*); +#endif u32 sqlite3VdbeSerialPut(unsigned char*, Mem*, u32); u32 sqlite3VdbeSerialGet(const unsigned char*, u32, Mem*); void sqlite3VdbeDeleteAuxData(sqlite3*, AuxData**, int, int); int sqlite2BtreeKeyCompare(BtCursor *, const void *, int, int, int *); Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -1907,12 +1907,15 @@ }else{ /* We are currently listing subprograms. Figure out which one and ** pick up the appropriate opcode. */ int j; i -= p->nOp; + assert( apSub!=0 ); + assert( nSub>0 ); for(j=0; i>=apSub[j]->nOp; j++){ i -= apSub[j]->nOp; + assert( inOp || j+1aOp[i]; } /* When an OP_Program opcode is encounter (the only opcode that has @@ -3430,14 +3433,20 @@ ** ** The 8 and 9 types were added in 3.3.0, file format 4. Prior versions ** of SQLite will not understand those serial types. */ +#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 /* ** Return the serial-type for the value stored in pMem. ** ** This routine might convert a large MEM_IntReal value into MEM_Real. +** +** 2019-07-11: The primary user of this subroutine was the OP_MakeRecord +** opcode in the byte-code engine. But by moving this routine in-line, we +** can omit some redundant tests and make that opcode a lot faster. So +** this routine is now only used by the STAT3/4 logic. */ u32 sqlite3VdbeSerialType(Mem *pMem, int file_format, u32 *pLen){ int flags = pMem->flags; u32 n; @@ -3494,10 +3503,11 @@ n += pMem->u.nZero; } *pLen = n; return ((n*2) + 12 + ((flags&MEM_Str)!=0)); } +#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ /* ** The sizes for serial types less than 128 */ static const u8 sqlite3SmallTypeSizes[] = { @@ -4600,11 +4610,15 @@ return 0; /* Corruption */ } nCmp = MIN( pPKey2->aMem[0].n, nStr ); res = memcmp(&aKey1[szHdr], pPKey2->aMem[0].z, nCmp); - if( res==0 ){ + if( res>0 ){ + res = pPKey2->r2; + }else if( res<0 ){ + res = pPKey2->r1; + }else{ res = nStr - pPKey2->aMem[0].n; if( res==0 ){ if( pPKey2->nField>1 ){ res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1); }else{ @@ -4614,14 +4628,10 @@ }else if( res>0 ){ res = pPKey2->r2; }else{ res = pPKey2->r1; } - }else if( res>0 ){ - res = pPKey2->r2; - }else{ - res = pPKey2->r1; } } assert( vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, res) || CORRUPT_DB Index: src/vdbeblob.c ================================================================== --- src/vdbeblob.c +++ src/vdbeblob.c @@ -359,15 +359,16 @@ Incrblob *p = (Incrblob *)pBlob; int rc; sqlite3 *db; if( p ){ + sqlite3_stmt *pStmt = p->pStmt; db = p->db; sqlite3_mutex_enter(db->mutex); - rc = sqlite3_finalize(p->pStmt); sqlite3DbFree(db, p); sqlite3_mutex_leave(db->mutex); + rc = sqlite3_finalize(pStmt); }else{ rc = SQLITE_OK; } return rc; } Index: src/vdbesort.c ================================================================== --- src/vdbesort.c +++ src/vdbesort.c @@ -1726,17 +1726,20 @@ if( i==nWorker ){ /* Use the foreground thread for this operation */ rc = vdbeSorterListToPMA(&pSorter->aTask[nWorker], &pSorter->list); }else{ /* Launch a background thread for this operation */ - u8 *aMem = pTask->list.aMemory; - void *pCtx = (void*)pTask; + u8 *aMem; + void *pCtx; + assert( pTask!=0 ); assert( pTask->pThread==0 && pTask->bDone==0 ); assert( pTask->list.pList==0 ); assert( pTask->list.aMemory==0 || pSorter->list.aMemory!=0 ); + aMem = pTask->list.aMemory; + pCtx = (void*)pTask; pSorter->iPrev = (u8)(pTask - pSorter->aTask); pTask->list = pSorter->list; pSorter->list.pList = 0; pSorter->list.szPMA = 0; if( aMem ){ Index: src/wal.c ================================================================== --- src/wal.c +++ src/wal.c @@ -3476,10 +3476,11 @@ while( iOffsetpDirty){ if( (p->flags & PGHDR_WAL_APPEND)==0 ) continue; iFrame++; rc = walIndexAppend(pWal, iFrame, p->pgno); } + assert( pLast!=0 || nExtra==0 ); while( rc==SQLITE_OK && nExtra>0 ){ iFrame++; nExtra--; rc = walIndexAppend(pWal, iFrame, pLast->pgno); } Index: src/walker.c ================================================================== --- src/walker.c +++ src/walker.c @@ -23,13 +23,26 @@ ** as the second argument. */ static int walkWindowList(Walker *pWalker, Window *pList){ Window *pWin; for(pWin=pList; pWin; pWin=pWin->pNextWin){ - if( sqlite3WalkExprList(pWalker, pWin->pOrderBy) ) return WRC_Abort; - if( sqlite3WalkExprList(pWalker, pWin->pPartition) ) return WRC_Abort; - if( sqlite3WalkExpr(pWalker, pWin->pFilter) ) return WRC_Abort; + int rc; + rc = sqlite3WalkExprList(pWalker, pWin->pOrderBy); + if( rc ) return WRC_Abort; + rc = sqlite3WalkExprList(pWalker, pWin->pPartition); + if( rc ) return WRC_Abort; + rc = sqlite3WalkExpr(pWalker, pWin->pFilter); + if( rc ) return WRC_Abort; + + /* The next two are purely for calls to sqlite3RenameExprUnmap() + ** within sqlite3WindowOffsetExpr(). Because of constraints imposed + ** by sqlite3WindowOffsetExpr(), they can never fail. The results do + ** not matter anyhow. */ + rc = sqlite3WalkExpr(pWalker, pWin->pStart); + if( NEVER(rc) ) return WRC_Abort; + rc = sqlite3WalkExpr(pWalker, pWin->pEnd); + if( NEVER(rc) ) return WRC_Abort; } return WRC_Continue; } #endif @@ -61,22 +74,26 @@ if( rc ) return rc & WRC_Abort; if( !ExprHasProperty(pExpr,(EP_TokenOnly|EP_Leaf)) ){ if( pExpr->pLeft && walkExpr(pWalker, pExpr->pLeft) ) return WRC_Abort; assert( pExpr->x.pList==0 || pExpr->pRight==0 ); if( pExpr->pRight ){ + assert( !ExprHasProperty(pExpr, EP_WinFunc) ); pExpr = pExpr->pRight; continue; }else if( ExprHasProperty(pExpr, EP_xIsSelect) ){ + assert( !ExprHasProperty(pExpr, EP_WinFunc) ); if( sqlite3WalkSelect(pWalker, pExpr->x.pSelect) ) return WRC_Abort; - }else if( pExpr->x.pList ){ - if( sqlite3WalkExprList(pWalker, pExpr->x.pList) ) return WRC_Abort; - } + }else{ + if( pExpr->x.pList ){ + if( sqlite3WalkExprList(pWalker, pExpr->x.pList) ) return WRC_Abort; + } #ifndef SQLITE_OMIT_WINDOWFUNC - if( ExprHasProperty(pExpr, EP_WinFunc) ){ - if( walkWindowList(pWalker, pExpr->y.pWin) ) return WRC_Abort; - } + if( ExprHasProperty(pExpr, EP_WinFunc) ){ + if( walkWindowList(pWalker, pExpr->y.pWin) ) return WRC_Abort; + } #endif + } } break; } return WRC_Continue; } @@ -114,12 +131,13 @@ if( sqlite3WalkExpr(pWalker, p->pLimit) ) return WRC_Abort; #if !defined(SQLITE_OMIT_WINDOWFUNC) && !defined(SQLITE_OMIT_ALTERTABLE) { Parse *pParse = pWalker->pParse; if( pParse && IN_RENAME_OBJECT ){ + /* The following may return WRC_Abort if there are unresolvable + ** symbols (e.g. a table that does not exist) in a window definition. */ int rc = walkWindowList(pWalker, p->pWinDefn); - assert( rc==WRC_Continue ); return rc; } } #endif return WRC_Continue; Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -2268,10 +2268,11 @@ int i, j, k; LogEst iReduce = 0; /* pLoop->nOut should not exceed nRow-iReduce */ assert( (pLoop->wsFlags & WHERE_AUTO_INDEX)==0 ); for(i=pWC->nTerm, pTerm=pWC->a; i>0; i--, pTerm++){ + assert( pTerm!=0 ); if( (pTerm->wtFlags & TERM_VIRTUAL)!=0 ) break; if( (pTerm->prereqAll & pLoop->maskSelf)==0 ) continue; if( (pTerm->prereqAll & notAllowed)!=0 ) continue; for(j=pLoop->nLTerm-1; j>=0; j--){ pX = pLoop->aLTerm[j]; Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -746,10 +746,12 @@ ** expression (*ppExpr) in place. */ static int selectWindowRewriteExprCb(Walker *pWalker, Expr *pExpr){ struct WindowRewrite *p = pWalker->u.pRewrite; Parse *pParse = pWalker->pParse; + assert( p!=0 ); + assert( p->pWin!=0 ); /* If this function is being called from within a scalar sub-select ** that used by the SELECT statement being processed, only process ** TK_COLUMN expressions that refer to it (the outer SELECT). Do ** not process aggregates or window functions at all, as they belong @@ -845,10 +847,11 @@ ExprList **ppSub /* IN/OUT: Sub-select expression-list */ ){ Walker sWalker; WindowRewrite sRewrite; + assert( pWin!=0 ); memset(&sWalker, 0, sizeof(Walker)); memset(&sRewrite, 0, sizeof(WindowRewrite)); sRewrite.pSub = *ppSub; sRewrite.pWin = pWin; @@ -1011,16 +1014,29 @@ sqlite3DbFree(db, pTab); } return rc; } + +/* +** Unlink the Window object from the Select to which it is attached, +** if it is attached. +*/ +void sqlite3WindowUnlinkFromSelect(Window *p){ + if( p->ppThis ){ + *p->ppThis = p->pNextWin; + if( p->pNextWin ) p->pNextWin->ppThis = p->ppThis; + p->ppThis = 0; + } +} /* ** Free the Window object passed as the second argument. */ void sqlite3WindowDelete(sqlite3 *db, Window *p){ if( p ){ + sqlite3WindowUnlinkFromSelect(p); sqlite3ExprDelete(db, p->pFilter); sqlite3ExprListDelete(db, p->pPartition); sqlite3ExprListDelete(db, p->pOrderBy); sqlite3ExprDelete(db, p->pEnd); sqlite3ExprDelete(db, p->pStart); @@ -1194,21 +1210,18 @@ ** Attach window object pWin to expression p. */ void sqlite3WindowAttach(Parse *pParse, Expr *p, Window *pWin){ if( p ){ assert( p->op==TK_FUNCTION ); - /* This routine is only called for the parser. If pWin was not - ** allocated due to an OOM, then the parser would fail before ever - ** invoking this routine */ - if( ALWAYS(pWin) ){ - p->y.pWin = pWin; - ExprSetProperty(p, EP_WinFunc); - pWin->pOwner = p; - if( p->flags & EP_Distinct ){ - sqlite3ErrorMsg(pParse, - "DISTINCT is not supported for window functions"); - } + assert( pWin ); + p->y.pWin = pWin; + ExprSetProperty(p, EP_WinFunc); + pWin->pOwner = p; + if( (p->flags & EP_Distinct) && pWin->eFrmType!=TK_FILTER ){ + sqlite3ErrorMsg(pParse, + "DISTINCT is not supported for window functions" + ); } }else{ sqlite3WindowDelete(pParse->db, pWin); } } @@ -1215,19 +1228,22 @@ /* ** Return 0 if the two window objects are identical, or non-zero otherwise. ** Identical window objects can be processed in a single scan. */ -int sqlite3WindowCompare(Parse *pParse, Window *p1, Window *p2){ +int sqlite3WindowCompare(Parse *pParse, Window *p1, Window *p2, int bFilter){ if( p1->eFrmType!=p2->eFrmType ) return 1; if( p1->eStart!=p2->eStart ) return 1; if( p1->eEnd!=p2->eEnd ) return 1; if( p1->eExclude!=p2->eExclude ) return 1; if( sqlite3ExprCompare(pParse, p1->pStart, p2->pStart, -1) ) return 1; if( sqlite3ExprCompare(pParse, p1->pEnd, p2->pEnd, -1) ) return 1; if( sqlite3ExprListCompare(p1->pPartition, p2->pPartition, -1) ) return 1; if( sqlite3ExprListCompare(p1->pOrderBy, p2->pOrderBy, -1) ) return 1; + if( bFilter ){ + if( sqlite3ExprCompare(pParse, p1->pFilter, p2->pFilter, -1) ) return 1; + } return 0; } /* @@ -1565,12 +1581,14 @@ int nPeer; int lblNext; int lblBrk; int addrNext; - int csr = pMWin->csrApp; + int csr; + assert( pMWin!=0 ); + csr = pMWin->csrApp; nPeer = (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0); lblNext = sqlite3VdbeMakeLabel(pParse); lblBrk = sqlite3VdbeMakeLabel(pParse); @@ -2015,10 +2033,11 @@ pNew->pOrderBy = sqlite3ExprListDup(db, p->pOrderBy, 0); pNew->eFrmType = p->eFrmType; pNew->eEnd = p->eEnd; pNew->eStart = p->eStart; pNew->eExclude = p->eExclude; + pNew->regResult = p->regResult; pNew->pStart = sqlite3ExprDup(db, p->pStart, 0); pNew->pEnd = sqlite3ExprDup(db, p->pEnd, 0); pNew->pOwner = pOwner; } } Index: test/altertab3.test ================================================================== --- test/altertab3.test +++ test/altertab3.test @@ -17,10 +17,11 @@ # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. ifcapable !altertable { finish_test return } + ifcapable windowfunc { do_execsql_test 1.0 { CREATE TABLE t1(a, b); CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN @@ -235,7 +236,114 @@ {CREATE VIEW v1 AS SELECT * FROM t1 WHERE ( SELECT t1.a FROM t1, t2 ) IN () OR t1.a=5} } +#------------------------------------------------------------------------- +reset_db +do_execsql_test 11.1 { + CREATE TABLE t1( + a,b,c,d,e,f,g,h,j,jj,jjb,k,aa,bb,cc,dd,ee DEFAULT 3.14, + ff DEFAULT('hiccup'),Wg NOD NULL DEFAULT(false) + ); + + CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN + SELECT a, sum() w3 FROM t1 + WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM abc)); + END; +} + +do_catchsql_test 11.2 { + ALTER TABLE t1 RENAME TO t1x; +} {1 {error in trigger b: no such table: abc}} + +do_execsql_test 11.3 { + DROP TRIGGER b; + CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN + SELECT a, sum() w3 FROM t1 + WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM t1)); + END; +} {} + +do_execsql_test 11.4 { + ALTER TABLE t1 RENAME TO t1x; + SELECT sql FROM sqlite_master WHERE name = 'b'; +} { +{CREATE TRIGGER b AFTER INSERT ON "t1x" WHEN new.a BEGIN + SELECT a, sum() w3 FROM "t1x" + WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM "t1x")); + END} +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 12.1 { +CREATE TABLE t1(a,b,c,d,e,f,g,h,j,jj,Zjj,k,aQ,bb,cc,dd,ee DEFAULT 3.14, +ff DEFAULT('hiccup'),gg NOD NULL DEFAULT(false)); +CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN + +SELECT b () OVER , dense_rank() OVER d, d () OVER w1 +FROM t1 +WINDOW +w1 AS +( w1 ORDER BY d +ROWS BETWEEN 2 NOT IN(SELECT a, sum(d) w2,max(d)OVER FROM t1 +WINDOW +w1 AS +(PARTITION BY d +ROWS BETWEEN '' PRECEDING AND false FOLLOWING), +d AS +(PARTITION BY b ORDER BY d +ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) +) PRECEDING AND 1 FOLLOWING), +w2 AS +(PARTITION BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), +w3 AS +(PARTITION BY b ORDER BY d +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) +; +SELECT a, sum(d) w2,max(d)OVER FROM t1 +WINDOW +w1 AS +(PARTITION BY d +ROWS BETWEEN '' PRECEDING AND false FOLLOWING), +d AS +(PARTITION BY b ORDER BY d +ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) +; + +END; +} + +do_execsql_test 12.2 { + ALTER TABLE t1 RENAME TO t1x; +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 13.1 { + CREATE TABLE t1(a); + CREATE TRIGGER r1 INSERT ON t1 BEGIN + SELECT a(*) OVER (ORDER BY (SELECT 1)) FROM t1; + END; +} + +do_execsql_test 13.2 { + ALTER TABLE t1 RENAME TO t1x; +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 14.1 { + CREATE TABLE t1(a); + CREATE TABLE t2(b); + CREATE TRIGGER AFTER INSERT ON t1 BEGIN + SELECT sum() FILTER (WHERE (SELECT sum() FILTER (WHERE 0)) AND a); + END; +} + +do_catchsql_test 14.2 { + ALTER TABLE t1 RENAME TO t1x; +} {1 {error in trigger AFTER: no such column: a}} finish_test + Index: test/check.test ================================================================== --- test/check.test +++ test/check.test @@ -492,7 +492,37 @@ do_execsql_test 10.1 { CREATE TABLE t1(x); CREATE VIEW v1(y) AS SELECT x FROM t1; PRAGMA integrity_check; } {ok} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 11.0 { + CREATE TABLE t1 (Col0 CHECK(1 COLLATE BINARY BETWEEN 1 AND 1) ) ; +} +do_execsql_test 11.1 { + INSERT INTO t1 VALUES (NULL); +} +do_execsql_test 11.2 { + INSERT INTO t1 VALUES (NULL); +} + +do_execsql_test 11.3 { + CREATE TABLE t2(b, a CHECK( + CASE 'abc' COLLATE nocase WHEN a THEN 1 ELSE 0 END) + ); +} +do_execsql_test 11.4 { + INSERT INTO t2(a) VALUES('abc'); +} +do_execsql_test 11.5 { + INSERT INTO t2(b, a) VALUES(1, 'abc'||''); +} +do_execsql_test 11.6 { + INSERT INTO t2(b, a) VALUES(2, 'abc'); +} + +finish_test + finish_test ADDED test/checkfault.test Index: test/checkfault.test ================================================================== --- test/checkfault.test +++ test/checkfault.test @@ -0,0 +1,41 @@ +# 2019 July 17 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# This file contains fault-injection test cases for the +# sqlite3_db_cacheflush API. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix cffault +source $testdir/malloc_common.tcl + +do_execsql_test 1.0 { + CREATE TABLE t1 (Col0 CHECK(1 COLLATE BINARY BETWEEN 1 AND 1) ) ; + CREATE TABLE t2(b, a CHECK( + CASE 'abc' COLLATE nocase WHEN a THEN 1 ELSE 0 END) + ); +} + +do_faultsim_test 1.1 -faults oom* -body { + execsql { INSERT INTO t1 VALUES ('ABCDEFG') } +} -test { + faultsim_test_result {0 {}} +} + +do_faultsim_test 1.2 -faults oom* -body { + execsql { INSERT INTO t2(a) VALUES('abc') } +} -test { + faultsim_test_result {0 {}} +} + + +finish_test Index: test/close.test ================================================================== --- test/close.test +++ test/close.test @@ -76,7 +76,14 @@ } {1 {(21) bad parameter or other API misuse}} do_test 1.4.4 { sqlite3_finalize $STMT } {SQLITE_OK} + +do_test 1.5 { + set DB [sqlite3_open test.db] + sqlite3_blob_open $DB main t1 x 2 0 BLOB + sqlite3_close_v2 $DB + sqlite3_blob_close $BLOB +} {} finish_test ADDED test/filter1.test Index: test/filter1.test ================================================================== --- test/filter1.test +++ test/filter1.test @@ -0,0 +1,106 @@ +# 2018 May 8 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix filter1 + +ifcapable !windowfunc { + finish_test + return +} + +do_execsql_test 1.0 { + CREATE TABLE t1(a); + CREATE INDEX i1 ON t1(a); + INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9); +} + +do_execsql_test 1.1 { SELECT sum(a) FROM t1; } 45 +do_execsql_test 1.2 { SELECT sum(a) FILTER( WHERE a<5 ) FROM t1; } 10 + +do_execsql_test 1.3 { + SELECT sum(a) FILTER( WHERE a>9 ), + sum(a) FILTER( WHERE a>8 ), + sum(a) FILTER( WHERE a>7 ), + sum(a) FILTER( WHERE a>6 ), + sum(a) FILTER( WHERE a>5 ), + sum(a) FILTER( WHERE a>4 ), + sum(a) FILTER( WHERE a>3 ), + sum(a) FILTER( WHERE a>2 ), + sum(a) FILTER( WHERE a>1 ), + sum(a) FILTER( WHERE a>0 ) + FROM t1; +} {{} 9 17 24 30 35 39 42 44 45} + +do_execsql_test 1.4 { + SELECT max(a) FILTER (WHERE (a % 2)==0) FROM t1 +} {8} + +do_execsql_test 1.5 { + SELECT min(a) FILTER (WHERE a>4) FROM t1 +} {5} + +do_execsql_test 1.6 { + SELECT count(*) FILTER (WHERE a!=5) FROM t1 +} {8} + +do_execsql_test 1.7 { + SELECT min(a) FILTER (WHERE a>3) FROM t1 GROUP BY (a%2) ORDER BY 1; +} {4 5} + +do_execsql_test 1.8 { + CREATE VIEW vv AS + SELECT sum(a) FILTER( WHERE a>9 ), + sum(a) FILTER( WHERE a>8 ), + sum(a) FILTER( WHERE a>7 ), + sum(a) FILTER( WHERE a>6 ), + sum(a) FILTER( WHERE a>5 ), + sum(a) FILTER( WHERE a>4 ), + sum(a) FILTER( WHERE a>3 ), + sum(a) FILTER( WHERE a>2 ), + sum(a) FILTER( WHERE a>1 ), + sum(a) FILTER( WHERE a>0 ) + FROM t1; + SELECT * FROM vv; +} {{} 9 17 24 30 35 39 42 44 45} + + +#------------------------------------------------------------------------- +# Test some errors: +# +# .1 FILTER on a non-aggregate function, +# .2 Window function in FILTER clause, +# .3 Aggregate function in FILTER clause, +# +reset_db +do_execsql_test 2.0 { + CREATE TABLE t1(a); + INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9); +} + +do_catchsql_test 2.1 { + SELECT upper(a) FILTER (WHERE a=1) FROM t1 +} {1 {FILTER may not be used with non-aggregate upper()}} + +do_catchsql_test 2.2 { + SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1 +} {1 {misuse of window function max()}} + +do_catchsql_test 2.3 { + SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1 +} {1 {misuse of aggregate function count()}} + +finish_test + + ADDED test/filter2.tcl Index: test/filter2.tcl ================================================================== --- test/filter2.tcl +++ test/filter2.tcl @@ -0,0 +1,132 @@ +# 2018 May 19 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +source [file join [file dirname $argv0] pg_common.tcl] + +#========================================================================= + + +start_test filter2 "2019 July 2" + +ifcapable !windowfunc + +execsql_test 1.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); + INSERT INTO t1 VALUES + (1, 7), (2, 3), (3, 5), (4, 30), (5, 26), (6, 23), (7, 27), + (8, 3), (9, 17), (10, 26), (11, 33), (12, 25), (13, NULL), (14, 47), + (15, 36), (16, 13), (17, 45), (18, 31), (19, 11), (20, 36), (21, 37), + (22, 21), (23, 22), (24, 14), (25, 16), (26, 3), (27, 7), (28, 29), + (29, 50), (30, 38), (31, 3), (32, 36), (33, 12), (34, 4), (35, 46), + (36, 3), (37, 48), (38, 23), (39, NULL), (40, 24), (41, 5), (42, 46), + (43, 11), (44, NULL), (45, 18), (46, 25), (47, 15), (48, 18), (49, 23); +} + +execsql_test 1.1 { SELECT sum(b) FROM t1 } + +execsql_test 1.2 { SELECT sum(b) FILTER (WHERE a<10) FROM t1 } + +execsql_test 1.3 { SELECT count(DISTINCT b) FROM t1 } + +execsql_test 1.4 { SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1 } + +execsql_test 1.5 { + SELECT min(b) FILTER (WHERE a>19), + min(b) FILTER (WHERE a>0), + max(a+b) FILTER (WHERE a>19), + max(b+a) FILTER (WHERE a BETWEEN 10 AND 40) + FROM t1; +} + +execsql_test 1.6 { + SELECT min(b), + min(b), + max(a+b), + max(b+a) + FROM t1 + GROUP BY (a%10) + ORDER BY 1, 2, 3, 4; +} + +execsql_test 1.7 { + SELECT min(b) FILTER (WHERE a>19), + min(b) FILTER (WHERE a>0), + max(a+b) FILTER (WHERE a>19), + max(b+a) FILTER (WHERE a BETWEEN 10 AND 40) + FROM t1 + GROUP BY (a%10) + ORDER BY 1, 2, 3, 4; +} + +execsql_test 1.8 { + SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1 +} + +execsql_test 1.9 { + SELECT (a%5) FROM t1 GROUP BY (a%5) + HAVING sum(b) FILTER (WHERE b<20) > 34 + ORDER BY 1 +} + +execsql_test 1.10 { + SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb + FROM t1 + GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34 + ORDER BY 1 +} + +execsql_test 1.11 { + SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb + FROM t1 + GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34 + ORDER BY 2 +} + +execsql_test 1.12 { + SELECT (a%5), + sum(b) FILTER (WHERE b<20) AS bbb, + count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc + FROM t1 GROUP BY (a%5) + ORDER BY 2 +} + +execsql_test 1.13 { + SELECT + string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0), + string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1), + count(*) FILTER (WHERE b%2!=0), + count(*) FILTER (WHERE b%2!=1) + FROM t1; +} + +execsql_float_test 1.14 { + SELECT + avg(b) FILTER (WHERE b>a), + avg(b) FILTER (WHERE b19), + min(b) FILTER (WHERE a>0), + max(a+b) FILTER (WHERE a>19), + max(b+a) FILTER (WHERE a BETWEEN 10 AND 40) + FROM t1; +} {3 3 88 85} + +do_execsql_test 1.6 { + SELECT min(b), + min(b), + max(a+b), + max(b+a) + FROM t1 + GROUP BY (a%10) + ORDER BY 1, 2, 3, 4; +} {3 3 58 58 3 3 66 66 3 3 71 71 3 3 88 88 4 4 61 61 5 5 54 54 + 7 7 85 85 11 11 79 79 16 16 81 81 24 24 68 68} + +do_execsql_test 1.7 { + SELECT min(b) FILTER (WHERE a>19), + min(b) FILTER (WHERE a>0), + max(a+b) FILTER (WHERE a>19), + max(b+a) FILTER (WHERE a BETWEEN 10 AND 40) + FROM t1 + GROUP BY (a%10) + ORDER BY 1, 2, 3, 4; +} {3 3 58 58 3 3 71 39 4 4 38 61 7 7 85 85 11 5 54 45 16 16 81 81 + 18 3 66 61 21 3 88 68 23 11 79 79 24 24 68 68} + +do_execsql_test 1.8 { + SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1 +} {{}} + +do_execsql_test 1.9 { + SELECT (a%5) FROM t1 GROUP BY (a%5) + HAVING sum(b) FILTER (WHERE b<20) > 34 + ORDER BY 1 +} {3 4} + +do_execsql_test 1.10 { + SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb + FROM t1 + GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34 + ORDER BY 1 +} {3 49 4 46} + +do_execsql_test 1.11 { + SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb + FROM t1 + GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34 + ORDER BY 2 +} {4 46 3 49} + +do_execsql_test 1.12 { + SELECT (a%5), + sum(b) FILTER (WHERE b<20) AS bbb, + count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc + FROM t1 GROUP BY (a%5) + ORDER BY 2 +} {2 25 3 0 34 2 1 34 4 4 46 4 3 49 5} + +do_execsql_test 1.13 { + SELECT + group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0), + group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1), + count(*) FILTER (WHERE b%2!=0), + count(*) FILTER (WHERE b%2!=1) + FROM t1; +} {7_3_5_23_27_3_17_33_25_47_13_45_31_11_37_21_3_7_29_3_3_23_5_11_25_15_23 30_26_26_36_36_22_14_16_50_38_36_12_4_46_48_24_46_18_18 27 19} + + +do_test 1.14 { + set myres {} + foreach r [db eval {SELECT + avg(b) FILTER (WHERE b>a), + avg(b) FILTER (WHERE b([set r2]+0.0001)} { + error "list element [set i] does not match: got=[set r] expected=[set r2]" + } + incr i + } + set {} {} +} {} + +do_execsql_test 1.15 { + SELECT + a/5, + sum(b) FILTER (WHERE a%5=0), + sum(b) FILTER (WHERE a%5=1), + sum(b) FILTER (WHERE a%5=2), + sum(b) FILTER (WHERE a%5=3), + sum(b) FILTER (WHERE a%5=4) + FROM t1 GROUP BY (a/5) ORDER BY 1; +} {0 {} 7 3 5 30 1 26 23 27 3 17 2 26 33 25 {} 47 3 36 13 45 31 11 + 4 36 37 21 22 14 5 16 3 7 29 50 6 38 3 36 12 4 7 46 3 48 23 {} + 8 24 5 46 11 {} 9 18 25 15 18 23} + +finish_test ADDED test/filterfault.test Index: test/filterfault.test ================================================================== --- test/filterfault.test +++ test/filterfault.test @@ -0,0 +1,44 @@ +# 2018 May 8 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix filterfault + +ifcapable !windowfunc { + finish_test + return +} + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b, c, d); + INSERT INTO t1 VALUES(1, 2, 3, 4); + INSERT INTO t1 VALUES(5, 6, 7, 8); + INSERT INTO t1 VALUES(9, 10, 11, 12); +} +faultsim_save_and_close + +do_faultsim_test 1 -faults oom-t* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + SELECT sum(a) FILTER (WHERE b<5), + count() FILTER (WHERE d!=c) + FROM t1 GROUP BY c ORDER BY 1; + } +} -test { + faultsim_test_result {0 {{} 1 {} 1 1 1}} +} + + +finish_test Index: test/fuzzdata7.db ================================================================== --- test/fuzzdata7.db +++ test/fuzzdata7.db cannot compute difference between binary files Index: test/fuzzdata8.db ================================================================== --- test/fuzzdata8.db +++ test/fuzzdata8.db cannot compute difference between binary files Index: test/select1.test ================================================================== --- test/select1.test +++ test/select1.test @@ -1097,7 +1097,72 @@ } {1 2 3} do_execsql_test select1-17.3 { SELECT * FROM t1,(SELECT * FROM t2 WHERE y=2 UNION ALL SELECT * FROM t2 WHERE y=3 ORDER BY y,z LIMIT 4); } {1 2 3} + +# 2019-07-24 Ticket https://sqlite.org/src/tktview/c52b09c7f38903b1311 +# +do_execsql_test select1-18.1 { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(c); + CREATE TABLE t2(x PRIMARY KEY, y); + INSERT INTO t1(c) VALUES(123); + INSERT INTO t2(x) VALUES(123); + SELECT x FROM t2, t1 WHERE x BETWEEN c AND null OR x AND + x IN ((SELECT x FROM (SELECT x FROM t2, t1 + WHERE x BETWEEN (SELECT x FROM (SELECT x COLLATE rtrim + FROM t2, t1 WHERE x BETWEEN c AND null + OR x AND x IN (c)), t1 WHERE x BETWEEN c AND null + OR x AND x IN (c)) AND null + OR NOT EXISTS(SELECT -4.81 FROM t1, t2 WHERE x BETWEEN c AND null + OR x AND x IN ((SELECT x FROM (SELECT x FROM t2, t1 + WHERE x BETWEEN (SELECT x FROM (SELECT x BETWEEN c AND null + OR x AND x IN (c)), t1 WHERE x BETWEEN c AND null + OR x AND x IN (c)) AND null + OR x AND x IN (c)), t1 WHERE x BETWEEN c AND null + OR x AND x IN (c)))) AND x IN (c) + ), t1 WHERE x BETWEEN c AND null + OR x AND x IN (c))); +} {} +do_execsql_test select1-18.2 { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(c); + CREATE TABLE t2(x PRIMARY KEY, y); + INSERT INTO t1(c) VALUES(123); + INSERT INTO t2(x) VALUES(123); + SELECT x FROM t2, t1 WHERE x BETWEEN c AND (c+1) OR x AND + x IN ((SELECT x FROM (SELECT x FROM t2, t1 + WHERE x BETWEEN (SELECT x FROM (SELECT x COLLATE rtrim + FROM t2, t1 WHERE x BETWEEN c AND (c+1) + OR x AND x IN (c)), t1 WHERE x BETWEEN c AND (c+1) + OR x AND x IN (c)) AND (c+1) + OR NOT EXISTS(SELECT -4.81 FROM t1, t2 WHERE x BETWEEN c AND (c+1) + OR x AND x IN ((SELECT x FROM (SELECT x FROM t2, t1 + WHERE x BETWEEN (SELECT x FROM (SELECT x BETWEEN c AND (c+1) + OR x AND x IN (c)), t1 WHERE x BETWEEN c AND (c+1) + OR x AND x IN (c)) AND (c+1) + OR x AND x IN (c)), t1 WHERE x BETWEEN c AND (c+1) + OR x AND x IN (c)))) AND x IN (c) + ), t1 WHERE x BETWEEN c AND (c+1) + OR x AND x IN (c))); +} {123} +do_execsql_test select1-18.3 { + SELECT 1 FROM t1 WHERE ( + SELECT 2 FROM t2 WHERE ( + SELECT 3 FROM ( + SELECT x FROM t2 WHERE x=c OR x=(SELECT x FROM (VALUES(0))) + ) WHERE x>c OR x=c + ) + ); +} {1} +do_execsql_test select1-18.4 { + SELECT 1 FROM t1, t2 WHERE ( + SELECT 3 FROM ( + SELECT x FROM t2 WHERE x=c OR x=(SELECT x FROM (VALUES(0))) + ) WHERE x>c OR x=c + ); +} {1} finish_test Index: test/vtab1.test ================================================================== --- test/vtab1.test +++ test/vtab1.test @@ -872,10 +872,18 @@ do_test vtab1.7-13 { execsql { SELECT rowid, a, b, c FROM real_abc } } {} + +# PRAGMA index_info and index_xinfo are no-ops on a virtual table +do_test vtab1.7-14 { + execsql { + PRAGMA index_info('echo_abc'); + PRAGMA index_xinfo('echo_abc'); + } +} {} ifcapable attach { do_test vtab1.8-1 { set echo_module "" execsql { Index: test/window1.test ================================================================== --- test/window1.test +++ test/window1.test @@ -255,10 +255,13 @@ SELECT trim(x) OVER (ORDER BY y) FROM t1; } {1 {trim() may not be used as a window function}} do_catchsql_test 7.1.7 { SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y); } {1 {no such window: abc}} +do_catchsql_test 7.1.8 { + SELECT row_number(x) OVER () FROM t1 +} {1 {wrong number of arguments to function row_number()}} do_execsql_test 7.2 { SELECT lead(y) OVER win, lead(y, 2) OVER win, @@ -1165,8 +1168,26 @@ 4 D cc 8.25 {} | 12 L cc 'xyZ' L | 11 K cc 'xyz' K | } -finish_test +# 2019-07-18 +# Check-in [7ef7b23cbb1b9ace] (which was itself a fix for ticket +# https://www.sqlite.org/src/info/1be72aab9) introduced a new problem +# if the LHS of a BETWEEN operator is a WINDOW function. The problem +# was found by (the recently enhanced) dbsqlfuzz. +# +do_execsql_test 30.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a, b, c); + INSERT INTO t1 VALUES('BB','aa',399); + SELECT + count () OVER win1 NOT BETWEEN 'a' AND 'mmm', + count () OVER win3 + FROM t1 + WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING + EXCLUDE CURRENT ROW), + win2 AS (PARTITION BY b ORDER BY a), + win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING ); +} {1 1} - +finish_test Index: test/window2.tcl ================================================================== --- test/window2.tcl +++ test/window2.tcl @@ -422,10 +422,17 @@ rank() OVER win AS rank, cume_dist() OVER win AS cume_dist FROM t1 WINDOW win AS (ORDER BY 1); } +execsql_test 4.10 { + SELECT count(*) OVER (ORDER BY b) FROM t1 +} + +execsql_test 4.11 { + SELECT count(distinct a) FILTER (WHERE b='odd') FROM t1 +} finish_test Index: test/window2.test ================================================================== --- test/window2.test +++ test/window2.test @@ -890,7 +890,15 @@ } incr i } set {} {} } {} + +do_execsql_test 4.10 { + SELECT count(*) OVER (ORDER BY b) FROM t1 +} {3 3 3 6 6 6} + +do_execsql_test 4.11 { + SELECT count(distinct a) FILTER (WHERE b='odd') FROM t1 +} {3} finish_test Index: test/window9.test ================================================================== --- test/window9.test +++ test/window9.test @@ -95,8 +95,42 @@ SELECT b=='2' FROM t1 } {1 0} do_execsql_test 2.2.2 { SELECT b=='2', rank() OVER (ORDER BY a) FROM t1 } {1 1 0 2} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 3.0 { + CREATE TABLE t1(a); + CREATE TABLE t2(a,b,c); +} + +do_execsql_test 3.1 { + SELECT EXISTS(SELECT 1 FROM t1 ORDER BY sum(a) OVER ()) FROM t1; +} + +do_execsql_test 3.2 { + SELECT sum(a) OVER () FROM t2 + ORDER BY EXISTS(SELECT 1 FROM t2 ORDER BY sum(a) OVER ()); +} + +do_catchsql_test 3.3 { + SELECT a, sum(a) OVER (ORDER BY a DESC) FROM t2 + ORDER BY EXISTS( + SELECT 1 FROM t2 ORDER BY sum(a) OVER (ORDER BY a) + ) OVER (ORDER BY a); +} {1 {near "OVER": syntax error}} + +do_catchsql_test 3.4 { + SELECT y, y+1, y+2 FROM ( + SELECT c IN ( + SELECT min(a) OVER (), + (abs(row_number() OVER())+22)/19, + max(a) OVER () FROM t1 + ) AS y FROM t2 + ); +} {1 {sub-select returns 3 columns - expected 1}} + finish_test Index: test/windowerr.tcl ================================================================== --- test/windowerr.tcl +++ test/windowerr.tcl @@ -62,8 +62,11 @@ errorsql_test 3.2 { SELECT sum(a) OVER win FROM t1 WINDOW win AS (ROWS BETWEEN 10 PRECEDING AND x'ABCD' FOLLOWING) } +errorsql_test 3.3 { + SELECT row_number(a) OVER () FROM t1; +} finish_test Index: test/windowerr.test ================================================================== --- test/windowerr.test +++ test/windowerr.test @@ -105,7 +105,12 @@ # PG says ERROR: argument of ROWS must be type bigint, not type bit do_test 3.2 { catch { execsql { SELECT sum(a) OVER win FROM t1 WINDOW win AS (ROWS BETWEEN 10 PRECEDING AND x'ABCD' FOLLOWING) } } } 1 + +# PG says ERROR: function row_number(integer) does not exist +do_test 3.3 { catch { execsql { + SELECT row_number(a) OVER () FROM t1; +} } } 1 finish_test Index: test/without_rowid1.test ================================================================== --- test/without_rowid1.test +++ test/without_rowid1.test @@ -28,10 +28,14 @@ INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena'); SELECT *, '|' FROM t1 ORDER BY c, a; } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |} integrity_check without_rowid1-1.0ic + +do_execsql_test without_rowid1-1.0ixi { + SELECT name, key FROM pragma_index_xinfo('t1'); +} {c 1 a 1 b 0 d 0} do_execsql_test without_rowid1-1.1 { SELECT *, '|' FROM t1 ORDER BY +c, a; } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |} @@ -118,10 +122,13 @@ } {abc def} do_execsql_test 2.1.2 { UPDATE t4 SET a = 'ABC'; SELECT * FROM t4; } {ABC def} +do_execsql_test 2.1.3 { + SELECT name, coll, key FROM pragma_index_xinfo('t4'); +} {a nocase 1 b BINARY 0} do_execsql_test 2.2.1 { DROP TABLE t4; CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID; INSERT INTO t4(a, b) VALUES('abc', 'def'); @@ -130,16 +137,26 @@ do_execsql_test 2.2.2 { UPDATE t4 SET a = 'ABC', b = 'xyz'; SELECT * FROM t4; } {xyz ABC} + +do_execsql_test 2.2.3 { + SELECT name, coll, key FROM pragma_index_xinfo('t4'); +} {a nocase 1 b BINARY 0} + do_execsql_test 2.3.1 { CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID; INSERT INTO t5(a, b) VALUES('abc', 'def'); UPDATE t5 SET a='abc', b='def'; } {} + +do_execsql_test 2.3.2 { + SELECT name, coll, key FROM pragma_index_xinfo('t5'); +} {b BINARY 1 a BINARY 1} + do_execsql_test 2.4.1 { CREATE TABLE t6 ( a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a) ) WITHOUT ROWID; @@ -150,10 +167,15 @@ do_execsql_test 2.4.2 { SELECT * FROM t6 ORDER BY b, a; SELECT * FROM t6 ORDER BY c; } {ABC def ghi ABC def ghi} + +do_execsql_test 2.4.3 { + SELECT name, coll, key FROM pragma_index_xinfo('t6'); +} {b BINARY 1 a nocase 1 c BINARY 0} + #------------------------------------------------------------------------- # Unless the destination table is completely empty, the xfer optimization # is disabled for WITHOUT ROWID tables. The following tests check for # some problems that might occur if this were not the case. Index: test/without_rowid6.test ================================================================== --- test/without_rowid6.test +++ test/without_rowid6.test @@ -22,10 +22,13 @@ WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<1000) INSERT INTO t1(a,b,c,d,e) SELECT i, i+1000, printf('x%dy',i), 0, 0 FROM c; ANALYZE; } {} +do_execsql_test without_rowid6-101 { + SELECT name, key FROM pragma_index_xinfo('t1'); +} {a 1 b 1 c 1 d 1 e 0} do_execsql_test without_rowid6-110 { SELECT c FROM t1 WHERE a=123; } {x123y} do_execsql_test without_rowid6-120 { SELECT c FROM t1 WHERE b=1123; @@ -49,10 +52,13 @@ PRIMARY KEY(b) ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9); SELECT a FROM t1 WHERE b>3 ORDER BY b; } {4 1} +do_execsql_test without_rowid6-201 { + SELECT name, key FROM pragma_index_xinfo('t1'); +} {b 1 a 0 c 0} do_execsql_test without_rowid6-210 { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>3 ORDER BY b; } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../} do_execsql_test without_rowid6-220 { @@ -103,10 +109,13 @@ PRIMARY KEY(b,c) ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9); SELECT a FROM t1 WHERE b>3 ORDER BY b; } {4 1} +do_execsql_test without_rowid6-501 { + SELECT name, key FROM pragma_index_xinfo('t1'); +} {b 1 c 1 a 0} do_execsql_test without_rowid6-510 { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>3 ORDER BY b; } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../} do_execsql_test without_rowid6-520 { ADDED test/without_rowid7.test Index: test/without_rowid7.test ================================================================== --- test/without_rowid7.test +++ test/without_rowid7.test @@ -0,0 +1,56 @@ +# 2019 July 17 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#************************************************************************* +# This file implements regression tests for SQLite library. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix without_rowid7 + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b COLLATE nocase, PRIMARY KEY(a, a, b)) WITHOUT ROWID; +} + +do_catchsql_test 1.1 { + INSERT INTO t1 VALUES(1, 'one'), (1, 'ONE'); +} {1 {UNIQUE constraint failed: t1.a, t1.b}} + + +do_execsql_test 2.0 { + CREATE TABLE t2(a, b, PRIMARY KEY(a COLLATE nocase, a)) WITHOUT ROWID; +} + +do_execsql_test 2.1 { + INSERT INTO t2 VALUES(1, 'one'); + SELECT b FROM t2; +} {one} + +do_execsql_test 2.2a { + PRAGMA index_info(t2); +} {0 0 a 1 0 a} +do_execsql_test 2.2b { + SELECT *, '|' FROM pragma_index_info('t2'); +} {0 0 a | 1 0 a |} +do_execsql_test 2.3a { + PRAGMA index_xinfo(t2); +} {0 0 a 0 nocase 1 1 0 a 0 BINARY 1 2 1 b 0 BINARY 0} +do_execsql_test 2.3b { + SELECT *, '|' FROM pragma_index_xinfo('t2'); +} {0 0 a 0 nocase 1 | 1 0 a 0 BINARY 1 | 2 1 b 0 BINARY 0 |} + +do_execsql_test 2.4 { + CREATE TABLE t3(a, b, PRIMARY KEY(a COLLATE nocase, a)); + PRAGMA index_info(t3); +} {} + + + +finish_test Index: tool/lempar.c ================================================================== --- tool/lempar.c +++ tool/lempar.c @@ -565,10 +565,11 @@ } } #endif /* YYWILDCARD */ return yy_default[stateno]; }else{ + assert( i>=0 && i