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

[Radovan Antloga]

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

[Radovan Antloga]

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

[sqlite] Changes in version 3.28.0 makes sql run slower

[Radovan Antloga]

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.

[Radovan Antloga]

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

[Radovan Antloga]

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] error message: [FireDAC][Phys][SQLite] Database is locked

[Radovan Antloga]

Try this:

1. check file atribute if it is not read only
  maybe is not BitDefender

2. copy your database to some test database in
  same folder. Try to edit with simple editor
  and save that file to see if is possible.

R.A.

Lars Frederiksen je 12.10.2018 ob 14:23 napisal:

I have tried this, unfortunately without any luck.
I think the problem might be in the sqlite DB-system. I have disabled ALL 
protecting parameters to find the culprit module - without luck.
My sqlite version is: SQLite version 3.19.3 2017-06-08 14:26:16
There might be a newer and better version without this error?

/Lars


-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Radovan Antloga
Sendt: 12. oktober 2018 10:10
Til: sqlite-users@mailinglists.sqlite.org
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

Try to change database name (extension).

For example:
mydb.sqlite
mydb.sdb
mydb.db3

I would use .sqlite extension and exclude that
files from antivirus check.

Regards

Lars Frederiksen je 12.10.2018 ob 9:11 napisal:

Hello,

I have made (in Delphi) a program that writes to a sqlite database. This is
a simple stand-alone solution. Now something is blocking for the programs
attempt to writing to the databse. There is a an error message:
[FireDAC][Phys][SQLite] Database is locked.

A friend of mine has analysed the scenario and tells me that BitDefender is
the Problem.  In BitDefender "Application Access" I have given my program
permission to "Work".

Any solution on how to "tell" BitDefender that it is OK to make changes in
my database? (I have tried BitDefender support - but no ticket answer!)

Maybe also somebody knows an antivirus/anti-malware solution which are not
so "hard" as BitDefender? (and with a userfriendly GUI).

Thank you so much for any help. I am rather "desperate" for some help!

Best Regards

Lars

   

   


___
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Radovan Antloga]

Try to change database name (extension).

For example:
mydb.sqlite
mydb.sdb
mydb.db3

I would use .sqlite extension and exclude that
files from antivirus check.

Regards

Lars Frederiksen je 12.10.2018 ob 9:11 napisal:

Hello,

I have made (in Delphi) a program that writes to a sqlite database. This is
a simple stand-alone solution. Now something is blocking for the programs
attempt to writing to the databse. There is a an error message:
[FireDAC][Phys][SQLite] Database is locked.

A friend of mine has analysed the scenario and tells me that BitDefender is
the Problem.  In BitDefender "Application Access" I have given my program
permission to "Work".

Any solution on how to "tell" BitDefender that it is OK to make changes in
my database? (I have tried BitDefender support - but no ticket answer!)

Maybe also somebody knows an antivirus/anti-malware solution which are not
so "hard" as BitDefender? (and with a userfriendly GUI).

Thank you so much for any help. I am rather "desperate" for some help!

Best Regards

Lars

  

  


___
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] How to get RecNo when clicking on a DBGrid?

[Radovan Antloga]

DbGrid is linked with DataSource and DataSource
with DataSet. DataSet can be TFDQuery or TFDTable.

You can read TFDTable.RecNo onclick event of DbGrid.
Why do you need that?

You do not have to write fields. Just put dbedit controls
on your form and link them with DataSource. On record
change they will be automaticaly populated with data.

Ragards Radovan

Lars Frederiksen je 29.09.2018 ob 17:33 napisal:

Hello,

  


I am using Delphi and FireDAC.

I have som editfields in the upper region of a tabsheet and a DBGrid in the
lower part. When I click on my DBGrid I would like to write the fields from
the choosen Record to the editfields.

I wonder if it is possible to get the RecNo of a record when clicking on a
DBGrid in my program.??

  


An alternative solution could be putting the selected records in a
stringgrid. I have a lot of trouble with these DataControls.!?!

  


Best Regards

Lars

  

  


___
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] SQLITE PERCENTAGE

[Radovan Antloga]

select 100.0 * sum(survived) / count(*) as tot_pct_survived
FROM passengers

Regards
Radovan

frknozdmr01 je 26.09.2018 ob 3:03 napisal:

Hi, I need to find the percent of passengers survived? (total). How do you do
it? I tried the comment below but it did not work.

SELECT
 SUM(CASE WHEN survived=1 THEN 1.0 ELSE 0.0 END) /
 CAST(COUNT(*) AS FLOAT)*100
 AS tot_pct_survived
FROM passengers;


This is the table;

CREATE TABLE passengers (
 id INTEGER NOT NULL,
 lname TEXT,
 title TEXT,
 class TEXT,
 age FLOAT,
 sex TEXT,
 survived INTEGER,
 code INTEGER
);




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Thank you Peter!

I like sqlite so much and I think we all benefit if
errors are fixed. I see I was not very clear with my
first post. Will do better next time. Yes would
be nice if people would try to understand first
not just think on title text.

Talking about sqlite I use it for web development,
desktop apps. I also created tool or gui sqlite manager
and will try to sell it in near future. I needed very
good tool because I work with sqlite every day. It
is just for windows platform. I support different
datetime formats, blobs, compress etc. See this page:

http://www.arsistemi.si/izdelki/sqlite-4-all.html

Every report is created with sqlite tables. I read
data from different RDBMS to sqlite and then work
with data as needed.

petern je 22.12.2017 ob 23:19 napisal:

Radovan.  Thank you for sticking to your guns.  Your appeal to expected
behavior under other DB engines was also a very good post.  I see the usual
suspects of the echo chamber uselessly piled on against you to clog the
forum nevertheless.  That happens too frequently.

BTW, here is a simpler test suite which pinpoints the problem:

SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
d
1

CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
c
1

CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c);
SELECT * FROM test;
d
1

And still one more that illustrates the contradictory and unstable
behavior. Use '*' instead of 'd' and it works as expected.

CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
d
1

Obviously the most immediate well formed alias 'd' should be carried
through irrespective of nesting.

FYI, there was a checkin just now with some changes to track intermediate
aliases:

http://www.sqlite.org/src/info/5efd854fe2147033

Peter

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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Thank you Klaus!

Klaus Maas je 22.12.2017 ob 20:30 napisal:

Radovan is correct.
Executing the same command sequence in version 3.11.0 and 3.21.0 
results in different column names for table test2.

(Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0)
I marked the results with '<='
Klaus


SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite>
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite>
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
d  <=
1
sqlite>



SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
a   <=
1
sqlite>

email signature Klaus Maas

On 2017-12-22 20:11, Radovan Antloga wrote:

Just try this sql-s:

create table test(a int, b int);
insert into test values (1,1);

