limbo/testing/values.test

#!/usr/bin/env tclsh

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

do_execsql_test values-1 {
    values(1, 2);
} {1|2};

do_execsql_test values-2 {
    values(1, 2), (3, 4);
} {1|2
3|4};

do_execsql_test values-3 {
    values(1+1, 2*3);
} {2|6};

do_execsql_test values-in-from {
  select * from (values(3, 4, 5), (5, 6, 7), (8, 9, 10));
} {3|4|5
  5|6|7
  8|9|10};

do_execsql_test values-in-join {
  select * from (values(1, 2)) join (values(3, 4), (5, 6));
} {1|2|3|4
  1|2|5|6};

do_execsql_test_skip_lines_on_specific_db 1 {:memory:} values-double-quotes-simple {
    .dbconfig dqs_dml on
    VALUES ("double_quoted_string");
} {double_quoted_string}

do_execsql_test_skip_lines_on_specific_db 1 {:memory:} values-double-quotes-multiple {
    .dbconfig dqs_dml on
    VALUES ("first", "second"), ("third", "fourth");
} {first|second
third|fourth}

do_execsql_test_skip_lines_on_specific_db 1 {:memory:} values-mixed-quotes {
    .dbconfig dqs_dml on
    VALUES ("double", 'single'), ('single', "double");
} {double|single
single|double}

do_execsql_test_skip_lines_on_specific_db 1 {:memory:} values-double-quotes-subquery {
    .dbconfig dqs_dml on
    SELECT * FROM (VALUES ("subquery_string"));
} {subquery_string}

# regression test for: https://github.com/tursodatabase/turso/issues/2158
do_execsql_test_on_specific_db {:memory:} values-between {
    CREATE TABLE t0 (c0);
    INSERT INTO t0 VALUES ((0 BETWEEN 0 AND 0)), (0);
    SELECT * FROM t0;
} {1
0}

do_execsql_test_in_memory_any_error values-illegal-column-ref {
  CREATE TABLE t0 (c0);
  INSERT INTO t0 VALUES (c0);
}

# Unquoted identifiers in VALUES should produce a parse error
do_execsql_test_in_memory_any_error values-unquoted-identifier-single {
  values(asdf);
}

do_execsql_test_in_memory_any_error values-unquoted-identifier-mixed {
  values(1, asdf);
}

# Also verify correlated VALUES references outer columns row-by-row
do_execsql_test_on_specific_db {:memory:} values-correlated-values-in-select {
  CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
  INSERT INTO users (name) VALUES ('jack'), ('jill');
  SELECT id, (VALUES(name)) AS name_again FROM users;
} {1|jack
2|jill}