path: root/test/values.test

# 2024 March 3
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix values


do_execsql_test 1.0 {
  CREATE TABLE x1(a, b, c);
}


explain_i {
  INSERT INTO x1(a, b, c) VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
}
do_execsql_test 1.1.1 {
  INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
}
do_execsql_test 1.1.2 {
  SELECT * FROM x1;
} {
  1 1 1
  2 2 2
  3 3 3
  4 4 4
}

do_execsql_test 1.2.0 {
  DELETE FROM x1
}
do_execsql_test 1.2.1 {
  INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3) UNION ALL SELECT 4, 4, 4;
  SELECT * FROM x1;
} {1 1 1  2 2 2   3 3 3  4 4 4}

sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 4

do_execsql_test 1.2.2 {
  DELETE FROM x1;
  INSERT INTO x1 
  VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5) 
  UNION ALL SELECT 6, 6, 6;
  SELECT * FROM x1;
} {1 1 1  2 2 2   3 3 3  4 4 4  5 5 5  6 6 6}

do_execsql_test 1.2.3 {
  DELETE FROM x1;
  INSERT INTO x1 
  VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
  UNION ALL SELECT 6, 6, 6;
  SELECT * FROM x1;
} {1 1 1  2 2 2   3 3 3  4 4 4  6 6 6}

do_execsql_test 1.2.4 {
  DELETE FROM x1;
  INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3) UNION ALL SELECT 6, 6, 6;
  SELECT * FROM x1;
} {
 1 1 1
 2 2 2
 3 3 3
 6 6 6
}

set a 4
set b 5
set c 6
do_execsql_test 1.2.5 {
  DELETE FROM x1;
  INSERT INTO x1 
  VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), 
        (4, 4, $a), (5, 5, $b), (6, 6, $c)
}

do_execsql_test 1.2.6 {
  SELECT * FROM x1;
} {
  1 1 1
  2 2 2
  3 3 3
  4 4 4
  5 5 5
  6 6 6
}

#-------------------------------------------------------------------------
# SQLITE_LIMIT_COMPOUND_SELECT set to 0.
#
reset_db

do_execsql_test 2.0 {
  CREATE TABLE x1(a, b, c);
}

sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 3

do_catchsql_test 2.1.1 {
  INSERT INTO x1 VALUES
      (1, 1, 1), 
      (2, 2, 2), 
      (3, 3, 3), 
      (4, 4, 4), 
      (5, 5, 5), 
      (6, 6, 6), 
      (7, 7, 7), 
      (8, 8, 8), 
      (9, 9, 9), 
      (10, 10, 10, 10)
} {1 {all VALUES must have the same number of terms}}

do_catchsql_test 2.1.2 {
  INSERT INTO x1 VALUES
      (1, 1, 1), 
      (2, 2, 2, 2), 
      (3, 3, 3), 
      (4, 4, 4), 
      (5, 5, 5), 
      (6, 6, 6), 
      (7, 7, 7), 
      (8, 8, 8), 
      (9, 9, 9), 
      (10, 10, 10)
} {1 {all VALUES must have the same number of terms}}

sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 0

do_execsql_test 2.2 {
  INSERT INTO x1 VALUES
      (1, 1, 1), 
      (2, 2, 2), 
      (3, 3, 3), 
      (4, 4, 4), 
      (5, 5, 5), 
      (6, 6, 6), 
      (7, 7, 7), 
      (8, 8, 8), 
      (9, 9, 9), 
      (10, 10, 10)
} {}
do_execsql_test 2.3 {
  INSERT INTO x1 VALUES
      (1, 1, 1), 
      (2, 2, 2), 
      (3, 3, 3), 
      (4, 4, 4), 
      (5, 5, 5), 
      (6, 6, 6), 
      (7, 7, 7), 
      (8, 8, 8), 
      (9, 9, 9), 
      (10, 10, 10)
      UNION ALL 
      SELECT 5, 12, 12
      ORDER BY 1
} {}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 3.0 {
  CREATE TABLE y1(x, y);
}

