Re: [sqlite] Join trouble

[Fowler, Jeff]

As a suggestion, try doing this outside of your application first,
simply by running sqlite3 from the command line. That way you can see if
the problem has is because of something you're doing vs. sqlite itself.
The join syntax I suggested (including the rtrim function) works fine
here using the current release of sqlite, as does the "inner join"
syntax.

- Jeff

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Johnstone, Simon
Sent: Monday, February 11, 2008 11:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Join trouble

I have tried the following but get the following error 

Warning: sqlite_query() [function.sqlite-query]: no such function: rtrim
in C:\Program Files\Apache Software
Foundation\Apache2.2\htdocs\swiftdist\website\database\loginsuccess.php
on line 22
SQL logic error or missing database
SELECT tblusers.company, tblcompany.company FROM tblusers, tblcompany
WHERE rtrim(tblusers.company) = rtrim(tblcompany.company)

How do I create the sql statement so that it includes the functions
I followed the link you suggested but it was a bit too complicated for
me I am a very very new programmer.

I have also tried 

$query = "SELECT a.company, b.company FROM tblusers a inner join
tblcompany b on (a.company = b.company) ";

This works but yet again doesn't print any results to the screen.

Simon Johnstone
Customer Service Data Officer

Tel + 44 (0) 1484 465 500 Ext 5154
Fax: +44 (0) 1484 465 586
E-mail [EMAIL PROTECTED]
Web: www.davidbrown.com

David Brown Engineering Limited
Company No 331925 England
Park Works, Park Road, Huddersfield HD4 5DD England

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Fowler, Jeff
Sent: 11 February 2008 16:17
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Join trouble

Simon,

If the query is returning no rows, my guess would be trailing spaces in
the data. This has been discussed at length and there is a new collating
sequence (http://www.sqlite.org/cvstrac/chngview?cn=4732)to address it.
Either use it or try saying:
WHERE rtrim(tblusers.company) = rtrim(tblcompany.company)

- Jeff

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Johnstone, Simon
Sent: Monday, February 11, 2008 11:11 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Join trouble

I am having serious trouble creating a join on my database. I have
tested it in other databases and it works so it must be a problem with
sqlite and the join??? I don't know but am really struggling. 

 

$query = "SELECT name, username, currency, company, salescontact,
orders2006, orders2007, sales2006, sales2007, company FROM tblusers,
tblcompany WHERE tblusers.company = tblcompany.company";

 

The page loads up but there is just no data from the database, if I use
any of the databases on their own they work fine its only when I join
them

 

Cheers for any help

 

Simon Johnstone

Customer Service Data Officer

 

Tel + 44 (0) 1484 465 500 Ext 5154

Fax: +44 (0) 1484 465 586

E-mail [EMAIL PROTECTED]
<blocked::mailto:[EMAIL PROTECTED]> 

Web: www.davidbrown.com <blocked::http://www.davidbrown.com/> 

 

David Brown Engineering Limited

Company No 331925 England

Park Works, Park Road, Huddersfield HD4 5DD England

 

___
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
___
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] Join trouble

[Fowler, Jeff]

Simon,

If the query is returning no rows, my guess would be trailing spaces in
the data. This has been discussed at length and there is a new collating
sequence (http://www.sqlite.org/cvstrac/chngview?cn=4732)to address it.
Either use it or try saying:
WHERE rtrim(tblusers.company) = rtrim(tblcompany.company)

- Jeff

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Johnstone, Simon
Sent: Monday, February 11, 2008 11:11 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Join trouble

I am having serious trouble creating a join on my database. I have
tested it in other databases and it works so it must be a problem with
sqlite and the join??? I don't know but am really struggling. 

 

$query = "SELECT name, username, currency, company, salescontact,
orders2006, orders2007, sales2006, sales2007, company FROM tblusers,
tblcompany WHERE tblusers.company = tblcompany.company";

 

The page loads up but there is just no data from the database, if I use
any of the databases on their own they work fine its only when I join
them

 

Cheers for any help

 

Simon Johnstone

Customer Service Data Officer

 

Tel + 44 (0) 1484 465 500 Ext 5154

Fax: +44 (0) 1484 465 586

E-mail [EMAIL PROTECTED]
 

Web: www.davidbrown.com  

 

David Brown Engineering Limited

Company No 331925 England

Park Works, Park Road, Huddersfield HD4 5DD England

 

___
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] How to select Strict Affinity or No Affinity modes?Attention: DRH

