SQLite

Check-in [f2c4a584e8]
Login

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

Overview
Comment:Add test cases to test/unionvtab.test.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | union-vtab
Files: files | file ages | folders
SHA3-256: f2c4a584e83ffcc1cedd39460eb06d6f231a1a87f269bb3e75055232a866067e
User & Date: dan 2017-07-17 21:03:13.376
Context
2017-07-18
18:19
Add the union-vtab extension - still experimental and under active development. (check-in: 948f95046b user: drh tags: trunk)
2017-07-17
21:03
Add test cases to test/unionvtab.test. (Closed-Leaf check-in: f2c4a584e8 user: dan tags: union-vtab)
20:25
Enhance error detection and fix other issues in unionvtab code. (check-in: 9c3f1b9a82 user: dan tags: union-vtab)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/misc/unionvtab.c.
︙︙
648
649
650
651
652
653
654
655

656

657
658
659
660
661
662
663

        }
        if( iMax!=LARGEST_INT64 && iMax<pSrc->iMax ){
          zSql = sqlite3_mprintf("%z %s rowid<=%lld", zSql, zWhere, iMax);
        }
      }
    }
  }


  if( rc==SQLITE_OK ){

    pCsr->pStmt = unionPrepare(&rc, pTab->db, zSql, &pTab->base.zErrMsg);
  }
  sqlite3_free(zSql);

  if( rc!=SQLITE_OK ) return rc;
  return unionNext(pVtabCursor);
}









>

>








648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665

        }
        if( iMax!=LARGEST_INT64 && iMax<pSrc->iMax ){
          zSql = sqlite3_mprintf("%z %s rowid<=%lld", zSql, zWhere, iMax);
        }
      }
    }
  }


  if( rc==SQLITE_OK ){
    if( zSql==0 ) return SQLITE_OK;
    pCsr->pStmt = unionPrepare(&rc, pTab->db, zSql, &pTab->base.zErrMsg);
  }
  sqlite3_free(zSql);

  if( rc!=SQLITE_OK ) return rc;
  return unionNext(pVtabCursor);
}
︙︙
Changes to test/unionvtab.test.
︙︙
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

}

#-------------------------------------------------------------------------
# Error conditions.
#
#   2.1.*: Attempt to create a unionvtab table outside of the TEMP schema.
#   2.2.*: Tables that do not exist.
#   2.3.*: Non WITHOUT ROWID tables.
#   2.4.*: Tables with mismatched schemas.
#
do_catchsql_test 2.1.1 {
  CREATE VIRTUAL TABLE u1 USING unionvtab("VALUES(NULL, 't1', 1, 100)");
} {1 {unionvtab tables must be created in TEMP schema}}
do_catchsql_test 2.1.2 {
  CREATE VIRTUAL TABLE main.u1 USING unionvtab("VALUES('', 't1', 1, 100)");








|








79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

}

#-------------------------------------------------------------------------
# Error conditions.
#
#   2.1.*: Attempt to create a unionvtab table outside of the TEMP schema.
#   2.2.*: Tables that do not exist.
#   2.3.*: Non rowid tables.
#   2.4.*: Tables with mismatched schemas.
#
do_catchsql_test 2.1.1 {
  CREATE VIRTUAL TABLE u1 USING unionvtab("VALUES(NULL, 't1', 1, 100)");
} {1 {unionvtab tables must be created in TEMP schema}}
do_catchsql_test 2.1.2 {
  CREATE VIRTUAL TABLE main.u1 USING unionvtab("VALUES('', 't1', 1, 100)");
︙︙
102
103
104
105
106
107
108















109
110
111
112
113
114
115

do_catchsql_test 2.2.2 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('aux', 't555', 1, 100)");
} {1 {no such rowid table: aux.t555}}
do_catchsql_test 2.2.3 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('xua', 't555', 1, 100)");
} {1 {no such rowid table: xua.t555}}
