do_execsql_test 3.1.1 {
  DELETE FROM y1;
  INSERT INTO y1 VALUES(1, 2), (3, 4), (row_number() OVER (), 5);
}
do_execsql_test 3.1.2 {
  SELECT * FROM y1;
} {1 2  3 4  1 5}
do_execsql_test 3.2.1 {
  DELETE FROM y1;
  INSERT INTO y1 VALUES(1, 2), (3, 4), (row_number() OVER (), 6)
    , (row_number() OVER (), 7)
}
do_execsql_test 3.1.2 {
  SELECT * FROM y1;
} {1 2  3 4  1 6  1 7}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 4.0 {
  CREATE TABLE x1(a PRIMARY KEY, b) WITHOUT ROWID;
}

foreach {tn iLimit} {1 0    2 3} { 
  sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT $iLimit

  do_execsql_test 4.1.1 {
    DELETE FROM x1;
    INSERT INTO x1 VALUES
        (1, 1),
        (2, (SELECT * FROM  (VALUES('a'), ('b'), ('c'), ('d')) ))
  }
  do_execsql_test 4.1.2 {
    SELECT * FROM x1
  } {1 1 2 a}

  do_execsql_test 4.2.1 {
    DELETE FROM x1;
    INSERT INTO x1 VALUES
        (1, 1),
        (2, 2),
        (3, 3),
        (4, 4),
        (5, (SELECT * FROM  (VALUES('a'), ('b'), ('c'), ('d')) ))
  }
  do_execsql_test 4.2.2 {
    SELECT * FROM x1
  } {1 1 2 2 3 3 4 4 5 a}

  do_execsql_test 4.3.1 {
    DELETE FROM x1;
    INSERT INTO x1 VALUES
        (1, (SELECT * FROM  (VALUES('a'), ('b'), ('c'), ('d'), ('e')) ))
  }
  do_execsql_test 4.3.2 {
    SELECT * FROM x1
  } {1 a}
}

#------------------------------------------------------------------------
reset_db

do_execsql_test 5.0 {
  CREATE VIEW v1 AS VALUES(1, 2, 3), (4, 5, 6), (7, 8, 9);
}
do_execsql_test 5.1 {
  SELECT * FROM v1
} {1 2 3 4 5 6 7 8 9}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(1), (2);
}

do_execsql_test 6.1 {
  SELECT ( VALUES( x ), ( x ) ) FROM t1;
} {1 2}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES('x'), ('y');
}

do_execsql_test 6.1 {
  SELECT * FROM t1, (VALUES(1), (2))
} {x 1 x 2 y 1 y 2}

do_execsql_test 6.2 {
  VALUES(CAST(44 AS REAL)),(55);
} {44.0 55}

#------------------------------------------------------------------------
do_execsql_test 7.1 {
  WITH x1(a, b) AS (
    VALUES(1, 2), ('a', 'b')
  )
  SELECT * FROM x1 one, x1 two
} {
  1 2  1 2
  1 2  a b
  a b  1 2
  a b  a b
}

#-------------------------------------------------------------------------
reset_db

set VVV {
  ( VALUES('a', 'b'), ('c', 'd'), (123, NULL) )
}
set VVV2 {
  ( 
  SELECT 'a' AS column1, 'b' AS column2 
  UNION ALL SELECT 'c', 'd' UNION ALL SELECT 123, NULL
  )
}

do_execsql_test 8.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES('d'), (NULL), (123)
}
foreach {tn q res} {
  1 "SELECT * FROM t1 LEFT JOIN VVV" {
    d a b   d c d   d 123 {}
    {} a b   {} c d   {} 123 {}
    123 a b   123 c d   123 123 {}
  }

  2 "SELECT * FROM t1 LEFT JOIN VVV ON (column1=x)" {
    d {} {}
    {} {} {}
    123 123 {}
  }

  3 "SELECT * FROM t1 RIGHT JOIN VVV" {
    d a b   d c d   d 123 {}
    {} a b   {} c d   {} 123 {}
    123 a b   123 c d   123 123 {}
  }

  4 "SELECT * FROM t1 RIGHT JOIN VVV ON (column1=x)" {
    123 123 {}
    {} a b
    {} c d
  }

  5 "SELECT * FROM t1 FULL OUTER JOIN VVV ON (column1=x)" {
    d {} {}
    {} {} {}
    123 123 {}
    {} a b
    {} c d
  }

  6 "SELECT count(*) FROM VVV" { 3 }

  7 "SELECT (SELECT column1 FROM VVV)" { a }

  8 "SELECT * FROM VVV UNION ALL SELECT * FROM VVV" {
    a b c d 123 {}
    a b c d 123 {}
  }

  9 "SELECT * FROM VVV INTERSECT SELECT * FROM VVV" {
    123 {} a b c d 
  }

  10 "SELECT * FROM VVV eXCEPT SELECT * FROM VVV" { }

  11 "SELECT * FROM VVV eXCEPT SELECT 'a', 'b'" { 123 {} c d }

} {
  set q1 [string map [list VVV $VVV] $q]
  set q2 [string map [list VVV $VVV2] $q]
  set q3 "WITH VVV AS $VVV $q"

  do_execsql_test 8.1.$tn.1 $q1 $res
  do_execsql_test 8.1.$tn.2 $q2 $res
  do_execsql_test 8.1.$tn.3 $q3 $res
}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 9.1 {
  VALUES(456), (123), (NULL) UNION ALL SELECT 122 ORDER BY 1
} { {} 122 123 456 }