select d from (select c as d from (select a as c from test));

will return name d.

create table test2 as
select d from (select c as d from (select a as c from test));

will create table test2 with column name a.

with PostgreSQL I get table test2 with name d.

Thank you very much for your time!


Richard Hipp je 22.12.2017 ob 20:06 napisal:

On 12/22/17, Radovan Antloga <radovan.antl...@siol.net> wrote:

My point is you do not have to change anything regards
to how select works. SELECT statement is working just
great or OK or fine.

CREATE TABLE AS
SELECT 

does not give same name as SELECT does.

SELECT give name d

CREATE TABLE AS SELECT
create table with name a instead of d.

Can you provide a simple test case for this behavior?



___
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

THANK YOU!

Richard Hipp je 22.12.2017 ob 20:29 napisal:

Your trouble ticket is here: https://sqlite.org/src/tktview/3b4450072511



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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Just try this sql-s:

create table test(a int, b int);
insert into test values (1,1);

select d from (select c as d from (select a as c from test));

will return name d.

create table test2 as
select d from (select c as d from (select a as c from test));

will create table test2 with column name a.

with PostgreSQL I get table test2 with name d.

Thank you very much for your time!


Richard Hipp je 22.12.2017 ob 20:06 napisal:

On 12/22/17, Radovan Antloga <radovan.antl...@siol.net> wrote:

My point is you do not have to change anything regards
to how select works. SELECT statement is working just
great or OK or fine.

CREATE TABLE AS
SELECT 

does not give same name as SELECT does.

SELECT give name d

CREATE TABLE AS SELECT
create table with name a instead of d.

Can you provide a simple test case for this behavior?



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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

What is correct?

Do you understand what I write?
What is my point? Tell me please.

David Raymond je 22.12.2017 ob 20:04 napisal:

Correct.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Friday, December 22, 2017 1:45 PM
To: SQLite mailing list
Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

On 12/22/17, Radovan Antloga <radovan.antl...@siol.net> wrote:

In my example I have AS clause so rule 1.

You did not understand my question.

I think what you are asking for is that we should enhance rule 1 so
that it applies even if the AS clause is contained within a subquery.


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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

My point is you do not have to change anything regards
to how select works. SELECT statement is working just
great or OK or fine.

CREATE TABLE AS
SELECT 

does not give same name as SELECT does.

SELECT give name d

CREATE TABLE AS SELECT
create table with name a instead of d.

Richard Hipp je 22.12.2017 ob 19:45 napisal:

On 12/22/17, Radovan Antloga <radovan.antl...@siol.net> wrote:

In my example I have AS clause so rule 1.

You did not understand my question.

I think what you are asking for is that we should enhance rule 1 so
that it applies even if the AS clause is contained within a subquery.


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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

In my example I have AS clause so rule 1.

sqlite select statement is correct or name
is correct.

sqlite create table as statement create table
with different name that select statement return
Problem is different result or name.


Richard Hipp je 22.12.2017 ob 19:27 napisal:

These are the rules that SQLite uses to name a column in the result set:

(1) If there is an AS clause, use it.

(2) If the result-set value comes from a table column (even
indirectly, such as through a subquery, but not if the value is
altered by an expression) then use the name of the column as it
appears in the original CREATE TABLE statement for the table.

(3) Otherwise, name the result-set column using the SQL text in the
SELECT statement.

What set of rules are you proposing that we should use instead?



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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Just tried my example with PostgreSQL that have
create table as statement.

It work as expected. It creates table test2 with
column name d. sqlite3 creates table with column
name a.

sqlite3 try to mimic postgresql but in this example
is not.

Simon Slavin je 22.12.2017 ob 17:58 napisal:


On 22 Dec 2017, at 4:50pm, Radovan Antloga <radovan.antl...@siol.net> wrote:


select d from (select c AS d from (select a AS c from test));

I get d as column name. If I create table with
create table as I get a as column name.

As I wrote, you did not specify AS for d, so you cannot depend on a column name.

If you want to know what the column will be called, specify AS:

select d AS myColumnNameHere from (select c AS d from (select a AS c from 
test));

Now you know what the column will be called.

Simon.
___
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

What?

I have to write select d as d. You are not
serious. You dont understand what I wrote.

I get select correct but create table as
does not have same name as select has. Why
different result?

If select gives some name I don't care what
I expect create table as give me same name.
Is this so hard to understand.

Simon Slavin je 22.12.2017 ob 17:58 napisal:


On 22 Dec 2017, at 4:50pm, Radovan Antloga <radovan.antl...@siol.net> wrote:


select d from (select c AS d from (select a AS c from test));

I get d as column name. If I create table with
create table as I get a as column name.

As I wrote, you did not specify AS for d, so you cannot depend on a column name.

If you want to know what the column will be called, specify AS:

select d AS myColumnNameHere from (select c AS d from (select a AS c from 
test));

Now you know what the column will be called.

Simon.
___
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Thank you very much to understand my point!
This is exactly what I think. I have name
and select is working ok. It gives me my
name but create table as not.

David Raymond je 22.12.2017 ob 17:59 napisal:

I think the underlying feeling here is that if you're not doing anything 
tricky, and just straight up referencing a column name, that it should be fair 
to assume that the result will have that column name.

I.e. with
create table foo (bar);
if I run
select bar from foo;
I should be able to assume the result column is named "bar".

So...
select a as c from test
is certain to have a field named "c",

select c as d from (something with a field named "c")
is certain to have a field named "d",

but select d from (something with a field named "d")
doesn't give a field named "d"?

I'd say it's fair to be confused at that.
I definitely don't want to have to do queries in the form of "select foo as foo, bar 
as bar, baz as baz..."

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, December 22, 2017 11:34 AM
To: SQLite mailing list
Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0



On 22 Dec 2017, at 3:04pm, Tim Streater  wrote:


2) I don't see this issue mentioned when I read the PHP documentation about 
their SQLite interface, nor do I see it in the Xojo docs about *their* 
interface either. I assume their interfaces are not rewriting SELECT statements 
to include AS for every column selected, so should they be warning their users 
about it?

If you want to depend on the name of a column, use AS.  If column names without 
AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that 
standard, then there would need to be a warning.  But there’s no standard for 
column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

Simon.
___
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Sorry but this is not related to my example.
I have AS in inner select. My select return
name as specified.

Problem I have is with create table as where
name is changed.

I give example like this:

select d from (select c AS d from (select a AS c from test));

I get d as column name. If I create table with
create table as I get a as column name.


Simon Slavin je 22.12.2017 ob 17:33 napisal:


On 22 Dec 2017, at 3:04pm, Tim Streater  wrote:


2) I don't see this issue mentioned when I read the PHP documentation about 
their SQLite interface, nor do I see it in the Xojo docs about *their* 
interface either. I assume their interfaces are not rewriting SELECT statements 
to include AS for every column selected, so should they be warning their users 
about it?