do_execsql_test 2.4.0 {
  CREATE TABLE x1(a BLOB, b);
  CREATE TABLE x2(a BLOB, b);
  CREATE TEMP TABLE x3(a BLOB, b);

  CREATE TABLE aux.y1(one, two, three INTEGER PRIMARY KEY);
  CREATE TEMP TABLE y2(one, two, three INTEGER PRIMARY KEY);








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








102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130

do_catchsql_test 2.2.2 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('aux', 't555', 1, 100)");
} {1 {no such rowid table: aux.t555}}
do_catchsql_test 2.2.3 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('xua', 't555', 1, 100)");
} {1 {no such rowid table: xua.t555}}

do_execsql_test 2.3.0 {
  CREATE TABLE wr1(a, b, c PRIMARY KEY) WITHOUT ROWID;
  CREATE VIEW v1 AS SELECT * FROM t1;
  CREATE VIEW v2 AS SELECT _rowid_, * FROM t1;
}
do_catchsql_test 2.3.1 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('main', 'wr1', 1, 2)");
} {1 {no such rowid table: main.wr1}}
do_catchsql_test 2.3.2 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'v1', 1, 2)");
} {1 {no such rowid table: v1}}
do_catchsql_test 2.3.3 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'v2', 1, 2)");
} {1 {no such rowid table: v2}}

do_execsql_test 2.4.0 {
  CREATE TABLE x1(a BLOB, b);
  CREATE TABLE x2(a BLOB, b);
  CREATE TEMP TABLE x3(a BLOB, b);

  CREATE TABLE aux.y1(one, two, three INTEGER PRIMARY KEY);
  CREATE TEMP TABLE y2(one, two, three INTEGER PRIMARY KEY);
︙︙
138
139
140
141
142
143
144

145















































































146
147

  set sql "CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\")"
  do_catchsql_test 2.4.$tn "
    DROP TABLE IF EXISTS temp.a1;
    CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\");
  " $res
}


















































































finish_test









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



153
154
155
156
157
158
159
160
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
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
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242

  set sql "CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\")"
  do_catchsql_test 2.4.$tn "
    DROP TABLE IF EXISTS temp.a1;
    CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\");
  " $res
}

#-------------------------------------------------------------------------
#
reset_db
load_static_extension db unionvtab
do_execsql_test 3.0 {
  CREATE TABLE tbl1(a INTEGER PRIMARY KEY, b);
  CREATE TABLE tbl2(a INTEGER PRIMARY KEY, b);
  CREATE TABLE tbl3(a INTEGER PRIMARY KEY, b);

  WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
  INSERT INTO tbl1 SELECT ii, '1.' || ii FROM ss;

  WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
  INSERT INTO tbl2 SELECT ii, '2.' || ii FROM ss;

  WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
  INSERT INTO tbl3 SELECT ii, '3.' || ii FROM ss;

  CREATE VIRTUAL TABLE temp.uu USING unionvtab(
    "VALUES(NULL,'tbl2', 26, 74), (NULL,'tbl3', 75, 100), (NULL,'tbl1', 1, 25)"
  );
}

do_execsql_test 3.1 {
  SELECT * FROM uu WHERE rowid = 10;
} {10 {1.10}}
do_execsql_test 3.2 {
  SELECT * FROM uu WHERE rowid = 25;
} {25 {1.25}}

do_execsql_test 3.3 { SELECT count(*) FROM uu WHERE rowid <= 24 } {24}

# The following queries get the "wrong" answers. This is because the
# module assumes that each source table contains rowids from only within
# the range specified. For example, (rowid <= 25) matches 100 rows. This
# is because the module implements (rowid <= 25) as a full table scan
# of tbl1 only.
do_execsql_test 3.4.1 { SELECT count(*) FROM uu WHERE rowid <= 25 } {100}
do_execsql_test 3.4.2 { SELECT count(*) FROM uu WHERE rowid <= 26 } {126}
do_execsql_test 3.4.3 { SELECT count(*) FROM uu WHERE rowid <= 73 } {173}
do_execsql_test 3.4.4 { SELECT count(*) FROM uu WHERE rowid <= 74 } {200}
do_execsql_test 3.4.5 { SELECT count(*) FROM uu WHERE rowid <= 75 } {275}
do_execsql_test 3.4.6 { SELECT count(*) FROM uu WHERE rowid <= 99 } {299}
do_execsql_test 3.4.7 { SELECT count(*) FROM uu WHERE rowid <= 100 } {300}
do_execsql_test 3.4.8 { SELECT count(*) FROM uu WHERE rowid <= 101 } {300}

