[sqlite] Bug report for MAX()

[Dave McKee]

I can replicate this behaviour if I insert a zero-length string into the
column.

sqlite> create table foo(a);
sqlite> insert into foo values(5);
sqlite> insert into foo values("");
sqlite> select max(a) from foo;

sqlite> select min(a) from foo;
5
sqlite> select avg(a) from foo;
2.5

Is this a possible explanation?

On Wed, Nov 25, 2015 at 11:37 AM, Simon Slavin  wrote:

>
> On 25 Nov 2015, at 11:09am, R Smith  wrote:
>
> > sqlite> SELECT max(UnitCost) FROM BOMData;
> >
> > sqlite> SELECT min(UnitCost) FROM BOMData;
> > 0.0
>
> Can you please post the result of
>
> SELECT DISTINCT typeof(UnitCost) FROM BOMData;
>
> (I think that's how you do it.  You might need to use GROUP BY.)
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Fwd: Outdated section of docs?

[Dave McKee]

Hmmm... actually, looks like Windows 10 has regressed back to only having
one set of DST rules...

Manually changed timezone to Pacific US.  Results are from before reboot
but don't seem to have changed after.

David.

Windows 10
Dates from https://www.timeanddate.com/time/dst/1985.html
Using sqlite-shell-win32-x86-3081101.zip
--
2012: Sunday, 11 March
sqlite> select datetime("2012-03-10T12:00", 'localtime');
2012-03-10 04:00:00
sqlite> select datetime("2012-03-11T12:00", 'localtime');
2012-03-11 05:00:00

2007: Sunday, 11 March
sqlite> select datetime("2007-03-10T12:00", 'localtime');
2007-03-10 04:00:00
sqlite> select datetime("2007-03-11T12:00", 'localtime');
2007-03-11 05:00:00

2006: Sunday, 2 April
sqlite> select datetime("2006-04-01T12:00", 'localtime');
2006-04-01 05:00:00
sqlite> select datetime("2006-04-02T12:00", 'localtime');
2006-04-02 05:00:00
sqlite> select datetime("2006-04-03T12:00", 'localtime');
2006-04-03 05:00:00

sqlite> select datetime("2006-03-11T12:00", 'localtime');
2006-03-11 04:00:00
sqlite> select datetime("2006-03-12T12:00", 'localtime');
2006-03-12 05:00:00

1985: Sunday, 28 April
sqlite> select datetime("1985-03-09T12:00", 'localtime');
1985-03-09 04:00:00
sqlite> select datetime("1985-03-10T12:00", 'localtime');
1985-03-10 05:00:00


On 22 September 2015 at 17:32, Dave McKee  wrote:

>
> -- Forwarded message --
> From: Jonathan Moules 
> Date: Mon, Sep 21, 2015 at 5:31 PM
> Subject: [sqlite] Outdated section of docs?
> To: sqlite-users at mailinglists.sqlite.org
>
>
> Hi,
> I was reading this page (http://sqlite.org/lang_datefunc.html), and at
> the very bottom it says:
> /
> /
>
>/"Non-Vista Windows platforms only support one set of DST rules.
>Vista only supports two. Therefore, on these platforms, historical
>DST calculations will be incorrect. For example, in the US, in 2007
>the DST rules changed. Non-Vista Windows platforms apply the new
>2007 DST rules to all previous years as well. Vista does somewhat
>better getting results correct back to 1986, when the rules were
>also changed."/
>
>
> I wonder if that's a section that was written years ago, and the bits that
> apply to Vista also apply to the Windows releases since then? I don't know
> enough about Windows Timezone things to be able to find out easily but this
> reads like it was written back in the era of Vista and probably holds for
> newer releases too.
> Cheers,
> Jonathan
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

Re: [sqlite] 64bit compatibility warnings

[Dave McKee]

> By definition, A % B < B. Thus, if B fits into an int (be it 32-bit or
> 16-bit or otherwise), then A % B would too.


I'm not sure this is *strictly* true if negative numbers are involved; e.g.
-4 % -3 = -1.
But it's still true to say it's closer to zero, and thus should always fit
into an int.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .dump - what isn't preserved?

[Dave McKee]

I'm considering working with a dump of the SQL database, and I'm anxious to
make sure that nothing gets lost in the transformation.

I'm aware that the database will effectively be vacuumed and that rowids
won't be preserved. I'm happy about the first and can live with the second.

What else isn't losslessly maintained in a dump -> load cycle?

Is there anything else that would negatively affect database performance,
the results of SQL queries or the databases configuration (e.g. WAL mode)?

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

Re: [sqlite] type definitions or aliases

[Dave McKee]

> Is it possible to use triggers for this?

Yes.

"Triggers may be created on views, as well as ordinary tables, by
specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON
INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is
not an error to execute an INSERT, DELETE or UPDATE statement on the view,
respectively. Instead, executing an INSERT, DELETE or UPDATE on the view
causes the associated triggers to fire. The real tables underlying the view
are not modified (except possibly explicitly, by a trigger program)."

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

Re: [sqlite] count infact passed rows of OFFSET select

[Dave McKee]

If you get no rows returned, you could query SELECT COUNT(*) FROM table1;
(If you get rows returned, you know that the number skipped is the size of
your offset.)


On Thu, Dec 6, 2012 at 11:44 AM, Steffen Mangold <
steffen.mang...@balticsd.de> wrote:

> Hi,
>
> I want to know if there is a possibility to count the infact passed row if
> I make an OFFSET SELECT.
> For example:
>
> table1
> ---
> Row1
> Row2
> Row3
> Row4
> Row5
>
>
> Select * from table1 Order by rowID DESC Lilit 2 Offset 10;
>
> Here the Result is null, but I want to detect that I passed 5 existing
> rows.
>
> regards
>
> Steffen Mangold
>
> ___
> 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] to find table exists or not

[Dave McKee]

I can see situations in which you might want to create a new table with a
name that wasn't already taken: at which point simple knowledge would let
you reconsider your choice of table name without affecting the db in any
way, success or fail.


On Thu, Dec 6, 2012 at 10:57 AM, Hick Gunter  wrote:

> Both applications can be done without external logic using
>
> CREATE TABLE IF NOT EXISTS ...
> (detect that the table is missing AND create it)
>
> and (if required)
>
> INSERT OR IGNORE INTO ...
> (set missing options to default while keeping pre-set values)
>
> -Ursprüngliche Nachricht-
> Von: Stephen Chrzanowski [mailto:pontia...@gmail.com]
> Gesendet: Donnerstag, 06. Dezember 2012 11:21
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] to find table exists or not
>
> I can think of two reasons why you wouldn't want to blindly delete the
> table, but verify that it exists.
>
> - First run of the program that creates the database from square one.  If
> the table exists, skip over the create routine and continue.  I routinely
> do this for creating an Options database (Program preferences, etc) instead
> of relying on ini files (In Windows) since they're limited in volume of
> data, and the kind of data that can be stored within.
>
> - If using a temporary table validating that the table exists to begin with
> before you start throwing data at it.
>
> The wrapper I use contains a routine that returns a Boolean if the table
> exists or not, so I don't have to rely on constructing SQL statements.
>
> On Wed, Dec 5, 2012 at 9:57 AM, Hick Gunter  wrote:
>
> > Why do you only want to see if the table is there?
> >
> > You can always do
> >
> > DROP TABLE IF EXISTS ...
> > CREATE TABLE ...
> >
> > to replace the definition or
> >
> > CREATE TABLE IF NOT EXISTS ...
> >
> > to keep the old definition.
> >
> > -Ursprüngliche Nachricht-
> > Von: Durga D [mailto:durga.d...@gmail.com]
> > Gesendet: Mittwoch, 05. Dezember 2012 15:33
> > An: General Discussion of SQLite Database
> > Betreff: [sqlite] to find table exists or not
> >
> > Hi all,
> >
> >
> >  I just want to find whether table exists or not in a database.
> >
> >
> > Is it correct query?
> >
> >
> > select distinct tbl_name from sqlite_master where tbl_name = 'abc';
> >
> >
> > Is there any other better way to find whether table exists or not.
> >
> >
> > Thanks in advance.
> >
> >
> > Regards,
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> --
> >  Gunter Hick
> > Software Engineer
> > Scientific Games International GmbH
> > Klitschgasse 2 - 4, A - 1130 Vienna, Austria
> > FN 157284 a, HG Wien
> > Tel: +43 1 80100 0
> > E-Mail: h...@scigames.at
> >
> > This e-mail is confidential and may well also be legally privileged. If
> > you have received it in error, you are on notice as to its status and
> > accordingly please notify us immediately by reply e-mail and then delete
> > this message from your system. Please do not copy it or use it for any
> > purposes, or disclose its contents to any person as to do so could be a
> > breach of confidence. Thank you for your cooperation.
> > ___
> > 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
>
>
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This e-mail is confidential and may well also be legally privileged. If
> you have received it in error, you are on notice as to its status and
> accordingly please notify us immediately by reply e-mail and then delete
> this message from your system. Please do not copy it or use it for any
> purposes, or disclose its contents to any person as to do so could be a
> breach of confidence. Thank you for your cooperation.
> ___
> 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] what diffrent with this two sql command?

