Re: [sqlite] Do I need to migrate to MySQL?

[Darren Duncan]

Mr. Puneet Kishor wrote:
> On Jun 3, 2011, at 6:16 PM, Darren Duncan wrote:
>> a.  MySQL silently ignores all CHECK constraints in all engines, so for
>> example you can't even tell it you want a column to only hold values
>> between 1 and 10. Its in the MySQL docs:  "The CHECK clause is parsed but
>> ignored by all storage engines."
> 
> Yes and no. Apparently the above was true before 5.0.2, but apparently it has
> been fixed since then. From the docs,

No, it hasn't been fixed at all.

Check constraints are accepted and silently ignored, and even with strictness 
enabled, you still don't even get a warning, never mind a rejection.

 From http://dev.mysql.com/doc/refman/5.5/en/create-table.html (the MySQL 5.5 
manual):  "The CHECK clause is parsed but ignored by all storage engines.".

Whenever I listed a MySQL deficiency in this thread, that I encountered, I made 
sure to check whether the latest MySQL version still had the problem, and I 
only 
reported deficiencies that are not addressed in the latest available version, 
so 
5.5.x in this case.

Also, I'm using version 5.0.30 specifically, which is newer than 5.0.2.

Observe ...

---

mysql> create table test2 (myposint integer, check (myposint > 0));
Query OK, 0 rows affected (0.00 sec)

mysql> SET sql_mode = 'STRICT_TRANS_TABLES'; SET sql_mode = 
'STRICT_ALL_TABLES';Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test2 set myposint = -1;
Query OK, 1 row affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from test2;
+--+
| myposint |
+--+
|   -1 |
+--+
1 row in set (0.00 sec)

mysql> desc test2;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| myposint | int(11) | YES  | | NULL|   |
+--+-+--+-+-+---+
1 row in set (0.00 sec)

---

> "Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values
> and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that
> remains the default behavior, but you can change the server SQL mode to
> select more traditional treatment of bad values such that the server rejects
> them and aborts the statement in which they occur."
> 
> and
> 
> "In MySQL 5.0.2 and up, you can select stricter treatment of input values by
> using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL modes:
> 
> SET sql_mode = 'STRICT_TRANS_TABLES'; SET sql_mode = 'STRICT_ALL_TABLES';

That is talking about inserting values not of the declared base types of the 
columns, and not about CHECK constraints.

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

Re: [sqlite] Do I need to migrate to MySQL?

[Ian Hardingham]

Tom, thank you so much for the extensive advice.

Ian