If you want to depend on the name of a column, use AS.  If column names without 
AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that 
standard, then there would need to be a warning.  But there’s no standard for 
column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

Simon.
___
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] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

How do you test your sql-s? I write sql
from step to step from very simple to complex.
So I have select and finally I need table for
that. It was simple until now. Just put
create table as before select and you get
same result (same names) as in select.

Your example is not the same. If using expression
name can be different and can change. This is
true for every datatase I know. In Firebird If
I write select 1+2 from rdb$database I get
column name ADD. If I specify with AS clause
I get name I want.

This works in sqlite and is not a problem. If
I do not specify name it can change from version
to version and everbody knows it must be specified.

But in my example name is explicitly defined in
inner select and for select I get correct name
but for create table as I do not.

Please dont tell me again what is documented because
at first very little was and not as one would like but
I understand that. It is not the point in this example.

The point is different behavior or result or names
when you have select or create table as from that select.
It should give same result or is better just not using
create table as at all because you will never know
what you can expect. Maybe as you eplained some day
create table as will create name like 1, 2, 3, 4

Long names sometimes are used for excel export or some
simple reports with just sql behind.


R Smith je 22.12.2017 ob 12:34 napisal:


On 2017/12/22 11:06 AM, Radovan Antloga wrote:

I'm using sqlite from version 2. I have 1000+
sql-s written already. I understand that
outermost statement must have AS clause as I
have explained below (select d as d ...).
Sqlite authors always say that new version can
not break sql-s for milions of users using
sqlite. But this change does.


It didn't break for millions of users, it only broke for the few who 
relied on behaviour that is documented to not be reliable and that 
might change in future.


Usually the Dev team is not in the habit of going around messing with 
features just because it is documented as unspecified, but in this 
case, it had to change to fix another real bug that had real-world 
problematic implications.




Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?


The first "d" tells SQLite where to find or how to evaluate the value, 
the second "d" tells SQLite how to name the output of that SELECT. It 
isn't any more ridiculous than SELECT (P+3) AS 'P+3' would be ridiculous.
This is not English101, this is Programming. Rules are rules, even 
silly ones.



.../and later/...
>If I have long name then I would have
>select some_long_name_to_understand_column as 
some_long_name_to_understand_column

>and if I have 20 columns like that. Just ridiculous or what.
>So once again I use AS clause but why I must use AS clause in
>outer statement if name is already defined in inner select.

Why would you use such a long silly name in the inner query KNOWING 
that it is ignored (wrt. to output naming anyway)?  That doesn't make 
a sound argument.


The CREATE TABLE Evaluates the select with some special considerations 
(as documented), nothing prescribes the resulting column name except 
the final outer statement's AS clause (as documented). Internally 
sqlite doesn't even see Aliases, it has numbers for columns. The outer 
select is however the boss, when it says "this-column" AS 'this_name' 
then sqlite (and any other DB engine) /has/ to abide by that (as 
documented).  The aliasing in the inner queries matter none other than 
to allow reliable reference pointing between query levels (as 
documented).


If it were undocumented behaviour you'd still not have a case but some 
anger would be understandable, however, you've explicitly relied on 
/documented-to-be-non-reliable/ behaviour, I still feel your pain, but 
it still isn't a bug and calling it "ridiculous" still won't change that.
(Btw. - the devs might change the behaviour again, maybe even to your 
liking if they feel merit and it doesn't break another behaviour, the 
point remains that it must not be trusted until it becomes the 
documented behaviour.)



I sincerely hope not too many of those 1000+ sql-s need fixing...
Good luck!
Ryan

___
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] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

But column name is specified in inner select with AS clause.
It can be used in outer select and work as expected.

select d from (select c AS d from (select a AS c from test));

I hope you did read my example. You can see explicitly defined

name in select a AS c from test

then again explicitly defined new name

select c AS d from 

and finally outer select can select only name d or what you think?

select statement gives correct answer or column name

but when you put CREATE TABLE AS in front of this statement you
get a as column name not d in created table. Do you understand that?

So outer select must have select d AS d so column must be renamed
into it self to get proper name?!

If I have long name then I would have

select some_long_name_to_understand_column as 
some_long_name_to_understand_column


and if I have 20 columns like that. Just ridiculous or what.

So once again I use AS clause but why I must use AS clause in
outer statement if name is already defined in inner select.




Hick Gunter je 22.12.2017 ob 10:57 napisal:

The problem lies in your reliance on unspecified behaviour. Unspecified 
behaviour is allowed to change.

I am sure you have read (and ignored) the following guarantee taken from 
http://sqlite.org/c3ref/column_name.html:

"Column Names In A Result Set
...
The name of a result column is the value of the "AS" clause for that column, if 
there is an AS clause. If there is no AS clause then the name of the column is unspecified and 
may change from one release of SQLite to the next."


Here is another common trap to fall into, taken from 
http://sqlite.org/lang_select.html :

" The ORDER BY clause

If a SELECT statement that returns more than one row does not have an ORDER BY 
clause, the order in which the rows are returned is undefined."

The order in which rows are returned is determined by the selected query plan. 
This may change after running ANALYZE, adding or deleting indices, or when 
changes are made to the Query Planner itself.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Radovan Antloga
Gesendet: Freitag, 22. Dezember 2017 10:06
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias 
in 3.21.0

I'm using sqlite from version 2. I have 1000+ sql-s written already. I 
understand that outermost statement must have AS clause as I have explained 
below (select d as d ...).
Sqlite authors always say that new version can not break sql-s for milions of 
users using sqlite. But this change does.

Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?


Hick Gunter je 22.12.2017 ob 7:55 napisal:

The behaviour does not need to match what you think of as consistent.

The only way to force a certain column name is with the AS clause *on the 
outermost statement*. Otherwise, the column name is implementation defined and 
may change between releases. You should not be relying on column names other 
than those you explicitly set using the AS clause. This is a common mistake.

If you really need the column names, then just CREATE TABLE first (this gives 
you defined column names AND declared data types) and the INSERT INTO ... 
SELECT later (which ignores the generated column names from the select 
statement).

-Ursprüngliche Nachricht-
Von: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
Radovan Antloga
Gesendet: Donnerstag, 21. Dezember 2017 16:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field
alias in 3.21.0

Behaviour is not consistent when using create table as or just select
statement.
Try this simple test.

create table test(a int, b int);
insert into test values (1, 1);

select d from (select c as d from (select a as c from test));

you get column name d as expected
but when you have create table as statement

create table test2 as
select d from (select c as d from (select a as c from test));

you get table test2 with column name a.

If you change to this

create table test2 as
select d as d from (select c as d from (select a as c from test));

you will get name correct. I think it should be the same as when just using 
select statement.

