Re: [sqlite] No feedback for executing a mathematical expression

[radovan5]

Hi,

because sqlite calculate expression 30/57 as zero or 0.
So this give then divide by zero error when 55/0 is calculated.
Search more on google "Divide by 0 not giving error in sqlite".

If you need to calculate you must change this so that one number
has affinity real like this:

select 99-(55/(30.0/57))

But I like that when you have integer numbers is like div function.
You remember div function like: 10 div 5 = 2.


On 10.03.2020 08:21, Octopus ZHANG wrote:

Hi all,


I try to run a simple math expression, but SQLite gives no feedback :

sqlite> select 99-(55/(30/57));




Should I expect it to return nothing?


Thank you



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

[radovan5]

In SQLite type guid does not exist. Look at this documentation:
https://www.sqlite.org/datatype3.html

Affinity for guid would be BLOB but in trigger you store TEXT.
I do not use .NET but I think you should use different parameter
type. I would use TEXT type.

Regards R.A.

On 14.02.2020 15:09, Andy KU7T wrote:

Hi,

I am having trouble searching for a Guid. I think some conversion is missing.  
I wonder if someone can point me to the error…

Column:
[ID] GUID(16)
Index:
CREATE UNIQUE INDEX [ID_INDEX] ON [DXLOG]([ID] COLLATE [BINARY] ASC);
Trigger:
CREATE TRIGGER [AUTOGENERATE_ID] AFTER INSERT ON [DXLOG] FOR EACH ROW WHEN 
([NEW].[ID] IS NULL)
BEGIN
   UPDATE
 [DXLOG]
   SET
 [ID] = HEX (RANDOMBLOB (16))
   WHERE
 [NEW].[ID] IS NULL AND [DXLOG].[ROWID] = [NEW].[ROWID];
END;

Inserting a row simply will create a new guid for me. Good. Reading it like 
this:

SELECT ID FROM [Dxlog] WHERE RowId = @RowID

Where @RowId is the LastInsertedRowId.
And getting it in code:

Dim rdr2 As SQLiteDataReader = Nothing
rdr2 = theDB.ExecuteQuery("SELECT ID FROM [Dxlog] WHERE RowId = @RowID", 
sqlParameters)
rdr2.Read()
Me.mvarId = rdr2.Item("ID")

The returned ID is indeed a Guid. In SQLite it looks like this:

40FD6722384053ED3BA45CD1C5FDB30D
And in .NET: {40fd6722-3840-53ed-3ba4-5cd1c5fdb30d}

Now, when I am trying to search for it like this, I get nothing:

 sqlParameters = New List(Of SQLiteParameter)()
 AddSqlParam(sqlParameters, "@ID", DbType.Guid, Me.Id)

 rdr2 = theDB.ExecuteQuery("SELECT * FROM [Dxlog] WHERE ID = 
@ID", sqlParameters)
 rdr2.Read()


Do I have to converte the Guid to binary somehow?

Thanks
Andy

Sent from Mail for Windows 10

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[radovan5]

ANALYZE dosn't help because data is loaded from RDBMS
for processing every time.

Why this change give different times (nobody is interested):

"and R.ID_ARHDAJ is not null"

to

"and ifnull(R.ID_ARHDAJ,0) <> 0"

Regards Radovan


On 06.12.2019 15:52, Simon Slavin wrote:

On 6 Dec 2019, at 10:05am, radovan5  wrote:


Just in my case analyze is quite slow also. So in the end I get same time. It 
is faster to use just workaround in sql.

The results of ANALYZE are stored in the database.  So although the total time 
is about the same when you do run it, once you have run ANALYZE with realistic 
data, you do not need to run it again.  Running the query will be faster every 
time in the future.

Some programmers include ANALYZE in a monthly or yearly maintenance task.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[radovan5]

Can you explain why workaround is fast. Why one version is fast and
other slow. I'm talking about this change in where:

"and R.ID_ARHDAJ is not null"

to

"and ifnull(R.ID_ARHDAJ,0) <> 0"

For me analyze is no improvement because data is loaded from RDBMS
and would have to run always after load.

Regards Radovan

On 06.12.2019 14:20, Richard Hipp wrote:

On 12/6/19, Keith Medcalf  wrote:

Perhaps the optimizer could make a "left join" that is not actually an outer
join into a "cross join" and preserve the nesting order ... ?


It could do that, but that would kind of defeat the whole purpose of
the optimization, which was to free up the planner to choose a
different nesting order in cases where the LEFT JOIN didn't really
matter.

I suspect that ANALYZE might also solve the OP's problem.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[radovan5]

Well I have to write sql like that because I must have one table in 
from. Some component I have
depend on this. This has worked fast before but I have workaround so is 
not a problem.


Regards Radovan

On 06.12.2019 11:35, Keith Medcalf wrote:

The join that you are using is not an outer join because you have constrained R.ID_ARHDAJ (which 
cannot be null in the table) to not be null, and the only way it can be null is if the left join is 
a "miss", meaning that it is really an inner join, not an outer join).  The optimizer 
spots this and does not force the nesting order implied by the "left join" operator -- 
choosing instead to process the query as an inner join.  If the reason that you are using this 
construction is specifically for the purpose of enforcing the nesting order then you should be 
phrasing your query as such:

 select R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
   from IZBPLA P
