AryaWu/sqlite

1# 2015-09-102#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements tests for sqlite3_value_subtype() and12# sqlite3_result_subtype() interfaces.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18do_execsql_test subtype1-100 {19  SELECT test_getsubtype('hello');20} {0}21do_execsql_test subtype1-110 {22  SELECT test_getsubtype(test_setsubtype('hello',123));23} {123}24do_execsql_test subtype1-120 {25  SELECT typeof(test_setsubtype('hello',123));26} {text}27do_execsql_test subtype1-130 {28  SELECT test_setsubtype('hello',123);29} {hello}30 31# 2022-06-0932# https://sqlite.org/forum/forumpost/3d9caa45cbe38c7833#34# Avoid carrying subtypes through into a subquery that has been flattened35# or to which the outer WHERE clause has been pushed down.36#37reset_db38do_execsql_test subtype1-200 {39  CREATE TABLE t1(a); INSERT INTO t1 VALUES ('x');40  CREATE VIEW t2(b) AS SELECT json(TRUE);41  CREATE TABLE t3(b); INSERT INTO t3 VALUES(json(TRUE));42}43do_execsql_test subtype1-210 {44  SELECT * FROM t3, t1 WHERE NOT json_quote(b);45} {1 x}46do_execsql_test subtype1-220 {47  SELECT * FROM t2, t1 WHERE NOT json_quote(b);48} {1 x}49do_execsql_test subtype1-230 {50  WITH t4(a) AS MATERIALIZED (SELECT json(1)) SELECT subtype(a) FROM t4;51} {0}52do_execsql_test subtype1-231 {53  WITH t4(a) AS NOT MATERIALIZED (SELECT json(1)) SELECT subtype(a) FROM t4;54} {0}55 56# 2023-03-0157# https://sqlite.org/forum/forumpost/37dd14a53858# 59# Additional tests to show that subtypes do not traverse subquery boundaries.60#61do_execsql_test subtype1-300 {62  CREATE TABLE t0(c0);63  INSERT INTO t0 VALUES ('1');64  CREATE VIEW v0(c0) AS SELECT CASE WHEN 1 THEN json_patch('1', '1') END 65                        FROM t0 GROUP BY t0.c0;66  SELECT * FROM v0 WHERE json_quote(v0.c0) != '1';67} {1}68do_execsql_test subtype1-310 {69  SELECT *, json_quote(y) FROM (SELECT +json('1') AS y);70} {1 {"1"}}71do_execsql_test subtype1-320 {72  SELECT *, json_quote(y) FROM (SELECT +json('1') AS y)73   WHERE json_quote(y)='"1"';74} {1 {"1"}}75 76# 2024-11-2677# subtype survives if()78#79do_execsql_test subtype1-400 {80  WITH t400(id,j) AS (VALUES81    (1,'{a:{x:1,y:2},b:{x:3,y:4}}'),82    (2,'not json')83  )84  SELECT id, subtype(if(json_valid(j,6),j->'a')) FROM t400;85} {1 74 2 0}86 87finish_test88