Best Regards
Radovan


select a from (select b from (select c from test)))

Richard Hipp je 21.12.2017 ob 14:52 napisal:

The behavior change is a bug fix.  See
http://sqlite.org/src/info/de3403bf5ae for details.

On 12/21/17, Radovan Antloga <radovan.antl...@siol.net> wrote:

I have table (create statement):

CREATE TABLE SOPP1 (
  STAT  varchar(1) collate systemnocase,
  RID  varchar(2) collate systemnocase,
  VP  integer,
  BLANK  varchar(6) collate systemnocase,
  NAZIV  varchar(24) collate systemn

Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

I'm using sqlite from version 2. I have 1000+
sql-s written already. I understand that
outermost statement must have AS clause as I
have explained below (select d as d ...).
Sqlite authors always say that new version can
not break sql-s for milions of users using
sqlite. But this change does.

Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?


Hick Gunter je 22.12.2017 ob 7:55 napisal:

The behaviour does not need to match what you think of as consistent.

The only way to force a certain column name is with the AS clause *on the 
outermost statement*. Otherwise, the column name is implementation defined and 
may change between releases. You should not be relying on column names other 
than those you explicitly set using the AS clause. This is a common mistake.

If you really need the column names, then just CREATE TABLE first (this gives 
you defined column names AND declared data types) and the INSERT INTO ... 
SELECT later (which ignores the generated column names from the select 
statement).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Radovan Antloga
Gesendet: Donnerstag, 21. Dezember 2017 16:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field alias in 
3.21.0

Behaviour is not consistent when using
create table as
or just select statement.
Try this simple test.

create table test(a int, b int);
insert into test values (1, 1);

select d from (select c as d from (select a as c from test));

you get column name d as expected
but when you have create table as statement

create table test2 as
select d from (select c as d from (select a as c from test));

you get table test2 with column name a.

If you change to this

create table test2 as
select d as d from (select c as d from (select a as c from test));

you will get name correct. I think it should be the same as when just using 
select statement.

Best Regards
Radovan


select a from (select b from (select c from test)))

Richard Hipp je 21.12.2017 ob 14:52 napisal:

The behavior change is a bug fix.  See
http://sqlite.org/src/info/de3403bf5ae for details.

On 12/21/17, Radovan Antloga <radovan.antl...@siol.net> wrote:

I have table (create statement):

CREATE TABLE SOPP1 (
 STAT  varchar(1) collate systemnocase,
 RID  varchar(2) collate systemnocase,
 VP  integer,
 BLANK  varchar(6) collate systemnocase,
 NAZIV  varchar(24) collate systemnocase,
 KN  varchar(12) collate systemnocase,
 A  varchar(1) collate systemnocase,
 B  varchar(1) collate systemnocase,
 RACUN  varchar(1) collate systemnocase,
 URE  varchar(1) collate systemnocase,
 ZN  varchar(1) collate systemnocase,
 TOCKE  varchar(1) collate systemnocase,
 PRC  varchar(1) collate systemnocase,
 UP  varchar(1) collate systemnocase,
 IZPIS  varchar(1) collate systemnocase,
 D  varchar(1) collate systemnocase,
 F2U  varchar(1) collate systemnocase,
 F2O  varchar(1) collate systemnocase,
 F2T  varchar(1) collate systemnocase,
 F2Z  varchar(1) collate systemnocase,
 F2P_1  integer,
 F2P_2  integer,
 F2P_3  integer,
 F5  varchar(1) collate systemnocase,
 AJPES  varchar(1) collate systemnocase,
 ZZ  integer,
 VD  integer,
 NS  integer,
 MES  integer,
 NORURE  varchar(1) collate systemnocase,
 G  varchar(1) collate systemnocase,
 E  varchar(1) collate systemnocase,
 H  varchar(1) collate systemnocase,
 I  varchar(1) collate systemnocase,
 J  varchar(1) collate systemnocase,
 SM  varchar(1) collate systemnocase,
 NO  varchar(1) collate systemnocase,
 PRIO  varchar(1) collate systemnocase,
 V_1  varchar(1) collate systemnocase,
 V_2  varchar(1) collate systemnocase,
 V_3  varchar(1) collate systemnocase,
 V_4  varchar(1) collate systemnocase,
 V_5  varchar(1) collate systemnocase,
 V_6  varchar(1) collate systemnocase,
 V_7  varchar(1) collate systemnocase,
 V_8  varchar(1) collate systemnocase,
 V_9  varchar(1) collate systemnocase,
 V_10  varchar(1) collate systemnocase,
 V_11  varchar(1) collate systemnocase,
 V_12  varchar(1) collate systemnocase,
 FOR  integer,
 P_1  integer,
 P_2  integer,
 P_3  integer,
 P_4  integer,
 P_5  integer,
 P_6  integer,
 primary key (RID, VP, BLANK));

When I create new table using this sql:

drop table if exists WM4P;
create table WM4P as
select
 P, A, B, AB, U, H, ZZ,
 case
   when AB in ('7') then 99
   when AB in ('57', '58', '59', '5M') then null
   when AB = '56' and ZZ = 12 then 01
   when AB = '56' then 02
   when A = '3' then 03
   when AB in ('1M') then 08
   when AB in ('10') then 07
   when AB in ('12') the

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Behaviour is not consistent when using
create table as
or just select statement.
Try this simple test.

create table test(a int, b int);
insert into test values (1, 1);

select d from (select c as d from (select a as c from test));

you get column name d as expected
but when you have create table as statement

create table test2 as
select d from (select c as d from (select a as c from test));

you get table test2 with column name a.

If you change to this

create table test2 as
select d as d from (select c as d from (select a as c from test));

you will get name correct. I think it should
be the same as when just using select statement.

Best Regards
Radovan


select a from (select b from (select c from test)))

Richard Hipp je 21.12.2017 ob 14:52 napisal:

The behavior change is a bug fix.  See
http://sqlite.org/src/info/de3403bf5ae for details.

On 12/21/17, Radovan Antloga <radovan.antl...@siol.net> wrote:

I have table (create statement):

