/ Check-in [c21f58d8]
Login

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

Overview
Comment:Fix a problem with using stat4 data to estimate the number of rows scanned by a range constraint on the second or subsequent column of any index where an affinity transformation must be applied to the constraint argument.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c21f58d84859e479a6cc619671a0df48b2f9692e
User & Date: dan 2013-09-02 07:16:40
Context
2013-09-02
11:52
Add tests to check if ANALYZE is choosing common non-periodic samples for the stat4 table. check-in: 17584299 user: dan tags: trunk
07:16
Fix a problem with using stat4 data to estimate the number of rows scanned by a range constraint on the second or subsequent column of any index where an affinity transformation must be applied to the constraint argument. check-in: c21f58d8 user: dan tags: trunk
2013-08-31
18:36
Cleanup of the windows VFS, including added support for Cygwin, fixes for compiler warnings under unusual configurations, and improved diagnostic error messages. check-in: c94933f1 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

2549
2550
2551
2552
2553
2554
2555




2556

2557
2558
2559
2560
2561
2562
2563
  if( nEq==pBuilder->nRecValid
   && nEq<p->nSampleCol
   && p->nSample 
   && OptimizationEnabled(pParse->db, SQLITE_Stat3) 
  ){
    UnpackedRecord *pRec = pBuilder->pRec;
    tRowcnt a[2];




    u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;


    /* Variable iLower will be set to the estimate of the number of rows in 
    ** the index that are less than the lower bound of the range query. The
    ** lower bound being the concatenation of $P and $L, where $P is the
    ** key-prefix formed by the nEq values matched against the nEq left-most
    ** columns of the index, and $L is the value in pLower.
    **







>
>
>
>
|
>







2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
  if( nEq==pBuilder->nRecValid
   && nEq<p->nSampleCol
   && p->nSample 
   && OptimizationEnabled(pParse->db, SQLITE_Stat3) 
  ){
    UnpackedRecord *pRec = pBuilder->pRec;
    tRowcnt a[2];
    u8 aff;
    if( nEq==p->nColumn ){
      aff = SQLITE_AFF_INTEGER;
    }else{
      aff = p->pTable->aCol[p->aiColumn[nEq]].affinity;
    }

    /* Variable iLower will be set to the estimate of the number of rows in 
    ** the index that are less than the lower bound of the range query. The
    ** lower bound being the concatenation of $P and $L, where $P is the
    ** key-prefix formed by the nEq values matched against the nEq left-most
    ** columns of the index, and $L is the value in pLower.
    **

Changes to test/analyze9.test.

551
552
553
554
555
556
557
558























































559
560
561
    do_eqp_test 12.$tn.5 $sql {/t4 USING INDEX t4a/}
    set sql {
      SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
    }
    do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
  }
}
























































finish_test










>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
    do_eqp_test 12.$tn.5 $sql {/t4 USING INDEX t4a/}
    set sql {
      SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
    }
    do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
  }
}

#-------------------------------------------------------------------------
# Check that affinities are taken into account when using stat4 data to
# estimate the number of rows scanned by a rowid constraint.
#
drop_all_tables
do_test 13.1 {
  execsql {
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(a);
    CREATE INDEX i2 ON t1(b, c);
  }
  for {set i 0} {$i<100} {incr i} {
    if {$i %2} {set a abc} else {set a def}
    execsql { INSERT INTO t1(rowid, a, b, c) VALUES($i, $a, $i, $i) }
  }
  execsql ANALYZE
} {}
do_eqp_test 13.2.1 {
  SELECT * FROM t1 WHERE a='abc' AND rowid<15 AND b<20
} {/SEARCH TABLE t1 USING INDEX i1/}
do_eqp_test 13.2.2 {
  SELECT * FROM t1 WHERE a='abc' AND rowid<'15' AND b<20
} {/SEARCH TABLE t1 USING INDEX i1/}
do_eqp_test 13.3.1 {
  SELECT * FROM t1 WHERE a='abc' AND rowid<100 AND b<20
} {/SEARCH TABLE t1 USING INDEX i2/}
do_eqp_test 13.3.2 {
  SELECT * FROM t1 WHERE a='abc' AND rowid<'100' AND b<20
} {/SEARCH TABLE t1 USING INDEX i2/}

#-------------------------------------------------------------------------
# Check also that affinities are taken into account when using stat4 data 
# to estimate the number of rows scanned by any other constraint on a 
# column other than the leftmost.
#
drop_all_tables
do_test 14.1 {
  execsql { CREATE TABLE t1(a, b INTEGER, c) }
  for {set i 0} {$i<100} {incr i} {
    set c [expr $i % 3]
    execsql { INSERT INTO t1 VALUES('ott', $i, $c) }
  }
  execsql {
    CREATE INDEX i1 ON t1(a, b);
    CREATE INDEX i2 ON t1(c);
    ANALYZE;
  }
} {}
do_eqp_test 13.2.1 {
  SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1
} {/SEARCH TABLE t1 USING INDEX i1/}
do_eqp_test 13.2.2 {
  SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1
} {/SEARCH TABLE t1 USING INDEX i1/}

finish_test