[sqlite] Warnings during compilation on Linux ia64
Hi, I compile SQLite 3.3.4 on Linux ia64 box and noticed the following warnings during the compilation: ./src/table.c: In function `sqlite3_get_table': ./src/table.c:149: warning: cast to pointer from integer of different size ./src/table.c: In function `sqlite3_free_table': ./src/table.c:194: warning: cast from pointer to integer of different size Seems to be harmless, but anyway - the following will fix it away: 149c149 res.azResult[0] = (char*)res.nData; --- res.azResult[0] = (char*)(size_t)res.nData; 191c191 int i, n; --- size_t i, n; 194c194 n = (int)azResult[0]; --- n = (size_t)azResult[0]; -- Alexei Alexandrov
[sqlite] SQLite tests failures on Linux IA64
Hi, I'm running SQLite 3.3.4 tests suite on Linux (Red Hat 4 Update 2) Itanium box. I got the following test failures: 4 errors out of 24863 tests Failures on these tests: printf-8.1 printf-8.2 printf-9.5 types3-1.3 Information on the console: printf-8.1... Error: integer value too large to represent printf-8.2... Error: integer value too large to represent printf-9.5... Expected: [1 C ] Got: [] types3-1.3... Expected: [wideInt integer] Got: [int integer] Is it expected? -- Alexei Alexandrov
[sqlite] Unaligned access in SQLite on Itanium
Hi, I don't know whether it's been already reported or not, so anyway. There are places in SQLite where unaligned access exception is generated on Itanium. The unaligned access means that someone tries to read or write memory crossing 8-bytes boundary. Usually this occur as a result of pointers casting. I investigated it a little bit and found that the reason is this line in func.c file: p-sum += sqlite3_value_int64(argv[0]); It is a part of sumStep function. p-sum is long double, that is 16 bytes size. It means that it must be aligned on 16 bytes boundary, but it is 0x60094248 in my case. The reason it is not aligned is that address returned by sqlite3_aggregate_context is not aligned on 16 bytes boundary which is a must for ia64 systems and which is the alignment of malloc returned pointers. So the reason comes to Mem structure and I can see that zShort member is not forced to be aligned on 16 bytes and in fact it is aligned on 8 bytes. This is where I got stuck because I don't know whether Mem structure is on-disk structure or not. Is it OK to change it? P.S. To catch the unaligned access on Itanium, the easiest way is to say prctl --unaligned=signal ulimit -c unlimited After that unaligned access will cause SIGBUS and core dump will be generated. P.P.S. Unaligned access can lead to serious performance degradations and on some OSes (HP-UX) there isn't default unaligned access handler so it will just crash. -- Alexei Alexandrov
[sqlite] Re: Unaligned access in SQLite on Itanium
Also, I've verified that this is exactly the reason of unaligned accesses by making SumCtx bigger so that it doesn't fit into NBFS bytes. After that I was able to execute all tests without unaligned access exceptions. But those printf-8.1 printf-8.2 printf-9.5 types3-1.3 still fail, so it is not related. On 3/25/06, Alexei Alexandrov [EMAIL PROTECTED] wrote: Hi, I don't know whether it's been already reported or not, so anyway. There are places in SQLite where unaligned access exception is generated on Itanium. The unaligned access means that someone tries to read or write memory crossing 8-bytes boundary. Usually this occur as a result of pointers casting. I investigated it a little bit and found that the reason is this line in func.c file: p-sum += sqlite3_value_int64(argv[0]); It is a part of sumStep function. p-sum is long double, that is 16 bytes size. It means that it must be aligned on 16 bytes boundary, but it is 0x60094248 in my case. The reason it is not aligned is that address returned by sqlite3_aggregate_context is not aligned on 16 bytes boundary which is a must for ia64 systems and which is the alignment of malloc returned pointers. So the reason comes to Mem structure and I can see that zShort member is not forced to be aligned on 16 bytes and in fact it is aligned on 8 bytes. This is where I got stuck because I don't know whether Mem structure is on-disk structure or not. Is it OK to change it? P.S. To catch the unaligned access on Itanium, the easiest way is to say prctl --unaligned=signal ulimit -c unlimited After that unaligned access will cause SIGBUS and core dump will be generated. P.P.S. Unaligned access can lead to serious performance degradations and on some OSes (HP-UX) there isn't default unaligned access handler so it will just crash. -- Alexei Alexandrov -- Alexei Alexandrov
Re: [sqlite] SQLite tests failures on Linux IA64
Alexei Alexandrov [EMAIL PROTECTED] wrote: Hi, I'm running SQLite 3.3.4 tests suite on Linux (Red Hat 4 Update 2) Itanium box. I got the following test failures: 4 errors out of 24863 tests Failures on these tests: printf-8.1 printf-8.2 printf-9.5 types3-1.3 Information on the console: printf-8.1... Error: integer value too large to represent printf-8.2... Error: integer value too large to represent printf-9.5... Expected: [1 C ] Got: [] types3-1.3... Expected: [wideInt integer] Got: [int integer] Is it expected? These are the kinds of errors I would expect to see if you compiled the test suite using an archaic version of Tcl. What Tcl version are you using? None of these errors is of any consequence. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Unaligned access in SQLite on Itanium
Alexei Alexandrov [EMAIL PROTECTED] wrote: Hi, I don't know whether it's been already reported or not, so anyway. There are places in SQLite where unaligned access exception is generated on Itanium. The unaligned access means that someone tries to read or write memory crossing 8-bytes boundary. Usually this occur as a result of pointers casting. I investigated it a little bit and found that the reason is this line in func.c file: p-sum += sqlite3_value_int64(argv[0]); This was fixed by check-in [3084] which occurred a few hours after 3.3.4 was released (2006-Feb-11). http://www.sqlite.org/cvstrac/chngview?cn=3084 -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Unaligned access in SQLite on Itanium
As far as I understand, this fix will take the problem away only because SumCtx is bigger now. But the problem with sqlite3_aggregate_context will remain: the pointer returned will not be aligned properly if the size of the context is less or equal 32 bytes. Am I correct? On 3/25/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Alexei Alexandrov [EMAIL PROTECTED] wrote: Hi, I don't know whether it's been already reported or not, so anyway. There are places in SQLite where unaligned access exception is generated on Itanium. The unaligned access means that someone tries to read or write memory crossing 8-bytes boundary. Usually this occur as a result of pointers casting. I investigated it a little bit and found that the reason is this line in func.c file: p-sum += sqlite3_value_int64(argv[0]); This was fixed by check-in [3084] which occurred a few hours after 3.3.4 was released (2006-Feb-11). http://www.sqlite.org/cvstrac/chngview?cn=3084 -- D. Richard Hipp [EMAIL PROTECTED] -- Alexei Alexandrov
Re: [sqlite] Unaligned access in SQLite on Itanium
Alexei Alexandrov [EMAIL PROTECTED] wrote:
As far as I understand, this fix will take the problem away only
because SumCtx is bigger now. But the problem with
sqlite3_aggregate_context will remain: the pointer returned will not
be aligned properly if the size of the context is less or equal 32
bytes. Am I correct?
The context should be 8-byte aligned regardless. And after
check-in [3084] there are no long doubles stored in the context
so 8-byte alignment is sufficient - provided of course that you
do not implement your own private aggregate functions that
require a 16-byte aligned context.
The Mem structure does not appear on disk or in any API (except
as the opaque structure pointer sqlite3_value*) so it can be
revised as needed to force 16-byte alignment. Perhaps
the following definition of Mem would work better:
struct Mem {
u16 flags;
u8 type;
u8 enc;
char *z;
int n;
i64 i;
double r;
char zShort[NBFS];
void (*xDel)(void *);
};
Assuming the entire structure is 16-byte aligned and pointers
and integers are all 8-bytes and 8-byte aligned, then there
would be 4 bytes of padding between Mem.enc and Mem.z. This
would result in zShort appearing on a 16-byte boundary, would
it not?
--
D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] SQLite tests failures on Linux IA64
Alexei Alexandrov [EMAIL PROTECTED] wrote: I use those that come with the distribution. Since it's one of the latest distros (Red Hat EL 4 Update 2), it's not that archaic: $ rpm -qa | grep tcl tcl-devel-8.4.7-2 tcl-8.4.7-2 That should be sufficient. printf-8.1... Error: integer value too large to represent printf-8.2... Error: integer value too large to represent printf-9.5... Expected: [1 C ] Got: [] I really have no clue what is causing these problems and having no ia64 machine, I have no way to debug. types3-1.3... Expected: [wideInt integer] Got: [int integer] The types3-1.3 error occurs because on ia64, a regular old integer is sufficient to hold the value whereas on an ix86 machine, a long long int is required. No biggie. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] SQLite: which platforms are supported?
Alexei Alexandrov [EMAIL PROTECTED] wrote: Hi, We consider using SQLite for some of our applications and I would like to a= sk whether there are existing cases of using SQLite on the following OSes: - Mac - Windows - Linux and with following architectures - x86 - x86_64 - ia64 I just would like to get some information about whether there are any showstoppers or serious problems that we can face with if we try to use SQLite on those platforms. From your subsequent posts, I presume that you are figuring out for yourself that there are no showstoppers, though perhaps some minor alignment issues on ia64. I personally test each release on ix86 Linux (currently SuSE 9.2), on Win2K (running under VMWare on the afore mentioned SuSE box) and on MacOS 10.4 on a G5. That's all the hardware I have access to. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] SQLite: which platforms are supported?
From your subsequent posts, I presume that you are figuring out for yourself that there are no showstoppers, though perhaps some minor alignment issues on ia64. I personally test each release on ix86 Linux (currently SuSE 9.2), on Win2K (running under VMWare on the afore mentioned SuSE box) and on MacOS 10.4 on a G5. That's all the hardware I have access to. Yes, it seems that there are no showstoppers so far. Your test package is a great thing and I really like it. Though, I hardly will be able to use it on Windows x86_64/ia64 because I'm not sure that there is TCL available for those architecture on Windows. A side note: from previous experience with some other DB engines I know that sometimes alignment issues are not minor at all. They can be deeply in on-disk format and sometimes it is difficult to avoid them without having to change the on-disk format. This is why I'm checking it out early in the game. So far, it seems to be OK. Thanks to you for that. P.S. As kind of access to additional hardware have you ever heard about HP testdrive machines? You can take a look at it here: http://www.testdrive.hp.com/accounts/register.shtml -- Alexei Alexandrov
Re: [sqlite] SQLite: which platforms are supported?
Alexei Alexandrov [EMAIL PROTECTED] wrote: A side note: from previous experience with some other DB engines I know that sometimes alignment issues are not minor at all. They can be deeply in on-disk format and sometimes it is difficult to avoid them without having to change the on-disk format. SQLite uses a byte-order and alignment independent on-disk format so that SQLite databases can be freely copied between machines with different processor and memory architectures. SQLite *never* stores an in-memory data structure directly to disk. So any alignment issues that might come up (and they do from time to time) are easily fixed. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Unaligned access in SQLite on Itanium
Alexei Alexandrov [EMAIL PROTECTED] wrote:
Perhaps the following definition of Mem would work better:
struct Mem {
u16 flags;
u8 type;
u8 enc;
char *z;
int n;
i64 i;
double r;
char zShort[NBFS];
void (*xDel)(void *);
};
Not exactly, since 'int' is still 4 bytes on Linux ia64. long/size_t
is 8 bytes. In fact, I think that the rightest thing here would be to
put zShort at the beginning of the structure, because in this case it
would get the same alignment as returned from malloc and the rest of
fields are strictly typed so that they are aligned by compiler
properly.
But when I try to put zShort at the beginning, some strange thing
happens - SQLite doesn't like it. I start SQLite shell, and it says
immediately (or when I create a simple table):
Come to think of it, I there are some places in the code that
assume that zShort[] is at the end of the structure. There
are places that memcpy() the first part of the structure and
ignore zShort[]. So moving zShort[] to any other place in the
structure will not work unless those places are changed. And
those places are there for efficiency reasons so I am reluctant
to change them.
Perhaps a better fix is this:
struct Mem {
i64 i;
double r;
char *z;
int n;
u16 flags;
u8 type;
u8 enc;
void (*xDel)(void *);
union {
long double notUsed1;
char zShort[NBFS];
};
};
The compiler would then (hopefully) insert an appropriate
amount of padding prior to zShort so that it had the same
alignment restructions as a long double.
This approach uses an anonymous union, which I confess is
a C construct that I have never in 22 years of C programming
had the occasion to employ. It seems to work well enough
using gcc 3.3.4. But down in my gut I have this nagging
fealing that it will likely break on some compilers.
--
D. Richard Hipp [EMAIL PROTECTED]
[sqlite] Column names in a UNION
Who can tell me what the correct column names should be for a UNION. For example: SELECT a, b FROM t1 UNION SELECT x, y FROM t2; The result set of the query above has two columns. Should those columns be named a and b or x and y? Does anybody know what the SQL standard says? Do all the other SQL database engines get it right or is there some disagreement? -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Column names in a UNION
Microsoft SQL seems to use the column names from the first select. So that would be a, b in your example. Rob - Original Message - From: [EMAIL PROTECTED] To: Sqlite-users sqlite-users@sqlite.org Sent: Saturday, March 25, 2006 3:45 PM Subject: [sqlite] Column names in a UNION Who can tell me what the correct column names should be for a UNION. For example: SELECT a, b FROM t1 UNION SELECT x, y FROM t2; The result set of the query above has two columns. Should those columns be named a and b or x and y? Does anybody know what the SQL standard says? Do all the other SQL database engines get it right or is there some disagreement? -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] Questions to autocommit mode
Hello, I have some questions to autocommit mode of SQLite 3.3.4 and its transactions. Did I understand it right that new or changed row data is only written to disk when the db connection is closed with sqlite3_close? Did I understand it right that after a transaction commit, autocommit is enabled again and the data from the transaction is written to disk not before the db connection is closed like in autocommit mode with sqlite3_close? Is there a way to force a diskwrite without closing the db connection? Thanks Markus
Re: [sqlite] Questions to autocommit mode
Markus Kolb [EMAIL PROTECTED] wrote: Hello, I have some questions to autocommit mode of SQLite 3.3.4 and its transactions. Did I understand it right that new or changed row data is only written to disk when the db connection is closed with sqlite3_close? No. Data is guaranteed to be written to disk when you COMMIT, and a COMMIT happens automatically after every statement in autocommit mode. SQLite transactions are (among other things) Durable. That means that all the data is safely on the disk surface before COMMIT returns (autocommit or explicit commit). You can take an power failure or OS crash at any point after a COMMIT and the data will survive (assuming your disk drive doesn't get wasted - nothing really we can do about that.) Where did you get the idea that data is only written to disk on close? Is there some point in the documentation that needs to be clarified? Did I understand it right that after a transaction commit, autocommit is enabled again Yes. and the data from the transaction is written to disk not before the db connection is closed like in autocommit mode with sqlite3_close? No. See above. Is there a way to force a diskwrite without closing the db connection? Yes. See above. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] Wierd between results
I am not sure if I am being crazy, but I seem to be getting a wierd result when using 'BETWEEN'. if use SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10; I get 0 results but if I do SELECT count(call_id) as num_rows WHERE ring_time = 7; I get 39 results SELECT count(call_id) as num_rows WHERE ring_time 6 and ring_time 10; Also gives 0 results. Where am I going wrong? sqlite 3.2.8 windows XP Lloydie-T
Re: [sqlite] Unaligned access in SQLite on Itanium
Perhaps a better fix is this:
struct Mem {
i64 i;
double r;
char *z;
int n;
u16 flags;
u8 type;
u8 enc;
void (*xDel)(void *);
union {
long double notUsed1;
char zShort[NBFS];
};
};
The compiler would then (hopefully) insert an appropriate
amount of padding prior to zShort so that it had the same
alignment restructions as a long double.
This approach uses an anonymous union, which I confess is
a C construct that I have never in 22 years of C programming
had the occasion to employ. It seems to work well enough
using gcc 3.3.4. But down in my gut I have this nagging
fealing that it will likely break on some compilers.
Well, it's supported by most compilers today, but I try to avoid
anonymous unions in C code as well. They are fairly standard for C++,
but not for C. But the approach seems fine to me - the alignment will
be forced in this case.
Re: [sqlite] Column names in a UNION
On 3/25/06, Rob Lohman [EMAIL PROTECTED] wrote: Microsoft SQL seems to use the column names from the first select. So that would be a, b in your example. This seems to be the case for current stable versions of Firebird, Postgres and MySQL as well. -- Nemanja Corlija [EMAIL PROTECTED]
Re: [sqlite] Wierd between results
If i do SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 10 and 6; I get 633 results. Is that normal? - Original Message - From: Lloyd Thomas [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Saturday, March 25, 2006 3:48 PM Subject: [sqlite] Wierd between results I am not sure if I am being crazy, but I seem to be getting a wierd result when using 'BETWEEN'. if use SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10; I get 0 results but if I do SELECT count(call_id) as num_rows WHERE ring_time = 7; I get 39 results SELECT count(call_id) as num_rows WHERE ring_time 6 and ring_time 10; Also gives 0 results. Where am I going wrong? sqlite 3.2.8 windows XP Lloydie-T
Re: [sqlite] Wierd between results
Go back to my original problem if I change 'between 6 and 10' to 'between 6 and 9', I get the expected results. SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 9; 232 Am I finding a bug or is my syntax incorrect? - Original Message - From: Lloyd Thomas [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Saturday, March 25, 2006 4:14 PM Subject: Re: [sqlite] Wierd between results If i do SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 10 and 6; I get 633 results. Is that normal? - Original Message - From: Lloyd Thomas [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Saturday, March 25, 2006 3:48 PM Subject: [sqlite] Wierd between results I am not sure if I am being crazy, but I seem to be getting a wierd result when using 'BETWEEN'. if use SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10; I get 0 results but if I do SELECT count(call_id) as num_rows WHERE ring_time = 7; I get 39 results SELECT count(call_id) as num_rows WHERE ring_time 6 and ring_time 10; Also gives 0 results. Where am I going wrong? sqlite 3.2.8 windows XP Lloydie-T
Re: [sqlite] Wierd between results
Lloyd Thomas [EMAIL PROTECTED] wrote: I am not sure if I am being crazy, but I seem to be getting a wierd result when using 'BETWEEN'. if use SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10; I get 0 results but if I do SELECT count(call_id) as num_rows WHERE ring_time = 7; I get 39 results SELECT count(call_id) as num_rows WHERE ring_time 6 and ring_time 10; Also gives 0 results. Please send me your database by private email and I will take a look. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Column names in a UNION
Since we're on this topic, what do other databases return for these queries? sqlite select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; a|b 1|2 3|4 sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) where b = 3; b|a 3|4 sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) where b = 2; sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) where b = 2; e|b 1|2 sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) where b 0; e|b 1|2 3|4 --- Nemanja Corlija [EMAIL PROTECTED] wrote: On 3/25/06, Rob Lohman [EMAIL PROTECTED] wrote: Microsoft SQL seems to use the column names from the first select. So that would be a, b in your example. This seems to be the case for current stable versions of Firebird, Postgres and MySQL as well. -- Nemanja Corlija [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Questions to autocommit mode
[EMAIL PROTECTED] wrote: Markus Kolb [EMAIL PROTECTED] wrote: Hello, I have some questions to autocommit mode of SQLite 3.3.4 and its transactions. Did I understand it right that new or changed row data is only written to disk when the db connection is closed with sqlite3_close? No. Data is guaranteed to be written to disk when you COMMIT, and a COMMIT happens automatically after every statement in autocommit mode. SQLite transactions are (among other things) Durable. That means that all the data is safely on the disk surface before COMMIT returns (autocommit or explicit commit). You can take an power failure or OS crash at any point after a COMMIT and the data will survive (assuming your disk drive doesn't get wasted - nothing really we can do about that.) Where did you get the idea that data is only written to disk on close? Is there some point in the documentation that needs to be clarified? I got the idea from this page: http://www.sqlite.org/lockingv3.html 7.0: By default, SQLite version 3 operates in autocommit mode. In autocommit mode, all changes to the database are committed as soon as all operations associated with the current database connection complete. The SQL command COMMIT does not actually commit the changes to disk. It just turns autocommit back on. Then, at the conclusion of the command, the regular autocommit logic takes over and causes the actual commit to disk to occur. And I got the idea because my INSERTS with sqlite3_exec are not written to disk but the memory usage of my program grows with each INSERT. I am using autocommit and I call sqlite3_exec with a SQL-INSERT inside a sqlite_callback()-function which gets called for each row of a SQL-SELECT (sqlite3_exec, too). It is working but the inserts seem to stay in memory till the connection is closed and with more inserts each insert needs more time. I call my program which does many row inserts. I use the sqlite3 tool to have a look at the DB while my program is running. I do a SELECT * FROM table; and before my program has finished there is no new row result. Starting with an empty table it is empty until my prog has finished. If I kill my program after e.g. 5 minutes (in this time it has done many many INSERTS) there is no new data in the DB file afterwards. So again my question what can I do to get the data written to DB file or why there is this behavior? Thanks Markus
Re: [sqlite] Questions to autocommit mode
Markus Kolb [EMAIL PROTECTED] wrote: I am using autocommit and I call sqlite3_exec with a SQL-INSERT inside a sqlite_callback()-function which gets called for each row of a SQL-SELECT (sqlite3_exec, too). So again my question what can I do to get the data written to DB file or why there is this behavior? The transaction will not COMMIT until the outer query finishes. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Column names in a UNION
On 3/25/06, Joe Wilson [EMAIL PROTECTED] wrote: Since we're on this topic, what do other databases return for these queries? sqlite select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; a|b 1|2 3|4 mysql select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; +---+---+ | a | b | +---+---+ | 1 | 2 | | 3 | 4 | +---+---+ postgres=# select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; a | b ---+--- 1 | 2 3 | 4 (2 rows) sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) where b = 3; b|a 3|4 mysql select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 3; Empty set (0.00 sec) postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 3; a | b ---+--- (0 rows) sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) where b = 2; mysql select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 2; +---+---+ | a | b | +---+---+ | 1 | 2 | +---+---+ postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 2; a | b ---+--- 1 | 2 (1 row) sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) where b = 2; e|b 1|2 mysql select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b = 2; +---+---+ | a | b | +---+---+ | 1 | 2 | +---+---+ postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b = 2; a | b ---+--- 1 | 2 (1 row) sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) where b 0; e|b 1|2 3|4 mysql select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b 0; +---+---+ | a | b | +---+---+ | 1 | 2 | | 3 | 4 | +---+---+ postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b 0; a | b ---+--- 1 | 2 3 | 4 (2 rows) Firebird doesn't seem to support SELECT w/o FROM. -- Nemanja Corlija [EMAIL PROTECTED]
Re: [sqlite] Wierd between results
Lloyd Thomas [EMAIL PROTECTED] wrote: if use SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10; I get 0 results You DID specify a table in your actual queries, didn't you? select * from tbl; t x y -- -- -- 1 100 101 2 100 102 3 100 103 4 100 103 5 200 210 6 200 220 7 200 230 8 300 199 select count(t) from tbl where y=103; count(t) -- 2 select count(t) from tbl where y between 102 and 210; count(t) -- 5 Regards
Re: [sqlite] Wierd between results
Yeah I did. sorry about the rushed typing. DRH managed to sort it out for me. I had imported a ver 2 database to version 3 and changed a time values to integers, but forgot to change the column to integer. Lloydie-T - Original Message - From: Kurt Welgehausen [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Saturday, March 25, 2006 5:39 PM Subject: Re: [sqlite] Wierd between results Lloyd Thomas [EMAIL PROTECTED] wrote: if use SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10; I get 0 results You DID specify a table in your actual queries, didn't you? select * from tbl; t x y -- -- -- 1 100 101 2 100 102 3 100 103 4 100 103 5 200 210 6 200 220 7 200 230 8 300 199 select count(t) from tbl where y=103; count(t) -- 2 select count(t) from tbl where y between 102 and 210; count(t) -- 5 Regards
Re: [sqlite] Column names in a UNION
Oracle 8i: SQL select 1 as a, 2 as b from dual UNION ALL select 3 as b, 4 as a from dual; A B -- -- 1 2 3 4 SQL select * from (select 1 as a, 2 as b from dual UNION ALL select 3 as b, 4 as a from dual) where b = 3; no rows selected SQL select * from (select 1 as a, 2 as b from dual UNION ALL select 3 as b, 4 as a from dual) where b = 4; A B -- -- 3 4 - Original Message From: Nemanja Corlija [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Saturday, March 25, 2006 9:03:27 AM Subject: Re: [sqlite] Column names in a UNION On 3/25/06, Joe Wilson [EMAIL PROTECTED] wrote: Since we're on this topic, what do other databases return for these queries? sqlite select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; a|b 1|2 3|4 mysql select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; +---+---+ | a | b | +---+---+ | 1 | 2 | | 3 | 4 | +---+---+ postgres=# select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; a | b ---+--- 1 | 2 3 | 4 (2 rows) sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) where b = 3; b|a 3|4 mysql select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 3; Empty set (0.00 sec) postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 3; a | b ---+--- (0 rows) sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) where b = 2; mysql select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 2; +---+---+ | a | b | +---+---+ | 1 | 2 | +---+---+ postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 2; a | b ---+--- 1 | 2 (1 row) sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) where b = 2; e|b 1|2 mysql select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b = 2; +---+---+ | a | b | +---+---+ | 1 | 2 | +---+---+ postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b = 2; a | b ---+--- 1 | 2 (1 row) sqlite select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) where b 0; e|b 1|2 3|4 mysql select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b 0; +---+---+ | a | b | +---+---+ | 1 | 2 | | 3 | 4 | +---+---+ postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b 0; a | b ---+--- 1 | 2 3 | 4 (2 rows) Firebird doesn't seem to support SELECT w/o FROM. -- Nemanja Corlija [EMAIL PROTECTED]
Re: [sqlite] Column names in a UNION
Same is true for Oracle 8i/9i/10g. E. --- Rob Lohman [EMAIL PROTECTED] wrote: Microsoft SQL seems to use the column names from the first select. So that would be a, b in your example. Rob - Original Message - From: [EMAIL PROTECTED] To: Sqlite-users sqlite-users@sqlite.org Sent: Saturday, March 25, 2006 3:45 PM Subject: [sqlite] Column names in a UNION Who can tell me what the correct column names should be for a UNION. For example: SELECT a, b FROM t1 UNION SELECT x, y FROM t2; The result set of the query above has two columns. Should those columns be named a and b or x and y? Does anybody know what the SQL standard says? Do all the other SQL database engines get it right or is there some disagreement? -- D. Richard Hipp [EMAIL PROTECTED] -- * Zoner PhotoStudio 8 - Your Photos perfect, shared, organised! www.zoner.com/zps __ Stops spam 100% for your email accounts or you get paid. http://www.cashette.com
Re: AW: [sqlite] Bad performance with large joins?
As has been stated in the past SQLite is a small foot print RDBMS, one of the things keeping it small is that it does not have a large query optimizer. One of the things that can happen is if you have lots of indexes the optimizer may pick something non optimum My tricks are as follows: 1. reorder the from tables 2. get the optimizer to ignore indexes that I know cannot help I do this in the where clause, by adding a zero or concatenating a depending upon the data type examples: where sex = M changed to where sex|| = M where children = 2 changed to where children+0 = 2 Steffen Schwigon wrote: Christian Schwarz [EMAIL PROTECTED] writes: Practically one such line for each table and each column. Why on each column? I just took the existing DB-import-script from that project. But ... For example, when your where-clause contains columns A, B and C (in this order) you should create *one* index on A, B and C. ... you are right, creating a combined index solves the speed problem. Thanks. Separate indexes on column A, B and C are not that useful. In this case, SQLite would most probably use the separate index on column A. Which would be a pity, wouldn't it? Postgres for instance seems to do something more clever there, at least it's much faster, even with the trivial setting of an index on each column. Anyway, thanks for your answer. GreetinX Steffen
Re: AW: [sqlite] Bad performance with large joins?
Jim Dodgen [EMAIL PROTECTED] wrote: 2. get the optimizer to ignore indexes that I know cannot help I do this in the where clause, by adding a zero or concatenating a depending upon the data type examples: where sex = M changed to where sex|| = M It is more efficient to use a unary + operator, like this: where +sex = 'M' The unary + is a no-op (no code is generated for it) but it does disqualify the column it prepends to for use in an index just like the || operator.