CREATE TABLE SOPP1 (
STAT  varchar(1) collate systemnocase,
RID  varchar(2) collate systemnocase,
VP  integer,
BLANK  varchar(6) collate systemnocase,
NAZIV  varchar(24) collate systemnocase,
KN  varchar(12) collate systemnocase,
A  varchar(1) collate systemnocase,
B  varchar(1) collate systemnocase,
RACUN  varchar(1) collate systemnocase,
URE  varchar(1) collate systemnocase,
ZN  varchar(1) collate systemnocase,
TOCKE  varchar(1) collate systemnocase,
PRC  varchar(1) collate systemnocase,
UP  varchar(1) collate systemnocase,
IZPIS  varchar(1) collate systemnocase,
D  varchar(1) collate systemnocase,
F2U  varchar(1) collate systemnocase,
F2O  varchar(1) collate systemnocase,
F2T  varchar(1) collate systemnocase,
F2Z  varchar(1) collate systemnocase,
F2P_1  integer,
F2P_2  integer,
F2P_3  integer,
F5  varchar(1) collate systemnocase,
AJPES  varchar(1) collate systemnocase,
ZZ  integer,
VD  integer,
NS  integer,
MES  integer,
NORURE  varchar(1) collate systemnocase,
G  varchar(1) collate systemnocase,
E  varchar(1) collate systemnocase,
H  varchar(1) collate systemnocase,
I  varchar(1) collate systemnocase,
J  varchar(1) collate systemnocase,
SM  varchar(1) collate systemnocase,
NO  varchar(1) collate systemnocase,
PRIO  varchar(1) collate systemnocase,
V_1  varchar(1) collate systemnocase,
V_2  varchar(1) collate systemnocase,
V_3  varchar(1) collate systemnocase,
V_4  varchar(1) collate systemnocase,
V_5  varchar(1) collate systemnocase,
V_6  varchar(1) collate systemnocase,
V_7  varchar(1) collate systemnocase,
V_8  varchar(1) collate systemnocase,
V_9  varchar(1) collate systemnocase,
V_10  varchar(1) collate systemnocase,
V_11  varchar(1) collate systemnocase,
V_12  varchar(1) collate systemnocase,
FOR  integer,
P_1  integer,
P_2  integer,
P_3  integer,
P_4  integer,
P_5  integer,
P_6  integer,
primary key (RID, VP, BLANK));

When I create new table using this sql:

drop table if exists WM4P;
create table WM4P as
select
P, A, B, AB, U, H, ZZ,
case
  when AB in ('7') then 99
  when AB in ('57', '58', '59', '5M') then null
  when AB = '56' and ZZ = 12 then 01
  when AB = '56' then 02
  when A = '3' then 03
  when AB in ('1M') then 08
  when AB in ('10') then 07
  when AB in ('12') then null
  when A in ('1', '5') and H = '1' then 02
  when A in ('5') then 02
  when A in ('1') then 01
end as M4_OP
from (
select
  VP as P, ifnull(A,'') as A, ifnull(B,'') as B,
ifnull(A,'')||ifnull(B,'') as AB,
  ifnull(URE,'') as U, ifnull(H,'') as H, ZZ
from SOPP1
);

You will see that first column name is VP instead of P.
In previous versions name was P not VP.

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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

I have table (create statement):

CREATE TABLE SOPP1 (
  STAT  varchar(1) collate systemnocase,
  RID  varchar(2) collate systemnocase,
  VP  integer,
  BLANK  varchar(6) collate systemnocase,
  NAZIV  varchar(24) collate systemnocase,
  KN  varchar(12) collate systemnocase,
  A  varchar(1) collate systemnocase,
  B  varchar(1) collate systemnocase,
  RACUN  varchar(1) collate systemnocase,
  URE  varchar(1) collate systemnocase,
  ZN  varchar(1) collate systemnocase,
  TOCKE  varchar(1) collate systemnocase,
  PRC  varchar(1) collate systemnocase,
  UP  varchar(1) collate systemnocase,
  IZPIS  varchar(1) collate systemnocase,
  D  varchar(1) collate systemnocase,
  F2U  varchar(1) collate systemnocase,
  F2O  varchar(1) collate systemnocase,
  F2T  varchar(1) collate systemnocase,
  F2Z  varchar(1) collate systemnocase,
  F2P_1  integer,
  F2P_2  integer,
  F2P_3  integer,
  F5  varchar(1) collate systemnocase,
  AJPES  varchar(1) collate systemnocase,
  ZZ  integer,
  VD  integer,
  NS  integer,
  MES  integer,
  NORURE  varchar(1) collate systemnocase,
  G  varchar(1) collate systemnocase,
  E  varchar(1) collate systemnocase,
  H  varchar(1) collate systemnocase,
  I  varchar(1) collate systemnocase,
  J  varchar(1) collate systemnocase,
  SM  varchar(1) collate systemnocase,
  NO  varchar(1) collate systemnocase,
  PRIO  varchar(1) collate systemnocase,
  V_1  varchar(1) collate systemnocase,
  V_2  varchar(1) collate systemnocase,
  V_3  varchar(1) collate systemnocase,
  V_4  varchar(1) collate systemnocase,
  V_5  varchar(1) collate systemnocase,
  V_6  varchar(1) collate systemnocase,
  V_7  varchar(1) collate systemnocase,
  V_8  varchar(1) collate systemnocase,
  V_9  varchar(1) collate systemnocase,
  V_10  varchar(1) collate systemnocase,
  V_11  varchar(1) collate systemnocase,
  V_12  varchar(1) collate systemnocase,
  FOR  integer,
  P_1  integer,
  P_2  integer,
  P_3  integer,
  P_4  integer,
  P_5  integer,
  P_6  integer,
  primary key (RID, VP, BLANK));

When I create new table using this sql:

drop table if exists WM4P;
create table WM4P as
select
  P, A, B, AB, U, H, ZZ,
  case
    when AB in ('7') then 99
    when AB in ('57', '58', '59', '5M') then null
    when AB = '56' and ZZ = 12 then 01
    when AB = '56' then 02
    when A = '3' then 03
    when AB in ('1M') then 08
    when AB in ('10') then 07
    when AB in ('12') then null
    when A in ('1', '5') and H = '1' then 02
    when A in ('5') then 02
    when A in ('1') then 01
  end as M4_OP
from (
  select
    VP as P, ifnull(A,'') as A, ifnull(B,'') as B, 
ifnull(A,'')||ifnull(B,'') as AB,

    ifnull(URE,'') as U, ifnull(H,'') as H, ZZ
  from SOPP1
  );

You will see that first column name is VP instead of P.
In previous versions name was P not VP.

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] Date time functions not working

[Radovan Antloga]

You must have ISO8601 that has format
-MM-DD not /MM/DD. If you compare
strings it will not work. See result of
this query:

select date('now', '-1 days')

you get: 2017-08-23

Regards
Radovan

Matthew Halliday je 24.08.2017 ob 15:21 napisal:

Looks like it's me.

VBScript "NOW" = dd/mm/ whereas ISO "NOW" = /mm/dd.

Doh!

Last part of the INSERT string reads:

   ,'" & strFreeSpace & "','" & pctFreeSpace & "','" & NOW & "');"

Changing it to:

   ,'" & strFreeSpace & "','" & pctFreeSpace & "', datetime('NOW') ); so
it inserts the SQLite 'NOW' instead.

See how well that works.  I need to update the other date/time stamps
though.



