On Nov 2, 2005, at 7:36 AM, [EMAIL PROTECTED] wrote:
Am I alone in thinking that a division operator that does
different things depending on the declared datatype of a
column is an abomination?
I don't think you're alone. I do think you're wrong.
By declaring column a as INTEGER, and column
> Does the check constraint fail or not? Or do different
> database engines do different things?
In Firebird 1.5 it does fail.
Though I agree with Darren's remarks, so not failing it seems to be
more flexible.
--
Nemanja Corlija <[EMAIL PROTECTED]>
Indeed.
CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK(xmailto:[EMAIL PROTECTED]
Sent: Wednesday, November 02, 2005 8:12 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] CHECK constraints
*snip*
At least in Oracle, no, your example insert works fine. If you want
the insert to fail,
On Wed, Nov 02, 2005 at 06:30:51PM -0500, [EMAIL PROTECTED] wrote:
> In a CHECK constraint, if the expression is NULL (neither true
> nor false) does the constraint fail?
At least in Oracle, no, your example insert works fine. If you want
the insert to fail, you need to add a "not null" constrain
My information is that MySQL does not enforce check constraints - your
testing supports this.
Regards.
rayB
|-+>
| | "Marcus Welz"|
| | <[EMAIL PROTECTED]|
| | om> |
| |
I'm not sure if this applies, but in my experience it is normal for a
unique value constraint to be satisfied on columns with null values,
as is a foreign key constraint, which is only evaluated on not-null
values. Following that precedent, I would say that the CHECK
constraint should pass if
To be honest, I didn't expect that either. I guess with NULL meaning
"absence of a value" the logic here is that since it cannot determine the
value of NULL, it let's it pass. Why that was chosen over failing the check
doesn't make sense to me.
MySQL 3.23.58 gives a syntax error on the table defin
MS SQL 2000 wil alsol insert (5, NULL)
From: "Marcus Welz" <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To:
Subject: RE: [sqlite] CHECK constraints
Date: Wed, 2 Nov 2005 19:24:09 -0500
PostgreSQL 8.0 will happily insert (5, NULL).
-Original Message-
From: [EMAIL PROTECTED]
"Marcus Welz" <[EMAIL PROTECTED]> wrote:
> PostgreSQL 8.0 will happily insert (5, NULL).
>
Hmmm.. Not what I expected, nor what I implemented.
But the implementation is easily changed and there is
no point in trying to be "logical" about the behavior
of NULLs, I've learned. I will probably modi
PostgreSQL 8.0 will happily insert (5, NULL).
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 02, 2005 6:31 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] CHECK constraints
In a CHECK constraint, if the expression is NULL (neither true
no
In a CHECK constraint, if the expression is NULL (neither true
nor false) does the constraint fail?
Example:
CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK( x
Brass Tilde wrote:
The problem with the grade-school rule is that, assuming the
last digit is uniformly distributed, you'll be rounding up 5
times out of 9 and rounding down 4 times out of 9.
No, if the last digit is uniformly distributed, then 0 is as likely as any
other. You round down o
> The problem with the grade-school rule is that, assuming the
> last digit is uniformly distributed, you'll be rounding up 5
> times out of 9 and rounding down 4 times out of 9.
No, if the last digit is uniformly distributed, then 0 is as likely as any
other. You round down on 0, 1, 2, 3 & 4
Henry Miller wrote:
As for 5/2, my grade school teachers taught me that if I round it at
all, the answer is 3, NEVER 2. It is only latter in life that I
learned about bankers rounding which sometimes is 2, sometimes 3.I
have never seen a justification for rounding to 2, except for the bogus
> > Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
> > > So don't make the field 10 bytes long, make it only 8. SQLite won't
> > > care a bit, and will give you the value in whatever format you want.
> >
> > Then it's not type agnostic any more. You now have an 8 byte numeric
> > and
There is clearly no 1 correct answer. So instead of arguing the point
over and over, why don't the people who object simply apply the proposed
change and report back what issues your application has? Let's see how
many people are actually using this functionality, what breaks and weigh
the decision
On Wed, Nov 02, 2005 at 10:45:54AM -0600, Jay Sprenkle wrote:
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> > So don't make the field 10 bytes long, make it only 8. SQLite won't
> > care a bit, and will give you the value in whatever format you want.
>
> Then it's not type agnost
> Was this intended as part of the division discussion?
> If so, the original statement was:
> > Am I alone in thinking that a division operator that does
> > different things depending on the declared datatype of a
> > column is an abomination?
Yes. It was in answer to (paraphrased), "what resu
> Please let me restate the proposed change: I desire to change
> SQLite so that it no longer distinguishes between integer and
> real.
Put this way, this seems to be a good idea, provided that there isn't
a performance penalty. Most of the numbers I store in SQLite
databases are small integers,
On 11/2/05, Clark Christensen <[EMAIL PROTECTED]> wrote:
>
>
> - Original Message
> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Sent: Wednesday, November 02, 2005 07:36:58
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
>
> Consider the following SQL:
>
>CREATE
- Original Message
From: [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
Sent: Wednesday, November 02, 2005 07:36:58
Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Consider the following SQL:
CREATE TABLE t1(a INTEGER, b REAL);
INSERT INTO t1 VALUES(5,5);
SELECT a/2,
On 11/2/05, Brass Tilde <[EMAIL PROTECTED]> wrote:
> > > floating point operations. If you're creating a very large database
> > > why should you pay for 80 bits (an IEEE float) of storage when 8
> will
> > > do just fine?
>
> > > So don't make the field 10 bytes long, make it only 8. SQLite won't
> > floating point operations. If you're creating a very large database
> > why should you pay for 80 bits (an IEEE float) of storage when 8
will
> > do just fine?
> > So don't make the field 10 bytes long, make it only 8. SQLite won't
> > care a bit, and will give you the value in whatever forma
> > floating point operations. If you're creating a very large database
> why should
> > you pay for 80 bits (an IEEE float) of storage when 8 will do just
> fine?
>
> So don't make the field 10 bytes long, make it only 8. SQLite won't
> care a bit, and will give you the value in whatever format y
> If it should do division the same way for every operation then you
must
> require the result to be able to represent every possible division
> result. IE the result must be floating point. If you're writing the
inner
> loop for a quake engine there are very good reasons for not wanting to
pay
I'
Am I alone in thinking that a division operator that does
different things depending on the declared datatype of a
column is an abomination?
Absolutely not.
Of course he won't be alone, doesn't make him ( or you ) correct or in fact
incorrect.
I think allowing mathematical operators built in
I don't mean to muddy the water here. It has been a very long time
since I first used SQL, but I seem to remember one of the original
selling points was that it was "user friendly" and therefore "Type-less"
so even a "Manager" could write "English like queries" and not have to
resort to dealing wi
Agreed, BUT it is the Standard. I do not question the concept. Just
make certain it is well documented in the "Gray Area" and user
configurable. That way old code survives and new code will have the
OPTION of not conforming to the Standard.
Even now I am not looking forward to "upgrading" my ol
DB2 gives this:
CREATE TABLE t1(a INTEGER, b REAL)
INSERT INTO t1 VALUES(5,5)
SELECT a/2, b/2 FROM t1
1 2
---
2 +2.50E+000
1 record(s) selected.
-Original Message-
From: Rob Lohman [mailto:[E
Seconded! Give yourself a raise! ;)
I've seen Sqlite popping up all over. I was particularly pleased to see it being
supported as a data source option for open office.
On 11/1/05, Edward Wilson <[EMAIL PROTECTED]> wrote:
> Ditto - sqlite is pure beauty - thanks.
>
> --- Clay Dowling <[EMAIL PROT
A quick test here on MSSQL & Oracle:
Microsoft SQL 2000 & SQL 2005 (beta):
create table MATHTEST (
CINT int null,
CDEC decimal null,
CDPREC double precision null,
CFLOAT floatnul
On 11/2/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>2.5|2.5
MySQL 5 and MS Access 2003
>2|2.5
PostgreSQL 8 and FireBird 1.5
--
Nemanja Corlija <[EMAIL PROTECTED]>
On 11/2/05, Brass Tilde <[EMAIL PROTECTED]> wrote:
> > Am I alone in thinking that a division operator that does
> > different things depending on the declared datatype of a
> > column is an abomination?
>
> Absolutely not.
If it should do division the same way for every operation then you must
re
I think this is the best solution in the real world, although I agree
with DRH that different division operators are a bad thing.
Surely if somebody wants the result as an integer, they can use
sqlite3_column_int() to pull the result out? Or even write a function
to do whatever rounding they see
> Am I alone in thinking that a division operator that does
> different things depending on the declared datatype of a
> column is an abomination?
Absolutely not.
On Nov 2, 2005, at 9:36 AM, [EMAIL PROTECTED] wrote:
Consider the following SQL:
CREATE TABLE t1(a INTEGER, b REAL);
INSERT INTO t1 VALUES(5,5);
SELECT a/2, b/2 FROM t1;
From the above SQL, SQLite version 3.2.7 and earlier will return
2|2
If my proposed changes for 3.3.0 go in,
Consider the following SQL:
CREATE TABLE t1(a INTEGER, b REAL);
INSERT INTO t1 VALUES(5,5);
SELECT a/2, b/2 FROM t1;
From the above SQL, SQLite version 3.2.7 and earlier will return
2|2
If my proposed changes for 3.3.0 go in, then the result will be:
2.5|2.5
If I understand wha
[EMAIL PROTECTED] wrote:
Joe Wilson <[EMAIL PROTECTED]> wrote:
I've found a potential problem -
round() is not a good substitute for the old integer truncation.
Consider the previous Sqlite3 behavior:
sqlite> select 15/8;
1
sqlite> select round(15.0/8.0);
2
Can you recommend or provi
Edward Wilson said:
> What is the best approach to concurrency in this scenario?
>
Don't choke when you get a SQLITE_BUSY error. Just wait and try again in
a second or so.
Clay Dowling
--
Simple Content Management
http://www.ceamus.com
What is the best approach to concurrency in this scenario?
--- Clay Dowling <[EMAIL PROTECTED]> wrote:
>
> Dave Dyer said:
> >
> > If I designed a sqlite database to be used by a bunch of independent
> > applications, for example a bunch of CGI scripts each of which
> > opened the database, did
> No, but I would bet somewhere near the 90%+ range are "commercial"
> applications, requiring the management of fixed point bankers
> ("European" or "English") rounding numeric data.
I've been writing commercial applications for about 20 years now,
including 10 years on payroll applications for t
Zibetti Paolo <[EMAIL PROTECTED]> wrote:
>
> Insert into foo values(5.34);
> Insert into foo values(3.0);
>
> Table foo will contain two rows that both contain a real-type number, so, to
> read the values back from the DB, I can always use sqlite3_column_double().
> With your proposed change it a
On 11/1/2005 at 21:59 [EMAIL PROTECTED] wrote:
>John Stanton <[EMAIL PROTECTED]> wrote:
>>
>> Users love such a number system because it is natural and works like
the
>> Arithmetic they learned in Grade School.
>>
>> I find the idea of dividing two integers stored in binary form and
>> gett
No, but I would bet somewhere near the 90%+ range are "commercial"
applications, requiring the management of fixed point bankers
("European" or "English") rounding numeric data. Why should the many
suffer for the benefit of the few? BCD ain't rocket science. I learned
the concept in the Military
On Wed, Nov 02, 2005 at 06:43:31AM -0600, John Stanton wrote:
> Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
> You misunderstood the comment. A schoolchild expects to see 5/2 give
> exactly 2.5, not an approximate representation of 2.5 in a floating
> I question why we have to us
Dave Dyer said:
>
> If I designed a sqlite database to be used by a bunch of independent
> applications, for example a bunch of CGI scripts each of which
> opened the database, did some processing, and exited, would
> that be (a) safe (b) effecient ?
It's very safe. My own product does that (see
What about these three warnings do you think is a concern?
Have you actually looked at the code in question to see
if the possibility of data loss is real and unintentional?
What makes you think that these warnings are not just a case
of the compiler blowing smoke?
--
D. Richard Hipp <[EMAIL PR
Well, since you put it that way. May I go one step farther and
request
that this new Dynamic Type also adhere to "Bankers Rounding" commonly
implemented as BCD in other so equipped databases.
Please, no. I'll handle rounding in my application, I don't need it on
the database. Not all databa
1/3.
Store that mysql ;-) Or what about PI? Or the square root of 10?
Not all reals can be represented as fractions either. Surd-tastic. Whilst
computers are finite state machines such approximations are the most
reasonable compromise between accuracy and performance. Or should that be
pr
> Well, since you put it that way. May I go one step farther and
request
> that this new Dynamic Type also adhere to "Bankers Rounding" commonly
> implemented as BCD in other so equipped databases.
Please, no. I'll handle rounding in my application, I don't need it on
the database. Not all data
Arjen Markus wrote:
Fred Williams wrote:
Well, since you put it that way. May I go one step farther and request
that this new Dynamic Type also adhere to "Bankers Rounding" commonly
implemented as BCD in other so equipped databases.
For years I have spent countless hours testing and sweating
[EMAIL PROTECTED] wrote:
John Stanton <[EMAIL PROTECTED]> wrote:
Users love such a number system because it is natural and works like the
Arithmetic they learned in Grade School.
I find the idea of dividing two integers stored in binary form and
getting a gratuitous conversion to floating p
Yes to both questions. Just make sure that your application can handle
the situation of occasionally finding the database locked momentarily.
In our tests on an old 200MHz server such a CGI process executes in 10mS
or less and would permit maybe as many as 200 simultaneous users.
JS
Dave Dyer
No DB product can give you the number of rows in a result set until the
search has been completed. The Sqlite callback and step approach gives
the application the ability to process the query result as the query
finds each row, avoiding the need for intermediate storage, a most
efficient and r
Fred, you are right on the money. For some reason generations of
software has been inflicted on the commercial user with numbers stored
as floating point, a complete design failure. The consequent rounding
vagaries make the implementation of simple accounting or other
fundamental arithmetic a
I find your logic clear and rational, but feel that you do not carry it
through to its logical conclusion. If you are avoiding rigid typing and
fixed size records to finally escape from the Hollerith card mindset
which has afflicted IT for a century, then why embrace fixed size binary
integers
Edward Wilson wrote:
What I was trying to say was: with other db products the drivers (or something
somewhere)
calculated the number of rows returned in a query automagicly. I have never
had to do anything
'extra' to get the number of rows in a query other than
resultset-object.rowcout - 'ro
FYI, I tried the same script on windows xp2 sqlite 3.2.7, and it worked
fine also
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 01, 2005 7:13 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Page size problem
>
> "Anton Kuznet
Given what I've read so far, you shouldn't have to change any of your code.
Conceptually speaking, all numbers would be
stored as reals internally, though actually some
would be stored as integers if possible for
efficiency.
When you invoke a SQLite accessor function such
as double() or int
Most of the discussion so far was about proposed change number 2, on the
contrary I'm concerned about proposed change number 1.
Does this mean that a number that can be stored as an integer will be stored
as an integer and, thus, I will need to read it back as an integer ?
Here is what I mean: with
60 matches
Mail list logo
