AryaWu/sqlite

1# 2013 March 052#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 regression tests for SQLite library. Specifically,12# it tests that ticket [868145d012a1] is fixed.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18do_execsql_test tkt-868145d012.100 {19  CREATE TABLE p (20    id INTEGER PRIMARY KEY,21    uid VARCHAR(36),22    t INTEGER23  );24  25  CREATE TABLE pa (26    id INTEGER PRIMARY KEY,27    a_uid VARCHAR(36)28  );29  30  CREATE TABLE a (31    id INTEGER PRIMARY KEY,32    uid VARCHAR(36),33    t INTEGER34  );35  36  INSERT INTO pa VALUES(1,'1234');37  INSERT INTO pa VALUES(2,'2345');38  INSERT INTO p VALUES(3,'1234',97);39  INSERT INTO p VALUES(4,'1234',98);40  INSERT INTO a VALUES(5,'1234',98);41  INSERT INTO a VALUES(6,'1234',99);42} {}43do_execsql_test tkt-868145d012.110 {44  SELECT DISTINCT pa.id, p.id, a.id45  FROM46    pa47    LEFT JOIN p ON p.uid='1234'48    LEFT JOIN a ON a.uid=pa.a_uid49  WHERE50    a.t=p.t51  ;52} {1 4 5}53do_execsql_test tkt-868145d012.120 {54  SELECT DISTINCT pa.id, p.id, a.id55  FROM56    pa57    LEFT JOIN p ON p.uid='1234'58    LEFT JOIN a ON a.uid=pa.a_uid AND a.t=p.t59  ORDER BY 1, 2, 360  ;61} {1 3 {} 1 4 5 2 3 {} 2 4 {}}62 63    64finish_test65