[Fowler, Jeff]

I agree. After many years with SQL Server and Oracle (but new to
SQLite), the concept of storing different datatypes within the same
field is something I've had difficulty grasping. I'm not saying it's a
bad thing, but from a business perspective I can't think of a situation
where we would not want strict affinity. So if it becomes an option
we'll use it throughout our application.

- Jeff


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Samuel Neff
Sent: Friday, February 08, 2008 11:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How to select Strict Affinity or No Affinity
modes?Attention: DRH

I would like to have strict affinity mode too.  In our schemas we use
check constraints to enforce strict affinity.  Unless you're working in
a dynamic typed environment, I can't imagine why you would want to have
inconsistent data within a single database field.  Also for consistency
with (every?) other database engine out there, a strict affinity mode
would be good.
Strict affinity will also benefit all wrapper writers who write wrappers
following a framework that assumes strict field typing (which I think is
pretty much all of them since all other db's have strongly typed
fields).

Thanks,

Sam


On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote:

> I second the strict affinity mode as an optional feature, for the same

> reasons as Lee.
>
>A while back I ran into a problem while using the bit and feature 
> of sqlite and got unexpected results because sqlite changed the type 
> from a 64bit integer into a real. (I think)... In this case it would 
> have been simpler to debug, if there had been a type conversion
warning or a failure.
>
> Regards,
> Ken
>
>
___
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] Update value from either database table?

[Fowler, Jeff]

Without more info, here's the general approach:

UPDATE c
SET int_field = int_field+1
WHERE (table a condition) OR (table b condition)

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gussimulator
Sent: Monday, February 04, 2008 1:57 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Update value from either database table?

I have a field I need to update given a condition, but I don't know
wether the condition occurs in table A or table B, how can I perform
this query?

I have 2 identical tables in design, but one contains system data and
the other one contains user data... On my update routine (in C) I have
to increase an INTEGER field from either table given a condition... The
thing is, the condition could be present on both tables... what should I
do??

Thanks!

___
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] how to do this case when?

[Fowler, Jeff]

At the end (outside the subquery), add:

WHERE Field1 IN (SELECT Field1 FROM Table2)

- Jeff

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 31, 2008 3:15 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] how to do this case when?

How do I alter this SQL, so that the original field remains the same
when there is no match? A case when else end should do it, but I can't
get it right.

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1)

Thanks for any advice.

RBS




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] find the highest rank per group

[Fowler, Jeff]

Puneet,

I'm assuming you meant to say "where rank is the highest" rather than
title, as that's what your example shows.

Here's the syntax:
select * from table a
where rank = (select max(rank) from table b where b.id = a.id)
 
- Jeff
-Original Message-
From: P Kishor [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 28, 2008 10:42 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] find the highest rank per group

I have

id, name, .., title, rank
1, a, .., foo, 5
1, a, .., bar, 4
1, a, .., bar, 7
2, b, .., baz, 6
2, b, .., qux, 9

and so on

I want

1, a, .., bar, 7
2, b, .., qux, 9

that is, all the rows for each name where title is the highest.

SELECT id, name, .., title, MAX(rank)
FROM table
GROUP BY id, name, .., title

doesn't cut it as it finds

1, a, .., foo, 5
1, a, .., bar, 7
2, b, .., qux, 9

Instead, I want only one occurrence of "name" What would be the syntax
for this?

Thanks,

Puneet.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Number of elements in IN clause

[Fowler, Jeff]

Just experimented with the short-hand version. It seems to work only
when the "stuff" table has a single field, although the field name
doesn't matter. More than one field causes a query error, even if the
field name being compared is in the table.
I've actually never seen this notation, but it does make sense. It would
be nice if it matched on field name, that way the same syntax could be
used in both cases.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 28, 2008 9:20 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Number of elements in IN clause

"Samuel R. Neff" <[EMAIL PROTECTED]> wrote:
> I don't think it is standard SQL.  At the very least, it doesn't work 
> in MSSQL.  Standard is
> 
>   SELECT * FROM maintable WHERE key IN (select x from stuff);
> 
> SQLite shortened version is much nicer.. wish it was standard.
> 

SQLite also accepts the more verbose version shown above, of course.
The two statements do *exactly* the same thing.

