/ Check-in [a06a6392]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Merge the table-valued-function rowid fix.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | json
Files: files | file ages | folders
SHA1: a06a6392bd48baa8b9bac2624869c0cc7da7e779
User & Date: drh 2015-08-19 19:26:13
Context
2015-08-19
22:47
Add the json_each(JSON,PATH) table-valued-function. check-in: 3335ac17 user: drh tags: json
19:26
Merge the table-valued-function rowid fix. check-in: a06a6392 user: drh tags: json
19:01
Fix eponymous virtual tables so that they do not automatically make the first column the rowid. Enhance the generate_series virtual table to support rowid. check-in: a325a085 user: drh tags: table-valued-functions
18:31
Merge support for table-valued functions. check-in: 96a5d44d user: drh tags: json
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to ext/misc/series.c.

80
81
82
83
84
85
86

87
88
89
90
91
92
93
...
161
162
163
164
165
166
167

168
169
170
171
172
173
174
...
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
...
262
263
264
265
266
267
268

269
270
271
272
273
274
275
** serve as the underlying representation of a cursor that scans
** over rows of the result
*/
typedef struct series_cursor series_cursor;
struct series_cursor {
  sqlite3_vtab_cursor base;  /* Base class - must be first */
  int isDesc;                /* True to count down rather than up */

  sqlite3_int64 iValue;      /* Current value ("value") */
  sqlite3_int64 mnValue;     /* Mimimum value ("start") */
  sqlite3_int64 mxValue;     /* Maximum value ("stop") */
  sqlite3_int64 iStep;       /* Increment ("step") */
};

/*
................................................................................
static int seriesNext(sqlite3_vtab_cursor *cur){
  series_cursor *pCur = (series_cursor*)cur;
  if( pCur->isDesc ){
    pCur->iValue -= pCur->iStep;
  }else{
    pCur->iValue += pCur->iStep;
  }

  return SQLITE_OK;
}

/*
** Return values of columns for the row at which the series_cursor
** is currently pointing.
*/
................................................................................

/*
** Return the rowid for the current row.  In this implementation, the
** rowid is the same as the output value.
*/
static int seriesRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
  series_cursor *pCur = (series_cursor*)cur;
  *pRowid = pCur->iValue;
  return SQLITE_OK;
}

/*
** Return TRUE if the cursor has been moved off of the last
** row of output.
*/
................................................................................
    if( pCur->iStep>0 ){
      pCur->iValue -= (pCur->mxValue - pCur->mnValue)%pCur->iStep;
    }
  }else{
    pCur->isDesc = 0;
    pCur->iValue = pCur->mnValue;
  }

  return SQLITE_OK;
}

/*
** SQLite will invoke this method one or more times while planning a query
** that uses the generate_series virtual table.  This routine needs to create
** a query plan for each invocation and compute an estimated cost for that







>







 







>







 







|







 







>







80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
...
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
...
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
...
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
** serve as the underlying representation of a cursor that scans
** over rows of the result
*/
typedef struct series_cursor series_cursor;
struct series_cursor {
  sqlite3_vtab_cursor base;  /* Base class - must be first */
  int isDesc;                /* True to count down rather than up */
  sqlite3_int64 iRowid;      /* The rowid */
  sqlite3_int64 iValue;      /* Current value ("value") */
  sqlite3_int64 mnValue;     /* Mimimum value ("start") */
  sqlite3_int64 mxValue;     /* Maximum value ("stop") */
  sqlite3_int64 iStep;       /* Increment ("step") */
};

/*
................................................................................
static int seriesNext(sqlite3_vtab_cursor *cur){
  series_cursor *pCur = (series_cursor*)cur;
  if( pCur->isDesc ){
    pCur->iValue -= pCur->iStep;
  }else{
    pCur->iValue += pCur->iStep;
  }
  pCur->iRowid++;
  return SQLITE_OK;
}

/*
** Return values of columns for the row at which the series_cursor
** is currently pointing.
*/
................................................................................