On Thu, Aug 24, 2017 at 2:06 PM, David Raymond 
wrote:


The date data is in the format: "21/08/2017 16:27:03".  I thought TEXT
stored it in the ISO "/MM/DD" format?

What you declare the field as doesn't matter, so much as what you actually
store in it. But all the functions apart from strftime use -MM-DD
HH:MM:SS so if you're giving them slashes and a different order then
they're all gonna mess up.


Correct:

SELECT strftime(date_time, -2) as iDATE from tmp_dspace_import


Result
21/08/2017 11:43:05
21/08/2017 11:43:05
21/08/2017 11:43:05

That's saying use date_time as the format string where -2 is the time
string. I'm surprised that actually gave an answer rather than an error.
And that you're getting a result with slashes is really really suspicious.



Then you can have query like this:

select date_time, servername, drive, sum(diff_used) DailyUsed_mb
from tmp_dspace_import
where date_time >= julianday(current_date) - 1
group by date_time, servername, drive

This would be for yesterday and today. If you need more days in past
then change expression: date_time >= julianday(current_date) - 1

Just yesterday is then: date_time = julianday(current_date) - 1

for two days back: date_time >= julianday(current_date) - 2

etc...

Don't forget that if you have a date and time in the database, then you're
comparing a whole time to just a day there. I think you'd want something
like

where date(date_time) = date(current_date, "-1 days")  --for yesterday
where date(date_time) >= date(current_date, "-6 days") --for within the
last week
where date(date_time) between date(current_date, "-3 days") and
date(current_date, "-1 days") --3 days ago to yesterday.
___
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time functions not working

[Radovan Antloga]

Then you can have query like this:

select date_time, servername, drive, sum(diff_used) DailyUsed_mb
from tmp_dspace_import
where date_time >= julianday(current_date) - 1
group by date_time, servername, drive

This would be for yesterday and today. If you need more days in past
then change expression: date_time >= julianday(current_date) - 1

Just yesterday is then: date_time = julianday(current_date) - 1

for two days back: date_time >= julianday(current_date) - 2

etc...

Regards
Radovan


Matthew Halliday je 24.08.2017 ob 12:06 napisal:

Hi Radovan, just tried that -

if I use strftime(date_time, -1) it still gives me today, but also the last
4 days.

If I use date(date_time,'-1 days') I get a NULL.

SELECT date_time,

servername,

drive,

SUM(diff_used) AS DailyUsed_mb

FROM tmp_dspace_import

where date_time = date('now',' -1 day')


GROUP BY date_time,

servername,

drive;


returns a blank set.  Does the order in the query make a difference?


Its probably me missing something here.

On Thu, Aug 24, 2017 at 10:48 AM, Radovan Antloga <radovan.antl...@siol.net>
wrote:


Hi,

you don't have all columns in group by.
You should have grup by 1,2,3. Column 3
is expression.

Regards,
Radovan

Matthew Halliday je 24.08.2017 ob 11:41 napisal:

Correct:

SELECT strftime(date_time, -2) as iDATE from tmp_dspace_import


Result
21/08/2017 11:43:05
21/08/2017 11:43:05
21/08/2017 11:43:05

Not working:
select servername, drive, strftime(date_time, -2) as iDATE, SUM(diff_used)
AS DailyUsed

from tmp_dspace_import

group by servername, drive;


Result:

server_01C:24/08/2017 04:02:31-312
server_01 D:24/08/2017 04:02:310
server_01 E:24/08/2017 04:02:310


SELECT servername,

date_time,

drive,

SUM(diff_used) AS DailyUsed

FROM tmp_dspace_import

WHERE date_time = strftime(date_time, -2)

GROUP BY servername,

drive;


gives data from last 3 days.


21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
23/08/2017 11:45:33
23/08/2017 11:45:33
23/08/2017 11:45:33
24/08/2017 04:02:20
24/08/2017 04:02:20
24/08/2017 04:02:20

On Thu, Aug 24, 2017 at 10:27 AM, Clemens Ladisch <clem...@ladisch.de>
wrote:

Matthew Halliday wrote:

SELECT strftime('%Y-%m-%d',date_time , '-1 days') as DATE from
tmp_dspace_import

If I run it as a stand-alone single statement it works.

If I run it as part of a longer query I get either just 131 rows of just
today's data or a collumn of NULL values.


Obviously, the problem is with the longer query.  Which you have not
shown.


Regards,
Clemens
___
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-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] Date time functions not working

[Radovan Antloga]

Hi,

you don't have all columns in group by.
You should have grup by 1,2,3. Column 3
is expression.

Regards,
Radovan

Matthew Halliday je 24.08.2017 ob 11:41 napisal:

Correct:

SELECT strftime(date_time, -2) as iDATE from tmp_dspace_import


Result
21/08/2017 11:43:05
21/08/2017 11:43:05
21/08/2017 11:43:05

Not working:
select servername, drive, strftime(date_time, -2) as iDATE, SUM(diff_used)
AS DailyUsed

from tmp_dspace_import

group by servername, drive;


Result:

server_01C:24/08/2017 04:02:31-312
server_01 D:24/08/2017 04:02:310
server_01 E:24/08/2017 04:02:310


SELECT servername,

date_time,

drive,

SUM(diff_used) AS DailyUsed

FROM tmp_dspace_import

WHERE date_time = strftime(date_time, -2)

GROUP BY servername,

drive;


gives data from last 3 days.


21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
23/08/2017 11:45:33
23/08/2017 11:45:33
23/08/2017 11:45:33
24/08/2017 04:02:20
24/08/2017 04:02:20
24/08/2017 04:02:20

On Thu, Aug 24, 2017 at 10:27 AM, Clemens Ladisch 
wrote:


Matthew Halliday wrote:

SELECT strftime('%Y-%m-%d',date_time , '-1 days') as DATE from
tmp_dspace_import

If I run it as a stand-alone single statement it works.

If I run it as part of a longer query I get either just 131 rows of just
today's data or a collumn of NULL values.

Obviously, the problem is with the longer query.  Which you have not shown.


Regards,
Clemens
___
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] Bug using aggregate functions

[Radovan Antloga]

I use group by 99,99% with aggregate functions.
Just this one special case I have when I must
get min, max values and also I have group_concat
where I must filter records. Result is then
inserted into table where I have not null constraint
and I get error because min, max returned null.
I solved this with two sql-s. First is insert
and second is update.

I didn't know I could use dummy value for
grouping. I see this is not just sqlite
specific. I tried also with Firebird DB
and it works. So I will modify my program.

Thank you!

Clemens Ladisch je 07.02.2017 ob 8:40 napisal:

Radovan Antloga wrote:

select min(A)
from TEST
where B is null
   and A > 3;

if you replace min(A) with * you get empty result set as expected
but with min or max or avg you get one record