[Dave McKee]

... SELECT b FROM mytable WHERE b = c will give you each row of the
database which has identical b and c values.
... SELECT b FROM mytable AS m2 WHERE m2.b = m1.c will join the two views
of the database together, so if mytable contains

b,c
1,2
2,3

the combined table for this subquery looks like:
m1.b, m1.c, m2.b, m2.c
1,2,1,2
1,2,2,3
2,3,1,2
2,3,2,3

and it compares the middle two columns.

Hope this helps,
Dave.

(PS: I'm not particularly knowledgeable about SQLite, so this might well be
factually incorrect in important minor ways.)




On Tue, Dec 4, 2012 at 1:26 AM, YAN HONG YE  wrote:

>   SELECT c FROM mytable AS m1 WHERE NOT EXISTS (SELECT b FROM mytable AS
> m2 WHERE m2.b = m1.c);
>
>   SELECT c FROM mytable WHERE NOT EXISTS (SELECT b FROM mytable  WHERE b =
> c);
>
>
>
>
> Sincerely,
> Cordialement / Best Regards / Mit freundlichen Grüßen / Cordiali saluti !
> ___
> ---
> YE YANHONG/???- DASI/DRDS/ITVC/APFS/AVPM
> PSA PEUGEOT CITROEN(CHINA) AUTOMOTIVE TRADE CO,. LTD SHANGHAI BRANCH
> Tel: (+86) 21-2419 5488  Mobile:(+86) 13816808338  Fax: (+86) 21-2419 5004
> PSA internal call: 49 5488
> Email: yanhong...@mpsa.com
> Address: 6th Floor, Building 1, No. 1528, Gumei Road,
> Xuhui District, Shanghai
> Post Code:200233
> ??: ?1528?A1?6?
>
> ___
> 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] Stricter parsing rules

[Dave McKee]

The behaviour of quoting with [identifier] or `identifier` seems to do what
you want, but, as the docs say, these are not standard SQL.

http://www.sqlite.org/lang_keywords.html

It's not a great option, since it forces you to have nonstandard SQL, but
it's a potentially useful one to have on the table.

Dave.

sqlite> create table jam (valid int);
sqlite> insert into jam values (12);
sqlite> select [invalid] from [jam];
Error: no such column: invalid
sqlite> select `invalid` from jam;
Error: no such column: invalid
sqlite> select invalid from jam;
Error: no such column: invalid
sqlite> select "invalid" from jam;
invalid
sqlite> select 'invalid' from jam;
invalid




On Wed, Nov 14, 2012 at 10:22 PM, Simon Slavin  wrote:

>
> On 14 Nov 2012, at 9:46pm, BareFeetWare  wrote:
>
> > I agree. this tolerance by SQLite for misquoted identifiers allows a lot
> of errors. I'd really like to see the rules tightened to remove ambiguous
> input.
>
> Me three.  Perhaps this and a couple of similar things will be fixed in
> SQLite4.
>
> Simon.
> ___
> 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