I cannot believe that I would have put in the short-hand notation
without having seen it somewhere else first.
Does PostgreSQL support the short-hand version?

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Number of elements in IN clause

[Fowler, Jeff]

Yes, this is standard SQL. The syntax is:

SELECT * from maintable where key IN (SELECT key from anothertable)  

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 28, 2008 7:54 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Number of elements in IN clause

>SELECT * FROM maintable WHERE key IN stuff;

Thanks for that tip. Didn't know you could do that.
Can't remember this as standard SQL.

RBS



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 28 January 2008 12:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Number of elements in IN clause

Felix Radensky <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> Is there any limitation on the number of elements in IN clause ?
> Can one have, e.g. thousands of elements ? Also, can having to many 
> elements become inefficient at some point and one has to use some 
> other technique, i.e. comparing elements one by one in a loop ?
> 

You can create a table that contains the elements that you would
normally put in your IN clause:

   CREATE TEMP TABLE stuff(x);
   INSERT INTO stuff VALUES('one');
   INSERT INTO stuff VALUES('two');
   
   INSERT INTO stuff VALUES('one million');

Then run your query this way:

   SELECT * FROM maintable WHERE key IN stuff;

--
D. Richard Hipp <[EMAIL PROTECTED]>




-
To unsubscribe, send email to [EMAIL PROTECTED]


-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Foreign Constraint Triggers Across Attached Databases

[Fowler, Jeff]

Hi Sam,

Brainstorming your suggestion a bit, rather than allowing triggers to
function across databases (which is understandably not practical given
the architecture), perhaps there could be a way to define a "partition"
within a database (similar to creating a folder on a drive). Borrowing
from Oracle, maybe this could be called a "Schema", and a "create
schema" command could be used to create it. Queries referencing a table
in a different schema must preface the table name with the schema name -
e.g., "SELECT * FROM Sam.users".

Just some rainy day ideas..

- Jeff

-Original Message-
From: Samuel R. Neff [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 24, 2008 10:59 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Foreign Constraint Triggers Across Attached
Databases


I've run into two situations recently where I would have preferred to
write triggers across databases.  Both related to audit tracking of
data.

The first situation is that for every table, I have a corresponding
history table that records the history of every record.  So let's say I
have

CREATE TABLE Users (UserID, UserName);

then I also have

CREATE TABLE Users_History (UserHistoryID, TransactionID, ChangeType,
UserID, UserName)

and to track transactions I have 

CREATE TABLE Transactions(TransactionID, TrasnactionTS, UserID)

and to track the single active transaction I have

CREATE TABLE ActiveTransaction(TransactionID)

which is always blank except when in the middle of a transaction (which
always starts with inserting a record to that table, and then deleting
it right before commit).


So I have triggers on the Users table that whenever a record is
inserted, updated, or deleted, the corresponding new values for
insert/update and old values for delete are inserted into the history
table.  The triggers look like this:

CREATE TRIGGER HI_Users
AFTER INSERT ON Users
FOR EACH ROW BEGIN

SELECT RAISE(ABORT, 'Can not update database when no transaction
is active.  Create a new transaction in the Transactions table and
create an associated record in the ActiveTransaction table.')
WHERE (SELECT COUNT(*) FROM ActiveTransaction) = 0;

INSERT INTO 
Users_History (
TransactionID,
ChangeType,
UserID, UserName
)
SELECT
(SELECT MIN(TransactionID) FROM
ActiveTransaction),
'I',
NEW.UserID, NEW.UserName
;
END;


Due to the restriction that triggers cannot span databases, I have my
main data tables, history tables, and the ActiveTransaction table all in
the same database.  I'd really rather the history tables be in a
separate database because they can grow quite large and when I ask a
customer to e-mail me their database, I'd like them to be able to easily
e-mail the main data only without the extra history info.

Also, it would be much cleaner if the ActiveTransaction table was in
TEMP instead of in MAIN so each connection clearly has it's own table
(except where now they share the same table definition, just the data is
never shared due to convention of being populated only within a
transaction).

I hope these examples are helpful.  I would like to see the ability to
create a trigger that spans database some day and would expect that the
trigger could be defined and simply would error out if at runtime the
required database was not present.

Thanks,

Sam

---
We're Hiring! Seeking a passionate developer to join our team building
Flex based products. Position is in the Washington D.C. metro area. If
interested contact [EMAIL PROTECTED]
 
-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 24, 2008 6:56 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Foreign Constraint Triggers Across Attached
Databases


Triggers between two separate databases are not allowed since if
you DETACH one of the databases, the triggers obviously will no
longer work.  If two separate databases are so inseparably bound
that they need triggers between them, why not just make them a
single database?

The same goes for foreign key constraints.  There are *severe*
implementation difficulties trying to get this to work across
separate database.  If you have a foreign key in a separate
database, that really argues that the two databases ought to
be one.




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLite character comparisons

[Fowler, Jeff]

Hi Dennis,

I may very well be mistaken - wouldn't be the first time!! I tried to
access the actual specification from ANSI (www.ansi.org), but you either
have to purchase them or access one of the "drafts" (which a couple
folks in this thread have done), but the draft may or may not match the
actual standard. While Dr. Hipp's new RTRIM collation makes this a moot
point, the source for my statement came from this article:
http://support.microsoft.com/kb/316626


As I'd mentioned, we have both SQL Server 2005 and Oracle 10g and they
default to ignore trailing spaces on character compares, although it may
be possible to turn this off for those who don't like it. I believe (but
am not sure) that MYSql and PostGreSQL ignore them by default also. I
understand many of the arguments folks have against this and there's not
much point to continuing the discussion; I won't change their mind and
they won't change mine! But I guess that's why we have Republicans and
Democrats :-)

