Puneet,
I am not trying to give Adobe any sort of primacy; but I wouldn't call them
unimportant either.

The core concern, at least as I see it, is the undesirable effects of
sharing data between implementations that do not handle INT and INTEGER
primary keys compatibly.

I  don't use and won't use the "create table as select ... " syntax, but
others might, hence the advisory.

Here's a little story:  years ago, back in the days of dumb terminals,
oncology patients were dying in abnormally high numbers not long after
receiving their radiation treatment. Turned out that the software that
controlled the radiation dosage was written to be used with a dumb-terminal
that did not have cursor-positioning keys. It might have been a VT100. I
don't recall. You had to hit RETURN to move from field to field. But the
hospital had installed a  "compatible" terminal that did have these
cursor-arrow keys.  The hospital technician, ignorant of the consequences,
were in the habit of using the arrow keys to move the cursor around the
radiation dosage and timing screen, and then would key in their values and
execute the program. The numbers they keyed in were actually hitting the
wrong input fields.  The users' eyes told them they were on the dosage field
but they were actually keying in the value for the time, or vice-versa, and
the patients were being given lethal doses of radiation.

I saw this on 60 minutes or 20/20 or some show like that  -- I didn't write
that program or install the compatible terminal. But since then, whenever I
see the opportunity for things going FUBAR, I will say something.

Regards
Tim Romano
Swarthmore PA








On Tue, Jun 29, 2010 at 11:10 AM, P Kishor <punk.k...@gmail.com> wrote:

> On Tue, Jun 29, 2010 at 9:58 AM, Tim Romano <tim.romano...@gmail.com>
> wrote:
> > But there may be an argument for making the cloning more precise.
>
>
> The issue is that CREATE TABLE t AS SELECT... is not meant to clone a
> table. Not too long ago I encountered the same issue (search the mail
> archives). Igor, as usual, explained it succinctly and effectively --
>
> sqlite> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
> sqlite> INSERT INTO t (b) VALUES ('foo');
> sqlite> INSERT INTO t (b) VALUES ('bar');
> sqlite> INSERT INTO t (b) VALUES ('baz');
> sqlite> SELECT * FROM t;
> a           b
> ----------  ----------
> 1           foo
> 2           bar
> 3           baz
> sqlite> CREATE TABLE u AS SELECT a + 0.5 AS a, b FROM t;
> sqlite> SELECT * FROM u;
> a           b
> ----------  ----------
> 1.5         foo
> 2.5         bar
> 3.5         baz
> sqlite> .s
> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
> CREATE TABLE u(a,b TEXT);
> sqlite> SELECT Typeof(a) FROM u;
> Typeof(a)
> ----------
> real
> real
> real
> sqlite> CREATE TABLE v (a INTEGER PRIMARY KEY, b TEXT);
> sqlite> INSERT INTO v SELECT * FROM t;
> sqlite> SELECT * FROM v;
> a           b
> ----------  ----------
> 1           foo
> 2           bar
> 3           baz
> sqlite> DELETE FROM v;
> sqlite> INSERT INTO v SELECT a + 0.5, b FROM t;
> Error: datatype mismatch
> sqlite>
>
>
> In other words, don't use CREATE TABLE .. AS SELECT .. to clone.
> Instead, create the new table the way you want to, and then use INSERT
> to populate it with data from the old table.
>
>
> > It's a
> > bit of a mess, or at least it seems so to me because my first ten years
> of
> > database work was done with PICK, a database that was developed by PICK
> > Systems but licensed to many companies and marketed under different
> brands
> > with subtle functionality differences, yet applications that adhered to
> the
> > core PICK spec were completely portable across all implementations. I
> think
> > SQLite implementations should probably adhere to a core spec but I
> recognize
> > this as my bias, not dogma.
> >
> > Adobe (and possibly Google and some others who are involved in
> coordinating
> > their SQLite implementations --I'm not fully "up" on the details of who
> all
> > are involved in that cooperative effort, or the extent to which they have
> > reconciled their implementations) treats INT primary keys as aliases for
> >  the RowId, whereas SQLite does so only with INTEGER primary keys.
> >
> > Should one ever share  a SQLite database that happens to contain tables
> > defined via the "CREATE TABLE X as SELECT..."  cloning approach with
> someone
> > who is using an Adobe-based tool, the query results returned by the
> > Adobe-based tool will not jibe with the results returned by the SQlite
> > command-line utility.  On the table with INT primary key, Adobe will be
> > fetching the row by row-id.
> >
> > If a table  has foreign-key value of 10, and is joined to a table with an
> > INT primary key, Adobe will bring over the 10th row in the table even
> though
> > the value in the primary key column of that table may or may not contain
> the
> > number 10.
> >
> > In Adobe:
> >
> > CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT)
> > insert into FOO(1,'Groucho')
> > insert into FOO(2,'Chico')
> > Insert into FOO(999, 'Harpo')
> >
> > the row id of Harpo's row is 3 (it's PK value is 999) and Harpo will be
> > returned by a join when the foreign key  = 3 not when the foreign key =
> 999.
> >
>
>
> Right. But, the issue here may be that the sqlite world is much, much
> bigger than either the Adobe or the Google worlds. While those two
> companies may be a part of the sqlite consortium, sqlite itself runs
> on way more platforms and in configurations than Adobe+Google support.
>
> In any case, I am not even sure if the sql standard calls for CREATE
> TABLE t AS SELECT... to produce a clone of a table.
>
>
>
>
> >
> > Regards
> > Tim Romano
> > --
> > Most people assume one is wearing underwear.  Not so for intelligence.
> >
> >
> > On Tue, Jun 29, 2010 at 9:46 AM, Jay A. Kreibich <j...@kreibi.ch> wrote:
> >
> >> On Tue, Jun 29, 2010 at 06:59:18AM -0400, Tim Romano scratched on the
> wall:
> >> > CREATE  TABLE "main"."proto" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT
> >>  NOT
> >> > NULL , "name" TEXT)
> >> > CREATE  TABLE "main"."clone"  as select * from PROTO
> >> >
> >> > The primary key of table CLONE is defined as "INT" not "INTEGER".
> >>
> >>   The only information preserved by CREATE TABLE ... AS SELECT is the
> >>  column affinity (not "type").  These are equivalent, and both
> >>  translate to an INTEGER affinity.
> >>
> >>
> >>
> >>  ...which actually surprises me, since I was under the impression
> >>  CREATE TABLE ... AS SELECT always produced NONE affinities.  Is this
> >>  a semi-recent (last year) change?
> >>
> >>   -j
> >>
> >>
> >> --
> >> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> >>
> >> "Intelligence is like underwear: it is important that you have it,
> >>  but showing it to the wrong people has the tendency to make them
> >>  feel uncomfortable." -- Angela Johnson
> >> _______________________________________________
> >> 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
> >
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> -----------------------------------------------------------------------
> Assertions are politics; backing up assertions with evidence is science
> =======================================================================
> _______________________________________________
> 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

Reply via email to