cross join ARHDAJ R
 on R.ID_ARHPLA = P.ID_PLA
  where P.ID_PLA < 10;

so SQLite3 knows that you *always without exception* want R to be an inner loop 
to P no matter what anyone (including the statistics say about it).

Also, now that you have run ANALYZE the information about the relative shape of 
your data has been saved for the optimizer to use when optimizing queries.  You 
do not need to run ANALYZE again unless the shape significantly changes.

Generally though you would be better served to express the select as what it 
really is, run ANALYZE from time to time, and let the optimizer do what it is 
supposed to do, which is to get what you asked for as effeciently as possible.

select R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
   from IZBPLA P
   join ARHDAJ R
 on R.ID_ARHPLA = P.ID_PLA
  where P.ID_PLA < 10;

Perhaps the optimizer could make a "left join" that is not actually an outer join into a 
"cross join" and preserve the nesting order ... ?



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changes in version 3.28.0 makes sql run slower

[radovan5]

Yes it has and I get correct plan. Did not use analyze before or pragma 
optimize
but I see I would have to. Thank you for reminding me to this. Just in 
my case
analyze is quite slow also. So in the end I get same time. It is faster 
to use just

workaround in sql.

Regards Radovan

On 06.12.2019 10:33, Shawn Wagner wrote:

Does running an ANALYZE have any impact?

On Fri, Dec 6, 2019 at 12:47 AM radovan5  wrote:


Also tested with latest version 3.30.1. For showing problem I need just
2 tables (for join).

-- cca 30.000.000 recs
CREATE TABLE ARHDAJ(
ID_ARHDAJ integer primary key autoincrement,
ID_ARHPLA integer,
R integer,
O decimal(8,4),
Z decimal(12,2),
VEZA varchar(8) collate systemnocase);
CREATE UNIQUE INDEX ARHDAJ_IDX_ARHPLA on ARHDAJ (ID_ARHPLA,R,ID_ARHDAJ);
CREATE UNIQUE INDEX ARHDAJ_IDX_R on ARHDAJ (R,ID_ARHDAJ);

-- cca 100 recs
CREATE TABLE IZBPLA (
ID_PLAinteger primary key autoincrement,
ID_DELinteger,
L4MM  integer,
OEvarchar(2) collate systemnocase,
SMvarchar(4) collate systemnocase,
DNvarchar(10) collate systemnocase,
DDM   integer,
P integer,
U decimal(6,2),
UNdecimal(6,2),
O decimal(8,4),
ZOdecimal(12,2),
ZBdecimal(12,2),
ZNdecimal(12,2),
OSdecimal(8,4),
DOH   varchar(1) collate systemnocase);
CREATE UNIQUE INDEX IZBPLA_IDX_DEL on IZBPLA(ID_DEL, ID_PLA);
CREATE UNIQUE INDEX IZBPLA_IDX_P on IZBPLA(P, ID_PLA);

Problematic sql is:

select
  R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
from IZBPLA P
left join ARHDAJ R on R.ID_ARHPLA = P.ID_PLA
where P.ID_PLA < 10
  and R.ID_ARHDAJ is not null;

explain query plan before 3.28.0 is:

0: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowid 0".

So sqlite goes through large table but should go through small table and
search record in
large for every record.

Best Regards
Radovan Antloga

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Changes in version 3.28.0 makes sql run slower

[radovan5]

Also tested with latest version 3.30.1. For showing problem I need just 
2 tables (for join).