But all is well - the RTRIM collation option is a neat and simple
solution. My thanks to Dr. Hipp for that, and also to everyone who has
shown an interest in this issue.

- Jeff


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 21, 2008 11:46 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite character comparisons

Fowler, Jeff wrote:
> Hello All,
>  
> Not trying to be antagonistic, but I'm curious to know how many of you
agree with Darren's sentiments on this issue. To restate briefly, ANSI
SQL-92 specifies that when comparing two character fields, trailing
spaces should be ignored. Correct me if I'm wrong Darren, but you feel
this is a bad decision, and in fact SQLite's implementation of character
comparison (respecting trailing spaces) is superior to ANSI's specs.
Keep in mind this is not some obscure issue that can be subject to
different interpretations by different vendors; it's very clearly
stated: "The ANSI standard requires padding for the character strings
used in comparisons so that their lengths match before comparing them."
>   
Jeff,

I think you are mistaken about what the ANSI spec says.

There are two string types in ANSI SQL, character strings (which come is
several subtypes), and binary strings. The following excerpts are taken
from the SQL:1999 spec.

Section 4.2.1 Character Strings and Collations describes the operations
on character strings. It describes comparisons as
> Given a collating sequence, two character strings are identical if and

> only if they are equal in accordance with the comparison rules 
> specified in Subclause 8.2, . The collating 
> sequence used for a particular comparison is determined as in 
> Subclause 4.2.3, ''Rules determining collating sequence usage''.
Binary strings are defined in Section 4.3 as;
> A binary string is a sequence of octets that does not have either a 
> character set or collation associated with it.
And their comparison is detailed in 4.3.1 as;
> All binary strings are mutually comparable. A binary string is 
> identical to another binary string if and only if it is equal to that 
> binary string in accordance with the comparison rules specified in 
> Subclause 8.2, .

General Rules 3 and 4 of section 8.2  describe the
comparison of these strings. I have copied these sections below.

> 3) The comparison of two character strings is determined as follows:
>
> a) Let CS be the collating sequence indicated in Subclause 4.2.3, 
> ''Rules determining collating sequence usage'', based on the declared 
> types of the two character strings.
>
> b) If the length in characters of X is not equal to the length in 
> characters of Y, then the shorter string is effectively replaced, for 
> the purposes of comparison, with a copy of itself that has been 
> extended to the length of the longer string by concatenation on the 
> right of one or more pad characters, where the pad character is chosen

> based on CS. If CS has the NO PAD characteristic, then the pad 
> character is an implementation-dependent character different from any 
> character in the character set of X and Y that collates less than any 
> string under CS. Otherwise, the pad character is a .
>
> c) The result of the comparison of X and Y is given by the collating 
> sequence CS.
>
> d) Depending on the collating sequence, two strings may compare as 
> equal even if they are of different lengths or contain different 
> sequences of characters.
> When any of the operations
> MAX, MIN, and DISTINCT reference a grouping column, and the UNION, 
> EXCEPT, and INTERSECT operators refer to character strings, the 
> specific value selected by these operations from a set of such equal 
> values is implementation-dependent.
>
> NOTE 129 - If the coercibility characteristic of the comparison is 
> Coercible