/*
** Return the rowid for the current row.  In this implementation, the
** rowid is the same as the output value.
*/
static int seriesRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
  series_cursor *pCur = (series_cursor*)cur;
  *pRowid = pCur->iRowid;
  return SQLITE_OK;
}

/*
** Return TRUE if the cursor has been moved off of the last
** row of output.
*/
................................................................................
    if( pCur->iStep>0 ){
      pCur->iValue -= (pCur->mxValue - pCur->mnValue)%pCur->iStep;
    }
  }else{
    pCur->isDesc = 0;
    pCur->iValue = pCur->mnValue;
  }
  pCur->iRowid = 1;
  return SQLITE_OK;
}

/*
** SQLite will invoke this method one or more times while planning a query
** that uses the generate_series virtual table.  This routine needs to create
** a query plan for each invocation and compute an estimated cost for that

Changes to src/vtab.c.

1116
1117
1118
1119
1120
1121
1122

1123
1124
1125
1126
1127
1128
1129
  pMod->pEpoTab = pTab;
  pTab->zName = (char*)&pTab[1];
  memcpy(pTab->zName, pMod->zName, nName);
  pTab->nRef = 1;
  pTab->pSchema = db->aDb[0].pSchema;
  pTab->tabFlags |= TF_Virtual;
  pTab->nModuleArg = 0;

  addModuleArgument(db, pTab, pTab->zName);
  addModuleArgument(db, pTab, 0);
  addModuleArgument(db, pTab, pTab->zName);
  rc = vtabCallConstructor(db, pTab, pMod, pModule->xConnect, &zErr);
  if( rc ){
    sqlite3ErrorMsg(pParse, "%s", zErr);
    sqlite3DbFree(db, zErr);







>







1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
  pMod->pEpoTab = pTab;
  pTab->zName = (char*)&pTab[1];
  memcpy(pTab->zName, pMod->zName, nName);
  pTab->nRef = 1;
  pTab->pSchema = db->aDb[0].pSchema;
  pTab->tabFlags |= TF_Virtual;
  pTab->nModuleArg = 0;
  pTab->iPKey = -1;
  addModuleArgument(db, pTab, pTab->zName);
  addModuleArgument(db, pTab, 0);
  addModuleArgument(db, pTab, pTab->zName);
  rc = vtabCallConstructor(db, pTab, pMod, pModule->xConnect, &zErr);
  if( rc ){
    sqlite3ErrorMsg(pParse, "%s", zErr);
    sqlite3DbFree(db, zErr);

Changes to test/tabfunc01.test.

44
45
46
47
48
49
50






51
52
53
54
55
56
57
58
do_catchsql_test tabfunc01-1.7 {
  SELECT * FROM generate_series(1,9,2,11);
} {1 {too many arguments on generate_series - max 3}}

do_execsql_test tabfunc01-1.8 {
  SELECT * FROM generate_series(0,32,5) ORDER BY rowid DESC;
} {30 25 20 15 10 5 0}







do_execsql_test tabfunc01-2.1 {
  CREATE TABLE t1(x);
  INSERT INTO t1(x) VALUES(2),(3);
  SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}

finish_test







>
>
>
>
>
>








44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
do_catchsql_test tabfunc01-1.7 {
  SELECT * FROM generate_series(1,9,2,11);
} {1 {too many arguments on generate_series - max 3}}

do_execsql_test tabfunc01-1.8 {
  SELECT * FROM generate_series(0,32,5) ORDER BY rowid DESC;
} {30 25 20 15 10 5 0}
do_execsql_test tabfunc01-1.9 {
  SELECT rowid, * FROM generate_series(0,32,5) ORDER BY value DESC;
} {1 30 2 25 3 20 4 15 5 10 6 5 7 0}
do_execsql_test tabfunc01-1.10 {
  SELECT rowid, * FROM generate_series(0,32,5) ORDER BY +value DESC;
} {7 30 6 25 5 20 4 15 3 10 2 5 1 0}

do_execsql_test tabfunc01-2.1 {
  CREATE TABLE t1(x);
  INSERT INTO t1(x) VALUES(2),(3);
  SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}

finish_test