-- cca 30.000.000 recs
CREATE TABLE ARHDAJ(
  ID_ARHDAJ integer primary key autoincrement,
  ID_ARHPLA integer,
  R integer,
  O decimal(8,4),
  Z decimal(12,2),
  VEZA varchar(8) collate systemnocase);
CREATE UNIQUE INDEX ARHDAJ_IDX_ARHPLA on ARHDAJ (ID_ARHPLA,R,ID_ARHDAJ);
CREATE UNIQUE INDEX ARHDAJ_IDX_R on ARHDAJ (R,ID_ARHDAJ);

-- cca 100 recs
CREATE TABLE IZBPLA (
  ID_PLA    integer primary key autoincrement,
  ID_DEL    integer,
  L4MM  integer,
  OE    varchar(2) collate systemnocase,
  SM    varchar(4) collate systemnocase,
  DN    varchar(10) collate systemnocase,
  DDM   integer,
  P integer,
  U decimal(6,2),
  UN    decimal(6,2),
  O decimal(8,4),
  ZO    decimal(12,2),
  ZB    decimal(12,2),
  ZN    decimal(12,2),
  OS    decimal(8,4),
  DOH   varchar(1) collate systemnocase);
CREATE UNIQUE INDEX IZBPLA_IDX_DEL on IZBPLA(ID_DEL, ID_PLA);
CREATE UNIQUE INDEX IZBPLA_IDX_P on IZBPLA(P, ID_PLA);

Problematic sql is:

  select
    R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
  from IZBPLA P
  left join ARHDAJ R on R.ID_ARHPLA = P.ID_PLA
  where P.ID_PLA < 10
    and R.ID_ARHDAJ is not null;

explain query plan before 3.28.0 is:

0: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowidWorkaround is to change "and R.ID_ARHDAJ is not null" to "and 
ifnull(R.ID_ARHDAJ,0) <> 0".


So sqlite goes through large table but should go through small table and 
search record in

large for every record.

Best Regards
Radovan Antloga

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [FireDAC][Phys][SQLite]-326. Cannot perform the action, because the previous action is in progress.

[radovan5]

This is FireDAC error message not SQLite specific. I cannot reproduce it 
when
using default FireDAC settings. But I think you must check Database 
ResourceOptions
CmdExecMode. Change to am_NonBlocking. But I do not have to change that 
by default.


Regards Radovan

On 25.11.2019 18:34, Edson wrote:

Hi everyone!

I am using SQLite, with Delphi XE Rio and Firedac and I don't know why, a
cited error message appeared.

I've tried everything to unlock the database: I renamed the .db file,
changed the folder file, turned off the notebook, etc.,
but I can't unlock the .db database.

The error occurs when trying to open the query a second time, for example:

1) first execution - OK

qry.active: = False;
qry.SQL.Clear;
qry.SQL.Add ('DELETE FROM TAB_USERS');
qry.ExecSQL;

2) second execution - ERROR
qry.active: = False;
qry.SQL.Clear;
qry.SQL.Add ('INSERT INTO TAB_USERS ... ... ...');
qry.ExecSQL;

The error is fired at the line "qry.SQL.Clear;"

I don't know what else to do ... Does anyone have any tips?

Thankful!

  

  

  


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[radovan5]

Developers have problems using default round function so it is somethink 
we need.

So I have:

 ROUTINES FOR ROUNDING IEEE-754 FLOATS TO SPECIFIED NUMBER OF DECIMAL 
FRACTIONS


  These routines round input values to fit as closely as possible to an
  output number with desired number of decimal fraction digits.

  Because, in general, numbers with decimal fractions cannot be exactly
  represented in IEEE-754 floating binary point variables, error limits
  are used to determine if the input numbers are intended to represent an
  exact decimal fraction rather than a nearby value.   Thus an error limit
  will be taken into account when deciding that a number input such as
  1.295, which internally is represented 1.29499 9 …, really should
  be considered exactly 1.295 and that 0.2 9 ... really should
  be interpreted as 0.3 when applying the rounding rules.

I just provided 2 functions. But I have many options like (copy from code):

    (Abbr: 'HalfEven'; Dscr: 'Round to nearest or to even whole number '
 '(a.k.a Bankers) '),
    (Abbr: 'HalfPos' ; Dscr: 'Round to nearest or toward positive'),
    (Abbr: 'HalfNeg' ; Dscr: 'Round to nearest or toward negative'),
    (Abbr: 'HalfDown'; Dscr: 'Round to nearest or toward zero'),
    (Abbr: 'HalfUp'  ; Dscr: 'Round to nearest or away from zero'),
    (Abbr: 'RndNeg'  ; Dscr: 'Round toward negative. (a.k.a. Floor) '),
    (Abbr: 'RndPos'  ; Dscr: 'Round toward positive. (a.k.a. Ceil ) '),
    (Abbr: 'RndDown' ; Dscr: 'Round toward zero. (a.k.a. Trunc) '),
    (Abbr: 'RndUp'   ; Dscr: 'Round away from zero.') );


