mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-15 22:39:05 +00:00
76 lines
1.5 KiB
Plaintext
76 lines
1.5 KiB
Plaintext
|
|
CREATE VIRTUAL TABLE fts USING fts3(content TEXT);
|
|
INSERT INTO fts(rowid,content)
|
|
VALUES(1,'this is a test of the fts3 virtual'),
|
|
(2,'table used as part of a join together'),
|
|
(3,'with the DISTINCT keyword. There was'),
|
|
(4,'a bug at one time (2013-06 through 2014-04)'),
|
|
(5,'that prevented this from working correctly.'),
|
|
(11,'a row that occurs twice'),
|
|
(12,'a row that occurs twice');
|
|
|
|
CREATE TABLE t1(x TEXT PRIMARY KEY, y);
|
|
INSERT OR IGNORE INTO t1 SELECT content, rowid+100 FROM fts;
|
|
|
|
|
|
SELECT DISTINCT fts.rowid, t1.y
|
|
FROM fts, t1
|
|
WHERE fts MATCH 'that twice'
|
|
AND content=x
|
|
ORDER BY y;
|
|
|
|
|
|
SELECT DISTINCT fts.rowid, t1.x
|
|
FROM fts, t1
|
|
WHERE fts MATCH 'that twice'
|
|
AND content=x
|
|
ORDER BY 1;
|
|
|
|
|
|
SELECT DISTINCT t1.x
|
|
FROM fts, t1
|
|
WHERE fts MATCH 'that twice'
|
|
AND content=x
|
|
ORDER BY 1;
|
|
|
|
|
|
SELECT t1.x
|
|
FROM fts, t1
|
|
WHERE fts MATCH 'that twice'
|
|
AND content=x
|
|
ORDER BY 1;
|
|
|
|
|
|
SELECT DISTINCT t1.x
|
|
FROM fts, t1
|
|
WHERE fts MATCH 'that twice'
|
|
AND content=x;
|
|
|
|
|
|
SELECT t1.x
|
|
FROM fts, t1
|
|
WHERE fts MATCH 'that twice'
|
|
AND content=x;
|
|
|
|
|
|
SELECT DISTINCT t1.x
|
|
FROM fts, t1
|
|
WHERE fts.rowid=11
|
|
AND content=x
|
|
ORDER BY fts.rowid;
|
|
|
|
|
|
SELECT DISTINCT t1.*
|
|
FROM fts, t1
|
|
WHERE fts.rowid=11
|
|
AND content=x
|
|
ORDER BY fts.rowid;
|
|
|
|
|
|
SELECT DISTINCT t1.*
|
|
FROM fts, t1
|
|
WHERE fts.rowid=11
|
|
AND content=x
|
|
ORDER BY t1.y
|
|
|