/ Check-in [1ef0dc93]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add tests for "CREATE TABLE ... AS SELECT ..." statements to e_createtable.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1ef0dc9328f47506cb2dcd142150e96cb4755216
User & Date: dan 2010-09-29 13:31:00
Original User & Date: dan 2010-09-29 23:09:23
Context
2010-09-29
18:26
Add test cases to e_createtable.test. check-in: f34dc54d user: dan tags: trunk
13:31
Add tests for "CREATE TABLE ... AS SELECT ..." statements to e_createtable.test. check-in: 1ef0dc93 user: dan tags: trunk
01:54
Updates to sqlite3_finalize() documentation. check-in: 8c5994cf user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_createtable.test.

19
20
21
22
23
24
25
26
27
28
29
30
31


























32
33
34
35
36
37
38
...
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
...
580
581
582
583
584
585
586



587
588
589












































































































































































# Test organization:
#
#   e_createtable-0.*: Test that the syntax diagrams are correct.
#
#   e_createtable-1.*: Test statements related to table and database names, 
#       the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.
#
#
#

proc do_createtable_tests {nm args} {
  uplevel do_select_tests [list e_createtable-$nm] $args
}



























# EVIDENCE-OF: R-25262-01881 -- syntax diagram type-name
#
do_createtable_tests 0.1.1 -repair {
  drop_all_tables
} {
  1   "CREATE TABLE t1(c1 one)"                        {}
................................................................................
  2    "CREATE TABLE temp.sqlitehelloworld(x)"   {}
  3    {CREATE TABLE auxa."sqlite"(x, y)}        {}
  4    {CREATE TABLE auxb."sqlite-"(z)}          {}
  5    {CREATE TABLE "SQLITE-TBL"(z)}            {}
}


proc table_list {} {
  set res [list]
  db eval { pragma database_list } a {
    set dbname $a(name)
    set master $a(name).sqlite_master
    if {$dbname == "temp"} { set master sqlite_temp_master }
    lappend res $dbname [
      db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
    ]
  }
  set res
}

# EVIDENCE-OF: R-10195-31023 If a <database-name> is specified, it
# must be either "main", "temp", or the name of an attached database.
#
# EVIDENCE-OF: R-39822-07822 In this case the new table is created in
# the named database.
#
#   Test cases 1.2.* test the first of the two requirements above. The
................................................................................
do_execsql_test  e_createtable-1.11.2.2 { DROP TABLE t2 } {}
do_execsql_test  e_createtable-1.11.2.3 { DROP TABLE t4 } {}
do_execsql_test e_createtable-1.11.2.4 {
  SELECT name FROM sqlite_master;
  SELECT name FROM auxa.sqlite_master;
} {}





finish_test




















































































































































































|





>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







<
<
<
<
<
<
<
<
<
<
<
<
<







 







>
>
>
|
<

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
...
375
376
377
378
379
380
381













382
383
384
385
386
387
388
...
593
594
595
596
597
598
599
600
601
602
603

604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
# Test organization:
#
#   e_createtable-0.*: Test that the syntax diagrams are correct.
#
#   e_createtable-1.*: Test statements related to table and database names, 
#       the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.
#
#   e_createtable-2.*: Test "CREATE TABLE AS" statements.
#

proc do_createtable_tests {nm args} {
  uplevel do_select_tests [list e_createtable-$nm] $args
}


#-------------------------------------------------------------------------
# This command returns a serialized tcl array mapping from the name of
# each attached database to a list of tables in that database. For example,
# if the database schema is created with:
#
#   CREATE TABLE t1(x);
#   CREATE TEMP TABLE t2(x);
#   CREATE TEMP TABLE t3(x);
#
# Then this command returns "main t1 temp {t2 t3}".
#
proc table_list {} {
  set res [list]
  db eval { pragma database_list } a {
    set dbname $a(name)
    set master $a(name).sqlite_master
    if {$dbname == "temp"} { set master sqlite_temp_master }
    lappend res $dbname [
      db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
    ]
  }
  set res
}


# EVIDENCE-OF: R-25262-01881 -- syntax diagram type-name
#
do_createtable_tests 0.1.1 -repair {
  drop_all_tables
} {
  1   "CREATE TABLE t1(c1 one)"                        {}
................................................................................
  2    "CREATE TABLE temp.sqlitehelloworld(x)"   {}
  3    {CREATE TABLE auxa."sqlite"(x, y)}        {}
  4    {CREATE TABLE auxb."sqlite-"(z)}          {}
  5    {CREATE TABLE "SQLITE-TBL"(z)}            {}
}















# EVIDENCE-OF: R-10195-31023 If a <database-name> is specified, it
# must be either "main", "temp", or the name of an attached database.
#
# EVIDENCE-OF: R-39822-07822 In this case the new table is created in
# the named database.
#
#   Test cases 1.2.* test the first of the two requirements above. The
................................................................................
do_execsql_test  e_createtable-1.11.2.2 { DROP TABLE t2 } {}
do_execsql_test  e_createtable-1.11.2.3 { DROP TABLE t4 } {}
do_execsql_test e_createtable-1.11.2.4 {
  SELECT name FROM sqlite_master;
  SELECT name FROM auxa.sqlite_master;
} {}

#-------------------------------------------------------------------------
# Test cases e_createtable-2.* - test statements related to the CREATE
# TABLE AS ... SELECT statement.
#


