AryaWu/sqlite

1# 2017-01-162#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#12# Test cases for bugs:13#14#    https://sqlite.org/src/info/91e2e8ba6ff2e215#    https://sqlite.org/src/info/7ffd1ca1d2ad4ecf16#17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21# Ticket https://sqlite.org/src/info/91e2e8ba6ff2e2 (2011-09-19)22# Automatic index causes undesired type conversions23#24do_execsql_test affinity3-100 {25  CREATE TABLE customer (id INT PRIMARY KEY);26  CREATE TABLE apr (id INT PRIMARY KEY, apr REAL);27  28  CREATE VIEW v1 AS29  SELECT c.id, i.apr30  FROM customer c31  LEFT JOIN apr i ON i.id=c.id;32  33  CREATE VIEW v1rj AS34  SELECT c.id, i.apr35  FROM apr i36  RIGHT JOIN customer c ON i.id=c.id;37  38  CREATE VIEW v2 AS39  SELECT c.id, v1.apr40  FROM customer c41  LEFT JOIN v1 ON v1.id=c.id;42  43  CREATE VIEW v2rj AS44  SELECT c.id, v1.apr45  FROM v1 RIGHT JOIN customer c ON v1.id=c.id;46  47  CREATE VIEW v2rjrj AS48  SELECT c.id, v1rj.apr49  FROM v1rj RIGHT JOIN customer c ON v1rj.id=c.id;50  51  INSERT INTO customer (id) VALUES (1);52  INSERT INTO apr (id, apr) VALUES (1, 12);53  INSERT INTO customer (id) VALUES (2);54  INSERT INTO apr (id, apr) VALUES (2, 12.01);55}56do_execsql_test affinity3-110 {57  PRAGMA automatic_index=ON;58  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;59} {1 0.12 real 2 0.1201 real}60do_execsql_test affinity3-111 {61  PRAGMA automatic_index=ON;62  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1rj;63} {1 0.12 real 2 0.1201 real}64do_execsql_test affinity3-120 {65  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;66} {1 0.12 real 2 0.1201 real}67do_execsql_test affinity3-121 {68  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rj;69} {1 0.12 real 2 0.1201 real}70do_execsql_test affinity3-122 {71  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rjrj;72} {1 0.12 real 2 0.1201 real}73do_execsql_test affinity3-130 {74  PRAGMA automatic_index=OFF;75  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;76} {1 0.12 real 2 0.1201 real}77do_execsql_test affinity3-131 {78  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1rj;79} {1 0.12 real 2 0.1201 real}80do_execsql_test affinity3-140 {81  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;82} {1 0.12 real 2 0.1201 real}83do_execsql_test affinity3-141 {84  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rj;85} {1 0.12 real 2 0.1201 real}86do_execsql_test affinity3-142 {87  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rjrj;88} {1 0.12 real 2 0.1201 real}89 90# Ticket https://sqlite.org/src/info/7ffd1ca1d2ad4ecf  (2017-01-16)91# Incorrect affinity when using automatic indexes 92#93do_execsql_test affinity3-200 {94  CREATE TABLE map_integer (id INT, name);95  INSERT INTO map_integer VALUES(1,'a');96  CREATE TABLE map_text (id TEXT, name);97  INSERT INTO map_text VALUES('4','e');98  CREATE TABLE data (id TEXT, name);99  INSERT INTO data VALUES(1,'abc');100  INSERT INTO data VALUES('4','xyz');101  CREATE VIEW idmap as102      SELECT * FROM map_integer103      UNION SELECT * FROM map_text;104  CREATE TABLE mzed AS SELECT * FROM idmap;105}106 107do_execsql_test affinity3-210 {108  PRAGMA automatic_index=ON;109  SELECT * FROM data JOIN idmap USING(id);110} {4 xyz e}111do_execsql_test affinity3-220 {112  SELECT * FROM data JOIN mzed USING(id);113} {4 xyz e}114 115do_execsql_test affinity3-250 {116  PRAGMA automatic_index=OFF;117  SELECT * FROM data JOIN idmap USING(id);118} {4 xyz e}119do_execsql_test affinity3-260 {120  SELECT * FROM data JOIN mzed USING(id);121} {4 xyz e}122 123finish_test124