This is just how aggregate functions in SQL work.

When you're using GROUP BY, you get exactly one result row per group.
And if there are no rows that are grouped, there are no groups, and
therefore the result is empty.

When you're not using GROUP BY, you always get exactly one result row
out of the aggregate function(s), even if they aggregate an empty set.

If you do want an empty result if the (filtered) source table is empty,
you have to add grouping (by some dummy value):

 SELECT min(a)
 FROM Test
 WHERE b IS NULL
   AND a > 3
 GROUP BY NULL;


Regards,
Clemens
___
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] Bug using aggregate functions

[Radovan Antloga]

Hi,

I discovered a bug using sqlite 3.15.2. It is simple
to reproduce. Try this SQL-s:

CREATE TABLE TEST(
A integer primary key,
B integer);

-- insert some test data
insert into TEST (A,B) values (1, 1);
insert into TEST (A,B) values (2, null);

-- check count(*)
select count(*)
from TEST
where B is null
  and A > 3;

-- bug sql using agg function (also with other functions: max, avg)
select min(A)
from TEST
where B is null
  and A > 3;

if you replace min(A) with * you get empty result set as expected
but with min or max or avg you get one 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] Bug using aggregate functions

[Radovan Antloga]

Sorry to post this to quick. I just checked this
with Firebird and I get same result. I did not
expect that.

Sorry once again !!


Radovan Antloga je 06.02.2017 ob 18:34 napisal:

Hi,

I discovered a bug using sqlite 3.15.2. It is simple
to reproduce. Try this SQL-s:

CREATE TABLE TEST(
A integer primary key,
B integer);

-- insert some test data
insert into TEST (A,B) values (1, 1);
insert into TEST (A,B) values (2, null);

-- check count(*)
select count(*)
from TEST
where B is null
  and A > 3;

-- bug sql using agg function (also with other functions: max, avg)
select min(A)
from TEST
where B is null
  and A > 3;

if you replace min(A) with * you get empty result set as expected
but with min or max or avg you get one 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] Bug using aggregate functions

[Radovan Antloga]

Hi,

I discovered a bug using sqlite 3.15.2. It is simple
to reproduce. Try this SQL-s:

CREATE TABLE TEST(
A integer primary key,
B integer);

-- insert some test data
insert into TEST (A,B) values (1, 1);
insert into TEST (A,B) values (2, null);

-- check count(*)
select count(*)
from TEST
where B is null
  and A > 3;

-- bug sql using agg function (also with other functions: max, avg)
select min(A)
from TEST
where B is null
  and A > 3;

if you replace min(A) with * you get empty result set as expected
but with min or max or avg you get one 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] More constraint questions: foreign_keys

[Radovan Antloga]

sql should be:

select b.Name, a.Title, c.Year
from Books a
left outer join Authors b on a.AuthorID = b.AuthorID
left outer join Years c on a.YearID = c.IDYear

I think your table Years is redundant.
Why store only one integer in table Years
as you could store directly into Books.
So I would have just Year in table Books.

Regards Radovan

jose isaias cabrera je 21.12.2016 ob 9:05 napisal:

With this code,

-- Begin
PRAGMA foreign_keys=1;
BEGIN TRANSACTION;
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Authors;
DROP TABLE IF EXISTS Years;
CREATE TABLE Authors(AuthorId INTEGER PRIMARY KEY, Name TEXT);
INSERT INTO Authors VALUES(1, 'Jane Austen');
INSERT INTO Authors VALUES(2, 'Leo Tolstoy');
INSERT INTO Authors VALUES(3, 'Joseph Heller');
INSERT INTO Authors VALUES(4, 'Charles Dickens');

CREATE TABLE Years
(
 IDYear INTEGER PRIMARY KEY,
 Year TEXT DEFAULT '1980'
);
INSERT INTO Years VALUES(1,'1982');
INSERT INTO Years VALUES(2,'1992');
INSERT INTO Years VALUES(3,'2016');
INSERT INTO Years(IDYear) VALUES(4);

CREATE TABLE Books
(
 BookId INTEGER PRIMARY KEY, Title TEXT, AuthorId INTEGER, YearID INTEGER,
FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId),
 FOREIGN KEY(YearID) REFERENCES Years(IDYear)
);
INSERT INTO Books VALUES(1,'Emma',1,1);
INSERT INTO Books VALUES(2,'War and Peace',2,1);
INSERT INTO Books VALUES(3,'Catch XII',3,2);
INSERT INTO Books VALUES(4,'David Copperfield',4,3);
INSERT INTO Books VALUES(5,'Good as Gold',3,4);
INSERT INTO Books VALUES(6,'Anna Karenia',2,3);
COMMIT;

-- end

If I do,

SELECT Name, Title, Year FROM Authors NATURAL JOIN Books, Years;

I get repeated data.  But, what I want is,

Jane Austen|Emma|1982
Leo Tolstoy|War and Peace|1982
Joseph Heller|Catch XII|1992
Charles Dickens|David Copperfield|2016
Joseph Heller|Good as Gold|1980
Leo Tolstoy|Anna Karenia|2016

Any help would be greatly appreciated.  Thanks.

jos
___
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] Autoincrement sequence not updated by UPDATE

[Radovan Antloga]

After line:
UPDATE foo SET bar=5678;

put this sql command:
COMMIT;

If you execute all statements in one sql
(except last), they are executed in one transaction.

Regards
Radovan


Adam Goldman je 27.10.2016 ob 11:52 napisal:

Hi,

I expected the test case below to print 5679, but it prints 1235
instead. I tested under a few versions including 3.15.0. It's a bit of a
corner case and I worked around it in my application, but I guess it's a
bug.

CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
INSERT INTO foo (bar) VALUES(1234);
UPDATE foo SET bar=5678;
DELETE FROM foo;
INSERT INTO foo DEFAULT VALUES;
SELECT * FROM foo;

-- Adam
___
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] Problem with rename table

[Radovan Antloga]

Yes my first idea was to rename back to old name
but you cant because every sql fails with error
malformed database schema. Interest thing is that
sql Richard suggested works and no error message
returned. It looks like triggers are parsed when
other sql executed and produce error.
I found another solution and just edited with
good hex editor and change trigger definition
(rename table). I could to that without changing
size I just replace last space character with
2 so I get dokumenti2 table name. Mybe this can
be fauture request that when table is renamed
also trigger that belong to this table should
get table renamed.

Best Regards
Radovan

Bob McFarlane je 08.09.2016 ob 2:09 napisal:

Please reply if you sent this. Thanks.





-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Richard Hipp
Sent: Wednesday, September 7, 2016 7:49 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Problem with rename table

On 9/6/16, Radovan Antloga <radovan.antl...@siol.net> wrote:

Hi Richard !