Radovan

On 24.05.2019 14:34, Simon Slavin wrote:

On 24 May 2019, at 1:30pm, Jose Isaias Cabrera  wrote:


Dr. Hipp, how many more scenarios, where round gives the wrong answer, exist?  
Thanks.

As Dr. Hipp wrote, round was giving the right answer.  All you need to do is 
pass the number 3.255 as the parameter.  If you're passing the wrong number, 
don't blame SQLite for getting the wrong result.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[radovan5]

Well I do. But when I use sqlite in PHP I have default round.

Radovan

On 24.05.2019 13:39, Alessandro Merolli wrote:

Great! Now use SQLite API and add a new user defined function for your used 
case.
I suppose that SQlite should always follow a well-defined pattern: in this case 
as Mr. Hipp said, it is IEEE754.
Cheers!


On 24 May 2019, at 08:27, radovan5  wrote:

In Delphi I have 2 functions that works (I override default sqlite round):

const
   ExtEps = 1.0842021725E-19;
   DblEps = 2.2204460493E-16;
   KnownErrorLimit = 1.234375;
   SafetyFactor = 2;
   MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor;
   MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor;

function RoundExt(const AValue: Extended; const ADigit: Integer = -2): Extended;
var
   E: Extended;
begin
   E := IntPower(10, -ADigit);
   Result := Round(AValue * (1 + MaxRelErrExt) * E) / E;
end;

function RoundDbl(const AValue: Double; const ADigit: Integer = -2): Double;
var
   E: Double;
begin
   E := IntPower(10, -ADigit);
   Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E;
end;

You could implement it in sqlite.

Regards Radovan


On 24.05.2019 13:13, Richard Hipp wrote:

On 5/24/19, Hajo Bruns  wrote:

Hi,
the round function seems to round inconsistently:

ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
gives
5,49  3,56  3,25

Last result should be 3.26

3.255 cannot be exactly represented as an IEEE754 double-precision
binary floating point number.  So the system has to use an
approximation.  The closest approximation is
3.25489341858963598497211933135986328125 and that value
rounds to 3.25.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round function inconsistent

[radovan5]

In Delphi I have 2 functions that works (I override default sqlite round):

const
  ExtEps = 1.0842021725E-19;
  DblEps = 2.2204460493E-16;
  KnownErrorLimit = 1.234375;
  SafetyFactor = 2;
  MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor;
  MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor;

function RoundExt(const AValue: Extended; const ADigit: Integer = -2): 
Extended;

var
  E: Extended;
begin
  E := IntPower(10, -ADigit);
  Result := Round(AValue * (1 + MaxRelErrExt) * E) / E;
end;

function RoundDbl(const AValue: Double; const ADigit: Integer = -2): 
Double;

var
  E: Double;
begin
  E := IntPower(10, -ADigit);
  Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E;
end;

You could implement it in sqlite.

Regards Radovan


On 24.05.2019 13:13, Richard Hipp wrote:

On 5/24/19, Hajo Bruns  wrote:

Hi,
the round function seems to round inconsistently:

ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
gives
5,49  3,56  3,25

Last result should be 3.26

3.255 cannot be exactly represented as an IEEE754 double-precision
binary floating point number.  So the system has to use an
approximation.  The closest approximation is
3.25489341858963598497211933135986328125 and that value
rounds to 3.25.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] slow join, fast subselect

[radovan5]

Second select is not correct. You should have "from a" only not "from a,b".

R.Antloga

On 17.04.2019 10:55, Poor Yorick wrote:

I've used the following two test queries in a version of sqlite built against a
recent checkout of trunk, and also another recent version of sqlite.  a.ref is
indexed.  The subselect query is faster than the join query -- two orders of
magnitude faster on a larger dataset.  Is sqlite missing some easy optimisation
opportunity here?


select a.rowid
from a join b on a.rowid = b.rowid
where a.ref = $x


select a.rowid
from a,b
where a.ref = $x and a.rowid in (select rowid from b)




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users