On 04/06/2011 15:27, BareFeetWare wrote:
> On 03/06/2011, at 11:40 PM, Ian Hardingham wrote:
>
>> Hey guys, thank you very much for the help so far.
>>
>> The list of calls which I make during the "end match section", which can
>> take 2 seconds, is:
>>
>> SELECT * FROM multiturnTable WHERE id=? LIMIT 1
>>
>> UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=?
>>
>> UPDATE multiturnTable SET complete=1, score=? WHERE id=?
>>
>> SELECT * FROM userTable WHERE name='?'  twice
>>
>> UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?,
>> dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?',
>> scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice
> If properly constructed, I would expect this to be almost instant.
>
> I suggest:
>
> 1. Use integer columns instead of text for id primary key
> 2. Use foreign keys (ie "references")
> 3. Atomize your columns, ie only one piece of data per column (eg don't store 
> record as number tab number in one column).
> 4. For any column that is multiple choice (eg status), don't store as text, 
> store as an integer that references another table.
> 5. In general, properly normalise your tables, which includes the above. It's 
> hard to be more precise without knowing your data better.
> 5. Use triggers to update the related user table when a match changes (eg 
> ends).
> 6. Do all of an event in one transaction.
> 7. Do it all (or as much as possible) of an event in SQL, rather than passing 
> values back from SQL into application then back to SQL.
>
> At the basic level, do the foloowing:
>
> Change multiturnTable to this (I've only really changed the first three 
> columns):
>
> create table multiturnTable
> ( id integer primary key not null
> , player1 integer not null references userTable(id)
> , player2 integer not null references userTable(id)
> , date text
> , rating real
> , info text
> , complete integer
> , currentTurn integer
> , p1Submitted integer
> , p2Submitted integer
> , score real
> , gamemode text
> , turnLimit integer
> , turnTimeLimit integer
> , p1SubmitScore integer
> , p2SubmitScore integer
> , quickMatchId integer
> , nComments integer default 0
> , p1TurnSubTime real
> , p2TurnSubTime float
> , nRatings integer
> , p1GivenUp integer
> , p2GivenUp integer
> , dupId integer default -1
> , p1Declined integer default 0
> , p2Declined integer default 0
> , lastP1CommitTime text default '-1'
> , lastP2CommitTime text default '-1'
> )
> ;
>
> Change userTable to this:
>
> create table userTable
> ( id integer primary key not null
> , name text not null unique collate nocase
> , email text collate nocase
> , key text
> , status text
> , date text
> , playedFor integer
> , totalScore real default 0
> , totalRecordHigh integer default 0
> , totalRecordLow integer default 0
> , dailyScore real default 0
> , dailyRecordHigh integer default 0
> , dailyRecordLow integer default 0
> , dailyGameRecordHigh integer default 0
> , dailyGameRecordLow integer default 0
> , dailyGamesPlayed integer default 0
> , scoreStreak text default ''
> , scoreStreakNumber integer default 0
> , noEmail integer default 0
> , playedInfIds text default ''
> )
> ;
>
> -- These calculations are guesses. You will need to correct them:
>
> create trigger multiturnEndMatch
> after update of complete
> on multiturnTable
> when new.complete = 1 and old.complete != 1
> begin
>   update userTable
>   set totalScore = totalScore + new.p1SubmitScore
>   ,   dailyScore = dailyScore + new.p1SubmitScore
>   ,   dailyGamesPlayed = dailyGamesPlayed + 1
>   ,   scoreStreak = scoreStreak + new.p1SubmitScore
>   ,   scoreStreakNumber = scoreStreakNumber + 1
>   where id = old.player1
>   ;
>   update userTable
>   set totalScore = totalScore + new.p2SubmitScore
>   ,   dailyScore = dailyScore + new.p2SubmitScore
>   ,   dailyGamesPlayed = dailyGamesPlayed + 1
>   ,   scoreStreak = scoreStreak + new.p2SubmitScore
>   ,   scoreStreakNumber = scoreStreakNumber + 1
>   where id = old.player2
>   ;
> -- if player1 won:
>   update userTable
>   set totalRecordHigh = new.p1SubmitScore
>   ,   totalRecordLow = totalRecordLow + new.p2SubmitScore
>   where   new.p1SubmitScore>  new.p2SubmitScore and totalRecordHigh<  
> new.p1SubmitScore
>   ;
>   update userTable
>   set dailyRecordHigh = new.p1SubmitScore
>   ,   dailyRecordLow = new.p2SubmitScore
>   where   new.p1SubmitScore>  new.p2SubmitScore and dailyRecordHigh<  
> new.p1SubmitScore
>   ;
>   update userTable
>   dailyGameRecordHigh = new.p1SubmitScore
>   ,   dailyGameRecordLow = new.p2SubmitScore
>   

Re: [sqlite] Do I need to migrate to MySQL?

[Mr. Puneet Kishor]

On Jun 3, 2011, at 6:16 PM, Darren Duncan wrote:

> Mr. Puneet Kishor wrote:
>> On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote:
>>> MySQL should be avoided like the plague.
>> why?
>> This is a long standing (un)conventional wisdom to which I too have hewed.
>> Now, it so happens, I will be starting work on a project that uses MySQL
>> exclusively, and has done so for years. They have been very happy with it.
>> And, even though I feel like telling them that they should move to Pg, I
>> don't really know what the reasons are. I am not sure if all the reasons that
>> might be, are still valid.
>> Of course, I don't want this to become a long, religious threat that might be
>> inappropriate for this list, or bore most of the other readers to tears. But,
>> it merits asking, why should MySQL be avoided like the plague? It is a strong
>> statement that requires backing evidence, else it would be construed FUD.
> 
> Perhaps my statement was a bit strong, so I will clarify a bit.
> 

You don't say. "Should be avoided like the plague" is way more than a bit, 
hence, my response.


> *And* I'll give concrete examples.
> 
> 1.  Firstly, the context for my statement is someone who is not currently 
> using MySQL, and so they don't already have an investment in it and codebase 
> designed for it.
> 
> If one is already using MySQL, then that is the status quo and the question 
> is on whether benefits from a change to something else is worth the effort or 
> not.  But if one is not already using it, and their current DBMS can't be 
> used how they need, then they have to change anyway and the question is 
> between whether to move to MySQL or to something else instead; I am 
> addressing this latter situation, and you'll notice I also said sticking with 
> SQLite is even better if its usage can be fixed.


The same could be said of anything... "if its usage can be fixed" is a clause 
that can be interpreted widely.


> 
> 2.  I consider MySQL to be an 80% solution.
> 
> It does the job for which it is used adequately in many cases, and it is 
> successfully used in many places, including to drive many businesses and 
> organizations for mission-critical purposes.
> 


If "it is does the job for which it is used adequately" then it is a 100% 
solution.


> ..
> 
> a.  MySQL silently ignores all CHECK constraints in all engines, so for 
> example you can't even tell it you want a column to only hold values between 
> 1 and 10.
> Its in the MySQL docs:  "The CHECK clause is parsed but ignored by all 
> storage engines."


Yes and no. Apparently the above was true before 5.0.2, but apparently it has 
been fixed since then. From the docs, 

"Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and 
coerces them to legal values for data entry. In MySQL 5.0.2 and up, that 
remains the default behavior, but you can change the server SQL mode to select 
more traditional treatment of bad values such that the server rejects them and 
aborts the statement in which they occur."

and

"In MySQL 5.0.2 and up, you can select stricter treatment of input values by 
using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL modes:

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';




> 
> b.  That's just an example of how MySQL silently ignores lots of errors or 
> silently changes data on you, such as silently truncating text values that 
> are too long for a field when you insert them, so you've lost data without 
> even knowing it.  (Okay, I knew about this one previously.)


Shown by another poster to not be true.

No point in going through all the issues you pointed out. I am sure some of 
them are true, while others no longer true, or true but have work-arounds.


> ..
> 
> I say avoid MySQL like the plague because it will bite you in so many ways, 
> while an alternative like Postgres will only bite you in relatively few and 
> less painful ways.  Postgres is more of a 90-95% solution relative to MySQL's 
> 80%, assuming that there is no 100% solution.
> 
> I also know quite a number of savvy people in the developer communities who 
> have used both Postgres and MySQL, and a vast majority of those prefer 
> Postgres and strongly recommend it over MySQL when one has a choice, and so 
> do I.
> 

The main thing is to be aware of as much as possible, and then figure out 
work-arounds. No technology is perfect. There could be serious, 
project-specific show-stoppers that would cause one to choose a particular tech 
over another, but those would be project-specific.

I have no love for MySQL. All I want to emphasize is that statements like 
"avoid it like the plague" become sound-bytes with no substance. They are 
hollow and misleading as "Having problems? Get a Mac" or, on the other side, 
"Don't use Macs for serious work" or "Perl is dead" or "Apple is dead" and the 
like. These statements are fine for bloggers and tech journalists to make for 
their aim is to get page-hits and their 

Re: [sqlite] Do I need to migrate to MySQL?

