[sqlite] Feature request: degenerates in Lemon's report
Hello,
Could you allow an outputting of autoreduced states when they produce
conflicts? I have noticed that such errors are caused by small and
hard-to-find remnants in a grammar file. There is no other way to detect
such errors. A few lines are getting the problem off (function
lemon.c:ReportOutput):
==
--- "../../sqlite-src-324/tool/lemon.c" 2018-06-04
21:51:19.0 +0200
+++ lemon.c 2018-07-09 19:58:39.852430500 +0200
@@ -3294,9 +3740,19 @@
fp = file_open(lemp,".out","wb");
if( fp==0 ) return;
- for(i=0; inxstate; i++){
+ for(i=0; instate; i++){
stp = lemp->sorted[i];
-fprintf(fp,"State %d:\n",stp->statenum);
+if( i>=lemp->nxstate ){
+ for(ap=stp->ap; ap!=0; ap=ap->next){
+if( ap->type==SSCONFLICT ) break;
+if( ap->type==SRCONFLICT ) break;
+if( ap->type==RRCONFLICT ) break;
+ }
+ if( ap==0 ) continue;
+ fprintf(fp,"Degenerated state %d:\n",stp->statenum);
+} else {
+ fprintf(fp,"State %d:\n",stp->statenum);
+}
if( lemp->basisflag ) cfp=stp->bp;
else cfp=stp->cfp;
while( cfp ){
==
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Minor parser bug
Hello,
SQLite until the newest one:
sqlite> VALUES (1),();
Error: no tables specified
should be:
sqlite> VALUES (1),();
Error: near ")": syntax error
Fix:
src/parse.y:
values(A) ::= values(A) COMMA LP exprlist(Y) RP. {
to
values(A) ::= values(A) COMMA LP nexprlist(Y) RP. {
(missing ``n'' before ``exprlist'').
Desired effect is achieved (i.e. error, because that statement is not
preparable), however the message is misleading.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] https://www.sqlite.org/cgi/src/info/7fa8f16e586a52ac
Hello,
** ieee754(4503599627370496,972) -> +Inf
** ieee754(4503599627370496,972) -> -Inf
Missing - in the second mantissa, and a next missing -:
}else if( m==0 && e>1000 && e<1000 ){
sqlite3_result_double(context, 0.0);
return;
}
gives:
ieee754(0,0) == 4.5036e+015
ieee754(0,-1) == 2.2518e+015
ieee754(0,-10) == 4.39805e+012
ieee754(0,-100) == 3.55271e-015
ieee754(0,1) == 9.0072e+015
ieee754(0,10) == 4.61169e+018
ieee754(0,100) == 5.70899e+045
ieee754(0,-1074) == 2.22507e-308
ieee754(0,-1075) == 0
ieee754(0,971) == 8.98847e+307
ieee754(0,972) == 1.#INF
ieee754(4503599627370496,972) == 1.#INF
ieee754(-4503599627370496,972) == -1.#INF
BTW. What is a problem with not checking an exponent if a mantissa == 0?
What is this checking for? What results would be expected (other then 0)
if an exponent was out of (-1000;1000) range? That checking is from the
beginning, so I cannot deduce what rationale was for it. Info states that:
** ieee754(Y,Z)
**[...]
** In the second form, Y and Z are integers which are the mantissa and
** base-2 exponent of a new floating point number. The function returns
** a floating-point value equal to Y*pow(2,Z).
0 * 2^?? == 0. Zero times positive, finite number gives zero always.
There is no Inf in i64 type to produce NaN from 0 * 2^Inf.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3AtoF Handling of high numbers
Hello, On 2018-05-17 15:07, Stiefsohn, Roman wrote: Hello, i found out that SQLite is having problems with converting high numbers from string to double, located in the function "sqlite3AtoF": Yes -- it is having problems, however the problems are located in a compiler which lacks long double support and not in ``sqlite3AtoF''. Below is a table of input strings, and the returned double values, tested on Windows 7 64 bit with SQLite version 3.23.1: Input String: Converted Result 1.7976931348623152e+3081.7976931348623153e+308 1.7976931348623154e+3081.7976931348623157e+308 1.7976931348623155e+3081.7976931348623157e+308 1.7976931348623156e+3081.7976931348623157e+308 1.7976931348623157e+308INF 1.7976931348623158e+308INF Ok - should be: ...152e+308 => ...151e+308 ...154e+308 => ...153e+308 ...155e+308 => ...155e+308 ...156e+308 => ...155e+308 ...157e+308 => ...157e+308 ...158e+308 => ...157e+308 Use a compiler supporting long doubles -- results will be more adequate. Conversion must be done using higher precision then a destination type's one. There exist two operations (thus a rounding occurs twice), so you lose one (ln nof ops / ln 2 == 1) bit at least, what leads to not so accurate results if you are using the same precision as a precision of the destination type. In a border case: 1.7976931348623158e+308 === Mantissa (exact value): 1.7976931348623158 ==> 17976931348623158 * 10^-16 == /2 ==> 8988465674311579 * 2 * 10^-16 ==> (0x1.FEEF63F97D79B * 2^53) * 10^-16. The last factor goes to an exponent. Ten's exponent (approx value, however the most accurate, what is never occurring): 10^308 * 10^-16 ==> 10^292 ==> 0x402225AF3D53E7C2BCC068B1E... (243 hex digits) ==> 0x1.008896BCF54F9|F0AF301A2C79EB7036... * 2^970 == rounding ==> 0x1.008896BCF54FA * 2^970 Now we have a final number: (0x1.FEEF63F97D79B * 2^53) * (0x1.008896BCF54FA * 2^970) ==> 0x1.FEEF63F97D79B * 0x1.008896BCF54FA * 2^1023 ==> 1.F|946300C... * 2^1023 == rounding ==> 2 * 2^1023 == ps ==> 2^1024 ==> INF. If there was no more then one additional bit of a precision then INF would not appear at all and a finite number: 1.F8 * 2^1023 would emerge. Originally the behavior was discovered when executing a select statement with a comparison of the DBL_MAX Value (1.7976931348623158e+308 ): /* Entry in Table: Id Value 1 1.1754943508222878e-38 */ select id, value from table inner join ( select 1 as Id, 1.1754943508222878e-38 as Value ) table2 on table.id = table2.id where table.Value < (table2.Value - 2.22045e-16) or table.Value > (table2.Value + 2.22045e-16) 1. Could you include working examples in the future? 2. Where is DBL_MAX? 3. You are adding/subtracting numbers with a difference of 22 in ten's exponent. Is it intentional? If so, then the WHERE condition can be reduced to ``0 < -2.22045e-16 or 0 > +2.22045e-16'' => 0 or 0 => 0 => NULL => nothing. è This query would return 1 record, although there is a value with the exact same value inside the database ( executed with the sqlite c++ api without usage of prepared parameters) 4. Due to 3. if an above query had been working, it would not have returned any records/rows. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this really the best way to do this?
Hello, On 2018-05-17 10:40, Dominique Devienne wrote: On Wed, May 16, 2018 at 8:33 PM Keith Medcalf <kmedc...@dessus.com> wrote: SELECT coalsce((select action from blocked where mail='...'), 'OK') as action; Nice one Keith. Works (see below), but I find it a bit intuitive, since returning no row is somehow not intuitively equivalent (in my mind at least) to a scalar row with a NULL value. Your query makes no distinction between these two cases. Quite justly -- let us not introduce multi levels of non--existence (a source of many bugs): (1) semi non--existence / a value ``NULL'' / a person saying ``Hello, I do not exist''; (2) virtual non--existence / a value ``UNDEFINED'' / a person not existing virtually, because it is silenced (though breathing); (3) literal non--existence / a value not existing literally / a person not existing literally; A NULL column (blocking) ``action'' in a table ``blocked'' is an error and should be avoided -- it means ``you are blocked, but I do not know how''. If someone wants a (lately bound) default action, let the name be ``DEFAULT'' -- not NULL. -- best regards Cezary H. Noweta P.S. I know it is hard to return to a life after a programming in some very flexible lang, in which every even simplest task can be done in a trillion ways :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPSERT and overlapping UNIQUE indices
Hello, A minor observation: UPSERT makes indices to be strictly and sparingly endowed with UNIQUEness. For example: uniqueness + partitioning: CREATE TABLE t(num UNIQUE, cnt DEFAULT 1); CREATE UNIQUE INDEX idx_neg ON t(num) WHERE num < 0; CREATE UNIQUE INDEX idx_pos ON t(num) WHERE num >= 0; Now ``INSERT INTO t(num) VALUES(10) ON CONFLICT(num) DO UPDATE SET cnt=cnt+1;'' fails due to a fact that ``idx_pos'' will not pass. It is a frequent and common habit to put ``UNIQUE'' everywhere an entity is unique. From SQLite 3.24, above--mentioned, partial indices (and everything what is not strictly designed to enforce an uniqueness) should be non-UNIQUE ones. IMHO, this fact is worth of recalling in the doc of UPSERT clause as the fact can become a source of a confusion. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem b building sqlite on macOS android.
Hello, On 2018-05-15 02:59, Jens Alfke wrote: On May 12, 2018, at 12:35 PM, Mark Sibly <blitzmun...@gmail.com> wrote: I am having problems building sqlite with the android NDK for macos. The problem is actually with the ioctl.h header, nothing specific to SQLite. Simply, try to define ``BIONIC_IOCTL_NO_SIGNEDNESS_OVERLOAD'' according to ``ioctl.h''s recommendations: `` * Work around unsigned -> signed conversion warnings: many common ioctl * constants are unsigned. * * Since this workaround introduces an overload to ioctl, it's possible that it * will break existing code that takes the address of ioctl. If such a breakage * occurs, you can work around it by either: * - specifying a concrete, correct type for ioctl (whether it be through a cast * in `(int (*)(int, int, ...))ioctl`, creating a temporary variable with the * type of the ioctl you prefer, ...), or * - defining BIONIC_IOCTL_NO_SIGNEDNESS_OVERLOAD, which will make the * overloading go away. '' -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] probably recursive?
Hello, On 2018-05-04 20:02, Roman Fleysher wrote: For some reason, I did not receive email from Cezary, only comments on it. About 50% of e-mails from <sqlite-users@mailinglists.sqlite.org> is marked as SPAM by my server for unknown reason. Independently on an author. But, most importantly, could you elaborate more on how it works. I agree it is n-to-n problem. But the solution merges all data into a single cell with all pairs, which is counter to relational solution. Please, do not mind ``a single cell''. This is for the bijective mapping STRING <=> TABLE, which is caused by the fact that a recursive CTEs' state is a row/record and a whole table must be packed into one row for an ``one query'' solution. However, do not do it at home ;) You do not need (and should not) do it in an ``one query''. I'm sorry for disturbing you. Ad. solution: AFAIK your tables are created ad hoc and do not exist permanently. For each relation ``points'' my solution builds related entities (coordinates or Xs/Ys): CREATE TABLE xaxis AS SELECT x, COUNT(x) AS n FROM points GROUP BY x; CREATE TABLE yaxis AS SELECT y, COUNT(y) AS n FROM points GROUP BY y; For example for two points (1,2); (1,-4) xaxis(x, n): 1 2 yaxis(y, n): -4 1 2 1 xaxis <== points(x, y) ==> yaxis: 1 -4 1 2 ``points(x, y)'' could have (CASCADING) FOREIGN KEY x=>xaxis.x and y=>yaxis.y and DELETE TRIGGERs which could adjust counters in ``*axis'' tables (DELETEing FROM xaxis WHERE x==OLD.x AND n==0 and FROM yaxis WHERE y==OLD.y AND n==0; alternatively ``*axis'' could have UPDATE TRIGGERs which could DELETE FROM points when ``n'' column had achieved 0 or less then nX/nY -- triggers must be supressed while creating a ``*axis'' table in the latter case). Then ``iteratively'' (not ``recusively'') DELETE from ``*axis'' WHERE n < threshold until there are no too small Xs/Ys or tables are empty. I ask for details, if possible, because the actual problem that I have to solve is a bit more complicated: I have two of such lists good(x,y) and bad(x,y) with a coupling condition that if x is removed from one list, it must be removed from the other. This is easy to add for ones who understand how it works. It does complicate nearly nothing. AFAIU good/bad are points on the same ``image'' (implied by a coupling). Simply, add a column: points(x, y, is_good), or add a relational table to have two tables: points_good(x, y) and points_bad(x, y) with the same properties as the original ``points(x, y)'' -- that's all -- nothing more. If you do not want to lose your original data use Simon's solution (most effective), i.e. add ``deleted'' column to ``points'' and ``n_shadow'' column to ``*axis'' and modify/restore those columns instead of actual DELETEing/UPDATEing ``n''; alternatively (most obvious but not so effective in case of big sets of data) make copies of tables/databases. (1) DELETE FROM xaxis WHERE n < nX; DELETE FROM yaxis WHERE n < nY; (2) which fire CASCADE DELETE of relations FROM points (3) which fires DELETE TRIGGERs of points (4) which adjust referenced xaxis.n and y.axis.n (4a) and further DELETE FROM *axis WHERE n == 0 (4b) and further DELETE FROM *axis WHERE n < nX/nY (4c) and further fire UPDATE TRIGGERs of *axis, which in turn further do (4a) or (4b). I do not want to provide specified DDL statements as they would require a bit of testing -- unfortunately I cannot test SQLite now. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] probably recursive?
Hello, On 2018-05-04 03:07, R Smith wrote: On 2018/05/04 1:54 AM, Cezary H. Noweta wrote: At the beginning I would like to agree with that the problem is iterative rather then recursive one. However LOL, that might be the hackiest query I ever seen, but kudos mate, that's bloody marvellous! Thank you. ;) -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] probably recursive?
set then you will have to decrease nX/nY, append more points or generate new ``points'' table. ... just for fun. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Missing several important tricks in partial indexes
Hello, On 2018-02-27 08:27, Shevek wrote: If I create a partial index: create table a (a0, a1) create index idx on a (a0) where a1 is null; 3) The third issue is almost more major than the preceding two, and has nothing to do with covering, but with partial index selection AT ALL: If we do a select "WHERE a1 IS ?" and pass a null for the bind-value of ? it will never select the partial index, even though it's eligible. This is due to a fact that a code for VDBE is generated before any parameter substitution takes place. Such code contains a parameter substitution command (OP_Variable). Said code does not contain two branches: (1) for param == NULL; (2) for param != NULL. IMHO, a problem, you are describing, comes from a never-ending problem of SQL optimization: PREPARE vs. EXECUTION efficiency. It has been mentioned by DRH a few weeks ago, so it is considered by the team. There are plenty of things, which can be done by an optimizer. What about INDEX... WHERE a1 > 0 AND a1 < 100 AND a1 <> 78, and SELECT... WHERE a1 > 10 AND a1 < 20 OR 10*a1 = 306? Certainly, I do not think that your example falls into ``making an algebra system'' category, and I am calm that the previously-mentioned by DRH fact will be considered by the team while implementing of such optimization. Being or not NULL is one of the most frequent things which are occurring in db systems -- thus your wonderment seems to be quite justified. However it is desirable to take into account that every optimization speeds up an execution at the price of a preparation (as every index speeds up a query at the price of an update/insert) -- it is easy to say ``why such and that index is not considered in such and that case?'' -- I have not seen ``my system could speed up due to an elimination of rare optimizations''. Speeding up is done (too frequently) by adding consecutive optimizations (on an engine's side) and indices (on an user's side) without a balance. (Still, abstracting from your particular justified example of being NULL.) Not every system operates on trillion records dbs taken from a heaven and executes fancy queries -- there are also systems which update/insert something and run many relatively simple queries on a relatively small data. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange concatenation result
Hello,
On 2018-02-27 08:46, Simon Slavin wrote:
What should substr('abcd',0,-2) return? 'cd' or just 'd'? Or maybe just an
empty string?
NULL
Why?
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange concatenation result
Hello, On 2018-02-26 11:38, Hick Gunter wrote: The substr(x,y,z) function is defined only for nonzero values of y. SQlite can return whatever it feels like if you insist on providing invalid input. With "being nice to the user" and "making a best effort to return sensible data even for nonsense input" as design goals, mapping substr(x,0,z) to substr(x,1,z-1) seems quite a benign solution. ... and as such, that design could be documented. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange concatenation result
Hello, It seems that Y=0 denotes a fictitious empty position before the first one (Y=1).Is it the intended behaviour? The documentation (https://www.sqlite.org/lang_corefunc.html#substr), says nothing about this specific pattern. Even if it not intended, it will be very handy in some circumstances. Treating 0 as a non-existing position is more flexible behavior then generating errors, exceptions, roll--backs or a nuclear launch. Such behavior saves (or can save at least) time and code space on both sides: on the SQLite's side (eliminates checking against 0 and an effort related to it) and on your code's side. Additionally, the behavior is consistent and predictable -- it gives advantages only. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improper error message
Hello,
On 2018-02-20 23:59, Etienne Sanchez wrote:
Some other DBMS such as Postgres and SQL-Server implement the functions
"left" and "right". SQLite does not. But why do I get confusing error
messages when I (mistakenly) attempt to use them?
To illustrate my point:
select foo('abc', 2)
Error: no such function: foo
select left('abc', 2)
Error: near "(": syntax error
select right('abc', 2)
Error: near "(": syntax error
In the 2nd and 3rd cases I would expect a "no such function" error. The
"syntax error" messages are slightly misleading, they lead the user to
think that he has forgotten a parenthesis or a comma somewhere in the query.
As for "left", it's maybe due to the ambiguity with "left join", but then
what about "right"? (There is no ambiguity with "right join" since it is
not supported.)
Indeed, RIGHT is considered as JOIN_KW (look at
``select.c:sqlite3JoinType()'') even if not supported. Furthermore,
JOIN_KW is treated as an identifier, when in an expression: ``NATURAL'',
``LEFT'', ``OUTER'', ``RIGHT'', ``FULL'', ``INNER'', and ``CROSS'' --
all are treated as identifiers. So if you hava a column named ``left''
or ``right'', it will be treated accordingly.
``Treated'' does not mean that they are ``becoming'' identifiers, so you
cannot use them as function names: id ``('' [``DISTINCT''|``ALL''] expr
[, expr [...]] ``)''.
If you want to achieve a desired effect (i.e. ``no such function''
message), then copy block ``expr(A) ::= id(X) LP distinct(D) exprlist(Y)
RP. {...}'' in ``parse.y'' replacing ``id'' with ``JOIN_KW'', otherwise
the parser is expecting that an expression contains the sole
``left''/``right'' or a syntax error occurs.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] printf() problem padding multi-byte UTF-8 code points
Hello,
On 2018-02-17 18:39, Ralf Junker wrote:
Example SQL:
select
length(printf ('%4s', 'abc')),
length(printf ('%4s', 'äöü')),
length(printf ('%-4s', 'abc')),
length(printf ('%-4s', 'äöü'))
Output is 4, 3, 4, 3. Padding seems to take into account UTF-8 bytes
instead of UTF-8 code points.
Should padding not work on code points and output 4 in all cases as
requested?
If you are interested in a patch extending a functionality of
``printf()'' then http://sqlite.chncc.eu/utf8printf/. Adding ``l''
length modifier makes width/precision specifications being treated as
numbers of UTF-8 chars -- not bytes. ``SELECT length(printf ('%4ls',
'äöü'));'' will give 4.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] invalid date time
Hello, On 2018-02-19 13:08, Joe Mistachkin wrote: Cezary H. Noweta wrote: Use ``Flags=GetAllAsText'' when creating a SQLiteConnection. Excellent suggestion. Alternatively, you could use the GetString method on the SQLiteDataReader class. Indeed, however OP posted that ``GetString()'' does not work in his case, for unknown reason. Maybe a bit different approach will help. The GetValue method, by design, refers to the table schema so that it can convert the requested value into the declared type (which I assume is DateTime in this case). Yes and moreover, mentioned by you ``GetString()'' (which would be most concise and useful in this case as it does not influence on all connection) omits this schemata and should work in case of corrupted ``datetime'' fields/columns. I'm not sure where does the problem lie, if ``GetString()'' does not work. Additional info from OP would help. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] invalid date time
Hello,
On 2018-02-19 07:02, Olivier Leprêtre wrote:
I have an sqlite database with wrong information in a timestamp field. Using
System.Data.Sqlite, I want to get this information as a string inside a very
simple loop.
while (i < reader.FieldCount) {
txt = reader[i].ToString(); // or reader.GetString(i) or
Convert.ToString(reader.GetValue(i))
i++;
}
Problem is that if this works for all other fields (integer, varchar...) it
does not work for timestamp. Regardless reader function used, I get a
System.Format Exception Invalid DateTime when reading this field.
How can I avoid this internal cast and just get this information as a text
string, no matter its inside format ?
Use ``Flags=GetAllAsText'' when creating a SQLiteConnection.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] printf() problem padding multi-byte UTF-8 code points
Hello,
On 2018-02-18 00:36, Richard Hipp wrote:
The current behavior of the printf() function in SQLite, goofy though
it may be, exactly mirrors the behavior of the printf() C function in
the standard library in this regard.
So I'm not sure whether or not this is something that ought to be "fixed".
For the sake of sanity, such exception would be considered. I.e.
``length'' specification could mean number of ``multibyte characters''
-- not ``characters''. A C programmer has a chance to put all his
buffer, especially that there are no special provisions on multibyte
characters in the buffer (i.e. it must not begin nor end with an initial
shift state): for ( i = 0; len > i; i += 5 ) printf("%-5.5s", [i]); --
a bit non-sense but illustrates the problem.
On the other hand, SQLite's SQL has no access to memory buffers. In such
case, the C standard handles the situation (look at the end of ``s''
conversion specifier together with ``l'' flag): ``In no case is a
partial multibyte character written.''.
Is there somebody who things about a byte content of buffers, when he is
writing a software at a SQL level?
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] printf() problem padding multi-byte UTF-8 code points
Hello,
On 2018-02-18 01:46, Peter Da Silva wrote:
Printf's handling of unicode is inconsistent in other ways, too. I suspect that
there's still undefined behavior floating around in there too. Even wprintf
isn't entirely unsurprising:
You have supplied examples which are exchanged with each other and are
confirming ``unsuprisingness'':
LANG=en_US.UTF-8
Ok - so your native environment locale is ``UTF-8''.
% cat localized.c
Why that program is named ``localized'' if...
[...]
int main() {
wprintf (L"'%4ls'\n", L"äöü");
... you are using "C" locale for LC_CTYPE? Behavior entirely
unsurprising: there is no conversion from L"äöü" using "C" LC_CTYPE.
[...]
% cat delocalized.c
Why that program is named ``delocalized'' if...
[...]
setlocale(LC_ALL, "");
... you are using native environment locale (``UTF-8'') for LC_CTYPE?
Behavior entirely unsurprising: there is conversion from L"äöü" using
"UTF-8" LC_CTYPE.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIMIT versus sqlite3_step LIMIT times
Hello, On 2018-02-16 13:00, Dominique Devienne wrote: While you're technically right, I think of SQL as a declarative language, and as such I'd say giving this information to SQLite is a best practice IMHO. Unlikely in this case, but perhaps one day SQLite might be able to optimize "something" based on it. And in general, not all queries are that simple. Giving the query planner all possible information should be recommended. My $0.02. --DD Indeed, you are absolutely right -- in addition, I have made a silent assumption that OP calls SQLite from C code using a simple construct like ``for ( i = 0; nofrows > i; ++i ) ...step...'' -- there are scenarios where SQLite VDBE's loop can be faster. Ad. query planner -- I have mentioned that a cost of a full external sorting for WHERE condition can be reduced by a LIMIT clause -- this fact should be obviously considered. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIMIT versus sqlite3_step LIMIT times
Hello, On 2018-02-16 11:18, x wrote: If a query is sorted on an index is there any advantage to including LIMIT in the stmt as opposed to omitting it and stepping through the result set LIMIT times? No -- LIMIT appends an additional opcode to check the number of rows and introduces an effort related to an additional parsing. In general, LIMIT can reduce a cost of a full external sorting, however this does not apply to your query. BTW, in file ``src/select.c'' line 2377: Expr *pLimit;/* Saved values of p->nLimit */ should be Expr *pLimit;/* Saved values of p->pLimit */ -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Groups in C API
Hello,
On 2018-01-29 18:08, Stephan Buchert wrote:
But then I have a related question: to get my hands on each row in SELECTs
with GROUP BY I could write an aggregate extension function. How do I see
there, when a group ends and a new one starts? I.e. How do I implement the
xStep and xFinal C functions as requested at
https://sqlite.org/c3ref/create_function.html
SQLite manages an area of memory called ``aggregate context''. When you
call ``sqlite3_aggregate_context()'' from your ``xStep'' and/or
``xFinal'' then you will receive a pointer to that memory area, which is
kept as long as an invocation of ``xStep''/``xFinal'' considers the same
group of GROUP BY:
typedef struct myAggCtx {
int notTheFirstTime;
/* Other state variables follow */
} myAggCtx;
xStep(ctx, ...)
{
myAggCtx *actx = sqlite3_aggregate_context(ctx, sizeof(myAggCtx));
if ( 0 == actx->notTheFirstTime ) {
/* Do your starting of group stuff */
actx->notTheFirstTime = !0;
}
/* ... */
}
xFinal(ctx, ...)
myAggCtx *actx = sqlite3_aggregate_context(ctx, sizeof(myAggCtx));
if ( 0 == actx->notTheFirstTime ) {
/* First time allocated context so there was no records in the group */
}
/* ... */
}
Beware of passing the same number of bytes each time, because
``sqlite3_aggregate_context()'' does not reallocates. If an aggregate
context has been allocated previously, then
``sqlite3_aggregate_context()'' will returns the same memory area
regardless of subsequent numbers of bytes passed. I.e.
sqlite3_aggregate_context(ctx, 1) will return 1byte size memory --
subsequent call (while in the same group of GROUP BY)
sqlite3_aggregate_context(ctx, 100) will return the same 1byte size
memory.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Atoi64 bug(s)
Hello, On 2018-01-26 00:54, petern wrote: I am interested to see your solution where NUMERIC CAST has sensible interpretation between MAX_INT and MAX_REAL. The patch focuses on problems with TEXT=>NUM conversions. The sole exception is INT=>FLOAT, when a value could lose an information. IMO, simple INT saturation is not convenient for easy overflow detection in SQL. So there is work to be done where the upcasted number is large but not quite large enough for REAL saturation. Nearby upcasted INTs must sort sensibly. Indeed -- INT saturation is for more predictability of INTEGER CAST's results, when converted from TEXTs. Also, what happens to overflowing hex constants and from BLOB casts? HEX is not touched by CASTs and AFFINITIes (from TEXT). It is important to curate such patches in case the priority for execution speed/size cannot be reconciled with accuracy and generality. If your improvements make v3.23 slower or larger than v3.22, they may be rejected. My patch is not to be released as a replacement of SQLite. Speed tests showed that it is faster (even 10-30%) then SQLite, but only due to a fact that SQLite until 3.21 did not use an exponentiation by squares -- this is improved in SQLite 3.22. (I have not made the patch for 3.22 yet.) The patch is to illustrate the problem and as a reference. Nevertheless, I think users who prioritize dependability, accuracy, and generality over slightly degraded executable speed/size will be very interested to have your long form improvements. I'm using few compilers and platforms. There are many surprising constructs which can be 30% faster on old VS2005 (the last which produced EXE for Win16 -- aka 98/ME) then on VS2015. Fountain of bugs is a main problem in Watcom. MSVC has a buggy preprocessor (I have not tried VS2017 yet) which is grinding all macro from the beginning to the end, every round until there is nothing to expand. GCC had a minor problem with ASAN (mentioned on this list), and with a comparison of doubles<=>int64s (mentioned/fixed recently by DRH), and so on, and so on. If the patch is to illustrate the problem (if any), I do not want to obfuscate it and I'm trying to use most universal constructs. Besides that IMHO the problem (with trailing spaces and loss of precision) is too complicated to be effectively (and universally) resolved by someone other (me for sure) then the team. On the other side, the problem with saturation is completely resolvable by adding 5 characters to ``Atoi64()''. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Atoi64 bug(s)
Hello, On 2018-01-25 22:58, petern wrote: Thank you for expanding on your detailed observations. If you can, please post the long patch at your customary patch site http://sqlite.chncc.eu/ I was convinced that I had publicized my patch already. For the people who are interested in the patch, please give me a few hours to cut my new not-so-completely implemented functionalities from my draft version. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Atoi64 bug(s)
acy, and have proposed the (second part of the) patch which requires copying of one line of code with less then 20 chars of change, that's all. (Though it does not solves the problem in full.) And at the end: an introduction of such small changes is not worth of price, because you are not using such big numbers and are classifying the problem as an edge case? Really, is not it? I do not understand why are you bringing that argument up. Some kind of argument by an authority? As you said, probably the problem is an edge one. Probably most people contending with the problem have found more or less trivial solution. I have just shared my observations of a bit strange behavior and proposed a solution. What are you trying to say? That [NSQRN] as a result of conversion of a 'bignumstring' is better that saturated value MIN/MAX. That needless loss of info without any benefits is good? -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Atoi64 bug(s)
Hello,
On 2018-01-25 19:54, petern wrote:
CREATE TABLE IF NOT EXISTS a (a INTEGER);
INTEGER == NUMERIC in case of column declarations.
-- Note however, the constant table expression works fine...
SELECT CAST(column1 AS INTEGER) FROM (VALUES
('901'),('901 '));
-- "CAST(column1 AS INTEGER)"
-- 901
-- 901
This is due a fact that ``CAST AS INTEGER'' blindly calls ``Atoi64()''
and returns its result.
INTEGER/FLOAT handling/recognition is a bit more complicated in other
places, what causes that '901 ' will become 9e18.
For the same reason ``CAST ('901X' AS INTEGER)'' gives
INT 901, while ``SELECT CAST ('901X' AS
NUMERIC);'' gives FLOAT 9e18.
Due to a bit disordered treatment of values, my own patch involves many
changes. The ``concise'' patch, which I proposed in my original post,
eliminates: (1) (mod 2^64) bug, and (2) an erratic treatment of INTs and
FLOATs in some (not all) places. It changes only one line and adds one,
and does not change affinity/type system at all. (As opposed to my
``long'' version patch).
1. IMHO (mod 2^64) bug is serious. Speculative example: my app has a
dangerous number ``1234'' and is checking input text against it; then
``295147905179352827090'' -- OK, go on -- says my app; then suddenly
``CAST('295147905179352827090' AS INTEGER)'' ==> BOOM: 1234.
2. STRING to be INT must be reacting to ``attention'' command
immediately (even small, chaste space at the end will bother); to be
FLOAT, it can carousel from dusk till next dusk all the time.
There was no noticeable side effects (besides a performance) in old
32bit INT days, however now, 10 bits of each INT can be going to a vacuum.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Atoi64 bug(s)
Hello,
About year age I reported some strange behavior:
1.
https://www.sqlite.org/lang_expr.html#castexpr:
INTEGER: ``When casting a TEXT value to INTEGER, the longest possible
prefix of the value that can be interpreted as an integer number is
extracted from the TEXT value and the remainder ignored. [...] If there
is no prefix that can be interpreted as an integer number, the result of
the conversion is 0.''
sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
4807115922877859019
What prefix of ``12345678901234567890123'' can be interpreted as
``4807115922877859019''?
2.
Why trailing spaces are allowed in FLOATs and not in INTEGERs? It would
not be a problem, if INTs were 32bit. Now, a value is lost:
sqlite> CREATE TABLE a (a NUMERIC);
sqlite> INSERT INTO a VALUES
('901'),('901 ');
sqlite> SELECT * FROM a;
901
900
2a.
https://www.sqlite.org/lang_expr.html#castexpr:
NUMERIC: ``Casting a TEXT or BLOB value into NUMERIC first does a forced
conversion into REAL but then further converts the result into INTEGER
if and only if the conversion from REAL to INTEGER is lossless and
reversible.''
Why a cast to universal AFFINITY (NUMERIC -- designed to represent both
INTEGERs as well as FLOATs) could be skewed into FLOAT direction loosing
a INTEGER's LSBits? INT is not 32bit wide. Hopefully above is not true:
sqlite> SELECT CAST('901' AS NUMERIC);
901
However due to pt 2.:
sqlite> SELECT CAST('901 ' AS NUMERIC);
900
The most concise patch (without, for example ``SELECT
CAST('901X' AS NUMERIC);'') contains only two lines:
==
--- sqlite-src-322/src/util.c 2018-01-23 01:57:26.0 +0100
+++ sqlite-src-322/src/util.c 2018-01-25 14:22:18.428460300 +0100
@@ -625,6 +625,7 @@
zNum += (enc&1);
}
while( zNum='0' && c<='9'; i+=incr){
u = u*10 + c - '0';
}
- if( u>LARGEST_INT64 ){
+ if( 19 < i || u>LARGEST_INT64 ){
*pNum = neg ? SMALLEST_INT64 : LARGEST_INT64;
}else if( neg ){
*pNum = -(i64)u;
==
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .DUMP displays floats differently from SELECT
Hello, On 2018-01-22 13:00, Iulian Onofrei wrote: I have a "REAL" column with float values having up to 2 decimals, and using ".dump" with the latest version incorrectly converts them like this: "0.0" -> "0.0" "0.05" -> "0.050002775" > [...] Did you consider an using of INTEGERs as fixed-point numbers? Nearly the same arithmetic with one additional division by 100 after multiplication and one additional multiplication by 100 (of dividend) before division. I rely on dumps to track changes to some databases, so this breaks it completely, and I currently had to pin sqlite to an older version which isn't desired. IMHO, a better and more concise way is to rem out the following line: p->mode = p->cMode = MODE_Insert; in ``shell.c'' and to recompile it. Two slashes and the problem has gone. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .DUMP displays floats differently from SELECT
Hello,
On 2018-01-22 21:33, Keith Medcalf wrote:
The long and the short of it is that the output of .DUMP is correct.
I would say that the output is ``too correct''. ``.dump'' displays 20
meaningful digits.
Let's look at the 17 meaningful digits. Mantissa divides every
exponential range (i.e. range <B^n; B^{n+1}>, which is divided linearly)
into 1x2^52 equilengthy segments (binary/double) or 9x10^16 equilengthy
segments (decimal/17). The segment represents a rational number.
Every decimal exponential range overlaps 4 or 5 binary exponential
ranges. Let's consider decimal exponential range <0.1; 1> -- 17 digits
mantissa gives a density of (9x10^16)/(9x10^-1)=10^17 numbers per one.
Let's take the densest binary exponential range which overlaps the above
mentioned decimal one: <1/16;1/8> -- 53 digits binary mantissa gives a
density of (1x2^52)/(1x2^-4)=2^56=7.2...x10^16<10^17 numbers per one.
Let's find a pair of binary/decimal exponential ranges which overlap
each other and a ratio of binary numbers density to decimal numbers
density is highest possible (if greater then one, then same binary
numbers in the range cannot be represented by 17 digits decimal float).
Believe me or not, (for doubles) the ranges are: decimal:
<10^-146;10^-145> -- density: (9x10^16)/(9x10^-146)=10^162 numbers per
one; binary: <2^-486;2^-485> -- density:
(1x2^52)/(1x2^-486)=2^538=8.99..x10^161<10^162 numbers per one. The
ratio < 1. So 17 decimal digits is enough for an exact representation of
IEEE64 float.
I suppose that 20 digits is taken from the fact: 64ln(2)/ln(10)=19.2...,
however, for 64bit mantissa (long double) it is not enough (to be
represented exactly), for 53bit mantissa it is too many. Besides that,
17 digits + one rounding digit fit in i64, while 20 decimal digits do not.
The error of conversion depends on the width of intermediate floats --
not on the width of a integral part. Further increasing of number of
meaningful digits does nothing. It is important to use ``long double''
for multiplication. Previous (<= 3.21) implementation of ``AtoF()''
would loose 6 bits (40+ multiplications in the worst case), if it was
compiled with a compiler which mapped ``long double'' to ``double''.
Integer had 18/19 digits -- it did not helped.
For example, let's look at the first OP's number: 0.05 -- it is
1.Ax2^-5 => 0.050003 is enough for an exact
conversion. Neighboring numbers are: 1.9x2^-5 =>
0.049996, and 1.Bx2^-5 => 0.050010.
17 digits is also enough for the densest range
(<2^-486;2^-485>n<10^-146;10^-145>):
1.FF999x2^-486 => 1.0002594838824945E-146,
1.FF99Ax2^-486 => 1.0002594838824946E-146,
1.FF9999999999Bx2^-486 => 1.0002594838824947E-146.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieving constraint name
Hello, On 2018-01-07 23:35, Igor Korot wrote: Then maybe it could be considered to be included in the main tree by simply applying the patch. I'm afraid that if I change the order of fields in my patch, then it will not help. Exchanging the fields in the pragma is trivial. The whole patch is trivial also. At least for me, thus, by implication, for the team. I think that the reason for not extending the pragma lies elsewhere. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Hello,
To summarize:
On 2018-01-07 19:09, x wrote:
Because reading the whole record (all 3 fields) is more expensive than
just reading the index which has all the info you need to give a correct
answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field (ID)
so why not use that, particularly in the case where ‘ORDER BY ID’ was included in
the query?
OK - your creation statement causes that ``ID'' is an alias for
``rowid''. Why PK is not used, was mentioned by me:
On 2018-01-06 14:01, Cezary H. Noweta wrote:
Because the field is not NULL in Luuk's definition and NULL values are not
covered by the INDEX. SQLite assumes that you know what you are doing and tries
to find NULL values by full scan.
and Scott:
On 2018-01-07 20:30, Scott Robison wrote:
Integer primary key is by definition not null, so looking for a null value
on an index can't work.
Look at ``where.c:whereLoopAddBtreeIndex()'':
if( (eOp==WO_ISNULL || (pTerm->wtFlags_VNULL)!=0)
&& indexColumnNotNull(pProbe, saved_nEq)
){
continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
}
Even if you used ``ID INT PRIMARY...'' instead of ``ID INTEGER
PRIMARY...'' (which did not aliased ``rowid'' to ``ID'') results would
be the same, because ``ID'' is not NULL.
Now we are considering a full scan. As Luuk wrote:
On 2018-01-07 18:55, Luuk wrote:
Because reading the whole record (all 3 fields) is more expensive than
just reading the index which has all the info you need to give a correct
answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
Calculating a cost of a DB's searching is a bit complicated and cannot
be described in two words. You could start at a comment of
``where.c:whereLoopAddBtree()''. When your ratio of a length of an index
element to a length of a table row has been lowered, then index ``XXX''
is chosen as the most effective solution. (``rowid'' pseudo-index'
entries are considered as being equal to a whole row of a table).
However this index is used for a full scan -- not for a binary/log_2n
searching for values: ``Yahoo! My query is using an index so it
achieved a speed of light''. If it was true, then certainly ``XXX''
would be used earlier.
If you want to enforce some index to be used you will have to append
``INDEXED BY'' clause, however:
1. Choosing a bad index will result in no index at all.
2. As Scott noticed:
On 2018-01-07 20:30, Scott Robison wrote:
As to why it does a table scan, the primary key isn't a separate index. The
rowid primary key is part of the table itself.
For the purpose of ``SELECT'' there is a pseudo-index created for
``rowid'' -- it is impossible to enforce that pseudo-index. Unless you
use ``INT'' instead of ``INTEGER'' in your ``CREATE TABLE'' statement
(which will leave ``rowid'', and ``ID'' will not be alias for ``rowid'')
-- such primary index will be named ,,sqlite_autoindex__''
-- you will have to guess (or look into a db-file, or -- at best --
SELECT from ``sqlite_master'').
Primary key ``rowid'' (or alias) is not indexed, only ``primary keys''
other then ``rowid'' are. In fact it is impossible to have NULL
``rowid'' (or alias) in destroyed db -- due to a fact that it has NOT
NULL, int64 (variable length) format -- there is no place for ``I'm a
NULL'' flag. Non-rowid, NOT NULL, primary keys with NULL value could be
occurring eventually.
I hope, above will help and dispel any doubts.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Hello, On 2018-01-06 15:22, x wrote: Because the field is not NULL in Luuk's definition and NULL values are not covered by the INDEX. SQLite assumes that you know what you are doing and tries to find NULL values by full scan. The ID field in my definition is also not null. If so, then you should obtain results mentioned by Luuk: sqlite> Explain query plan select ID from Tbl where ID is null order by ID; selectid|order|from|detail 0|0|0|SCAN TABLE Tbl I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is returned in case of not ``NOT NULL'' field. ``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is used. Order by does not apply as it is the same as PK. Scanning by using PK results in ORDERed BY PK records. There is no need to use separate INDEX for ORDER BY clause. I’m not sure what you’re saying there Cezary. I recognise that the (non-existent) result set will all be NULLs and the order is therefore meaningless but if the query planner recognises that fact why does it not recognise that there will be no result set. Order is meaningful but it is the same as order of a table scanning process -- this is why ORDER BY is ``ignored'' -- not because there will be an empty result set. If you change to ORDER BY ID*2, then you will see that temporary index will be created. The same index is used for scanning and ordering, so there is no need to use it twice. If I run explain query plan select ID from Tbl where 0; // that’s where zero I again get the result SCAN TABLE Tbl USING COVERING INDEX XXX Again, I have the sole ``SCAN TABLE'' (without index -- in both cases: NULLs allowed and NOT NULL). Your result is obtained as if it was ORDER BY clause. However the EXPLAIN for the same query returns addropcode p1p2p3 p4p5comment 0 Init 0 8 0 00 Start at 8 1 Goto 0 7 0 00 2 OpenRead 1 109 0 k(2,,) 00root=109 iDb=0; tID 3 Rewind 1 7 1 0 00 4 IdxRowid1 1 0 00r[1]=rowid 5 ResultRow 1 1 0 00output=r[1] 6 Next1 4 0 01 7 Halt 0 0 0 00 8 Transaction0 0 392 0 01usesStmtJournal=0 9 Goto 0 1 0 00 which, if I’m reading it correctly, executes 0, 8, 9, 1 & 7 (Halt) and so comes up with the empty result set in a few milliseconds. That contrasts with the EXPLAINs of the IS NULL queries mentioned earlier which do an actual table scan. Indeed -- I have nearly the same: SQLite jumps directly to Halt in case of WHERE 0, except that I have Column instead of IdxRowid. Could you provide your table's CREATE command? AFAIR you are using SQLite 3.21, are not you? -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Hello, On 2018-01-06 13:33, x wrote: sqlite> Explain query plan select ID from Tbl where ID is null order by ID; selectid|order|from|detail 0|0|0|SCAN TABLE Tbl Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index XXX then the explain query plan will match mine (SCAN TABLE Tbl USING COVERING INDEX XXX). Your example muddies the water further though. Why is it scanning an entire table when it could scan the pk? Because the field is not NULL in Luuk's definition and NULL values are not covered by the INDEX. SQLite assumes that you know what you are doing and tries to find NULL values by full scan. It doesn’t even use the ID pk despite the fact it’s the requested order by?? That is explained by Cezary, if the explanation was unclear answer to his post... I wasn’t specifically replying to your post, I just clicked reply to the latest reply. I don’t see where Cezary explains it though. In your example (a field allowing NULLs) PK is used: 2 Null 0 1 000 3 Affinity 1 1 0 D 00 4 SeekGE 1 9 1 1 00 5 IdxGT 1 9 1 1 00 6 Column 1 0 200 7 ResultRow 2 1 000 8 Next 1 5 000 9 Halt 0 0 000 ``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is used. Order by does not apply as it is the same as PK. Scanning by using PK results in ORDERed BY PK records. There is no need to use separate INDEX for ORDER BY clause. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Hello, On 2018-01-05 21:56, David Raymond wrote: sqlite> explain select id from tbl where id = null; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 8 000 Start at 8 1 OpenRead 0 3 0 0 00 root=3 iDb=0; tbl 2 Explain0 0 0 SEARCH TABLE tbl USING INTEGER PRIMARY KEY (rowid=?) 00 3 Null 0 1 000 r[1]=NULL 4 SeekRowid 0 7 100 intkey=r[1]; pk 5 Copy 1 2 000 r[2]=r[1] 6 ResultRow 2 1 000 output=r[2] 7 Halt 0 0 000 8 Transaction0 0 2 0 01 usesStmtJournal=0 9 Goto 0 1 000 Run Time: real 0.000 user 0.00 sys 0.00 ??? My 3.21 checks if NULL is NULL and exits: 2 Null 0 1 3 IsNull 1 9 ... 9 Halt 0 0 -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Hwllo, On 2018-01-05 21:19, x wrote: Tbl has 2.4 million records and has an INTEGER PRIMARY KEY ID which is NOT NULL. SELECT ID FROM Tbl where ID = NULL It has no sense as ``='' returns NULL when one of operands is NULL. NULL evaluates to FALSE. Above query can be described as SELECT NOTHING. SELECT ID FROM Tbl WHERE ID IS NULL takes around 350 milliseconds to run and the explain query plan shows it uses some covering index. SQLite is looking for NULL values of ID. Please, please, do not require to optimize such query due to the fact that your field does not contain NULLs. :-) I’m sure I’ve read something before about this but can’t find it in the documentation? STH IS STH allows NULLs and returns 1 if both are NULLs, 0 otherwise. http://sqlite.org/lang_expr.html#isisnot -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer and recursive common table expressions
Hello, On 2018-01-04 01:53, R Smith wrote: Not to mention that if you wait several years, depending on your processor/compiler, the integer 64 value might wrap around and x<=3 might become true once more, producing rows again :) Unfortunately, it will be stuck when int becomes double (at 9223372036854775808 -- still much time :-). ``We are programmers and responsible for programming. -O99 is responsible for thinking. Who in the hell implemented -O when there had not been -O?'' :-) -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SPAM] Re: Emulate right-join
Hello, On 2017-12-06 19:07, R Smith wrote: You mean make SQLite less Lite, but with Zero computational advantage, by simply adding syntactic sugar bloat? - I'm going to have to vote No on that. (Luckily my vote counts extremely little.) I think the reason SQLite never implemented it is precisely because of the fact that it simply amounts to syntactic specialization and no real computational advantage. That said, I'm not against adding those joins, just perhaps implemented in a most-efficient way rather than a simple transcription of my lazy-code. (Unless of course that ends up being the most efficient way to do it.) I'm all with you. You have taken my words. CROSS OUTER joins as simple UNION of both joins are (or can be) very inefficient, especially when a number of widow records is relatively small (as compared to a number of all records) -- this results in two, long running joins, one of which goes to a vacuum. I can see real, however negative, computational advantage. Although that way is a very good academic illustration of OUTER joins. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
Hello, On 2018-01-03 01:44, Cezary H. Noweta wrote: MySQL has a separator specified by a distinct clause. I'm sorry -- I meant ``distinct'' == ``separate/different'' (a clause named ``SEPARATOR''). Not to be confused with ``DISTINCT'' clause in SQLite's ``group_concat(DISTINCT...)''. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
Hello,
On 2018-01-03 00:02, Tony Papadimitriou wrote:
MySQL does not seem to have a problem with it.
MySQL has a separator specified by a distinct clause. In SQLite it is
specified by a second expression, which, in a canonical and intuitive
point of view, is a constant string. However it can vary from record to
record and can be used in many fancy and/or useful ways:
sqlite> WITH RECURSIVE numbers(n,p) AS (VALUES(random()/10,0)
UNION ALL SELECT random()/10,n FROM numbers LIMIT 10) SELECT
group_concat(n,substr('<=>',3*(n<p),3)) FROM numbers;
8566622672>2879787174<=7821300466>-9054357747<=3166199899>-4120363042<=8151009951>-7018229290<=4454709919<=8212308797
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP_CONCAT with DISTINCT bug
Hello,
On 2018-01-02 22:39, Scott Robison wrote:
On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou <to...@acm.org> wrote:
create table t(s);
insert into t values ('A'),('A'),('B');
select group_concat(s,', ') from t group by null; -- OK
select group_concat(distinct s) from t group by null; -- OK
select group_concat(distinct s,', ') from t group by null; -- ERROR
-- The moment the optional delimiter is given along with DISTINCT you get this
error:
-- Error: near line 6: DISTINCT aggregates must have exactly one argument
A limitation of the SQL syntax.
Quite justly, not all the time params are obvious -- group functions are
not group functions with 1 column and n one-bind-time parameters:
sqlite> CREATE TABLE a(a,sep);
sqlite> INSERT INTO a VALUES('Hello', ','),('world', ';'),('shmorld', '
AND ');
sqlite> SELECT group_concat(a,sep) FROM a GROUP BY NULL;
Hello;world AND shmorld
Hopefully, SELECT FROM SELECT DISTINCT mentioned previously by Scott,
resolves the problem in an easy & painless way.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3AtoF()
Hello, On 2017-12-29 02:25, Richard Hipp wrote: Please test the latest trunk version (or any version after check-in https://www.sqlite.org/src/timeline?c=fd2e0e7a) and confirm that the modifications work for you. It works as expected. Thank you. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3AtoF()
Hello,
Could you consider an exponentiation by squaring (in the main release)
instead of current n-multiplication of exponents? I've observed that an
average time spent in sqlite3AtoF() is greatly reduced by 12% to over
30%, depending on an exponent's magnitude and a compiler. It is nothing
more then 3 lines of code and a noticeable advantage, for example:
==
--- util.c 2017-10-25 02:25:56.0 +0200
+++ util-esqr.c 2017-12-25 07:02:20.101110300 +0100
@@ -475,11 +475,12 @@
if( e==0 ){
/*OPTIMIZATION-IF-TRUE*/
result = (double)s;
}else{
- LONGDOUBLE_TYPE scale = 1.0;
+ LONGDOUBLE_TYPE scale = 1.0, t;
/* attempt to handle extremely small/large numbers better */
if( e>307 ){
/*OPTIMIZATION-IF-TRUE*/
if( e<342 ){
/*OPTIMIZATION-IF-TRUE*/
- while( e%308 ) { scale *= 1.0e+1; e -= 1; }
+ for ( e -= 308, t = 1.0e+1; 1 < e; e >>= 1, t *= t ) { if ( 1
& e ) scale *= t; }
+ if ( e ) scale *= t;
if( esign<0 ){
result = s / scale;
result /= 1.0e+308;
@@ -499,10 +500,8 @@
}
}
}else{
-/* 1.0e+22 is the largest power of 10 than can be
-** represented exactly. */
-while( e%22 ) { scale *= 1.0e+1; e -= 1; }
-while( e>0 ) { scale *= 1.0e+22; e -= 22; }
+for ( t = 1.0e+1; 1 < e; e >>= 1, t *= t ) { if ( 1 & e ) scale
*= t; }
+scale *= t;
if( esign<0 ){
result = s / scale;
}else{
==
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTime kind stored as undefined
Hello,
> my connection string looks like this:
> *var connection = new SQLiteConnection("DateTimeKind=Utc;Data
> Source=:memory:");*
> Here is a blog post about it (settig datetimekind in utc for sqlite) on
> which I based:
>
https://www.thomaslevesque.com/2015/06/28/how-to-retrieve-dates-as-utc-in-sqlite/
Still, I cannot reproduce te original problem. The following results are OK:
==
test.cs:
==
using System;
using System.Data.SQLite;
class Test
{
static void TestUtc(bool utc, bool ticks)
{
DateTime dtUtcNow = DateTime.UtcNow;
DateTime dt;
SQLiteConnection connection;
SQLiteCommand cmd;
SQLiteDataReader rdr;
int rows;
string connectionstring = "Data Source=:memory:;";
Console.WriteLine("* Testing " + (utc ? "UTC" : "UNSPECIFIED")
+ "/" + (ticks ? "TICKS" : "ISO8601") + "...");
Console.WriteLine("DateTime: {0}", dtUtcNow);
Console.WriteLine("DateTime.Kind: {0}", dtUtcNow.Kind);
if ( utc ) {
connectionstring += "DateTimeKind=Utc;";
}
if ( ticks ) {
connectionstring += "DateTimeFormat=Ticks;";
}
connection = new SQLiteConnection(connectionstring);
connection.Open();
cmd = new SQLiteCommand("CREATE TABLE IF NOT EXISTS [test]
([date] DATETIME NOT NULL);");
cmd.Connection = connection;
rows = cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new SQLiteCommand("INSERT INTO [test] ([date]) VALUES
(@date);");
cmd.Parameters.AddWithValue("@date", dtUtcNow);
cmd.Connection = connection;
rows = cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new SQLiteCommand("SELECT [date] FROM [test];");
cmd.Connection = connection;
rdr = cmd.ExecuteReader();
while ( rdr.Read() ) {
dt = rdr.GetDateTime(0);
Console.WriteLine("DateTime: {0}", dt);
Console.WriteLine("DateTime.Kind: {0}", dt.Kind);
}
rdr.Dispose();
cmd.Dispose();
connection.Dispose();
}
static void Main()
{
TestUtc(false, false);
TestUtc(true, false);
TestUtc(false, true);
TestUtc(true, true);
}
}
==
gives the following results:
==
* Testing UNSPECIFIED/ISO8601...
DateTime: 2017-12-16 15:48:39
DateTime.Kind: Utc
DateTime: 2017-12-16 16:48:39
DateTime.Kind: Unspecified
* Testing UTC/ISO8601...
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
* Testing UNSPECIFIED/TICKS...
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Unspecified
* Testing UTC/TICKS...
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
==
It looks that your app behaves as if it had default settings:
ISO8601/Unspecified, which results in Local/Unspecified. The sole thing
I have in mind is Connection Designer. Try to omit the Connection
Designer -- as far as I remember it had problems with some parameters:
CD doubled them or had named them incorrectly.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTime kind stored as undefined
I'm sorry -- the following post was sent to a private e-mail by an accident: Hello, On 2017-12-13 12:51, Michał Niegrzybowski wrote: > I have a table which has a column of type DateTime in my code I insert > there an actual UTC Date (which is not the same as my local time). When I > want to gather previously added record, my record contains date in his > DateTime column, but this DateTime is a localtime with kind specified to > 'undefined' instead of 'UTC'. I cannot reproduce the problem. Setting a format to ticks and a kind to UTC causes a storing/retrieving a valid UTC DateTime, which is stored as INTEGER. Could you provide your connection string? -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieving constraint name
Hello, On 2017-12-11 04:29, Igor Korot wrote: Thank you, but I need to keep the official SQLite code. Anyway, for the people who are interested in foreign key names: http://sqlite.chncc.eu/fknames/. Certainly, all tests expecting original output of ``PRAGMA foreign_key_list'' will fail. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieving constraint name
Hello, On 2017-12-11 01:04, Igor Korot wrote: On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta <c...@poczta.onet.pl> wrote: On 2017-12-10 07:21, Igor Korot wrote: The CREATE TABLE statement supports the following syntax: CREATE TABLE( , CONSTRAINT FOREIGN KEY() REFERENCES (ref_column_list>); [...] If not - does this mean that the only way to get the name is to parse the sql from sqlite_master? Or there is a better way? The answer is ``not''. Constraint names are ignored and disappearing without a trace except for ``CHECK'' constraint (the name is used to build an error message). Unparsed ``sql'' column of ``sqlite_master'' is the sole place which contains an indirect info about ``FOREIGN KEY'' constraint's name. Thank you for confirming. You are welcome. BTW, SQLite parses SQL every time it creates a table (by a SQL command or after an opening of BTree file) -- I believe there is no better way. You do not need to parse SQL on your own (it is hard, if not impossible, to establish a link between a name and a particular constraint). All you need is to append ``char *'' field to ``struct FKey'' and to inject a function ``build.c:sqlite3CreateForeignKey()'': ``pParse->constraintName'' will contain the constraint's name (note that the name is not dequoted -- you will have to dequote it; look at ``build.c:sqlite3AddCheckConstraint()'' to know how assigning of a constraint's name is done). This will allow you to build your own map of ``FOREIGN KEY'' names. For example, if you want to expand ``PRAGMA foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieving constraint name
Hello,
On 2017-12-10 07:21, Igor Korot wrote:
The CREATE TABLE statement supports the following syntax:
CREATE TABLE( , CONSTRAINT FOREIGN
KEY() REFERENCES (ref_column_list>);
However, the statement "PRAGME foreign_key_list;" does not list the
foreign key name ("fk_name" in the statement above).
Does the info for the aforementioned PRAGMA stored somewhere?
If yes - does it include the key name and it just not printed with the PRAGMA?
If not - does this mean that the only way to get the name is to parse the sql
from sqlite_master? Or there is a better way?
The answer is ``not''. Constraint names are ignored and disappearing
without a trace except for ``CHECK'' constraint (the name is used to
build an error message). Unparsed ``sql'' column of ``sqlite_master'' is
the sole place which contains an indirect info about ``FOREIGN KEY''
constraint's name.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS FCNTL question
Hello, On 2017-12-03 17:41, J Decker wrote: https://sqlite.org/c3ref/c_fcntl_busyhandler.html #define SQLITE_FCNTL_PDB 30 SQLITE_FCNTL_PDB has no documentation as to what it's for. The only place, where this FCNTL is used, is btree.c:sqlite3BtreeOpen(). The FCNTL informs a VFS driver on sqlite3 * connection using BTree file. It is useful when your VFS driver uses time-consuming I/O operations (for example, over a network) and asynchronous sqlite3_interrupt() has been invoked. Your driver can periodically check a status of sqlite3's isInterrupted flag and immediately stop time-consuming I/O without wasting a time for retrieving/writing a data which would be finally discarded due to an interrupt. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UTF8-BOM not disregarded in CSV import
Hello, On 2017-06-26 17:26, Scott Robison wrote: +1 FAQ quote: Q: When a BOM is used, is it only in 16-bit Unicode text? A: No, a BOM can be used as a signature no matter how the Unicode text is transformed: UTF-16, UTF-8, or UTF-32. Q: How I should deal with BOMs? A: Here are some guidelines to follow: 4. Where the precise type of the data stream is known (e.g. Unicode big-endian or Unicode little-endian), the BOM should not be used. In particular, whenever a data stream is declared to be UTF-16BE, UTF-16LE, UTF-32BE or UTF-32LE a BOM must not be used. (See also Q: What is the difference between UCS-2 and UTF-16?.) :-) -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UTF8-BOM not disregarded in CSV import
On 2017-06-26 15:01, jose isaias cabrera wrote: I have made a desicion to always include the BOM in all my text files whether they are UTF8, UTF16 or UTF32 little or big endian. I think all of us should also. I'm sorry, if I introduced ambiguity, but I had described SQLite's and SQLite shell's behavior -- the sole. I'm not entreating to kill all UTF-8 BOMs in the universe. Just because the "Unicode Gurus" didn't think so, does not mean they are right. I had a doctor give me the wrong diagnose. There were just too many symptoms that looked alike and they chosed one and went with it. The same thing happened, the Unicode Gurus, they never thought about the problems they would be causing today. Some applications do not place BOM on UTF8, UTF16 files, and then you have to go and find which one is it, and decode the file correctly. The problem, which you described, had not been introduced nor created by ``Unicode Gurus''. AFAIR, finding of a correct encoding/codepage (of files with an unknown origin) was present in the olden days, far before Unicode. UTF-8 is far easier recognizable then others. This can all be prevented by having a BOM. This would have helped, if it had been only UTF-8 and some single-byte code page. Yes, I know I am saying everything every body is, but what I am also saying is to let us all use the BOM, and also have every application we write welcome the BOM. What if I want to place 0xFEFF at the beginning of UTF-8? The second EF BB BF as BOM? OK - but the standard says ``there is no BOM''. This is what the standard is for. I agree with you -- where a character set is unmarked, there UTF-8 BOM is useful as an encoding signature. However, where SQLite is accepting only UTF-8, there I expect that placing EF BB BF at the beginning will be interpreted as codepoint -- not BOM, until it will say explicitly: ``My interpretation of EF BB BF is BOM''. I am not going to tell whether zero or all of my UTF-8 files have BOM neither whether or not to use/kill/welcome all UTF-8 BOMs -- it does not matter. However, in case of SQLite, Clemens' arguments are very stringent -- I hope SQLite shell's behavior will not change. For the sake of the standard conformance, thus predictability and determinedness. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UTF8-BOM not disregarded in CSV import
Hello, On 2017-06-23 22:12, Mahmoud Al-Qudsi wrote: I think you and I are on the same page here, Clemens? I abhor the BOM, but the question is whether or not SQLite will cater to the fact that the bigger names in the industry appear hell-bent on shoving it in users’ documents by default. Given that ‘.import’ and ‘.mode csv’ are “user mode” commands, perhaps leeway can be shown in breaking with standards for the sake of compatibility and sanity? IMHO, this is not a good way to show a leeway. The Unicode Standard has enough bad things in itself. It is not necessary to transform a good Unicode's thing into a bad one. Should SQLite disregard one sequence, or all sequences, or at most 2, 3, 10 ones at the beginning of a file? Such stream can be produced by a sequence of conversions done by a mix of conforming and ``breaking the standard for the sake of compatibility'' converters. To be clear: I understand your point very well - ``let's ignore optional BOM at the beginning'', but I want to show that there is no limit in such thinking. Why one optional? You have not pointed out what compatibility with. The next step is to ignore N BOMs for the sake of compatibility with breaking the standard for the sake of compatibility with breaking the standard for the sake of... lim = \infty. I cannot see any sanity here. The standard says: ``Only UTF-16/32 (even not UTF-16/32LE/BE) encoding forms can contain BOM''. Let's conform to this. Certainly, there are no objections to extend an import's functionality in such a way that it ignores the initial 0xFEFF. However, an import should allow ZWNBSP as the first character, in its basic form, to be conforming to the standard. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open_v2("",... schema name?
Hello, On 2017-04-28 12:16, Olivier Mascia wrote: http://sqlite.org/c3ref/open.html says: "If the filename is an empty string, then a private, temporary on-disk database will be created. This private database will be automatically deleted as soon as the database connection is closed." The same behavior is when filename is NULL. In both cases a pager's filename is set to an empty string. On such a successfully opened database, sqlite3_db_filename(db, "main") returns a NOT null pointer (to '\0'). The same behavior is seen when the opened database is an in-memory one (using ":memory:" in the sqlite3_open_v2 call). Indeed, look at the code (``sqlite3PagerFilename''): return (nullIfMemDb && pPager->memDb) ? "" : pPager->zFilename; Yet http://sqlite.org/c3ref/db_filename.html says: "If there is no attached database N on the database connection D, or if database N is a temporary or in-memory database, then a NULL pointer is returned." Actually, NULL means that there is no such schema (if provided N is NULL, then "main"'s filename is returned). An empty string (ptr to '\0') means that the schema is an on-disk temp, or an in-mem db, or ``temp'' schema. So I would have expected (from the documentation) to get a NULL pointer, at least for the :memory: database. And for the on-disk but private temporary one, the documentation is mute about what to except from sqlite3_db_filename(). I agree, that a minor DOC improvement would be nice in this case. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] gnu gcc address sanitizer finds stack-use-after-scope in sqlite3VdbeExec
Hello,
On 2017-04-19 19:31, Dan Kennedy wrote:
If you compile the code below with gcc 7.0.1:
gcc code.c -fsantitize=address -o tst
and then run:
./tst 2
Does the sanitizer make the same complaint?
[...]
/**/
[...]
switch( a ){
case 1: {
int res;
b = 1;
case 2:
res = b;
c = res;
break;
}
}
[...]
/**/
IMHO, ``res'' will not be even protruded to the sanitizer. ``vdbe.c''s
code is using a pointer to ``res'', passing it as a parameter -- thus
SQLite's ``res'' is handled by use-after-scope sanitizer, while ``pC''
and ``pCrsr'' are not.
It looks that GCC forgets to unpoison ``res'' for some reason. Looking
at the GCC's test cases I would suggest: (a) to place label
``OP_SeekRowid'' just after declarations (i.e. after ``u64 iKey;'',
and/or (b) to enclose ``case OP_NotExists:''...``break;'' into a nested,
inner block, and/or (c) to open the block _before_ ``case OP_SeekRowid:''.
Unfortunately, ``gcc-7-branch'' still is decompressing on my hardware,
so I cannot check which one(s) of above (a), (b), (c) will help.
Regardless of all, the original problem concerns GCC rather then SQLite
itself.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] gnu gcc address sanitizer finds stack-use-after-scope in sqlite3VdbeExec
On 2017-04-19 19:31, Dan Kennedy wrote:
On 04/18/2017 07:12 PM, Vitti wrote:
In my opinion this is probably due to erroneous usage of variable res
in the branches
of the huge switch in sqlite3VdbeExec
What's wrong with ``{ int res; res = 0; }''? The sanitizer should alert
in the following code: ``int *p; { int res; p = } p[0] = 0;''.
Does it not like us using a
switch() to jump into the middle of a block that contains variable
declarations?
If it had been true in general, then it would have tripped on earlier
``pC = p->apCsr[pOp->p1];'' or ``pCrsr = pC->uc.pCursor;'' (both ``pC''
and ``pCrsr'' are local to the block). It is too simple to be not
detected. I would bet on a bug in the sanitizer triggered by a
composition of (a) successive blocks conataining ``res'', (b) switch
into the place just after a declaration in the middle of a block, (c) an
using of a pointer to ``res'', and (d) a huge size of ``switch(...)''.
- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VT table behavior change between 3.10 and 3.17
Hello, On 2017-03-29 18:00, Richard Hipp wrote: One could omit all OP_Close opcodes and I think everything would still work. But sometimes an OP_Close can free up resources sooner rather than later. Also, some b-tree operations are faster if there is only a single open cursor on that b-tree, and so it is advantageous to keep the number of open cursors to a minimum. I've just found ``Check-in [32be7aae]'' (http://www.sqlite.org/cgi/src/info/32be7aae92ee48bf): Date: 2016-11-22 01:26:42 User: drh Comment: Remove unnecessary OP_Close opcodes for a size reduction and performance increase. AFAIK, your intention was to remove unnecessary OP_Close (in case of virtual table cursors). The dangling, problematic OP_Close is at the very end of ``update.c'' (updateVirtualTable(...)): sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0); -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VT table behavior change between 3.10 and 3.17
Hello, On 2017-03-29 18:00, Richard Hipp wrote: One could omit all OP_Close opcodes and I think everything would still work. But sometimes an OP_Close can free up resources sooner rather than later. Also, some b-tree operations are faster if there is only a single open cursor on that b-tree, and so it is advantageous to keep the number of open cursors to a minimum. OP_Close with virtual tables: +-+-+---+---+ | VERSION | SELECT | DELETE| UPDATE| +-+-+---+---+ | 3.10| AT THE END | IN THE MIDDLE | IN THE MIDDLE | +-+-+---+---+ | 3.17| DISAPPEARED | DISAPPEARED | AT THE END| +-+-+---+---+ ``AT THE END'' means just before OP_Halt. Update of virtual tables is treated separately so it could be easy adjusted for consistency. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VT table behavior change between 3.10 and 3.17
Hello, On 2017-03-29 15:34, Bob Friesenhahn wrote: If this is supposed to be the case, then it seems that xClose() is not being called before xUpdate() with 3.17. BTW. Why (while in UPDATE) there is still emitted Close just before Halt? Halt closes opened cursors at the very beginning. Maybe Close should be omitted as in SELECT/DELETE? -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VT table behavior change between 3.10 and 3.17
Hello, On 2017-03-29 13:07, Hick Gunter wrote: -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Cezary H. Noweta Gesendet: Mittwoch, 29. März 2017 12:37 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] VT table behavior change between 3.10 and 3.17 [...] Why xOpen-ed cursor cannot be used to write to a table? The xUpdate method alone is used to perform updates to virtual tables. It does not take a cursor argument. Also, there is no method taking a cursor argument that allows data to be changed. The documentation is correct in that UPDATE and DELETE queries involve reading from the table first, and xOpen will be called to do that. Pure INSERT queries don't even call xOpen. Indeed, the sole place, where VOpen is used, is a processing of WHERE clauses (UPDATE, DELETE have a WHERE clause, while INSERT, REPLACE have not). My questions concerned a connection between your first sentence (``According to the documentation...'') and the remaining part of your post. Now, I see that you have explained the behavior of SQLite rather then quoted the documentation. I'm sorry for OT disturbance. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VT table behavior change between 3.10 and 3.17
Hello, On 2017-03-29 10:48, Hick Gunter wrote: According to the documentation of Virtual Tables and Opcodes: [...] xOpen( table, cursor) is called to announce that SQLite intends to read from a table. A cursor cannot be used to write to a table. Do whatever is necessary to read from the backing store and set any VT implementation specific fields in the cursor structure (e.g. a file handle enabled for reading) Where have you taken above fragment from? The doc says: ``The xOpen method creates a new cursor used for accessing (read and/or writing) a virtual table.'' -- at least from 3.8.5 until now. It is not the best to rely on the assumption that xOpen-ed cursor is read-only, or have I missed something? Why xOpen-ed cursor cannot be used to write to a table? -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error using multiline command line argument with dot-command
Hello,
On 2017-03-07 18:16, Dan Kennedy wrote:
On 03/08/2017 12:03 AM, Rob Golsteijn wrote:
I want to report a minor issue for the Sqlite shell. It does not
handle multiline command line arguments in which the second line
contains a dot-command correctly.
If the same statements are passed via stdin they are handled fine.
Tested with Sqlite 3.15.2 on Ubuntu 14.04 using Bash.
Passing statements via stdin works fine:
echo "SELECT 1;
.mode csv
SELECT 1;" | sqlite3 mydb.sq3
Passing the statements via a command line argument gives an error:
sqlite3 mydb.sq3 "SELECT 1;
.mode csv
SELECT 1;"
Error: near ".": syntax error
A work around is:
sqlite3 mydb.sq3 "SELECT 1" ".mode csv" "SELECT 1"
Fortunately most of work is done already. It is enough to dirty patch
``fgets'':
After ``static char mainPrompt[20];'':
==
typedef struct CHNFILE {
int nCmd; /* nof elems in azCmd */
char**azCmd; /* arr to scan */
int nCmdCur; /* nof cur elem */
char*zCmdCur; /* next char to scan */
size_t nLenCmdCur; /* len of cur line */
} CHNFILE;
static CHNFILE chnfile = { 0 };
/*
* nCmd > 0 or UDB (7.1.7)
* azCmd != NULL or UDB (7.1.7)
*/
/* MT: NOT LOCKED */
static FILE *chnfileopen(int nCmd, char *azCmd[])
{
assert(0 < nCmd);
assert(NULL != azCmd);
if ( 0 != chnfile.nCmd ) {
return NULL;
}
chnfile.nCmd = nCmd;
chnfile.azCmd = azCmd;
chnfile.nCmdCur = 0;
chnfile.zCmdCur = strchr(azCmd[0], '\n');
chnfile.nLenCmdCur = NULL == chnfile.zCmdCur ? strlen(azCmd[0]) :
chnfile.zCmdCur - azCmd[0];
chnfile.zCmdCur = azCmd[0];
return (void *)
}
/* MT: NOT LOCKED */
static char *chnfilegets(char *s, int n, FILE *f)
{
if ( 0 == chnfile.nCmd || (void *) != f ) {
return fgets(s, n, f);
}
/* ``shall'' -- UDB (7.1.7) */
assert(NULL != s || 0 >= n || (int)chnfile.nCmd <= chnfile.nCmdCur);
/* nothing to read or EOF */
if ( 0 >= n || (int)chnfile.nCmd <= chnfile.nCmdCur ) {
return NULL;
}
/* Trailing '\0' */
--n;
/* Advance to the next line? */
if ( (size_t)n > chnfile.nLenCmdCur ) {
/* YES: Advance to the next line? */
char *p; /* Temp helper */
memcpy(s, chnfile.zCmdCur, chnfile.nLenCmdCur);
s[chnfile.nLenCmdCur] = '\n';
s[chnfile.nLenCmdCur + 1] = 0;
/* Next line */
if ( '\n' == chnfile.zCmdCur[chnfile.nLenCmdCur] && 0 !=
chnfile.zCmdCur[chnfile.nLenCmdCur + 1] ) {
/* Ends with '\n' [not followed by '\0' ???] */
chnfile.zCmdCur = [chnfile.nLenCmdCur + 1];
p = strchr(chnfile.zCmdCur, '\n');
chnfile.nLenCmdCur = NULL == p ? strlen(chnfile.zCmdCur) : p -
chnfile.zCmdCur;
} else {
/* Next element */
if ( (int)++chnfile.nCmdCur < chnfile.nCmd ) {
chnfile.zCmdCur = chnfile.azCmd[chnfile.nCmdCur];
p = strchr(chnfile.zCmdCur, '\n');
chnfile.nLenCmdCur = NULL == p ? strlen(chnfile.zCmdCur) : p -
chnfile.zCmdCur;
}
}
} else {
/* NO: Advance to the next line? */
memcpy(s, chnfile.zCmdCur, n);
s[n] = 0;
chnfile.zCmdCur = [n];
chnfile.nLenCmdCur -= n;
}
return s;
}
/* MT: NOT LOCKED */
static int chnfileclose(FILE *f)
{
if ( 0 == chnfile.nCmd || != (void *)f ) {
return fclose(f);
}
chnfile.nCmd = 0;
return 0;
}
#define fgets chnfilegets
#define fclose chnfileclose
==
And replace the final ``if( !readStdin ){'' block in ``main'':
==
FILE *in;
in = chnfileopen(nCmd, azCmd);
if( in==0 ){
utf8_printf(stderr, "Error: cannot process command line\n");
rc = 1;
} else {
rc = process_input(, in);
fclose(in);
}
free(azCmd);
==
A bit dirty, but works fine though not extremely tested. A cleaner
solution probably requires a bit more work, but due to a shell's thrift
it is quite easy (there is a simple, non--forking flow: main =>
process_input => one_input_line => local_getline => fgets).
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused getting started
Hello,
On 2017-03-05 01:10, John Albertini wrote:
I can't seem to grasp what I need to download / install to use SQLite?
Can someone guide me through the process? Looking to use it with
RootsMagic.
If you want to use a tool like dBaseIII+ to examine/modify a database
created by a 3rd party, then you will have to download a shell binary:
http://sqlite.org/download.html => ``Precompiled Binaries for ...''.
Been using PCs since the mid 1980s and have used dBase III+ and Approach
previously.
From http://sqlite.org/about.html: ``SQLite is an in-process library
that implements a self-contained, serverless, zero-configuration,
transactional SQL database engine.'' Primarily, SQLite is a library.
Let's consider the following dBase program:
==
SET TALK OFF
CLEAR
CLEAR ALL
STORE 0 TO TOTAL_EMPLOYEES
STORE 0 TO TOTAL_SALARY
USE EMPLOYEE
DO WHILE .NOT. EOF()
TOTAL_EMPLOYEES = TOTAL_EMPLOYEES + 1
TOTAL_SALARY = TOTAL_SALARY + SALARY
SKIP
ENDDO
@1,1 SAY "Average salary: $"
IF 0 < TOTAL_EMPLOYEES
@1,18 SAY TOTAL_SALARY / TOTAL_EMPLOYEES PICTURE ".99"
ELSE
@1,18 SAY "0.00"
ENDIF
SET TALK ON
==
If dBase had been SQLite, then you would have created the
following--like ``salary.c'' file:
==
#include "dbase3.h"
int main()
{
dbase3 *db;
int result;
result = dbase3_context_init();
if ( DBASE3_OK == result ) {
dbase3_exec(db,
"SET TALK OFF\n"
"CLEAR\n" /* ... Rest of the program omitted */ );
dbase3_context_done(db);
}
return 0;
}
==
and compiled it: ``cl salary.c dbase3.c'', and run a resulting
salary.exe file.
Certainly, SQLite does not implement xBase/Vulcan language, in exchange
it uses SQL (similar to SQL subsystem of DOS' FoxPro 2.5 or 2.6 AFAIR).
There are no separate files per table/index, There is one
self--containing file per database (which contains all tables and indices).
If you are interested in the preceding scenario, then you will have to
download an amalgamation (all SQLite in one file):
http://sqlite.org/download.html => ``Source Code'' =>
``sqlite-amalgamation-*.zip''. It goes a working example:
1. Unzip it, and compile the shell: ``cl shell.c sqlite3.c''.
2. Run ``shell.exe'' and execute the following commands:
.open company.db
CREATE TABLE employees (salary);
INSERT INTO employees VALUES (1000), (1120), (920), (840), (1220);
.quit
Now, you have created ``company.db'' SQLite database file.
3. Create ``salary.c'' with the following content:
==
#include
#include "sqlite3.h"
int moneyDisp(void *unused, int cc, char *values[], char *names[])
{
(void)unused;
(void)cc;
(void)values;
(void)names;
if ( 0 < cc ) {
if ( NULL != names[0] && 0 != names[0][0] ) {
printf("%s: ", names[0]);
}
printf("$%s\n", NULL == values[0] || 0 == values[0][0] ? "0" :
values[0]);
}
return 0;
}
int main()
{
int result;
sqlite3 *db;
char *errMsg;
result = sqlite3_open("company.db", );
if ( SQLITE_OK != result ) {
/* open error handling */
} else {
result = sqlite3_exec(db,
"SELECT AVG(salary) AS 'Average salary' FROM employees;",
moneyDisp,
NULL,
);
if ( SQLITE_OK != result ) {
/* select error handling */
}
sqlite3_free(errMsg);
result = sqlite3_close(db);
if ( SQLITE_OK != result ) {
/* close error handling */
}
}
return 0;
}
======
4. Compile and run:
cl salary.c sqlite3.c
salary.exe
You should receive something like:
Average salary: $1020.0
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] last_insert_rowid() returns wrong value after insert in a fts5 virtual table.
Hello,
On 2017-02-27 11:41, Dan Kennedy wrote:
CREATE VIRTUAL TABLE f USING fts3(x);
BEGIN;
INSERT INTO f VALUES('one');
INSERT INTO f VALUES('two');
INSERT INTO f VALUES('three');
INSERT INTO f VALUES('four');
COMMIT;
INSERT INTO f VALUES('five');
SELECT last_insert_rowid();
The last SELECT statement returns integer value 2, not 5 as you would
expect.
Indeed, something opposite to the original problem: works (FTS5: not so)
fine until you use transactions.
Not sure if this is something we can fix or not.
In FTS5 ``*_data'' is updated by ``REPLACE ...''. It is impossible to
disable updating of lastRowid. There are commands OP_Insert with P5 |=
OPFLAG_LASTROWID hardcoded. Such late update is bypassing (or
overwriting) virtual table xUpdate's ``pRowid'' feature.
IMHO, the problem requires redesigning of FTSes or SQLite core (for
example by allowing to disable lastRowid's updating temporarily). I'm
not as fluent in SQLite as to see an ``append-one-line'' solution.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] last_insert_rowid() returns wrong value after insert in a fts5 virtual table.
Hello, While working on the Perl DBD:SQLite driver, I found the following bug in sqlite : the last_insert_rowid() method (or SQL function) returns the constant value 10 after any insert into a fts5 virtual table. This bug is new in fts5 : previous versions fts4 and fts3 returned the correct rowid value. This is caused by xSync/xCommit, which updates f5_data/FTS5_STRUCTURE_ROWID row at the very end of xCommit instead of at the end of xUpdate. I think, this is due to a performance. As a temporary solution I'd suggest to disable autocommit. For example, try to enclose your SQL commands in BEGIN/COMMIT. Until you exec COMMIT, f5_data is not updated and last_insert_rowid() returns a value set by ``VUpdate'' opcode -- not overwritten by xSync/xCommit updates. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unresolved External sqlite3_user_add
On 2017-02-21 23:16, Simon Slavin wrote: On 21 Feb 2017, at 10:11pm, Andrew Stewart <astew...@arguscontrols.com> wrote: What I am trying to do is hide my data. Then user-authentication is useless to you. You need encryption. ... and if you do not need a hardcore encryption, then you could consider using of ``src\test_vfstrace.c'' (look at ``src\test_demovfs.c'', ``ext\misc\vfs*.c'', too) VFS filter driver. If all you need is to prevent standard tools/libraries from opening your DB, you will have to add some simple XOR/ADD operation on a read/written data. On the other side, an encryption does not ensure an user separation -- the encryption concerns a whole db-file, so one user having access to an encrypted db-file could virtually read other user's data. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem compiling 3.17.0 in MSVS 12
Hello, On 2017-02-21 07:32, Green Fields wrote: cl sqlite3.c -link -dll -out:sqlite3.dll cl sqlite3.c -DSQLITE_API=__declspec(dllexport) -link -dll -out:sqlite3.dll nmake /f makefile.msc nmake /f makefile.msc DYNAMIC_SHELL=1 -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile Windows dll with MinGW with stdcall calling convention?
Hello, On 2017-02-20 20:07, Keith Medcalf wrote: Yes, and I see the defines for those in the sqlite3.c file, but they are not used anywhere ... That is, I would have expected to see: SQLITE_API void SQLITE_APICALL sqlite3_.() in all the function definitions. But I don't. Is there some "special" building of the amalgamation required for these macro's to appear in the function definitions? Yes. They are used while building the amalgamation (``sqlite3.c'', ``sqlite3.h'', ``sqlite3ext.h'') from sources (``-+useapicall'' tcl script option, ``USE_STDCALL'' nmake macro). You can build the amalgamation in such a way that those macros will appear. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile Windows dll with MinGW with stdcall calling convention?
Hello, On 2017-02-19 18:31, Bart Smissaert wrote: gcc -o SQLite3_StdCall.dll -shared sqlite3.c -s -Wl,--subsystem,windows,-- kill-at objdump of SQLite3_StdCall.dll sqlite3_close: 6574f392: 55 push %ebp 6574f393: 89 e5 mov%esp,%ebp 6574f395: 83 ec 18sub$0x18,%esp 6574f398: c7 44 24 04 00 00 00movl $0x0,0x4(%esp) 6574f39f: 00 6574f3a0: 8b 45 08mov0x8(%ebp),%eax 6574f3a3: 89 04 24mov%eax,(%esp) 6574f3a6: e8 ed fe ff ff call 0x6574f298 6574f3ab: c9 leave 6574f3ac: c3 ret Hopefully GCC is not as clever as CL, in particular it is not wiser then a programmer, who is using it. Thus, it emits 0xC3 instead of 0xC2, until you will tell it that you want __stdcall. Still, you have C calling convention -- not stdcall one. If you do not want to make CL working, I strongly recommend you to stop playing with GCC and to use other compiler, for example Open Watcom (1.9 or 2.0): wcl386 -dSQLITE_CDECL=__cdecl -ecd -bd -bm -l=NT_DLL sqlite3.c -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile Windows dll with MinGW with stdcall calling convention?
Hello, On 2017-02-19 22:25, Keith Medcalf wrote: Can you tell us how you do it with the amalgamation using MSVS? ``autoconf'' (containing the same amalgamated ``sqlite3.c'') has the following options for that task: -Gz -DSQLITE_CDECL=__cdecl -DSQLITE_APICALL=__stdcall -DSQLITE_CALLBACK=__stdcall -DSQLITE_SYSAPI=__stdcall dumpbin sqlite3.dll: _sqlite3_close@4: 1000F6F0: 6A 00 push0 1000F6F2: FF 74 24 08pushdword ptr [esp+8] 1000F6F6: E8 35 B4 05 00 call1006AB30 1000F6FB: C2 04 00 ret 4 sqlite3Close: 1006AB30: 56 pushesi [...] 1006AB89: 5E pop esi 1006AB8A: C2 08 00 ret 8 @OP: Another question is why are you trying to transform GCC into CL instead of to make CL working properly? Such things will give you nothing more then a lot of new problems including a nuclear launch :-). -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile Windows dll with MinGW with stdcall calling convention?
Hello, On 2017-02-18 17:35, Bart Smissaert wrote: This is simple in MSVS, but can't see how this can be done with the MinGW compiler. Any idea how this can be done with the sqlite3.c amalgation? I have just tried ``CFLAGS=-mrtd -DSQLITE_CDECL=__cdecl'' option on different compilers and configurations, but without a success. :-( ``-mrtd'' is not working as good as MSVS' ``-Gz''. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Decrypt .db file
Hello, On 2017-02-18 13:11, Richard Hipp wrote: On 2/17/17, Rone Qwerty <roneqwe...@yahoo.com> wrote: Dear sir, would you please inform me, how to dycrept .db file if i forgot the password. You must try all possible passwords, one by one, until you find the right one. This is very difficult, as there are many possible passwords. The whole point of encryption is to prevent people from accessing the file who do not know that password. If there were a easy way to recover the password, then it wouldn't be "encryption". Hmm... Just evocative $0.02 to set fire to I-ve-forgotten-pwd's imagination: 256bit key gives 2^256 possibilities, which is about a number of particles in the observable Universe. Would you try to count them all? If it had not been a prospective security reduction, then 128 bits would have been enough. The latter number is a more-or-less accurate approximation of a number of atoms on the Earth -- much less then 2^256, but still not easy obtainable. @OP: In practice you do not need to brute-force 2^256 or 2^128 permutations. Consider words which you could use as elements of the password. Together with numbers, special symbols, casing they will produce billions permutations at most. To be more lifelike, I would assume, that they will produce millions permutations -- quite obtainable result. Certainly, only if you have some small testable fragment (for example, one 128 bit AES block). As for me, recovery from forgotten password never exceeded 2 weeks, but often it fit in minutes, in case of ``normal'' (i.e. not like ``kaksj8ooY^*&^O*&*%P'') passwords. Good luck. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 tokenize unicode61 does not remove diacritics correctly?
Hello, On 2017-02-16 10:53, artur.krol.elea...@sqlite.org wrote: [...] The result is: eoałzzcneoałzzcnlł It seems diacritics from letter „ł” and „Ł” was not removed. Is it a sqlite bug? In general, overlays (slash, crossbars, etc.) are considered as diacritics, however, Unicode does not provide a decomposition mapping for ``ł'', or ``Ł''. Even if it is a bug, then it will concern the Unicode standard rather then SQLite FTS3 itself, as the latter is using the character database provided by the Unicode standard. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem compiling 3.17.0 in MSVS 12
Hello, On 2017-02-15 01:56, Bart Smissaert wrote: Downloaded again and now it looks more healthy: [...] b0245450e4f27eeefc0da4a871833eb1 sqlite3.c Still not an original one! MD5 of an original ``sqlite3.c'' is 1efd683943e0d2bce1495b3413e2e235. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem compiling 3.17.0 in MSVS 12
Hello, On 2017-02-15 01:26, Bart Smissaert wrote: OK, that gives this: [...] bd6dfd8b2b566ca64ff9f4c637e533f6 That means, you are not using an original, unmodified sqlite3.c file. Download/unpack an original autoconf package and try once again. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem compiling 3.17.0 in MSVS 12
Hello, On 2017-02-15 00:53, Bart Smissaert wrote: I tried first with the original source, without altering anything at all. I then had the errors. Could you download and unpack https://support.microsoft.com/en-us/help/841290/availability-and-description-of-the-file-checksum-integrity-verifier-utility then run from VS command prompt: fciv sqlite3.c && nmake /f Makefile.msc then send the output? You have run a similar command in a response to DRH. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem compiling 3.17.0 in MSVS 12
Hello,
On 2017-02-14 23:52, Bart Smissaert wrote:
I tried both with and without that backslash.
``That backslash''? Did you mean ``Those backslashes''?
In the first post you mentioned that the problem concerned ``while'',
however in the response to DRH, the first error appeared at ``if''.
1. ``while'' -- you had backslash at the end of the first line: error
was caused by a statement ``while'' out of a function, and you should
receive an additional syntax error at the first closing ``}'' after
``while''.
2. ``if'' -- you had no backslashes: error was caused by a statement
``if'' out of a function, and you received an additional syntax error at
final closing ``}'' (the first closing ``}'' was paired with opening
``{'' after ``if'').
All lines but the last one (containing final ``}'') of the macro must be
ended with ``\'' and a newline. Make sure that newline immediately
follows a backslash, because \ is not recognized as a
line continuation. A splicing physical lines to establish logical ones
is done at the very early stage of translation, even before
spaces/comments are removed.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: CREATE TABLE AS with GROUP BY preserves backticks in generated column name
Hello,
On 2017-02-10 20:34, Alek Storm wrote:
CREATE TABLE t1 (col1 INT);
CREATE TABLE t2 AS SELECT `col1` FROM t1 GROUP BY col1;
.schema t2
CREATE TABLE t2("`col1`" INT);
I expected: CREATE TABLE t2(col1 INT);
This comes from the fact that all TK_COLUMN expressions in SELECT ...
GROUP BY command are transformed into TK_AGG_COLUMN ones and as such
their names are taken from ``zSpan'' (i.e. original text of an
expression). Because the original text contains back quotes, it is
further enclosed in double quotes. The following patch would help,
however I'm not sure if an additional checking/validation is not required:
File ``select.c'', foo ``sqlite3ColumnsFromExprList(...)'', change the
following line:
if( pColExpr->op==TK_COLUMN && ALWAYS(pColExpr->pTab!=0) ){
into
if( (pColExpr->op==TK_COLUMN || pColExpr->op==TK_AGG_COLUMN) &&
ALWAYS(pColExpr->pTab!=0) ){
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2 consecutive rises in value
Hello, On 2016-10-20 01:58, Bart Smissaert wrote: I worked it round to get 3 consecutive drops: [...] JOIN C1 AS B ON B.ID = A.ID AND B.DT = (SELECT MAX(X.DT) FROM C1 AS X WHERE X.ID = A.ID AND X.DT > A.DT) JOIN C1 AS C ON C.ID = A.ID AND C.DT = (SELECT MAX(X.DT) FROM C1 AS X WHERE X.ID = A.ID AND X.DT < B.DT) JOIN C1 AS D ON D.ID = A.ID AND D.DT = (SELECT MAX(X.DT) FROM C1 AS X WHERE X.ID = A.ID AND X.DT < C.DT) Probably you have forgotten to change a direction of the first comparison. but I must have done something wrong as it picked 62 up from this: 62 2005-01-07 44 62 2006-02-01 47 62 2006-05-22 45 62 2007-04-05 45 62 2007-08-14 45 62 2008-05-21 46 62 2009-08-24 46 62 2010-10-08 45 62 2011-12-07 47 62 2013-01-17 46 62 2014-02-25 37 62 2015-03-30 39 62 2016-09-02 40 Any idea what I did wrong? The above mentioned JOIN was originally used to extract consecutive ISO8601_DATE triplets, so you should not change MIN to MAX, nor reverse comparison operators. If you want to have a quadruplets you should append fourth JOIN without any changes besides C to D: JOIN C1 AS D ON D.ID = A.ID AND D.DT = (SELECT MIN(X.DT) FROM C1 AS X WHERE X.ID = A.ID AND X.DT > C.DT) Optionally you can change MIN => MAX, ``>'' => ``<'' in JOIN (which results in reversed quadruplets) and do NOT reverse comparison operators in the final WHERE clause. You cannot both reverse tuplets and comparison operators in the final WHERE clause because one operation cancels an effect of other. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite3_errcode()
On 2016-09-29 15:17, Otto Wallenius wrote: [...] To my understanding it follows that if you first call sqlite3_column_blob() and then sqlite3_errcode(), the errcode might be SQLITE_NOMEM whether memory allocation failed or not: either it failed and the error code was set ot SQLITE_NOMEM, or it succeeded and the code happened to be SQLITE_NOMEM because it is undefined. Is this correct, and how can you check for errors in this case? Actually, sqlite3_errcode() is unchanged in case of success (i.e. it returns the last failure's code). If you want to use the sole, pure ``sqlite3_*'' API, then try to reset sqlite3_errcode() to SQLITE_OK by using the following NOP: sqlite3_exec(db, NULL, NULL, NULL, NULL); just before sqlite3_column_blob() or family is called and check a value of sqlite3_errcode() just after the called function has returned --- if the value is still SQLITE_OK (regardless of what the called function returned: 0/0.0/NULL or not) then an error has not occurred. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Patch that add table alias capability to delete/update statements.
Hello, Would you be so kind as to use ``Write e-mail'' instead of ``Reply'' when you are beginning a new thread? Thank you in advance. -- best regards Cezary H. Noweta
[sqlite] sqlite3_close does not propagate the error from sqlite3_io_methods.xClose
Hello, On 2016-04-14 16:16, Dobrean, Adrian wrote: > Is xSync guaranteed to be called only once, same as for xClose, if it is then > from flush I/O point of view we should be almost no difference. No --- it is not guaranteed even once, as I described in previous post. > However still xClose releases some resources we would need to know when they > fail. You should consider redesigning of your VFS driver architecture in such a way that releasing is always successful. -- best regards Cezary H. Noweta
[sqlite] sqlite3_close does not propagate the error from sqlite3_io_methods.xClose
Hello, On 2016-04-14 15:40, Richard Hipp wrote: > On 4/14/16, Dobrean, Adrian wrote: >> >> The problem is that when I close there are still I/O operations that >> sometimes need to happen, e.g. flush of some buffers, and I need to know if >> those operations finished successfully or not. > This shouldn't ever come up. xSync should have been called to flush > all I/O operations prior to xClose. ``xSync'' is not called when ``PRAGMA synchronous = OFF''. You should consider monitoring a state of ``synchronous'' by using ``SQLITE_FCNTL_PRAGMA'' > The xClose method is just releasing resources. Kind of like free() in > the standard library. Why doesn't standard-library free() return an > error code? These being said you should flush all buffers by ``xSync'', or disable all buffering and immediately pass all data down after ``PRAGMA synchronous = OFF'' (http://sqlite.org/pragma.html#pragma_synchronous). That is a designed behavior of a VFS driver. If you didn't comply with that schema, you would play on your own with SQLite's sources. -- best regards Cezary H. Noweta
[sqlite] Broken database after experiments with fts.
Hello, On 2016-03-29 19:25, Dan Kennedy wrote: >http://sqlite.org/src/info/d0a3853b37230c12 > The fix should appear as part of the 3.13 release (not 3.12 - the > version that was tagged earlier this morning). Could that check--in be incorporated into next minor version? It fixes rather serious bug which makes a database file corrupted in such a way that it cannot be repaired. (It can be repaired by copying all data to a new database or by binary editing of the database file.) -- best regards Cezary H. Noweta
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hello, On 2016-04-06 16:33, Richard Hipp wrote: > On 4/4/16, Mike Bayer wrote: >> The "type" column in PRAGMA table_info() is now a blank string when the >> target object is a view in 3.12.0. In 3.11.0 and prior versions, the >> typing information is returned, > I think the problem is fixed by this checkin: > https://www.sqlite.org/src/info/fb555c3c2af7f5e6 Perfunctory test showed that it works fine with plain 3.12.0 ([e9bb4cf4] Version 3.12.0 (user: drh, tags: trunk, release, version-3.12.0)) --- does not require intermediate check--ins. It goes even farther then 3.11.x because it keeps column's declared type when view's column name has changed (CREATE VIEW v(newname) ...). Thank you. -- best regards Cezary H. Noweta
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hello, On 2016-04-06 09:43, Darren Duncan wrote: > On 2016-04-05 10:19 AM, Richard Hipp wrote: >> It seems to me that the most consistent answer is that the "type" of >> columns in a VIEW should always be an empty string. > That's only consistent if you do the same thing with base tables. Non--consistency: sqlite> CREATE TABLE a (a DECIMAL(10)); sqlite> CREATE TABLE b AS SELECT * FROM a; sqlite> CREATE VIEW bv AS SELECT * FROM a; sqlite> PRAGMA table_info(b); 0|a|NUM|0||0 sqlite> PRAGMA table_info(bv); 0|a||0||0 Info on column's affinity (besides if it is a declared one --- DECIMAL, FLOAT --- or a real one --- NUMERIC, REAL) is still useful, when comparison is made. Column's affinity is still propagated: sqlite> CREATE TABLE b2 AS SELECT * FROM bv; sqlite> PRAGMA table_info(b2); 0|a|NUM|0||0 so the point is that ``PRAGMA table_info'' stopped displaying column's affinity in case of views. On the other side, views are not tables so a consistency does not require to preserve that info. In 3.11.x ``PRAGMA table_info'' was not displaying an affinity in case of expressions, too: === SHELL 3.11.x === sqlite> CREATE VIEW av2 AS SELECT CAST(+a AS NUMERIC) FROM a; sqlite> PRAGMA table_info(av2); 0|CAST(+a AS NUMERIC)||0||0 so the decision was to remove that view's info at all. Now, we know that this behavior was changed by an arbitrary, intentional decision. We can debate about correctitude of that decision (there are pros and cons), however more important thing is to describe the fact in the documentation: http://sqlite.org/pragma.html#pragma_table_info: ``The table named in the table_info pragma can also be a view.'' --- in case of views only column's id and name are displayed. > Alternately, you can exploit the explicit column list that is optional > in a CREATE VIEW: > >CREATE VIEW v3 (foo NUMERIC) AS SELECT w+x+y+z AS foo FROM t2; Syntax error. Did you mean: CREATE VIEW v3 (foo) AS SELECT CAST(w+x+y+z AS NUMERIC) FROM t2; -- best regards Cezary H. Noweta
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hello,
On 2016-04-05 19:19, Richard Hipp wrote:
> Please explain why you think it is important to know the "type" of a
> column in a view?
For the same reason as it is important in case of a table. Both could be
consistent.
> There are further inconsistencies here. Example:
>
> CREATE TABLE t1(x INTEGER);
> CREATE VIEW v1 AS SELECT x FROM t1;
> CREATE VIEW v2(x) AS SELECT x FROM t1;
> PRAGMA table_info('v1');
> PRAGMA table_info('v2');
>
> As of version 3.12.0, the two pragma's give the same answer. but in
> version 3.11.0, they were different. Which of the two answers
> returned by 3.11.0 is correct?
IMHO, INTEGER. ``v2(x)'' changes nothing but the name of the column. It
has no impact on the column's affinity.
> Or, consider this situation:
>
> CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL);
> CREATE VIEW v3 AS SELECT w+x+y+z FROM t2;
>
> What should "PRAGMA table_info('v3')" report as the column type?
Opposite to the above: w+x+y+z loses affinity so it would be displayed
as an empty string.
> It seems to me that the most consistent answer is that the "type" of
> columns in a VIEW should always be an empty string.
If so, Mike's postulate is arguable. This fact could be mentioned in a
description of ``table_info'' PRAGMA: in case of views only id and a
name of a column is meaningful.
-- best regards
Cezary H. Noweta
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hello, On 2016-04-05 17:05, Mike Bayer wrote: > OK...so *that* is a change. Is *that* by design, and if so should that > be described here http://sqlite.org/releaselog/3_12_0.html ? I hope that is by an accident. While info about ``notnull'' and ``defaultvalue'' (which is not carried in 3.11.x too) is not meaningful in case of read-only views, then ``affinity'' info is still important (for comparision). > Otherwise if this is an unexpected regression then I hope a ticket can > be filed. +1. -- best regards Cezary H. Noweta
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
Hello, On 2016-04-04 22:14, Mike Bayer wrote: > if it is by design that views no longer carry typing information, this > is a major behavioral change and no mention of it occurs in the release > notes for 3.12.0, so at the very least this change should be documented > if expected. This is not true. Column's affinity is still propagated to a view, though it is not displayed by ``PRAGMA table_info''. Try: sqlite> INSERT INTO users VALUES (2); sqlite> SELECT * FROM users_v WHERE user_id > '1'; 2 If an affinity was not carried then the above ``SELECT'' would return an empty set, while it returns ``2'' (as in SQLite 3.11.x). IMHO, this described by you behavior can be considered as a bug in ``PRAGMA table_info'', which takes column's affinity from ``CREATE TABLE'' command only as for now. -- best regards Cezary H. Noweta
[sqlite] Broken database after experiments with fts.
Hello, On 2016-03-29 20:36, Cezary H. Noweta wrote: > I'm sorry, if I've missed something, but what about Parse::isMultiWrite? > AFAIK, Parse::mayAbort is used solely in conjunction with > Parse::isMultiWrite. How can lone Parse::mayAbort influence on > OP_Transaction (or on any other behavior regarding CREATE VIRTUAL > TABLE)? I'm off the scent, while looking at the SQLite's repository. Probably, I've found it: does sqlite3StartTable() do the work with Parse::isMultiWrite? -- best regards Cezary H. Noweta
[sqlite] Broken database after experiments with fts.
Hello, On 2016-03-29 19:25, Dan Kennedy wrote: > I think that's the right thing to do as well. See here: > >http://sqlite.org/src/info/d0a3853b37230c12 > > The fix should appear as part of the 3.13 release (not 3.12 - the > version that was tagged earlier this morning). I'm sorry, if I've missed something, but what about Parse::isMultiWrite? AFAIK, Parse::mayAbort is used solely in conjunction with Parse::isMultiWrite. How can lone Parse::mayAbort influence on OP_Transaction (or on any other behavior regarding CREATE VIRTUAL TABLE)? I'm off the scent, while looking at the SQLite's repository. -- best regards Cezary H. Noweta
[sqlite] Broken database after experiments with fts.
Hello, On 2016-03-26 15:37, John Found wrote: > Why cannot drop the table test? > > sqlite> begin transaction; > sqlite> create virtual table test using fts5; > Error: vtable constructor failed: test > sqlite> commit; It is not required. Non-commited-and-non-rolledback, dangling transaction suppresses all operations on vtable ``test''. COMMIT is required to damage database file permanently. > sqlite> > sqlite> drop table test; > Error: vtable constructor failed: test > > sqlite> .tables > test test_content test_docsize > test_config test_data test_idx > > $sqlite3 --version > 3.11.1 2016-03-03 16:17:53 f047920ce16971e573bc6ec9a48b118c9de2b3a7 It should not be possible to damage a database by an using of clean SQL commands only. Hopefully SQLite has a helpful statement transaction, which can be used in this case. I must stipulate that in the following is an ad-hoc solution, which may be inaccurate and is included to illustrate a problem only. In ``vtab.c:sqlite3VtabBeginParse()'', at the beginning insert two lines: == sqlite3MultiWrite(pParse); sqlite3MayAbort(pParse); == or == pParse->isMultiWrite = 1; pParse->mayAbort = 1; == I'm not sure which solution is better (if any), but I've tested the former one and it works ok. It forces ``OP_Transaction'' to be a statement transaction, which causes rolling back all VDBE ops in case of an error. In ``vdbeaux.c:sqlite3VdbeAssertMayAbort()'' change: == if( opcode==OP_Destroy || opcode==OP_VUpdate || opcode==OP_VRename == to == if( opcode==OP_Destroy || opcode==OP_VUpdate || opcode==OP_VRename || opcode==OP_VCreate == This allows assertions to be passed, because ``CREATE VIRTUAL TABLE'' does not produce any of ``abortable'' opcodes. -- best regards Cezary H. Noweta
[sqlite] report a bug
Hello,
On 2016-03-23 07:32, Dan Kennedy wrote:
> On 03/23/2016 10:48 AM, ? wrote:
>> I build a table using fts5,like this,
>> CREATE VIRTUAL TABLE tbl_tha using fts5( key1, key2,TOKENIZE="thai");
>>
>> and then insert a record:
>> insert into tbl_tha values('??','??');
>> [...]
>> SQL1:select * from tbl_tha where tbl_tha match '';
>> [...]
>> SQL2 can query the result,but SQL1 return null;
>
> [...] So SQL1 is equivalent to:
>
> ... MATCH "??? + ??? + + ??"
You can change that behavior by changing
``fts5_expr.c:fts5ExprGetToken()'' in such a way that it will split a
text into separate tokens, for example:
change:
==
for(z2=[1]; sqlite3Fts5IsBareword(*z2); z2++);
pToken->n = (z2 - z);
==
to something like:
==
for(z2=[1]; sqlite3Fts5IsBareword(*z2); z2++) {
if ( isLastCharOfWord(z2) ) {
z2 = [numberOfBytesOfChar(z2)];
break;
}
}
pToken->n = (z2 - z);
==
However you must notice, that such correction breaks rules mentioned by
Dan Kennedy and have an impact on a whole FTS5 mechanism.
-- best regards
Cezary H. Noweta
[sqlite] Article about pointer abuse in SQLite
Hello, On 2016-03-22 00:35, James K. Lowden wrote: >[...] An example from Clang's discussion is > > int i = 10 << 31; Could you provide a link for that discussion? (Or google's phrase to retrieve such link?) -- best regards Cezary H. Noweta
[sqlite] Reserved column names
Hello, On 2016-03-21 08:57, Dominique Devienne wrote: > Seems like using square-brackets instead of double-quotes is non-standard: > https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Data_structure_definition/Delimited_identifiers Explicitly documented by SQLite: http://sqlite.org/lang_keywords.html -- best regards Cezary H. Noweta
[sqlite] CAST STRING => INTEGER
Hello, > The $64 bit question ;) is how much existing code might break if such > changes were made. One can argue that the existing implementation is > broken, but a lot of software has been written to use it as it is. What > happens to them if such an improvement is made? A nuclear launch? ;) As for now I cannot find a way to introduce such deep improvements other then external and independent fork of SQLite with an alternate type system. For example, is there any software which is expecting NULL or something other then ``0'' after it has cast ``zz'' to NUMBER? BWT. The implementation is not broken --- nobody uses broken things. :) -- best regards Cezary H. Noweta
[sqlite] CAST STRING => INTEGER
neg = 1;
zNum += incr;
} else if ( *zNum == '+' ) {
zNum += incr;
}
}
/* zStart points to a positive number part */
zStart = zNum;
/* Skip leading zeroes */
while ( zNum < zEnd && zNum[0] == '0' ) { zNum += incr; }
for ( i = 0; [i] < zEnd && (c = zNum[i]) >= '0' && c <= '9'; i
+= incr ) {
u = u * 10 + c - '0';
}
/* Now:
** (1) '-|+'
** (2) [zStart] '0'
** (3) [zNum] '20120300230230320843'
** (4) [zNum + i] '...'
** (5) [zEnd] trailing spaces and/or >U+00FF */
/* Let's apply valid range to the number */
if ( i > 19 * incr || u > LARGEST_INT64 ) {
*pNum = neg ? SMALLEST_INT64 : LARGEST_INT64;
/* Let's set tooBig if integer part together with leading sign does
not fit */
tooBig = i > 19 * incr || 0 == neg || LARGEST_INT64 < u - 1;
} else if ( neg ) {
*pNum = -(i64)u;
} else {
*pNum = (i64)u;
}
testcase( i==18 );
testcase( i==19 );
testcase( i==20 );
isTrash = nonNum || 0 != c && [i] < zEnd;
wereDigits = 0 < i || zStart < zNum;
if ( 0 == isTrash && 0 != wereDigits ) {
/* Clean and perfect integer so return 0; */
if ( 0 == tooBig ) return 0;
/* -SMALLEST_INT64 so, for unary minus, return 2; */
if ( LARGEST_INT64 == u - 1 ) return 2;
}
/* The following part is relevant only for sqlite3VdbeMemNumerify(),
** so it can be omitted in other cases
** (for example by supplying int nCheckReality parameter, or other). */
hasRealSyntax = 0 != tooBig || /* too big for 64-bit int, or */
[i] < zEnd && (
'.' == c && ( /* has dot '.'... */
wereDigits || /* preceded... */
[i + 1] < zEnd &&
'0' <= zNum[i + 1] && /* or followed by digits, or */
'9' >= zNum[i + 1]
) || (
'e' == c || 'E' == c /* has 'e' or 'E'... */
) && (
[i + 1] < zEnd && (
'0' <= zNum[i + 1] && /* followed by digits... */
'9' >= zNum[i + 1] || (
'-' == zNum[i + 1] || /* or followed by a sign... */
'+' == zNum[i + 1]
) &&
[i + 2] < zEnd &&
'0' <= zNum[i + 2] && /* and digits */
'9' >= zNum[i + 2]
)
)
);
return hasRealSyntax ? 1 : 3;
}
==
-- best regards
Cezary H. Noweta
[sqlite] How to read data from mem?
Hello, On 2016-03-16 07:03, Sairam Gaddam wrote: > Here the mem consists of entire row to be inserted. Format of this data is described here: http://sqlite.org/fileformat2.html#serialtype > When I read the mem > *pData, I found from the member pData->flags that it contains a BLOB. But > when I tried to print BLOB in pData->z, I didnt get correct results. How to > print a BLOB? This BLOB is not column's value converted into BLOB storage type --- this is all data of a whole record (all columns) inserted into a table according to the above specification. > I tried to print the contents in it by performing a deep copy of that mem > and converting it to real value using sqlite3VdbeRealValue(). But no > satisfactory result. When you are converting inserted BLOB into REAL then it is treated as TEXT and converted to REAL as if ``atof'' --- the first byte of ``pData->z'' is a header length (rather small number), which causes finish of conversion and returns 0.0. If you want to receive value of some column you must read above mentioned specification. If you are inserting REAL (notice that same REALs may be saved as INTEGERs!) you must found its offset in ``pData->z'' and extract data from this offset. If you want to take a control over particular data inserted into a table then the best method is to use triggers. -- best regards Cezary H. Noweta
[sqlite] CAST STRING => INTEGER
Hello,
On 2016-03-14 22:23, James K. Lowden wrote:
> [...]
Thank you for expanding my list of inconsistencies and things which can
be done in an other manner, with good reasons why ``other'' could become
``better''.
A rationale for my question was born while trying to fix rather obvious
inconsistencies described in
http://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg07399.html,
pts 2. & 4.:
CREATE TABLE tttest (col_num NUMERIC);
INSERT INTO tttest VALUES ...;
SELECT CAST(col_num AS NUMERIC) FROM tttest;
In the following table, ``...'' means 17 zeroes, '123' --- TEXT value,
123 --- INTEGER value:
++--++
|VALUE |col_num |CAST(col_num AS NUMERIC)|
+++-+---+-+-++
||is |IMHO shall be|ok?|is |IMHO shall be|ok? |
+++-+---+-+-++
|'9...1' | 9...1 | 9...1 |OK |9...1|9...1|OK |
|'9...1 '| 9...0 | 9...1 |-- |9...0|9...1|-- |
|'9...1x'|'9...1x'|'9...1x' |OK |9...0|9...1|-- |
+++-+---+-+-++
2nd row: why REALs can have trailing spaces, while INTEGERs cannot?
3rd row: why REALs can have trailing trash, while INTEGERs cannot?
While fixing, I spotted a problem mentioned by you:
> 2)sqlite> select cast('1' as int);
> cast('1' as int)
>
> 7766279631452241920
It would be:
1. left as is, i.e. CAST(manydigitstext AS INTEGER) ==
MAX(TEXT2INT(manydigitstext)%2^64,LARGEST_INT64) --- Clemens Ladisch'
opinion;
2. CAST(manydigitstext AS INTEGER) == {SMALLEST_INT64,LARGEST_INT64} ---
your opinion as I understood you well;
3. extremely (even exaggeratedly) adjusted with the doc's ``the longest
possible prefix of the value that can be interpreted as an integer
number is extracted'' and ignoring digits which cause overflow --- the
worst case scenario, as it could introduce many new inconsistencies.
-- best regards
Cezary H. Noweta
[sqlite] CAST STRING => INTEGER
Hello,
On 2016-03-14 13:25, Clemens Ladisch wrote:
> Cezary H. Noweta wrote:
>> Is your opinion
>
> Why would my opinion matter, as opposed to what SQLite actually does?
Because, SQLite behaves in a bit strange manner, which is opposite to
extreme carefulness of SQLite in other areas. I'm trying to know if this
behavior (or not so determined behavior) is intentional. SQLite's public
domain source could be an endorsement to the official documentation, but
under condition that the code's behavior is intentional and bug free ---
this is what I am trying to lay down.
>> that ``SELECT CAST(col AS INTEGER);'' should return (not so) random
>> result set, and receiving any INTEGER should mean that a source string
>> could have trillion or more possible values?
>
> The documentation does not specify how the textual representation of an
> out-of-range integer is to be converted, so anything SQLite does can be
> considered correct.
I do not agree. Due to ``lang_expr.html#castexpr'':
``When casting a TEXT value to INTEGER, the longest possible prefix of
the value that can be interpreted as an integer number is extracted from
the TEXT value and the remainder ignored.''
Now, the algorithm is: (1) calculate value MOD 2^64; (2) return MAX(the
(1)'s value, LARGEST_INT64). It looks a bit random with bias into
LARGEST_INT64 direction.
1. Why REALs out of range are saturated to <SMALLEST_INT64,
LARGEST_INT64> range, while TEXT integers are not (which is not documented)?
2. Why REAL => INTEGER conversion is extremely accurate described
together with a previous versions' behavior and a rationale for it,
while TEXT => INTEGER conversion is not?
Pro for your consideration of correctness would be using of a textual
comparision with -SMALLEST_INT64 in ``sqlite3Atoi64()'', however the
comparision is made under certain condition that a number was exactly 18
digits long, so it could be reduced to ``return u-1>LARGEST_INT64 ? 1 :
!neg && u-1==LARGEST_INT64 ? 2 : 0;'', which, in turn, cancels any
rationales for using a textual comparision.
Maybe somebody should put ``if ( i > 19 * incr || u > LARGEST_INT64 )
{'' instead of ``if( u>LARGEST_INT64 ){'' in mentioned ``sqlite3Atoi64()''?
It would be nice to know that source STRING (or at least the leading
digits as mentioned in the doc) was perfect INTEGER after
non-distinctive number (i.e. != SMALLEST_INT64, LARGEST_INT64, 0) has
been received from CAST, would not it be?
-- best regards
Cezary H. Noweta
[sqlite] CAST STRING => INTEGER
Hello, On 2016-03-14 12:03, Clemens Ladisch wrote: > The documentation is quite clear that INTEGERs have 64 bits. So trying > to use integers above 9223372036854775807 is something that is better > not to be done. How could somebody use integers above 9223372036854775807 if they do not exist? '1' is a TEXT, not INTEGER. I asked about CASTing to INTEGER. Is your opinion that ``SELECT CAST(col AS INTEGER);'' should return (not so) random result set, and receiving any INTEGER should mean that a source string could have trillion or more possible values? -- best regards Cezary H. Noweta
