SQLite
Check-in [465c819fdb601961eddb7304642c823d78111992]
Not logged in
Overview
SHA1 Hash:465c819fdb601961eddb7304642c823d78111992
Date: 2010-11-01 05:42:26
User: dan
Comment:Changes to test scripts so that they work with SQLITE_ENABLE_ICU.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_expr.test

932
933
934
935
936
937
938
939




940

941

942
943
944
945
946
947
948
...
981
982
983
984
985
986
987
988

989
990
991
992
993
994
995
....
1052
1053
1054
1055
1056
1057
1058



1059
1060
1061
1062
1063
1064

1065
1066
1067
1068
1069
1070
1071
#
# EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by
# default for unicode characters that are beyond the ASCII range.
#
# EVIDENCE-OF: R-44381-11669 the expression
# 'a' LIKE 'A' is TRUE but
# 'æ' LIKE 'Æ' is FALSE.
#




do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a'         } 1

do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0


# EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present,
# then the expression following the ESCAPE keyword must evaluate to a
# string consisting of a single character.
#
do_catchsql_test e_expr-14.6.1 { 
  SELECT 'A' LIKE 'a' ESCAPE '12' 
................................................................................
# EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by
# calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).
#
proc likefunc {args} {
  eval lappend ::likeargs $args
  return 1
}
db func like likefunc

set ::likeargs [list]
do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1
do_test         e_expr-15.1.2 { set likeargs } {def abc}
set ::likeargs [list]
do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1
do_test         e_expr-15.1.4 { set likeargs } {def abc X}
db close
................................................................................
do_test         e_expr-17.3.4 { set globargs } {Y X}
sqlite3 db test.db

# EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by
# default and so use of the REGEXP operator will normally result in an
# error message.
#



do_catchsql_test e_expr-18.1.1 { 
  SELECT regexp('abc', 'def') 
} {1 {no such function: regexp}}
do_catchsql_test e_expr-18.1.2 { 
  SELECT 'abc' REGEXP 'def'
} {1 {no such function: REGEXP}}


# EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
# the regexp() user function.
#
# EVIDENCE-OF: R-57289-13578 If a application-defined SQL function named
# "regexp" is added at run-time, that function will be called in order
# to implement the REGEXP operator.








>
>
>
>

>
|
>







 







|
>







 







>
>
>
|
|
|
|
|
|
>







932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
...
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
....
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
#
# EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by
# default for unicode characters that are beyond the ASCII range.
#
# EVIDENCE-OF: R-44381-11669 the expression
# 'a' LIKE 'A' is TRUE but
# 'æ' LIKE 'Æ' is FALSE.
#
#   The restriction to ASCII characters does not apply if the ICU
#   library is compiled in. When ICU is enabled SQLite does not act
#   as it does "by default".
#
do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a'         } 1
ifcapable !icu {
  do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0
}

# EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present,
# then the expression following the ESCAPE keyword must evaluate to a
# string consisting of a single character.
#
do_catchsql_test e_expr-14.6.1 { 
  SELECT 'A' LIKE 'a' ESCAPE '12' 
................................................................................
# EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by
# calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).
#
proc likefunc {args} {
  eval lappend ::likeargs $args
  return 1
}
db func like -argcount 2 likefunc
db func like -argcount 3 likefunc
set ::likeargs [list]
do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1
do_test         e_expr-15.1.2 { set likeargs } {def abc}
set ::likeargs [list]
do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1
do_test         e_expr-15.1.4 { set likeargs } {def abc X}
db close
................................................................................
do_test         e_expr-17.3.4 { set globargs } {Y X}
sqlite3 db test.db

# EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by
# default and so use of the REGEXP operator will normally result in an
# error message.
#
#   There is a regexp function if ICU is enabled though.
#
ifcapable !icu {
  do_catchsql_test e_expr-18.1.1 { 
    SELECT regexp('abc', 'def') 
  } {1 {no such function: regexp}}
  do_catchsql_test e_expr-18.1.2 { 
    SELECT 'abc' REGEXP 'def'
  } {1 {no such function: REGEXP}}
}

# EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
# the regexp() user function.
#
# EVIDENCE-OF: R-57289-13578 If a application-defined SQL function named
# "regexp" is added at run-time, that function will be called in order
# to implement the REGEXP operator.

Changes to test/like.test

111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
...
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
...
669
670
671
672
673
674
675
676
677
678
679
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

# Tests of the REGEXP operator
#
do_test like-2.1 {
  proc test_regexp {a b} {
    return [regexp $a $b]
  }
  db function regexp test_regexp
  execsql {
    SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1;
  }
} {{ABC abc xyz} abc abcd}
do_test like-2.2 {
  execsql {
    SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1;
................................................................................
  db eval {
    SELECT 1, x FROM t8 WHERE x LIKE '%h%';
    SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
  }
} {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr}


ifcapable like_opt {
  # Evaluate SQL.  Return the result set followed by the
  # and the number of full-scan steps.
  #
  db close
  sqlite3 db test.db
  proc count_steps {sql} {
    set r [db eval $sql]
................................................................................
    do_test like-9.5.2 {
      set res [sqlite3_exec_hex db {
         EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25'
      }]
      regexp {INDEX i2} $res
    } {1}
  }
}

# Do an SQL statement.  Append the search count to the end of the result.
#
proc count sql {
  set ::sqlite_search_count 0
  set ::sqlite_like_count 0
  return [concat [execsql $sql] scan $::sqlite_search_count \
           like $::sqlite_like_count]
}

# The LIKE and GLOB optimizations do not work on columns with
# affinity other than TEXT.
# Ticket #3901
#
do_test like-10.1 {
  db close
  sqlite3 db test.db
  execsql {
    CREATE TABLE t10(
      a INTEGER PRIMARY KEY,
      b INTEGER COLLATE nocase UNIQUE,
      c NUMBER COLLATE nocase UNIQUE,
      d BLOB COLLATE nocase UNIQUE,
      e COLLATE nocase UNIQUE,
      f TEXT COLLATE nocase UNIQUE
    );
    INSERT INTO t10 VALUES(1,1,1,1,1,1);
    INSERT INTO t10 VALUES(12,12,12,12,12,12);
    INSERT INTO t10 VALUES(123,123,123,123,123,123);
    INSERT INTO t10 VALUES(234,234,234,234,234,234);
    INSERT INTO t10 VALUES(345,345,345,345,345,345);
    INSERT INTO t10 VALUES(45,45,45,45,45,45);
  }
  count {
    SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY a;
  }
} {12 123 scan 5 like 6}
do_test like-10.2 {
  count {
    SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY a;
  }
} {12 123 scan 5 like 6}
do_test like-10.3 {
  count {
    SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY a;
  }
} {12 123 scan 5 like 6}
do_test like-10.4 {
  count {
    SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY a;
  }
} {12 123 scan 5 like 6}
do_test like-10.5 {
  count {
    SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY a;
  }
} {12 123 scan 3 like 0}
do_test like-10.6 {
  count {
    SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY a;
  }
} {12 123 scan 5 like 6}
do_test like-10.10 {
  execsql {
    CREATE TABLE t10b(
      a INTEGER PRIMARY KEY,
      b INTEGER UNIQUE,
      c NUMBER UNIQUE,
      d BLOB UNIQUE,
      e UNIQUE,
      f TEXT UNIQUE
    );
    INSERT INTO t10b SELECT * FROM t10;
  }
  count {
    SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY a;
  }
} {12 123 scan 5 like 6}
do_test like-10.11 {
  count {
    SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY a;
  }
} {12 123 scan 5 like 6}
do_test like-10.12 {
  count {
    SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY a;
  }
} {12 123 scan 5 like 6}
do_test like-10.13 {
  count {
    SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY a;
  }
} {12 123 scan 5 like 6}
do_test like-10.14 {
  count {
    SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY a;
  }
} {12 123 scan 3 like 0}
do_test like-10.15 {
  count {
    SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY a;
  }
} {12 123 scan 5 like 6}


# LIKE and GLOB where the default collating sequence is not appropriate
# but an index with the appropriate collating sequence exists.
#
do_test like-11.0 {
  execsql {
    CREATE TABLE t11(







|







 







|







 







|
<
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
...
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
...
669
670
671
672
673
674
675
676

677
678
679
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

# Tests of the REGEXP operator
#
do_test like-2.1 {
  proc test_regexp {a b} {
    return [regexp $a $b]
  }
  db function regexp -argcount 2 test_regexp
  execsql {
    SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1;
  }
} {{ABC abc xyz} abc abcd}
do_test like-2.2 {
  execsql {
    SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1;
................................................................................
  db eval {
    SELECT 1, x FROM t8 WHERE x LIKE '%h%';
    SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
  }
} {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr}


ifcapable like_opt&&!icu {
  # Evaluate SQL.  Return the result set followed by the
  # and the number of full-scan steps.
  #
  db close
  sqlite3 db test.db
  proc count_steps {sql} {
    set r [db eval $sql]
................................................................................
    do_test like-9.5.2 {
      set res [sqlite3_exec_hex db {
         EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25'
      }]
      regexp {INDEX i2} $res
    } {1}
  }


  # Do an SQL statement.  Append the search count to the end of the result.
  #
  proc count sql {
    set ::sqlite_search_count 0
    set ::sqlite_like_count 0
    return [concat [execsql $sql] scan $::sqlite_search_count \
             like $::sqlite_like_count]
  }

  # The LIKE and GLOB optimizations do not work on columns with
  # affinity other than TEXT.
  # Ticket #3901
  #
  do_test like-10.1 {
    db close
    sqlite3 db test.db
    execsql {
      CREATE TABLE t10(
        a INTEGER PRIMARY KEY,
        b INTEGER COLLATE nocase UNIQUE,
        c NUMBER COLLATE nocase UNIQUE,
        d BLOB COLLATE nocase UNIQUE,
        e COLLATE nocase UNIQUE,
        f TEXT COLLATE nocase UNIQUE
      );
      INSERT INTO t10 VALUES(1,1,1,1,1,1);
      INSERT INTO t10 VALUES(12,12,12,12,12,12);
      INSERT INTO t10 VALUES(123,123,123,123,123,123);
      INSERT INTO t10 VALUES(234,234,234,234,234,234);
      INSERT INTO t10 VALUES(345,345,345,345,345,345);
      INSERT INTO t10 VALUES(45,45,45,45,45,45);
    }
    count {
      SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.2 {
    count {
      SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.3 {
    count {
      SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.4 {
    count {
      SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.5 {
    count {
      SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 3 like 0}
  do_test like-10.6 {
    count {
      SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.10 {
    execsql {
      CREATE TABLE t10b(
        a INTEGER PRIMARY KEY,
        b INTEGER UNIQUE,
        c NUMBER UNIQUE,
        d BLOB UNIQUE,
        e UNIQUE,
        f TEXT UNIQUE
      );
      INSERT INTO t10b SELECT * FROM t10;
    }
    count {
      SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.11 {
    count {
      SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.12 {
    count {
      SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.13 {
    count {
      SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.14 {
    count {
      SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 3 like 0}
  do_test like-10.15 {
    count {
      SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
}

# LIKE and GLOB where the default collating sequence is not appropriate
# but an index with the appropriate collating sequence exists.
#
do_test like-11.0 {
  execsql {
    CREATE TABLE t11(