Re: [sqlite] [EXTERNAL] Unexpected result from SELECT * FROM (subquery);
Because the query planner needs to know the name(s) of the table(s) required for the query at "prepare time" and your query does not provide the name until "run time". -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Shane Dev Gesendet: Sonntag, 05. November 2017 08:04 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite] Unexpected result from SELECT * FROM (subquery); Hello, In sqlite3, I executed the following statements - sqlite> select name from tabs where rowid=1; tab1 sqlite> select * from tab1; first rec sqlite> select * from (select name from tabs where rowid=1); tab1 I expected the last statement to evaluate the subquery first to be 'tab1' and then execute SELECT * FROM tab1 to yield 'first rec'. Why didn't this happen? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] memory leak
You are preparing statements inside the loop, but only finalizing the last one
(i.e. outside the loop)
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Lev
Gesendet: Sonntag, 05. November 2017 01:28
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] memory leak
I'm fighting with some memory leak. From time to time the vsize of the process
goes up with 100k. I have several function like this, and I call them in each
second. My memory growth happens every 10 minutes or so.
int dbSqliteSelectSensorsToReport(sdmd_t *sdmd, sensor_t *sensor, int
deviceType, int target) { char sqlQuery[256];
char *tableName;
char *targetName;
int haveWork = 1;
int ret, retVal;
sqlite3_stmt *sq3Stmt;
/*Reset the state whatever it is*/
sensor->stat = SENSOR_DATA_INVALID;
do {
switch (deviceType) {
case SENSOR_TYPE_AN:
tableName = "analog";
break;
case SENSOR_TYPE_DIG:
tableName = "digital";
break;
case SENSOR_TYPE_UNKNOWN:
default:
tableName = NULL;
break;
}
switch (target) {
case TARGET_MQTT:
targetName = "read_mqtt";
break;
case TARGET_MODBUS: /*This makes no sence,
hence it is done in an other process... but whatever*/
targetName = NULL; break;
default:
targetName = NULL;
break;
}
if (targetName == NULL || tableName == NULL) {
retVal = DB_SQLITE_ERR;
break;
}
sprintf(sqlQuery, "SELECT rowid, sensor_id, value, time
FROM %s WHERE %s=0 LIMIT 1;", tableName,
targetName); /*Read one by one. I don't want to block
DB access.*/
sqlite3_prepare_v2(sdmd->db, sqlQuery, -1, &sq3Stmt,
NULL);
while (haveWork == 1) {
ret = sqlite3_step(sq3Stmt);
switch (ret) {
case SQLITE_ROW:
/*Read the data*/
sensor->id =
sqlite3_column_int64(sq3Stmt, 0); sensor->sensorId =
sqlite3_column_int64(sq3Stmt, 1); sensor->value =
sqlite3_column_double(sq3Stmt, 2); sensor->time =
sqlite3_column_int64(sq3Stmt, 3); sensor->stat =
SENSOR_DATA_VALID; sprintf(sensor->name, "%s_%d",
tableName, sensor->sensorId); break;
case SQLITE_DONE:
haveWork = 0;
retVal = DB_SQLITE_OK;
break;
case SQLITE_BUSY:
debug(sdmd->dbg, DEBUG_ERR, "%s():
sqlite (BUSY): %s\n", __func__,
sqlite3_errmsg(sdmd->db)); haveWork = 0; retVal =
DB_SQLITE_ERR; break;
default:
haveWork = 0;
debug(sdmd->dbg, DEBUG_ERR, "%s():
sqlite (%d): %s\n", __func__, ret,
sqlite3_errmsg(sdmd->db)); debug(sdmd->dbg, DEBUG_ERR,
"%s(): sqlite query: %s\n", __func__, sqlQuery); retVal
= DB_SQLITE_ERR; break; }
}
} while(0);
sqlite3_finalize(sq3Stmt); /*Release the dB*/
return retVal;
}
What do I do wrong?
Any help is appreciated.
Levente
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] DB on JFFS
In addition to putting mutiple inserts within a single transaction (this saves "disc" IO), you might like to look into using parameters (this saves on CPU spent parsing/compiling the statements. This leaves you with: (setup) connect prepare BEGIN (loop) bind step reset (cleanup) END finalize disconnect -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Lev Gesendet: Freitag, 03. November 2017 22:36 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] DB on JFFS I'm implementing an application that is on a flash filesystem (JFFS). The flash is connected to the CPU with SPI. I experience about 1s INSERT time. My DB isn't big, just about 200k. I implement a fifo like operation, so i have a fixed length row count. I DELETE old data from time to time. I call these function at each INSERT: sqlite3_prepare_v2(sq3, query, -1, &sq3_stmt, NULL); sqlite3_step(sq3_stmt); sqlite3_finalize(sq3_stmt); query holds the INSERT SQL. The question is what can I do to speed up the INSERT proces? The database is also opened by other processes, so I want to lock the table as short time as I can. Any hints are welcome. Thanks, Levente ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory leak
On 11/05/17 03:28 , Lev wrote:
> I'm fighting with some memory leak. From time to time the vsize of the
> process goes up with 100k. I have several function like this, and I
> call them in each second. My memory growth happens every 10 minutes or
> so.
>
> int dbSqliteSelectSensorsToReport(sdmd_t *sdmd, sensor_t *sensor, int
> deviceType, int target) { char sqlQuery[256];
> char *tableName;
> char *targetName;
> int haveWork = 1;
> int ret, retVal;
> sqlite3_stmt *sq3Stmt;
+ sq3Stmt = NULL;
> /*Reset the state whatever it is*/
>
> sensor->stat = SENSOR_DATA_INVALID;
>
> do {
>
> switch (deviceType) {
> case SENSOR_TYPE_AN:
> tableName = "analog";
> break;
> case SENSOR_TYPE_DIG:
> tableName = "digital";
> break;
> case SENSOR_TYPE_UNKNOWN:
> default:
> tableName = NULL;
> break;
> }
>
>
> switch (target) {
> case TARGET_MQTT:
> targetName = "read_mqtt";
> break;
> case TARGET_MODBUS: /*This makes no sence,
> hence it is done in an other process... but whatever*/
> targetName = NULL; break;
> default:
> targetName = NULL;
> break;
> }
>
> if (targetName == NULL || tableName == NULL) {
> retVal = DB_SQLITE_ERR;
> break;
> }
>
> sprintf(sqlQuery, "SELECT rowid, sensor_id, value, time
> FROM %s WHERE %s=0 LIMIT 1;", tableName,
> targetName); /*Read one by one. I don't want to block
> DB access.*/
>
> sqlite3_prepare_v2(sdmd->db, sqlQuery, -1, &sq3Stmt,
> NULL);
>
> while (haveWork == 1) {
> ret = sqlite3_step(sq3Stmt);
> switch (ret) {
> case SQLITE_ROW:
> /*Read the data*/
> sensor->id =
> sqlite3_column_int64(sq3Stmt, 0); sensor->sensorId =
> sqlite3_column_int64(sq3Stmt, 1); sensor->value =
> sqlite3_column_double(sq3Stmt, 2); sensor->time =
> sqlite3_column_int64(sq3Stmt, 3); sensor->stat =
> SENSOR_DATA_VALID; sprintf(sensor->name, "%s_%d",
> tableName, sensor->sensorId); break;
> case SQLITE_DONE:
> haveWork = 0;
> retVal = DB_SQLITE_OK;
> break;
> case SQLITE_BUSY:
> debug(sdmd->dbg, DEBUG_ERR, "%s():
> sqlite (BUSY): %s\n", __func__,
> sqlite3_errmsg(sdmd->db)); haveWork = 0; retVal =
> DB_SQLITE_ERR; break;
> default:
> haveWork = 0;
> debug(sdmd->dbg, DEBUG_ERR, "%s():
> sqlite (%d): %s\n", __func__, ret,
> sqlite3_errmsg(sdmd->db)); debug(sdmd->dbg, DEBUG_ERR,
> "%s(): sqlite query: %s\n", __func__, sqlQuery); retVal
> = DB_SQLITE_ERR; break; }
> }
> } while(0);
>
> sqlite3_finalize(sq3Stmt); /*Release the dB*/
>
> return retVal;
> }
>
>
> What do I do wrong?
I don't see anything that can trigger memory leak here.
You seems don't initialize sq3Stmt (see above +), so that you will call
sqlite3_finalize on random junk from stack on some error paths; but that
should not trigger any leaks (more likely result would be heap
corruption and crash).
And while current uses are probably safe, I'd replace sprintf with
snprintf to avoid any nasty surprises.
Maybe sqlite just use some memory for page cache (IIRC, by default,
cache size is 2MB).
Also allocated vsize can increase due to memory fragmentation (depending
on chosen malloc implementation and its fine-tuning).
You can also try running with valgrind or other memory debugger.
> Any help is appreciated.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] json1 WebPage
sqlite-users, I found a minor documentation formatting issue. On the page https://sqlite.org/json1.html in the overview section in items 7 and 15, the arguments to the functions are not all italicized. I tried to figure out how to make a ticket in the web interface to Fossil, but I couldn't figure out how to do it. I think SQLite is awesome, thanks! Brady ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] SQLite 3.16.2-3 - Incomplete support for ISO8601 strings with time zone
As explained on the linked page, right near the top:
"The date and time functions use a subset of IS0-8601 date and time formats."
SUBSET means some of, but not all of. ISO-8601 permits 24 hours, 60 minutes,
and 61 seconds in a day. However, this is not part of the "supported subset"
either. The supported subset permits hours in the range 00 through 23, minutes
in the range from 00 through 59, seconds in the range from 00.0 through
59.999.
The "acceptable" offset formats are [ ]{+|-}HH:MM
The space is optional, the + or - is required, the hours MUST be two digits
with a value between 00 and 14, the : is required, and the minutes must be two
digits between 00 and 59. +00:00 or -00:00 can be expressed as Z.
The subset of ISO-8601 which omits the ":" and minutes in the offset (or
leading zero's) is not part of the supported subset. Nor is using a "named"
offset, other than the Z alias for +00:00 to indicate GMT.
Note that this is NOT the timezone. It is the instant offset such that the
datetime string is always a "proper" and identifiable "instant in time". You
can only guess what the timezone/localization is from all the available
timezones which express the given "instant offset" at the GMT time given. Even
then you could be wrong.
Making the ":" or the minutes optional in the instant offset from Zulu time
would be an enhancement or extension to the supported "subset of ISO-8601".
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-dev [mailto:sqlite-dev-boun...@mailinglists.sqlite.org]
>On Behalf Of François Lepage
>Sent: Sunday, 5 November, 2017 07:37
>To: sqlite-...@mailinglists.sqlite.org
>Subject: [sqlite-dev] SQLite 3.16.2-3 - Incomplete support for
>ISO8601 strings with time zone
>
>Hello,
>
>From sqlite.org
>
>[...]the built-in Date And Time Functions
> of SQLite are capable of
>storing dates and times as TEXT, REAL, or INTEGER values:
>
>* TEXT as ISO8601 strings ("-MM-DD HH:MM:SS.SSS").
>* [...]
>
>According to my tests, there are some acceptable ISO8601 strings that
>can' t be processed with SQLite "Date And Time Functions", at least
>in version 3.16.2-3, when a time zone designator is used in numeric
>format.
>
>Consider the following valid ISO8601 strings :
>
>* 2017-11-05 14:05:43+00:00 - WORKS
>
>* 2017-11-05 14:05:43 -0400 - DOES NOT WORKS
>
>
>The space between the "ss" and the time zone seems to be tolerated (a
>good thing).
>
>Acceptable formats
>Z
>±hh:mm
>±hhmm
>±hh
>
>The fix would probably need to be implemented around here :
>
>date.c
>static int getDigits(const char *zDate, const char *zFormat, ...){
>[...] }
>
>static int parseTimezone(const char *zDate, DateTime *p) { [...] }
>static int parseHhMmSs(const char *zDate, DateTime *p){ [...] }
>
>
>My programming experience does not allow me to submit the patch
>myself. If anyone could fix this it would be great.
>
>Thanks,
>
>Francois
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected result from SELECT * FROM (subquery);
The table contains a single row with a single column that contains a string. Like this explanation. Much better than my attempt. What the outer select sees in its from clause is an ["unnamed-table"] that [contains one row containing 'tab1']. Thank you for pushing this point. The point I was trying to make and obviously failed was that the string returned by the select statement nested in the from clause is just a string which happens to be the same text as the name of a table. Looking the same does not make it the same. Klaus email signature Klaus Maas On 2017-11-05 14:20, Peter Da Silva wrote: The table contains a single row with a single column that contains a string. That doesn't make it a string. It's still a table. When you say "select ... from table" it doesn't matter where the table came from, it's still an operation on a table. You are not performing "select ... from 'tab1';", you're performing "select ... from unnamed-table" where "unnamed-table" contains one row containing 'tab1'. The result of *that* select is yet another unnamed table that the sqlite3 shell displays for you. Even if you perform select * from (select * from (select * from ( ... ) ) The result is still a table. Select is not an indirection operator like accessing an element of an array or a structure. On 2017-11-05, at 05:39, Klaus Maas wrote: Yes, correct. But the contents of the returned table are not objects, but merely values. In this case the returned table contains a single string value which happens to be the name of a table, but it is not the table. Or do I get this wrong? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: merge joins (preferably through a hint)
On 5 Nov 2017, at 11:04am, Richard Hipp wrote: > SQLite does do a merge in some cases, though not for what you would > traditionally call a join. For example, SQLite will do a merge to > combine the two halves of this query: > >SELECT a,b,c FROM tab1 UNION SELECT x,y,z FROM tab2 ORDER BY 1,2,3; In case it’s not clear from the above, SQL processes the UNION before the ORDER BY clause. If "merge join" didn’t already have a definition, we could use the term for that. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected result from SELECT * FROM (subquery);
The table contains a single row with a single column that contains a string. That doesn't make it a string. It's still a table. When you say "select ... from table" it doesn't matter where the table came from, it's still an operation on a table. You are not performing "select ... from 'tab1';", you're performing "select ... from unnamed-table" where "unnamed-table" contains one row containing 'tab1'. The result of *that* select is yet another unnamed table that the sqlite3 shell displays for you. Even if you perform select * from (select * from (select * from ( ... ) ) The result is still a table. Select is not an indirection operator like accessing an element of an array or a structure. On 2017-11-05, at 05:39, Klaus Maas wrote: > Yes, correct. > > But the contents of the returned table are not objects, but merely values. > > In this case the returned table contains a single string value which > happens to be the name of a table, but it is not the table. > > Or do I get this wrong? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3PcacheCleanAll() is CPU Bound on Windows 2K8R2
Good Morning all, I am hoping to get some help with an issue that we see periodically. We have a machine that does low volume, low stress but long term testing of our application. We have been able to get SQLite to become CPU bound after about 3 days of longevity testing on Windows Server 2008 R2. We have not been able to reproduce this on other OSes. Our test is pretty basic. We create the database, then every 50 minutes +- 25 min (random variation) we insert a record. That's about it. When we do the 'COMMIT TRANSACTION' we hang in this state. The stack trace is below. Has anyone seen something like this? ntoskrnl.exe!memset+0x85fntoskrnl.exe!IoFreeErrorLogEntry+0x6c3ntoskrnl.exe!KeSynchronizeExecution+0x692dsl.dll!sqlite3PcacheCleanAll+0xa5dsl.dll!pager_end_transaction+0x173dsl.dll!pager_playback+0x317dsl.dll!sqlite3PagerRollback+0xa2dsl.dll!sqlite3BtreeRollback+0xa7dsl.dll!sqlite3RollbackAll+0x94dsl.dll!sqlite3VdbeHalt+0x1eadsl.dll!sqlite3VdbeExec+0x69c0dsl.dll!sqlite3Step+0xeddsl.dll!sqlite3_step+0xadMyApp.exe!DBCommitTx+0xeb Any thoughts would be greatly appreciated. Thanks,Jacob ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected result from SELECT * FROM (subquery);
Yes, correct. But the contents of the returned table are not objects, but merely values. In this case the returned table contains a single string value which happens to be the name of a table, but it is not the table. Or do I get this wrong? email signature Klaus Maas Klaus On 2017-11-05 12:31, Peter Da Silva wrote: On 2017-11-05, at 05:28, Klaus Maas wrote: I thought it was because what SQL returns is a value (in this case a string) and not an object? The string value might be the same as the name of an object, but is not the object. Select returns a table, not a name or a string. The outer select operates on this unnamed table, not any particular cell in it. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected result from SELECT * FROM (subquery);
On 2017-11-05, at 05:28, Klaus Maas wrote: > I thought it was because what SQL returns is a value (in this case a string) > and not an object? > > The string value might be the same as the name of an object, but is not the > object. Select returns a table, not a name or a string. The outer select operates on this unnamed table, not any particular cell in it. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected result from SELECT * FROM (subquery);
I thought it was because what SQL returns is a value (in this case a string) and not an object? The string value might be the same as the name of an object, but is not the object. email signature Klaus Maas Klaus On 2017-11-05 11:51, Richard Hipp wrote: On 11/5/17, Shane Dev wrote: In sqlite3, I executed the following statements - sqlite> select name from tabs where rowid=1; tab1 sqlite> select * from tab1; first rec sqlite> select * from (select name from tabs where rowid=1); tab1 I expected the last statement to evaluate the subquery first to be 'tab1' and then execute SELECT * FROM tab1 to yield 'first rec'. Why didn't this happen? Because that is not the way SQL works. The statement SELECT * FROM (SELECT name FROM tabs WHERE rowid=1); is logically equivalent to: CREATE TEMP TABLE "some-random-name" AS SELECT name FROM tabs WHERE rowid=1; SELECT * FROM "some-random-name"; DROP TABLE "some-random-name"; SQL works on a compile-then-execute model. Each SQL statement is first analyzed and compiled into bytecode or into machine code or some other executable format. Then the resulting compiled code is run to generate a result. The names of tables and columns are fixed at compile-time and cannot be modified at runtime, since to do so would require on-the-fly changes to the compiled code. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: merge joins (preferably through a hint)
On 11/5/17, Davor Josipovic wrote: > Merge joins could be an incredible optimization in some cases for large > queries and would make sqlite much faster in such cases. SQLite does do a merge in some cases, though not for what you would traditionally call a join. For example, SQLite will do a merge to combine the two halves of this query: SELECT a,b,c FROM tab1 UNION SELECT x,y,z FROM tab2 ORDER BY 1,2,3; You are thinking that perhaps queries such as the following might be faster using a merge: SELECT * FROM tab1 JOIN tab2 ON tab1.a=tab2.x; I disagree. In order to do this as a merge, we'd need indexes on both tab1.a and tab2.x. (In order for the merge to be practical, and not require an arbitrary amount of auxiliary storage, both indexes would need to be UNIQUE.) But if you already either one of those two indexes, then the nested-loop join will already be blazing fast. It is difficult to see how switching to a merge join would make it go any faster. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected result from SELECT * FROM (subquery);
On 11/5/17, Shane Dev wrote: > > In sqlite3, I executed the following statements - > > sqlite> select name from tabs where rowid=1; > tab1 > sqlite> select * from tab1; > first rec > > sqlite> select * from (select name from tabs where rowid=1); > tab1 > > I expected the last statement to evaluate the subquery first to be 'tab1' > and then execute SELECT * FROM tab1 to yield 'first rec'. Why didn't this > happen? Because that is not the way SQL works. The statement SELECT * FROM (SELECT name FROM tabs WHERE rowid=1); is logically equivalent to: CREATE TEMP TABLE "some-random-name" AS SELECT name FROM tabs WHERE rowid=1; SELECT * FROM "some-random-name"; DROP TABLE "some-random-name"; SQL works on a compile-then-execute model. Each SQL statement is first analyzed and compiled into bytecode or into machine code or some other executable format. Then the resulting compiled code is run to generate a result. The names of tables and columns are fixed at compile-time and cannot be modified at runtime, since to do so would require on-the-fly changes to the compiled code. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unexpected result from SELECT * FROM (subquery);
Hello, In sqlite3, I executed the following statements - sqlite> select name from tabs where rowid=1; tab1 sqlite> select * from tab1; first rec sqlite> select * from (select name from tabs where rowid=1); tab1 I expected the last statement to evaluate the subquery first to be 'tab1' and then execute SELECT * FROM tab1 to yield 'first rec'. Why didn't this happen? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature request: merge joins (preferably through a hint)
Are there any plans to implement merge joins in sqlite? As far as I am aware, only nested loops are currently supported. Merge joins could be an incredible optimization in some cases for large queries and would make sqlite much faster in such cases. Personally, I would like to have this option rather as a sql HINT, than as an optimizer option, since the optimizer now, small and efficient as it is, does a great job. The merge join HINT could be used to greatly optimize specific queries - i.e. queries where poking the index many many times on an already ordered set is inefficient. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