[BareFeetWare]

On 03/06/2011, at 11:40 PM, Ian Hardingham wrote:

> Hey guys, thank you very much for the help so far.
> 
> The list of calls which I make during the "end match section", which can 
> take 2 seconds, is:
> 
> SELECT * FROM multiturnTable WHERE id=? LIMIT 1
> 
> UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=?
> 
> UPDATE multiturnTable SET complete=1, score=? WHERE id=?
> 
> SELECT * FROM userTable WHERE name='?'  twice
> 
> UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?, 
> dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?', 
> scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice

If properly constructed, I would expect this to be almost instant.

I suggest:

1. Use integer columns instead of text for id primary key
2. Use foreign keys (ie "references")
3. Atomize your columns, ie only one piece of data per column (eg don't store 
record as number tab number in one column).
4. For any column that is multiple choice (eg status), don't store as text, 
store as an integer that references another table.
5. In general, properly normalise your tables, which includes the above. It's 
hard to be more precise without knowing your data better.
5. Use triggers to update the related user table when a match changes (eg ends).
6. Do all of an event in one transaction.
7. Do it all (or as much as possible) of an event in SQL, rather than passing 
values back from SQL into application then back to SQL.

At the basic level, do the foloowing:

Change multiturnTable to this (I've only really changed the first three 
columns):

create table multiturnTable
(   id integer primary key not null
,   player1 integer not null references userTable(id)
,   player2 integer not null references userTable(id)
,   date text
,   rating real
,   info text
,   complete integer
,   currentTurn integer
,   p1Submitted integer
,   p2Submitted integer
,   score real
,   gamemode text
,   turnLimit integer
,   turnTimeLimit integer
,   p1SubmitScore integer
,   p2SubmitScore integer
,   quickMatchId integer
,   nComments integer default 0
,   p1TurnSubTime real
,   p2TurnSubTime float
,   nRatings integer
,   p1GivenUp integer
,   p2GivenUp integer
,   dupId integer default -1
,   p1Declined integer default 0
,   p2Declined integer default 0
,   lastP1CommitTime text default '-1'
,   lastP2CommitTime text default '-1'
)
;

Change userTable to this:

create table userTable
(   id integer primary key not null
,   name text not null unique collate nocase
,   email text collate nocase
,   key text
,   status text
,   date text
,   playedFor integer
,   totalScore real default 0
,   totalRecordHigh integer default 0
,   totalRecordLow integer default 0
,   dailyScore real default 0
,   dailyRecordHigh integer default 0
,   dailyRecordLow integer default 0
,   dailyGameRecordHigh integer default 0
,   dailyGameRecordLow integer default 0
,   dailyGamesPlayed integer default 0
,   scoreStreak text default ''
,   scoreStreakNumber integer default 0
,   noEmail integer default 0
,   playedInfIds text default ''
)
;

-- These calculations are guesses. You will need to correct them:

create trigger multiturnEndMatch
after update of complete
on multiturnTable
when new.complete = 1 and old.complete != 1
begin
update userTable
set totalScore = totalScore + new.p1SubmitScore
,   dailyScore = dailyScore + new.p1SubmitScore
,   dailyGamesPlayed = dailyGamesPlayed + 1
,   scoreStreak = scoreStreak + new.p1SubmitScore
,   scoreStreakNumber = scoreStreakNumber + 1
where id = old.player1
;
update userTable
set totalScore = totalScore + new.p2SubmitScore
,   dailyScore = dailyScore + new.p2SubmitScore
,   dailyGamesPlayed = dailyGamesPlayed + 1
,   scoreStreak = scoreStreak + new.p2SubmitScore
,   scoreStreakNumber = scoreStreakNumber + 1
where id = old.player2
;
-- if player1 won:
update userTable
set totalRecordHigh = new.p1SubmitScore
,   totalRecordLow = totalRecordLow + new.p2SubmitScore
where   new.p1SubmitScore > new.p2SubmitScore and totalRecordHigh < 
new.p1SubmitScore
;
update userTable
set dailyRecordHigh = new.p1SubmitScore
,   dailyRecordLow = new.p2SubmitScore
where   new.p1SubmitScore > new.p2SubmitScore and dailyRecordHigh < 
new.p1SubmitScore
;
update userTable
dailyGameRecordHigh = new.p1SubmitScore
,   dailyGameRecordLow = new.p2SubmitScore
where   new.p1SubmitScore > new.p2SubmitScore and dailyGameRecordHigh < 
new.p1SubmitScore
;
-- if player2 won:
update userTable
set totalRecordHigh = 