do_execsql_test 9.2 {
  VALUES (1, 2), (3, 4), (
    ( SELECT column1 FROM ( VALUES (5, 6), (7, 8) ) ),
    ( SELECT max(column2) FROM ( VALUES (5, 1), (7, 6) ) )
  )
} { 1 2 3 4 5 6 }

do_execsql_test 10.1 {
  CREATE TABLE a2(a, b, c DEFAULT 'xyz');
}
do_execsql_test 10.2 {
  INSERT INTO a2(a) VALUES(3),(4);
}

#-------------------------------------------------------------------------
reset_db
ifcapable fts5 {
  do_execsql_test 11.0 {
    CREATE VIRTUAL TABLE ft USING fts3(x);
  }
  do_execsql_test 11.1 {
    INSERT INTO ft VALUES('one'), ('two');
  }
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 12.0 {
  CREATE TABLE t1(a, b);
}
do_execsql_test 12.1 {
  INSERT INTO t1 SELECT 1, 2 UNION ALL VALUES(3, 4), (5, 6);
}
do_execsql_test 12.2 {
  SELECT * FROM t1
} {1 2 3 4 5 6}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 13.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES('xyz');

  SELECT (
      VALUES( (max(substr('abc', 1, 1), x)) ),
      (123),
      (456)
      )
  FROM t1;
} {xyz}

do_catchsql_test 13.1 {
  VALUES(300), (zeroblob(300) OVER win);
} {1 {zeroblob() may not be used as a window function}}

#--------------------------------------------------------------------------
reset_db
do_execsql_test 14.1 {
  PRAGMA encoding = utf16;
  CREATE TABLE t1(a, b);
} {}

db close
sqlite3 db test.db

do_execsql_test 14.2 {
  INSERT INTO t1 VALUES
    (17, 'craft'),
    (16, 'urtlek' IN(1,2,3));
}

#--------------------------------------------------------------------------
#
reset_db
do_eqp_test 15.1 {
  VALUES(1),(2),(3),(4),(5);
} {
  QUERY PLAN
  `--SCAN 5-ROW VALUES CLAUSE
}
do_execsql_test 15.2 {
  CREATE TABLE t1(a,b);
}
do_eqp_test 15.3 {
  INSERT INTO t1 VALUES
    (1,2),(3,4),(7,8);
} {
  QUERY PLAN
  `--SCAN 3-ROW VALUES CLAUSE
}
do_eqp_test 15.4 {
  INSERT INTO t1 VALUES
    (1,2),(3,4),(7,8),
    (5,coalesce(6,random()));
} {
  QUERY PLAN
  `--COMPOUND QUERY
     |--LEFT-MOST SUBQUERY
     |  `--SCAN 3-ROW VALUES CLAUSE
     `--UNION ALL
        `--SCAN CONSTANT ROW
}
do_eqp_test 15.5 {
  SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6)), (VALUES('a'),('b'),('c'));
} {
  QUERY PLAN
  |--SCAN 6-ROW VALUES CLAUSE
  `--SCAN 3-ROW VALUES CLAUSE
}
do_execsql_test 15.6 {
  CREATE TABLE t2(x,y);
}
do_eqp_test 15.7 {
  SELECT * FROM t2 UNION ALL VALUES(1,2),(3,4),(5,6),(7,8);
} {
  QUERY PLAN
  `--COMPOUND QUERY
     |--LEFT-MOST SUBQUERY
     |  `--SCAN t2
     `--UNION ALL
        `--SCAN 4-ROW VALUES CLAUSE
}

finish_test