do_execsql_test 3.5.1 { SELECT count(*) FROM uu WHERE rowid < 25 } {24}
do_execsql_test 3.5.2 { SELECT count(*) FROM uu WHERE rowid < 26 } {100}
do_execsql_test 3.5.3 { SELECT count(*) FROM uu WHERE rowid < 27 } {126}
do_execsql_test 3.5.4 { SELECT count(*) FROM uu WHERE rowid < 73 } {172}
do_execsql_test 3.5.5 { SELECT count(*) FROM uu WHERE rowid < 74 } {173}
do_execsql_test 3.5.6 { SELECT count(*) FROM uu WHERE rowid < 75 } {200}
do_execsql_test 3.5.7 { SELECT count(*) FROM uu WHERE rowid < 76 } {275}
do_execsql_test 3.5.8 { SELECT count(*) FROM uu WHERE rowid < 99 } {298}
do_execsql_test 3.5.9 { SELECT count(*) FROM uu WHERE rowid < 100 } {299}
do_execsql_test 3.5.10 { SELECT count(*) FROM uu WHERE rowid < 101 } {300}

do_execsql_test 3.6.1 { SELECT count(*) FROM uu WHERE rowid > 24 } {276}
do_execsql_test 3.6.1 { SELECT count(*) FROM uu WHERE rowid > 25 } {200}
do_execsql_test 3.6.2 { SELECT count(*) FROM uu WHERE rowid > 26 } {174}
do_execsql_test 3.6.3 { SELECT count(*) FROM uu WHERE rowid > 27 } {173}
do_execsql_test 3.6.4 { SELECT count(*) FROM uu WHERE rowid > 73 } {127}
do_execsql_test 3.6.5 { SELECT count(*) FROM uu WHERE rowid > 74 } {100}
do_execsql_test 3.6.6 { SELECT count(*) FROM uu WHERE rowid > 75 } {25}
do_execsql_test 3.6.7 { SELECT count(*) FROM uu WHERE rowid > 76 } {24}
do_execsql_test 3.6.8 { SELECT count(*) FROM uu WHERE rowid > 99 } {1}
do_execsql_test 3.6.9 { SELECT count(*) FROM uu WHERE rowid > 100 } {0}
do_execsql_test 3.6.10 { SELECT count(*) FROM uu WHERE rowid > 101 } {0}

do_execsql_test 3.7.1 { SELECT count(*) FROM uu WHERE rowid >= 24 } {277}
do_execsql_test 3.7.1 { SELECT count(*) FROM uu WHERE rowid >= 25 } {276}
do_execsql_test 3.7.2 { SELECT count(*) FROM uu WHERE rowid >= 26 } {200}
do_execsql_test 3.7.3 { SELECT count(*) FROM uu WHERE rowid >= 27 } {174}
do_execsql_test 3.7.4 { SELECT count(*) FROM uu WHERE rowid >= 73 } {128}
do_execsql_test 3.7.5 { SELECT count(*) FROM uu WHERE rowid >= 74 } {127}
do_execsql_test 3.7.6 { SELECT count(*) FROM uu WHERE rowid >= 75 } {100}
do_execsql_test 3.7.7 { SELECT count(*) FROM uu WHERE rowid >= 76 } {25}
do_execsql_test 3.7.8 { SELECT count(*) FROM uu WHERE rowid >= 99 } {2}
do_execsql_test 3.7.9 { SELECT count(*) FROM uu WHERE rowid >= 100 } {1}
do_execsql_test 3.7.10 { SELECT count(*) FROM uu WHERE rowid >= 101 } {0}

finish_test