RE: [sqlite] SQLite character comparisons

[Fowler, Jeff]

I don't recall asking for a vote on the subject or asking anyone to agree with 
me, but thank you for your input anyway Jay. 
 
And I do appreciate Dr H. for adding the "COLLATE RTRIM" declaration!
 
Regards,
 
- Jeff



From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
Sent: Sun 1/20/2008 11:56 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite character comparisons



On Jan 20, 2008 10:19 AM, Fowler, Jeff <[EMAIL PROTECTED]> wrote:
>
> Not trying to be antagonistic, but I'm curious to know how many of you agree 
> with Darren's sentiments on this issue.

Sqlite is DRH's project. Voting doesn't come into the picture
anywhere. If you convince him
to fix it then he might, if not you're wasting your time asking if
people agree with you.

>Sure - we can handle this situation by writing more code looking for
spaces everywhere they might occur.

The source for the database engine is provided. It would be more
efficient to change it there.
You could also provide the code back to the community as an option for
others to use.

I'm not trying to be unpleasant, I just believe where you're headed
isn't going to be as valuable as other options.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com <http://www.cthulhubucks.com/> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLite character comparisons

[Fowler, Jeff]

Hello All,
 
Not trying to be antagonistic, but I'm curious to know how many of you agree 
with Darren's sentiments on this issue. To restate briefly, ANSI SQL-92 
specifies that when comparing two character fields, trailing spaces should be 
ignored. Correct me if I'm wrong Darren, but you feel this is a bad decision, 
and in fact SQLite's implementation of character comparison (respecting 
trailing spaces) is superior to ANSI's specs. Keep in mind this is not some 
obscure issue that can be subject to different interpretations by different 
vendors; it's very clearly stated: "The ANSI standard requires padding for the 
character strings used in comparisons so that their lengths match before 
comparing them."
 
Does anyone know of another RDBMS (ANSI or no) that respects trailing spaces 
when comparing character data? We have both Oracle 10g and SQL Server 2005 in 
house and they both work "correctly" according to the specification. Has anyone 
tried it with DB2 or Informix? What about PostGres and MySQL? Although I 
haven't asked him, I'm guessing Zbigniew's suggestion a while back for an 
auto-trim feature stemmed from this issue. Other than saving space, would there 
be a need to trim data if WHERE, HAVING clauses and joins followed the spec? 
Also, other than performance (which seems to be the primary concern), would 
anyone would be negatively impacted if the current behavior were changed?
 
Our app creates SQLite tables dynamically based on the output from user-defined 
queries that run against data warehouses (of practically any "flavor") we have 
no control over, and we insert the results into SQLite. Sure - we can handle 
this situation by writing more code looking for spaces everywhere they might 
occur. But to me (and maybe only to me?), it makes sense for SQLite -- where 
reasonably possible -- to attempt to follow clear ANSI guidelines, allowing 
developers to override it only in cases where this adherence produces 
undesirable results. I can't see where this is undesirable from an applications 
standpoint, although I can see where performance may be a concern.
 
Regards,
 
Jeff Fowler
 