I can't find a solution how to fix my database after I have renamed
table DOKUMENTI to DOKUMENTI2.
Table DOKUMENTI had trigger dokumenti_trigger1 and after renaming
table I cant execute any sql. I forgot to drop trigger first. So now I
always get error:
malformed database schema (dokumenti_trigger1) - no such table
main.dokumenti.

Rename the table back to its old name?

Worst case:  You can drop all the triggers like this:

PRAGMA writable_schema=ON;
DELETE FROM sqlite_master WHERE type='trigger';

Then close and reopen your database, and you have no more triggers.
The same will work for views.  But if you try the above with tables or
indexes, you'll end up with a database that fails "PRAGMA integrity_check" -
though the corruption can be fixed with a VACUUM.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



TMGID:S1141121912621011
___
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] Problem with rename table

[Radovan Antloga]

Hi Richard !

I can't find a solution how to fix my database after
I have renamed table DOKUMENTI to DOKUMENTI2.
Table DOKUMENTI had trigger dokumenti_trigger1
and after renaming table I cant execute any sql. I forgot
to drop trigger first. So now I always get error:
malformed database schema (dokumenti_trigger1) -
no such table main.dokumenti.

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

Re: [sqlite] [BUG] Silent change in intergeral overflow handling in 3.7.6

[Radovan Antloga]

See this:

http://www.sqlite.org/releaselog/3_7_6.html

Regards,
R.Antloga

S, Jan Hudec piše:
> Hello All,
>
> [Is this correct place to report a bug, or can I create it in Fossil
> somehow?]
>
> Semantics of operations on integers changed between 3.7.5 and 3.7.6. It does
> not seem to be mentioned in change log (http://www.sqlite.org/news.html),
> though it may affect existing applications:
>
>   * Up to 3.7.5, integer operations are always carried out in 64-bit integers
> and overflow as expected for that type.
>   * From 3.7.6 on, integer operands of *some operations* (left shift being
> notable exception) are converted to real if the operation would overflow.
>
> I don't think either behaviour is specified anywhere. However, the old
> behaviour was IMO consistent and was very useful for two cases:
>
>   * When working with unsigned numbers on the application side (arithmetic
> operations except division (/) and right shift (>>) don't consider sign).
>   * To get some bit patterns when doing bitwise operations (since there are
> &  and |, but no negation and no hexadecimal literals).
>
> On the other hand the new behaviour will:
>
>   * Give MIN_INT64 for all operations that overflowed (the application expects
> integer, so will probably read with sqlite3_column_int64 and conversion of
> too large reals *is* specified to return MIN_INT64).
>   * Give inprecise result for cases where the overflows cancel out (e.g.
> a + b - c where a + b overflows, but a + (b - c) does not.
>
> Would you be so kind and at least visibly document this change, consider
> which behaviour is actually prefered and document the behaviour. Thanks.
>
>
> As for my particular case, I have a database with table (let's call it
> "objects") with large integeral primary key and many other tables refering to
> it. And another table (let's call it "sides"), which needs *two* rows for
> each row in "objects". Since "integer primary key" is faster than "primary
> key (object_id, side)" and since the rows are mostly handled independenty
> (and have many other things refer to them), I construct a primary key with:
>
>  object_id | (side<<  63)
>
> Now when I need to join "sides" and "objects", I need condition
>
>  object_id = side_id&  ~(1<<  63)
>
> but that's not valid syntax. There are two alternatives:
>
>  (1<<  63) - 1  or  -1 - (1<<  63)
>
> I used the first, because it is more readable to me (while the later is exact
> alternative to the bitwise not, it's not common knowledge, because other
> languages do have bitwise not). But it stopped working in 3.7.6 (the later
> still does, so I can convert it, but it's error-prone situation).
>
> Regards,
> Jan
>
> --
>  - Jan Hudec
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report

[Radovan Antloga]

S, Igor Tandetnik piše:
> Radovan Antloga<radovan.antl...@siol.net>  wrote:
>> create table test (a integer not null, b float not null);
>>
>> update test
>> set b = 0.0 / 0;
>>
>> you will get error: test.b may not be null
> Appears to be working as intended. Division by zero produces null, which 
> cannot be stored in test.b . What exactly seems to be the problem?

Oh I see sqlite allows division by zero but result
is null. Sorry I didn't expect it. Some other DB
gives division by zero error in this case.

But actually I like sqlite way more. I will fix some
of my sql-s to use this sqlite feature :-).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug report

[Radovan Antloga]

Example sql-s how to reproduce:

create table test (a integer not null, b float not null);
insert into test values (1, 0);
insert into test values (2, 0);
insert into test values (3, 0);

I actualy get values from other tables but in my
example they sum to zero so to simplify you can
try just this:

update test
set b = 0.0 / 0;

you will get error: test.b may not be null

I reproduced also with windows command-
line shell.

Best Regards !
Radovan Antloga
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Table is locked error

[Radovan Antloga]

I did not find any info about my situation in page
http://www.sqlite.org/lockingv3.html

I would just like a confirmation if this is expected
behaviour.

I have locking mode = normal. Steps are:

1. open database
2. open statement (simple select from T1)
and doing 1 step
3. execute statement (drop table T2)

I get SQLite error 6 database table is locked.

So while statements are in progress it is not possible
to drop any tables.


Best Regards
Radovan Antloga


__ Informacija od ESET NOD32 Antivirus, zbirka virusnih definicij 4990 
(20100401) __

To sporočilo je preveril ESET NOD32 Antivirus.

http://www.eset.si


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

Re: [sqlite] Using real

[Radovan Antloga]

I found that using cast(round(x * 100) as integer) will give me
result I want.

Thank you !
RA

- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Saturday, July 12, 2008 10:54 PM
Subject: Re: [sqlite] Using real


>
> On Jul 12, 2008, at 4:41 PM, Radovan Antloga wrote:
>
>> please check this sql:
>>
>> select cast((2.3 * 100) as integer), cast(round(2.3 * 100) as
>> integer), 2.3 * 100, cast(230.0 as integer)
>>
>> Is this proper behaviour ?
>>
>
> Yes it is.  The CASE(* AS INTEGER) operation rounds toward zero.  And
> 2.3*100.0 in IEEE float notation is
> 229.971578290569595992565155029296875 which when rounded
> toward zero yields 229.
>
> See also http://www.sqlite.org/faq.html#q16
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __ NOD32 3263 (20080711) Informacija __
>
> To sporoèilo je preveril protivirusni sistem NOD32.
> http://www.nod32.com
>
> 

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

[sqlite] Using real

[Radovan Antloga]

please check this sql:

select cast((2.3 * 100) as integer), cast(round(2.3 * 100) as integer), 2.3 * 
100, cast(230.0 as integer)

Is this proper behaviour ?

Thanks in advance,
RA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users