[sqlite] Auto Reply: sqlite-users Digest, Vol 42, Issue 4

[bogdan . coman]

This is an auto-replied message. I am out of office right now, with no access 
to e-mail.

I will be returning on Monday, 13 June.

If you need Berkeley DB assistance before then, please use My Oracle Support or 
the Oracle Berkeley DB Forums. For urgent issues, please contact Adam Whitter 
at adam.whit...@oracle.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Plan not optimized ?

[Luuk]

On 03-06-2011 14:29, Stéphane MANKOWSKI wrote:
> Hi,
> 
> First, think you for doing sqlite. I like it !
> 
> I am surprised by the result of "EXPLAIN QUERY PLAN" on some queries.
> 
> I will explain try to explain my issue.
> 
> 1-Open the attached database
>   This database contains a table "a":
>   CREATE TABLE a(id INT,v REAL,r_a_id INT)
>   This table "a" is pointing to an other table named "c" through the 
> attribute "r_a_id":
>   CREATE TABLE c(id INT,name)
> 
>   The view "v_c" is representing that table "c" with one more column 
> "total" computed by a SELECT on "a":
>   CREATE VIEW v_c AS SELECT *, (SELECT TOTAL(a.v) FROM a WHERE 
> a.r_a_id=c.id ) AS total FROM c
>   
> 
> 2-EXPLAIN QUERY PLAN SELECT id FROM v_c
> returns:
>   SCAN TABLE c (~74 rows)
> 
> This is correct
> 
> 3-EXPLAIN QUERY PLAN SELECT id FROM v_c
> returns:
>   SCAN TABLE c (~74 rows)
>   EXECUTE CORRELATED SCALAR SUBQUERY 1
>   SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
> 
> This is correct too because we have to compute the "total" column
> 
> 4-EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM v_c
> returns:
>   SCAN TABLE c (~74 rows)
>   EXECUTE CORRELATED SCALAR SUBQUERY 1
>   SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
>   EXECUTE CORRELATED SCALAR SUBQUERY 2
>   SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
>   EXECUTE CORRELATED SCALAR SUBQUERY 3
>   SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
> 
> For me this is not optimized (but I am not a db specialist) because the 
> computation of "total" is done 3 times instead of only one.
> 
> What do you think about that ?
> Is it a bug in the plan of execution or is it normal ?
> Do you know I can optimize my view to have only one "CORRELATED SCALAR" ?
> 
> I tried "EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM (select total 
> from v_c)" but I have the same result.
> 
> In advance, thank you for your help and your very good tool !
> 
> Regards,
> Stéphane MANKOWSKI
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

create view v_c2 as
select c.id, c.name, total(a.v) total
from c
inner join a ON a.r_a_id=c.id
group by c.id, c.name;

sqlite> EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM v_c2;
0|0|TABLE c
1|1|TABLE a WITH AUTOMATIC INDEX
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Plan not optimized ?

[Richard Hipp]

2011/6/3 Stéphane MANKOWSKI 