From: Darren Duncan [mailto:[EMAIL PROTECTED]
Sent: Fri 1/18/2008 4:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite character comparisons



At 4:11 PM -0500 1/18/08, Fowler, Jeff wrote:
>"Better" depends on who you ask - I'd say it's worse, and I bet most
>DBA's would agree. The ANSI standard is to ignore trailing spaces when
>comparing character strings in a WHERE clause, a HAVING clause, or a
>join. So I can take the exact same data, run the exact same query, yet
>get a different answer from SQLite vs. Oracle or SQL Server. In fact, we
>found this issue because we DID get a different answer.

And every SQL implementation already has many differences from every
other one, even with fundamentals, making them all generally
incompatible and proprietary, and this example is right in line with
the kinds of differences they have.  Other implementations have
distinct var-char and fixed-char types, while SQLite only has the
former.

Part of the problem here is that the ANSI standard is itself flawed.

(Maybe this particular case of trailing spaces is an exception, but
many parts of ANSI SQL, AFAIK, are more focused on what the syntax of
SQL is, and they say that the semantics, even of some fundamental
operations, is left up to the implementation to decide for itself.
So what good does it do you if SQL of the same syntax will compile on
different DBMSs if it behaves differently in each one?  And the
standard considers this valid.)

How many other programming langauges besides ANSI SQL treat trailing
spaces as insignificant.

>Regarding whether by extension it should be impossible to create strings
>with trailing spaces; I side with the SQLite developers who say it isn't
>the engine's job to trim blanks in data. Most other engines I've used do
>not trim spaces either, even if the field is a varchar.

And rightly so, you should not trim spaces, because spaces are significant.

Herein lies a wider part of the problem.  The ANSI SQL is
inconsistent in how it treats trailing spaces in strings.  On one
hand it wants to preserve them, but on the other hand it wants to
ignore them in its most fundamental operation other than preserving.

(With my "it should not be possible" sentence, I was not saying that
spaces should not be trimmed in the fictional scenario where a
character string does by definition not contain trailing spaces, but
that code specifying them should produce an error rather than
succeed.  The matter is analagous to what would happen if you write
code that tries to treat the character string literal 'foo' as a
number.)

>But - whether ANSI compliance is considered "bloat" is not really my
>place to comment upon.  I guess it's really is up to the SQLite team.

Y

RE: [sqlite] SQLite character comparisons

[Fowler, Jeff]

"Better" depends on who you ask - I'd say it's worse, and I bet most
DBA's would agree. The ANSI standard is to ignore trailing spaces when
comparing character strings in a WHERE clause, a HAVING clause, or a
join. So I can take the exact same data, run the exact same query, yet
get a different answer from SQLite vs. Oracle or SQL Server. In fact, we
found this issue because we DID get a different answer.

Regarding whether by extension it should be impossible to create strings
with trailing spaces; I side with the SQLite developers who say it isn't
the engine's job to trim blanks in data. Most other engines I've used do
not trim spaces either, even if the field is a varchar.

But - whether ANSI compliance is considered "bloat" is not really my
place to comment upon. I guess it's really is up to the SQLite team.
Purely from a business usability standpoint (not a programming one), I
would say there's no question that it's far more useful to do
comparisons the ANSI way. If for some reason I truly want to compare &
respect trailing spaces, I can still do that using a function such as
HEX(A) = HEX(B) or something better.

- Jeff Fowler

 

-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 18, 2008 3:33 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite character comparisons

At 10:43 AM -0500 1/17/08, Fowler, Jeff wrote:
>Hello All,
>I've used SQL Server for over 15 years, Oracle off & on when I have no 
>choice, but SQLite for a couple weeks. I've just learned (today) that 
>SQLite respects trailing spaces when comparing two character fields.
>I.e. 'SQLITE' <> 'SQLITE '
>Is this behavior intentional? Neither SQL Server nor Oracle do this.
>Just curious as to why it works this way.

Because respecting the actual contents of the string is the better way
to do things.

The strings 'SQLITE' and 'SQLITE ' are not the same string.  Just as the
strings 'SQLITE' and 'sqlite' are not the same string.  A computer
language is more logical, predictable, and easy to use when a test for
equality or inequality actually treats every distinct value as distinct.
If trailing spaces were supposed to be insignificant for an equality
test, then it should not be possible to define a string value containing
trailing spaces at all.

Moreover, treating 'SQLITE' and 'SQLITE ' as not being the same string
also is consistent with the SQLite philosophy, because it means SQLite
has fewer exceptions to be concerned with in a simpler set of rules, and
also not having to check lengths and space pad before each compare also
makes the code simpler, and less buggy, and it saves CPU cycles.  A
value equality test is a very common and fundamental thing to do in a
DBMS, and bloating that will have a big performance impact.

-- Darren Duncan


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Date arithmetic question

[Fowler, Jeff]

Guys,
 
I guess I'm the newest SQLite person on this email list and I know I'm 
definitely the dumbest. It seems like a lot of you are trying to justify why 
two dates that are one minute apart can have a function say they're one month 
apart. Don't look at it that way. Back when mainframes and dinosaurs ruled the 
world I used to be a fairly decent programmer, now I run a small company, so 
today I'm coming from a business standpoint not a programmer one. 
 
To give some background, we're in the process of embedding SQLite into our 
application, and yes it's an awesome product. Our software sits on top of huge 
data warehouses (hundreds of millions of rows) which are typically either 
Oracle or SQL Server. We run queries against these databases and store the 
result sets in SQLite, where we run subsequent queries to filter & format the 
output.
 
A huge number of businesses are date driven. Publishers want to renew 
subscriptions before they expire. Insurance companies need to renew policies. 
Our largest client sells service contracts which have a start & end  date. So 
it's an everyday occurrence for a manager to want to know how many customers 
will expire within the next three months, or what an average contract length is 
in months.
 
My request was for a new date function that returns the difference, or 
"calendar interval" if you prefer, between two dates. Without such a function 
we must say:
WHERE (strftime('%Y', LaborEndDate)*12+strftime('%m', LaborEndDate)) - 
(strftime('%Y', LaborStartDate)*12+strftime('%m', LaborStartDate)) < 3
 
Wow. This is quite a mouthfull for something that's so commonly needed, and 
it's harder to generate SQL automatically when a user clicks a checkbox. 
Clearly it's far simpler (and easier to program) if we could say:
WHERE datediff('month', LaborEndDate, LaborStartDate) < 3
 
Datediff also supports years, weeks, days, hours, seconds, and milliseconds. 
It's just a quick & easy way to make life easier. But.. of course I know the 
SQLite team can't slap in every enhancement that somebody suggests; so I just 
wanted to explain why this would be useful for some of us. We do have a vested 
interest in the product!
 
- Jeff




From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thu 1/17/2008 9:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date arithmetic question



Gerry Snyder <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> >
> > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should
> > return 1 even though the difference is really only 1 second?  Seems
> > goofy to me
> >
> >  
>
> I have been staring at this until I'm getting goofy.
>
> Written as it is, isn't the time interval 1 second short of two days?
>
> If you want an interval of 1 second shouldn't it be
>
> datediff('month', '2008-02-01 00:00:00','2008-01-31 23:59:59')
>
> ?
>
>
> Gerry, more confused than usual (as usual)
>

Yeah.  I got it backwards.  Sorry.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLite character comparisons

[Fowler, Jeff]

By the way.. I found this snippet. If I read it right, it seems that
IGNORING trailing spaces during string comparisons is ANSI standard.

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2,
, General rules #3) on how to compare strings with
spaces. The ANSI standard requires padding for the character strings
used in comparisons so that their lengths match before comparing them.
The padding directly affects the semantics of WHERE and HAVING clause
predicates and other Transact-SQL string comparisons. For example,
Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for
most comparison operations. 

-Original Message-
From: Zbigniew Baniewski [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 17, 2008 5:20 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite character comparisons

On Thu, Jan 17, 2008 at 10:43:20AM -0500, Fowler, Jeff wrote:

> I've used SQL Server for over 15 years, Oracle off & on when I have no

> choice, but SQLite for a couple weeks. I've just learned (today) that 
> SQLite respects trailing spaces when comparing two character fields.
> I.e. 'SQLITE' <> 'SQLITE '
>  
> Is this behavior intentional? Neither SQL Server nor Oracle do this.
> Just curious as to why it works this way.

PostgreSQL sees the strings that way too:

mydbase=> select 'str' = 'str';
 ?column? 
--
 t
(1 row)

mydbase=> select 'str' = 'str ';
 ?column? 
--
 f
(1 row)


Those are different strings, anyway. Can't recall now, does there any
setting to change this - but you can easily find out at their website.
-- 
pozdrawiam / regards

Zbigniew Baniewski


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Date arithmetic question

[Fowler, Jeff]

Remember gang, if you want to know how many seconds are between two
timestamps, you wouldn't ask for a difference in months. You'd say
something like DATEDIFF(seconds, t1, t2).

- Jeff

-Original Message-
From: Markus Hoenicka [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 17, 2008 3:47 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date arithmetic question

[EMAIL PROTECTED] writes:
 > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00')
should  > return 1 even though the difference is really only 1 second?
Seems  > goofy to me
 > 

well, this is one second rounded up to the next full month...If that is
the kind of information you want to compute, it's probably not that
goofy after all.

regards,
Markus

--
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Date arithmetic question

[Fowler, Jeff]

You're right of course - I just noticed the question pertains to the
algorithm, not the function itself. Sorry!

I just ran a simple test using "popular RDBMS product A" on one of our
internal databases, as follows:

select 'year difference:', datediff(yy,'12/31/2007','1/1/2008') --> year
difference: 1
select 'month difference:', datediff(mm,'1/31/2007','2/1/2007') -->
month difference: 1
select 'week difference:', datediff(wk,'1/5/2008','1/6/2008')   --> week
difference: 1

So for months, the function basically does the arithmetic I did myself
using the 
(strftime( '%Y', date1)*12+strftime('%m',date1)) -
(strftime('%Y',date2)*12+strftime('%m',date2))

Regardless of actual days, it simply decides that if two dates occur in
two consecutive calendar months, they are a month apart. Same w/ year
and week. Of course, for precise accuracy it's better to use days..

- Jeff

-Original Message-
From: Scott Baker [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 17, 2008 3:13 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date arithmetic question

Fowler, Jeff wrote:
> Yes - I've looked over the current date functions. I would propose a 
> single function addition that's hugely valuable in the business world.
> SQL Server has a function called "datediff" for date arithmetic. It 
> accepts three parameters. The first indicates the unit of scale 
> (years, months, weeks, days, etc. - I think it even goes into
milliseconds!).
> The next two parameters are the dates to compute the difference
between.
> It returns an integer.

I don't think you answered Richard's original question. What constitutes
a month? Since month lengths vary, there is no exactly science to "how
many months between these two dates." Otherwise your best bet is what he
already recommended.

SELECT (julianday(date2) - julianday(date1)) / 30.43666 AS Months;

--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Date arithmetic question

[Fowler, Jeff]

Yes - I've looked over the current date functions. I would propose a
single function addition that's hugely valuable in the business world.
SQL Server has a function called "datediff" for date arithmetic. It
accepts three parameters. The first indicates the unit of scale (years,
months, weeks, days, etc. - I think it even goes into milliseconds!).
The next two parameters are the dates to compute the difference between.
It returns an integer.

Regards,

- Jeff

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 17, 2008 2:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date arithmetic question

"Fowler, Jeff" <[EMAIL PROTECTED]> wrote:
> Hello All,
>  
> SQLite newbie here. I've looked through the email archives and website

> trying to find out how to compute the difference in months between two

> given dates. Each date is in -MM-DD HH:MM:SS format.
>  
> The best I've been able to come up with seems rather ugly:
>  
> SELECT (strftime( '%Y', date1)*12+strftime('%m',date1)) -
> (strftime('%Y',date1)*12+strftime('%m',date1))
>  
> Am I on the right track, or is there something obvious I'm missing?
>  

Computing the number of months between two dates is problematic since
the length of a month varies.  Is 2007-01-31 to 2007-02-28 a whole month
even thought it is only 28 days?  What about 2007-04-10 to 2007-05-08?
That is also 28 days.  Does it count as a whole month too, or is it two
days shy of a whole month? 

You can compute the number of days between two days very easily:

   SELECT julianday(date2) - julianday(date1);

And I suppose you could divide that value by 30.4366 (which is the
average number of days in a month) to get the number of months.  

So the answer to your question is that there is no easy answer to your
question because it depends on how you define the "difference in months"
and there does not appear to be a single intuitive definition for that
question.

Do you have a particular algorithm for "difference in months"
in mind?  

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQLite character comparisons

[Fowler, Jeff]

Hello All,
 
I've used SQL Server for over 15 years, Oracle off & on when I have no
choice, but SQLite for a couple weeks. I've just learned (today) that
SQLite respects trailing spaces when comparing two character fields.
I.e. 'SQLITE' <> 'SQLITE '
 
Is this behavior intentional? Neither SQL Server nor Oracle do this.
Just curious as to why it works this way.
 
Thanks,
 
- Jeff
 

[sqlite] Date arithmetic question

[Fowler, Jeff]

Hello All,
 
SQLite newbie here. I've looked through the email archives and website
trying to find out how to compute the difference in months between two
given dates. Each date is in -MM-DD HH:MM:SS format.
 
The best I've been able to come up with seems rather ugly:
 
SELECT (strftime( '%Y', date1)*12+strftime('%m',date1)) -
(strftime('%Y',date1)*12+strftime('%m',date1))
 
Am I on the right track, or is there something obvious I'm missing?
 
Thanks in advance,
 
Jeff Fowler