Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch query-planner-tweaks Excluding Merge-Ins
This is equivalent to a diff from b90c28be38 to d1248165e3
2011-08-08
| ||
17:18 | Add code to actually use the sqlite_stat2.cnt field in the query planner. This changes some plans resulting in a few failures in analyze5.test. (Closed-Leaf check-in: d1248165e3 user: drh tags: query-planner-tweaks) | |
2011-08-07
| ||
01:31 | Remove relevant elements from the sqlite_stat2 table when doing a DROP INDEX or DROP TABLE. (check-in: 3c8f97ae52 user: drh tags: trunk) | |
00:21 | The ANALYZE command adds the sqlite_stat2.cnt column if it does not already exist. (check-in: 794fde6f91 user: drh tags: query-planner-tweaks) | |
2011-08-05
| ||
21:13 | Add a column to the sqlite_stat2 table that contains the number of entries with exactly the same key as the sample. We do not yet do anything with this extra value. Some tests in analyze2.test are failing. (check-in: eb43422827 user: drh tags: query-planner-tweaks) | |
2011-08-03
| ||
22:06 | Merge the winopen-retry-logic branch into trunk. The biggest change here is to test scripts, which should now use such as copy_file and delete_file from tester.tcl rather than the raw file commands of TCL. (check-in: b90c28be38 user: drh tags: trunk) | |
16:40 | Update the OP_Move opcode to shift the pScopyFrom pointer of aliases when compiled with SQLITE_DEBUG. Ticket [d63523637517386191]. (check-in: a2135ad130 user: drh tags: trunk) | |
2011-08-02
| ||
23:45 | Add explanatory comment to the win32lock-2.2 test case. (Closed-Leaf check-in: 4cb17881d9 user: mistachkin tags: winopen-retry-logic) | |
Changes to src/analyze.c.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | /* ** 2005 July 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 contains code associated with the ANALYZE command. ** ** The ANALYZE command gather statistics about the content of tables ** and indices. These statistics are made available to the query planner ** to help it make better decisions about the best way to implement a ** query. ** ** Two system tables are created as follows: ** ** CREATE TABLE sqlite_stat1(tbl, idx, stat); ** CREATE TABLE sqlite_stat2(tbl, idx, sampleno, sample, cnt); ** ** Additional tables might be added in future releases of SQLite. ** The sqlite_stat2 table is only created and used if SQLite is ** compiled with SQLITE_ENABLE_STAT2. Older versions of SQLite ** omit the sqlite_stat2.cnt column. Newer versions of SQLite are ** able to use older versions of the stat2 table that lack the cnt ** column. ** ** Format of sqlite_stat1: ** ** There is normally one row per index, with the index identified by the ** name in the idx column. The tbl column is the name of the table to ** which the index belongs. In each such row, the stat column will be ** a string consisting of a list of integers. The first integer in this ** list is the number of rows in the index and in the table. The second ** integer is the average number of rows in the index that have the same ** value in the first column of the index. The third integer is the average ** number of rows in the index that have the same value for the first two ** columns. The N-th integer (for N>1) is the average number of rows in ** the index which have the same value for the first N-1 columns. For ** a K-column index, there will be K+1 integers in the stat column. If ** the index is unique, then the last integer will be 1. ** ** The list of integers in the stat column can optionally be followed ** by the keyword "unordered". The "unordered" keyword, if it is present, ** must be separated from the last integer by a single space. If the ** "unordered" keyword is present, then the query planner assumes that ** the index is unordered and will not use the index for a range query. ** ** If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat ** column contains a single integer which is the (estimated) number of ** rows in the table identified by sqlite_stat1.tbl. ** ** Format of sqlite_stat2: ** ** The sqlite_stat2 is only created and is only used if SQLite is compiled ** with SQLITE_ENABLE_STAT2. The "stat2" table contains additional information ** about the key distribution within an index. The index is identified by ** the "idx" column and the "tbl" column is the name of the table to which ** the index belongs. There are usually multiple rows in the sqlite_stat2 ** table for each index. ** ** The sqlite_stat2 entires for an index that have sampleno>=0 are ** sampled key values for the first column of the index taken at ** intervals along the index. The sqlite_stat2.sample column holds ** the value of the key in the left-most column of the index. ** ** The samples are numbered from 0 to S-1 ** where S is 10 by default. The number of samples created by the ** ANALYZE command can be adjusted at compile-time using the ** SQLITE_INDEX_SAMPLES macro. The maximum number of samples is ** SQLITE_MAX_SAMPLES, currently set to 100. There are places in the ** code that use an unsigned character to count samples, so an upper ** bound on SQLITE_MAX_SAMPLES is 255. ** ** Suppose the index contains C rows. And let the number ** of samples be S. SQLite assumes that the samples are taken from the ** following rows for i between 0 and S-1: ** ** rownumber = (i*C*2 + C)/(S*2) ** ** Conceptually, the index is divided into S bins and the sample is ** taken from the middle of each bin. The ANALYZE will not attempt ** to populate sqlite_stat2 for an index that holds fewer than S*2 ** entries. ** ** If the key value for a sample (the sqlite_stat2.sample column) is a ** large string or blob, SQLite will only use the first 255 bytes of ** that string or blob. ** ** The sqlite_stat2.cnt column contains the number of entries in the ** index for which sqlite_stat2.sample matches the left-most column ** of the index. In other words, sqlite_stat2.cnt holds the number of ** times the sqlite_stat2.sample value appears in the index.. Many ** older versions of SQLite omit the sqlite_stat2.cnt column. ** ** If the sqlite_stat2.sampleno value is -1, then that row holds a first- ** column key that is a frequently used key in the index. The ** sqlite_stat2.cnt column will hold the number of occurrances of that key. ** This information is useful to the query planner in cases where a ** large percentage of the rows in indexed field have one of a small ** handful of value but the balance of the rows in the index have ** distinct or nearly distinct keys. */ #ifndef SQLITE_OMIT_ANALYZE #include "sqliteInt.h" /* ** This routine generates code that opens the sqlite_stat1 table for ** writing with cursor iStatCur. If the library was built with the |
︙ | |||
39 40 41 42 43 44 45 | 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 | - + | ){ static const struct { const char *zName; const char *zCols; } aTable[] = { { "sqlite_stat1", "tbl,idx,stat" }, #ifdef SQLITE_ENABLE_STAT2 |
︙ | |||
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 | + + + + + + + + + + | sqlite3NestedParse(pParse, "DELETE FROM %Q.%s WHERE %s=%Q", pDb->zName, zTab, zWhereType, zWhere ); }else{ /* The sqlite_stat[12] table already exists. Delete all rows. */ sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb); } #ifdef SQLITE_ENABLE_STAT2 if( i==1 && iDb!=1 && pStat->nCol==4 ){ sqlite3NestedParse(pParse, "UPDATE %Q.sqlite_master SET sql='CREATE TABLE sqlite_stat2(%s)'" " WHERE name='sqlite_stat2'", pDb->zName, aTable[i].zCols ); sqlite3ChangeCookie(pParse, iDb); } #endif } } /* Open the sqlite_stat[12] tables for writing. */ for(i=0; i<ArraySize(aTable); i++){ sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb); sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32); sqlite3VdbeChangeP5(v, aCreateTbl[i]); VdbeComment((v, "%s", aTable[i].zName)); } } /* ** Generate code to do an analysis of all indices associated with ** a single table. */ |
︙ | |||
115 116 117 118 119 120 121 | 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 | - - + + + + + + + + + + + + + + + + - - - - - - - - | int i; /* Loop counter */ int topOfLoop; /* The top of the loop */ int endOfLoop; /* The end of the loop */ int jZeroRows = -1; /* Jump from here if number of rows is zero */ int iDb; /* Index of database containing pTab */ int regTabname = iMem++; /* Register containing table name */ int regIdxname = iMem++; /* Register containing index name */ |
︙ | |||
161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 | 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 | + + + + + - - - - + + + + + + + + + + - - + + + + - - - - - - + + + + + + + - - + + + + + - - - + + + + | sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); iIdxCur = pParse->nTab++; sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ int nCol; KeyInfo *pKey; int addrIfNot; /* address of OP_IfNot */ int *aChngAddr; /* Array of jump instruction addresses */ if( pOnlyIdx && pOnlyIdx!=pIdx ) continue; VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName)); nCol = pIdx->nColumn; pKey = sqlite3IndexKeyinfo(pParse, pIdx); if( iMem+1+(nCol*2)>pParse->nMem ){ pParse->nMem = iMem+1+(nCol*2); } aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*pIdx->nColumn); if( aChngAddr==0 ) continue; /* Open a cursor to the index to be analyzed. */ assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) ); sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb, (char *)pKey, P4_KEYINFO_HANDOFF); VdbeComment((v, "%s", pIdx->zName)); /* Populate the register containing the index name. */ sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0); #ifdef SQLITE_ENABLE_STAT2 /* If this iteration of the loop is generating code to analyze the ** first index in the pTab->pIndex list, then register regLast has ** not been populated. In this case populate it now. */ |
︙ | |||
232 233 234 235 236 237 238 | 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 | - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - + + + + + + + - + - - - - - - + - + - + + + + + - + - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | } /* Start the analysis loop. This loop runs through all the entries in ** the index b-tree. */ endOfLoop = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop); topOfLoop = sqlite3VdbeCurrentAddr(v); |
︙ | |||
484 485 486 487 488 489 490 | 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 | - + | if( (pIdx = sqlite3FindIndex(db, z, zDb))!=0 ){ analyzeTable(pParse, pIdx->pTable, pIdx); }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){ analyzeTable(pParse, pTab, 0); } sqlite3DbFree(db, z); } |
︙ | |||
552 553 554 555 556 557 558 559 | 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 | + + + + + + + + + + - - + + + + + + + + + + + + - + - - - + - - - - - - + + + + + + + + + + + + + + + + + + + + + + + - + + - - + + - + - + + + + + + + - - - - + + + + + - - + - - - - - - - - - - + + + + - - - - - - - - - - - - - - + + + + + + + + + + + + + + + - - - - - - - - - - + + + + + + + + - - - | pIndex->bUnordered = 1; break; } } return 0; } #if SQLITE_ENABLE_STAT2 /* ** Delete an array of IndexSample objects */ static void deleteIndexSampleArray( sqlite3 *db, /* The database connection */ IndexSampleArray *pArray /* Array of IndexSample objects */ ){ int j; if( pArray->a==0 ) return; for(j=0; j<pArray->n; j++){ |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
77 78 79 80 81 82 83 | 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | + - + + + | #endif /* ** The number of samples of an index that SQLite takes in order to ** construct a histogram of the table content when running ANALYZE ** and with SQLITE_ENABLE_STAT2 */ #ifndef SQLITE_INDEX_SAMPLES |
︙ | |||
608 609 610 611 612 613 614 615 616 617 618 619 620 621 | 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 | + | typedef struct FKey FKey; typedef struct FuncDestructor FuncDestructor; typedef struct FuncDef FuncDef; typedef struct FuncDefHash FuncDefHash; typedef struct IdList IdList; typedef struct Index Index; typedef struct IndexSample IndexSample; typedef struct IndexSampleArray IndexSampleArray; typedef struct KeyClass KeyClass; typedef struct KeyInfo KeyInfo; typedef struct Lookaside Lookaside; typedef struct LookasideSlot LookasideSlot; typedef struct Module Module; typedef struct NameContext NameContext; typedef struct Parse Parse; |
︙ | |||
1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 | 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 | + + + + + + + + + + + + + + + + + + + + + + + + | #define UNPACKED_NEED_FREE 0x0001 /* Memory is from sqlite3Malloc() */ #define UNPACKED_NEED_DESTROY 0x0002 /* apMem[]s should all be destroyed */ #define UNPACKED_IGNORE_ROWID 0x0004 /* Ignore trailing rowid on key1 */ #define UNPACKED_INCRKEY 0x0008 /* Make this key an epsilon larger */ #define UNPACKED_PREFIX_MATCH 0x0010 /* A prefix match is considered OK */ #define UNPACKED_PREFIX_SEARCH 0x0020 /* A prefix match is considered OK */ /* ** Each sample stored in the sqlite_stat2 table is represented in memory ** using a structure of this type. */ struct IndexSample { union { char *z; /* Value if eType is SQLITE_TEXT or SQLITE_BLOB */ double r; /* Value if eType is SQLITE_FLOAT */ i64 i; /* Value if eType is SQLITE_INTEGER */ } u; u8 eType; /* SQLITE_NULL, SQLITE_INTEGER ... etc. */ u8 nByte; /* Size in byte of text or blob. */ u32 nCopy; /* How many copies of this sample are in the database */ }; /* ** An array of IndexSample elements is as follows: */ struct IndexSampleArray { u16 n; /* Number of elements in the array */ u16 nAlloc; /* Space allocated to a[] */ IndexSample *a; /* The samples */ }; /* ** Each SQL index is represented in memory by an ** instance of the following structure. ** ** The columns of the table that are to be indexed are described ** by the aiColumn[] field of this structure. For example, suppose ** we have the following table and index: |
︙ | |||
1484 1485 1486 1487 1488 1489 1490 | 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 | - + - - + - - - - - - + + - - - - - | u8 autoIndex; /* True if is automatically created (ex: by UNIQUE) */ u8 bUnordered; /* Use this index for == or IN queries only */ char *zColAff; /* String defining the affinity of each column */ Index *pNext; /* The next index associated with the same table */ Schema *pSchema; /* Schema containing this index */ u8 *aSortOrder; /* Array of size Index.nColumn. True==DESC, False==ASC */ char **azColl; /* Array of collation sequence names for index */ |
︙ |
Changes to src/vdbe.c.
︙ | |||
1049 1050 1051 1052 1053 1054 1055 | 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 | - | */ case OP_Copy: { /* in1, out2 */ pIn1 = &aMem[pOp->p1]; pOut = &aMem[pOp->p2]; assert( pOut!=pIn1 ); sqlite3VdbeMemShallowCopy(pOut, pIn1, MEM_Ephem); Deephemeralize(pOut); |
︙ | |||
1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 | 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 | + | ** To force any register to be an integer, just add 0. */ case OP_AddImm: { /* in1 */ pIn1 = &aMem[pOp->p1]; memAboutToChange(p, pIn1); sqlite3VdbeMemIntegerify(pIn1); pIn1->u.i += pOp->p2; REGISTER_TRACE(pOp->p1, pIn1); break; } /* Opcode: MustBeInt P1 P2 * * * ** ** Force the value in register P1 to be an integer. If the value ** in P1 is not an integer and cannot be converted into an integer |
︙ | |||
2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 | 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 | + | memAboutToChange(p, pOut); /* Loop through the elements that will make up the record to figure ** out how much space is required for the new record. */ for(pRec=pData0; pRec<=pLast; pRec++){ assert( memIsValid(pRec) ); REGISTER_TRACE((pRec-pData0)+pOp->p1, pRec); if( zAffinity ){ applyAffinity(pRec, zAffinity[pRec-pData0], encoding); } if( pRec->flags&MEM_Zero && pRec->n>0 ){ sqlite3VdbeMemExpandBlob(pRec); } serial_type = sqlite3VdbeSerialType(pRec, file_format); |
︙ |
Changes to src/where.c.
︙ | |||
2418 2419 2420 2421 2422 2423 2424 | 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 | - - + + - - - - - + + + + + + + + + - + - + + - + + + - + + + + + + + + + + + + + - + + + - + - - - + | */ bestOrClauseIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost); } #endif /* SQLITE_OMIT_VIRTUALTABLE */ /* ** Argument pIdx is a pointer to an index structure that has an array of |
︙ | |||
2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 | 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 | + - + | c = pColl->xCmp(pColl->pUser, nSample, zSample, n, z); sqlite3DbFree(db, zSample); }else #endif { c = pColl->xCmp(pColl->pUser, aSample[i].nByte, aSample[i].u.z, n, z); } if( c==0 ) *pnCopy = aSample[i].nCopy; if( c-roundUp>=0 ) break; } } |
︙ | |||
2617 2618 2619 2620 2621 2622 2623 | 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 | - + + - + + - + - - + + - + - + - - - + + + - + - + | WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */ int *piEst /* OUT: Return value */ ){ int rc = SQLITE_OK; #ifdef SQLITE_ENABLE_STAT2 |
︙ | |||
2712 2713 2714 2715 2716 2717 2718 2719 | 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 2774 2775 2776 2777 | + - + + - + + - - - - - - - - - + + + + + + + + + + + + + + + | double *pnRow /* Write the revised row estimate here */ ){ sqlite3_value *pRhs = 0; /* VALUE on right-hand side of pTerm */ int iLower, iUpper; /* Range of histogram regions containing pRhs */ u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ double nRowEst; /* New estimate of the number of rows */ u32 nC = 0; /* Key copy count */ |
︙ | |||
2772 2773 2774 2775 2776 2777 2778 | 2803 2804 2805 2806 2807 2808 2809 2810 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 2836 2837 2838 2839 2840 2841 2842 2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 2855 | - - - + + + + + - + + - - + + - + - + - + - + - + | int iLower, iUpper; /* Range of histogram regions containing pRhs */ u8 aff; /* Column affinity */ int rc = SQLITE_OK; /* Subfunction return code */ double nRowEst; /* New estimate of the number of rows */ int nSpan = 0; /* Number of histogram regions spanned */ int nSingle = 0; /* Histogram regions hit by a single value */ int nNotFound = 0; /* Count of values that are not constants */ |
︙ | |||
3025 3026 3027 3028 3029 3030 3031 | 3059 3060 3061 3062 3063 3064 3065 3066 3067 3068 3069 3070 3071 3072 3073 | - + | /* "x IN (value, value, ...)" */ nInMul *= pExpr->x.pList->nExpr; } }else if( pTerm->eOperator & WO_ISNULL ){ wsFlags |= WHERE_COLUMN_NULL; } #ifdef SQLITE_ENABLE_STAT2 |
︙ |
Changes to test/analyze2.test.
︙ | |||
67 68 69 70 71 72 73 | 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 | - + - - - - - - - - - + + + + + + + + + - + - + | do_test analyze2-1.1 { execsql { CREATE TABLE t1(x PRIMARY KEY) } for {set i 0} {$i < 1000} {incr i} { execsql { INSERT INTO t1 VALUES($i) } } execsql { ANALYZE; |
︙ | |||
185 186 187 188 189 190 191 | 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 | - + - + - + - + - + | execsql ANALYZE execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't1_x' GROUP BY tbl,idx } |
︙ | |||
245 246 247 248 249 250 251 | 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 | - + - + | PRAGMA automatic_index=OFF; SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't3a' GROUP BY tbl,idx; PRAGMA automatic_index=ON; } |
︙ | |||
293 294 295 296 297 298 299 | 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 | - + | do_test analyze2-5.2 { execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE tbl = 't4' GROUP BY tbl,idx } |
︙ |
Changes to test/analyze5.test.
︙ | |||
124 125 126 127 128 129 130 | 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 | - + - - + + - - - + + + - + - - + + - - + + - - + + | 47 {z>=-100 AND z<=0.0} t1z 400 48 {z>=-100 AND z<0.0} t1z 50 49 {z>=-100 AND z<=1.0} t1z 700 50 {z>=-100 AND z<2.0} t1z 700 51 {z>=-100 AND z<=2.0} t1z 900 52 {z>=-100 AND z<3.0} t1z 900 |
︙ | |||
201 202 203 204 205 206 207 | 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 | - + - + | ANALYZE; } # Verify that range queries generate the correct row count estimates # foreach {testid where index rows} { 500 {x IS NULL AND u='charlie'} t1u 20 |
︙ |
Changes to test/analyze7.test.
︙ | |||
95 96 97 98 99 100 101 | 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | - - + + + + | execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~86 rows)}} } do_test analyze7-3.3 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} do_test analyze7-3.4 { |
︙ |
Added test/analyze8.test.