# Three Tcl commands:
#
#   select_column_names SQL
#     The argument must be a SELECT statement. Return a list of the names
#     of the columns of the result-set that would be returned by executing
#     the SELECT.
#
#   table_column_names TBL
#     The argument must be a table name. Return a list of column names, from
#     left to right, for the table.
#
#   table_column_decltypes TBL
#     The argument must be a table name. Return a list of column declared
#     types, from left to right, for the table.
#
proc sci {select cmd} {
  set res [list]
  set STMT [sqlite3_prepare_v2 db $select -1 dummy]
  for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
    lappend res [$cmd $STMT $i]
  }
  sqlite3_finalize $STMT
  set res
}
proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }
proc select_column_names    {sql} { sci $sql sqlite3_column_name }
proc table_column_names     {tbl} { tci $tbl sqlite3_column_name }
proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype }

# Create a database schema. This schema is used by tests 2.1.* through 2.3.*.
#
drop_all_tables
do_execsql_test e_createtable-2.0 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);
  CREATE TABLE t3(g BIGINT, h VARCHAR(10));
  CREATE TABLE t4(i BLOB, j ANYOLDATA);
  CREATE TABLE t5(k FLOAT, l INTEGER);
  CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n));
  CREATE TABLE t7(x INTEGER PRIMARY KEY);
  CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc');
  CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE);
} {}

# EVIDENCE-OF: R-64828-59568 The table has the same number of columns as
# the rows returned by the SELECT statement. The name of each column is
# the same as the name of the corresponding column in the result set of
# the SELECT statement.
#
do_createtable_tests 2.1 -tclquery {
  table_column_names x1
} -repair {
  catchsql { DROP TABLE x1 }
} {
  1    "CREATE TABLE x1 AS SELECT * FROM t1"                     {a b c}
  2    "CREATE TABLE x1 AS SELECT c, b, a FROM t1"               {c b a}
  3    "CREATE TABLE x1 AS SELECT * FROM t1, t2"                 {a b c d e f}
  4    "CREATE TABLE x1 AS SELECT count(*) FROM t1"              {count(*)}
  5    "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)}
}

# EVIDENCE-OF: R-37111-22855 The declared type of each column is
# determined by the expression affinity of the corresponding expression
# in the result set of the SELECT statement, as follows: Expression
# Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT"
# REAL "REAL" NONE "" (empty string)
#
do_createtable_tests 2.2 -tclquery {
  table_column_decltypes x1
} -repair {
  catchsql { DROP TABLE x1 }
} {
  1    "CREATE TABLE x1 AS SELECT a FROM t1"     {""}
  2    "CREATE TABLE x1 AS SELECT * FROM t3"     {INT TEXT}
  3    "CREATE TABLE x1 AS SELECT * FROM t4"     {"" NUM}
  4    "CREATE TABLE x1 AS SELECT * FROM t5"     {REAL INT}
}

# EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has
# no PRIMARY KEY and no constraints of any kind. The default value of
# each column is NULL. The default collation sequence for each column of
# the new table is BINARY.
#
#   The following tests create tables based on SELECT statements that read
#   from tables that have primary keys, constraints and explicit default 
#   collation sequences. None of this is transfered to the definition of
#   the new table as stored in the sqlite_master table.
#
#   Tests 2.3.2.* show that the default value of each column is NULL.
#
do_createtable_tests 2.3.1 -query {
  SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1
} {
  1    "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}}
  2    "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}}
  3    "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}}
  4    "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}}
}
do_execsql_test e_createtable-2.3.2.1 {
  INSERT INTO x1 DEFAULT VALUES;
  INSERT INTO x2 DEFAULT VALUES;
  INSERT INTO x3 DEFAULT VALUES;
  INSERT INTO x4 DEFAULT VALUES;
} {}
db nullvalue null
do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null}
do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null}
do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null}
do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null}
db nullvalue {}

drop_all_tables
do_execsql_test e_createtable-2.4.0 {
  CREATE TABLE t1(x, y);
  INSERT INTO t1 VALUES('i',   'one');
  INSERT INTO t1 VALUES('ii',  'two');
  INSERT INTO t1 VALUES('iii', 'three');
} {}

# EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are
# initially populated with the rows of data returned by the SELECT
# statement.
#
# EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending
# rowid values, starting with 1, in the order that they are returned by
# the SELECT statement.
#
#   Each test case below is specified as the name of a table to create
#   using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in
#   creating it. The table is created. 
#
#   Test cases 2.4.*.1 check that after it has been created, the data in the
#   table is the same as the data returned by the SELECT statement executed as
#   a standalone command, verifying the first testable statement above.
#
#   Test cases 2.4.*.2 check that the rowids were allocated contiguously
#   as required by the second testable statement above. That the rowids
#   from the contiguous block were allocated to rows in the order rows are
#   returned by the SELECT statement is verified by 2.4.*.1.
#
# EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement
# creates and populates a database table based on the results of a
# SELECT statement.
#
#   The above is also considered to be tested by the following. It is
#   clear that tables are being created and populated by the command in
#   question.
#
foreach {tn tbl select} {
  1   x1   "SELECT * FROM t1"
  2   x2   "SELECT * FROM t1 ORDER BY x DESC"
  3   x3   "SELECT * FROM t1 ORDER BY x ASC"
} {
  # Create the table using a "CREATE TABLE ... AS SELECT ..." command.
  execsql [subst {CREATE TABLE $tbl AS $select}]

  # Check that the rows inserted into the table, sorted in ascending rowid
  # order, match those returned by executing the SELECT statement as a
  # standalone command.
  do_execsql_test e_createtable-2.4.$tn.1 [subst {
    SELECT * FROM $tbl ORDER BY rowid;
  }] [execsql $select]

  # Check that the rowids in the new table are a contiguous block starting
  # with rowid 1. Note that this will fail if SELECT statement $select 
  # returns 0 rows (as max(rowid) will be NULL).
  do_execsql_test e_createtable-2.4.$tn.2 [subst {
    SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl
  }] {1 1}
}

finish_test