>
> 4-EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM v_c
> returns:
>SCAN TABLE c (~74 rows)
>EXECUTE CORRELATED SCALAR SUBQUERY 1
>SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
>EXECUTE CORRELATED SCALAR SUBQUERY 2
>SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
>EXECUTE CORRELATED SCALAR SUBQUERY 3
>SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
>
> For me this is not optimized (but I am not a db specialist) because the
> computation of "total" is done 3 times instead of only one.
>


SQLite does not currently implement common subexpression elimination.

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

Re: [sqlite] Do I need to migrate to MySQL?

[Ian Hardingham]

Thank you Igor, I'll do some more thorough profiling.

When I run the query:

UPDATE multiturnTable SET complete=1 WHERE id=-5

This takes ~45ms (as reported by SQLite's profile) - is this in the 
right ballpark?  I'm running a fairly fast modern intel chip here.

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

[sqlite] Plan not optimized ?

[Stéphane MANKOWSKI]

Hi,

First, think you for doing sqlite. I like it !

I am surprised by the result of "EXPLAIN QUERY PLAN" on some queries.

I will explain try to explain my issue.

1-Open the attached database
This database contains a table "a":
CREATE TABLE a(id INT,v REAL,r_a_id INT)
This table "a" is pointing to an other table named "c" through the 
attribute "r_a_id":
CREATE TABLE c(id INT,name)

The view "v_c" is representing that table "c" with one more column 
"total" computed by a SELECT on "a":
CREATE VIEW v_c AS SELECT *, (SELECT TOTAL(a.v) FROM a WHERE 
a.r_a_id=c.id ) AS total FROM c


2-EXPLAIN QUERY PLAN SELECT id FROM v_c
returns:
SCAN TABLE c (~74 rows)

This is correct

3-EXPLAIN QUERY PLAN SELECT id FROM v_c
returns:
SCAN TABLE c (~74 rows)
EXECUTE CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)

This is correct too because we have to compute the "total" column

4-EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM v_c
returns:
SCAN TABLE c (~74 rows)
EXECUTE CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
EXECUTE CORRELATED SCALAR SUBQUERY 2
SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
EXECUTE CORRELATED SCALAR SUBQUERY 3
SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)

For me this is not optimized (but I am not a db specialist) because the 
computation of "total" is done 3 times instead of only one.

What do you think about that ?
Is it a bug in the plan of execution or is it normal ?
Do you know I can optimize my view to have only one "CORRELATED SCALAR" ?

I tried "EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM (select total 
from v_c)" but I have the same result.

In advance, thank you for your help and your very good tool !

Regards,
Stéphane MANKOWSKI
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Luuk]

On 04-06-2011 01:16, Darren Duncan wrote:
> b.  That's just an example of how MySQL silently ignores lots of errors or 
> silently changes data on you, such as silently truncating text values that 
> are 
> too long for a field when you insert them, so you've lost data without even 
> knowing it.  (Okay, I knew about this one previously.)

It does not do so,
it gives a warning,
its up to the user to decide if something is done with that.

10:44:31 root@test[16]mysql> desc test1;
+-+-+--+-+-++
| Field   | Type| Null | Key | Default | Extra  |
+-+-+--+-+-++
| id  | int(8)  | NO   | PRI | NULL| auto_increment |
| USER_ID | int(8)  | NO   | MUL | NULL||
| TEXT| varchar(64) | NO   | | NULL||
+-+-+--+-+-++
3 rows in set (0.00 sec)

10:44:38 root@test[17]mysql> select * from test1 where user_id=;
Empty set (0.00 sec)

10:44:49 root@test[18]mysql> insert into test1 (user_id, text) values
(,'abcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyz');
Query OK, 1 row affected, 1 warning (0.00 sec)

10:45:04 root@test[19]mysql> show warnings;
+-+--+---+
| Level   | Code | Message   |
+-+--+---+
| Warning | 1265 | Data truncated for column 'TEXT' at row 1 |
+-+--+---+
1 row in set (0.00 sec)

10:45:19 root@test[20]mysql>

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