/ Check-in [2f88b9b3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Adjust the test suite to account for recent changes related to #2822. Most changes are related to English language error messages only. (CVS 4622)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2f88b9b3e3c9abc3ae4a5dcef82707dd74f8aace
User & Date: danielk1977 2007-12-13 07:58:51
Context
2007-12-13
08:15
Return an error if the user attempts to rename a view. Related to (but not a fix for) #2831. (CVS 4623) check-in: 19d56d99 user: danielk1977 tags: trunk
07:58
Adjust the test suite to account for recent changes related to #2822. Most changes are related to English language error messages only. (CVS 4622) check-in: 2f88b9b3 user: danielk1977 tags: trunk
03:45
ORDER BY in a compound SELECT will first match against the left-most SELECT. If there is no match there, it begins working its way to the right. (CVS 4621) check-in: 56063ec8 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.367 2007/12/13 03:45:08 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  */
  sNC.pEList = p->pEList;
  if( sqlite3ExprResolveNames(&sNC, p->pWhere) ||
     sqlite3ExprResolveNames(&sNC, p->pHaving) ){
    return SQLITE_ERROR;
  }
  if( p->pPrior==0 ){
    if( processOrderGroupBy(pParse, p, p->pOrderBy, 0, &sNC.hasAgg) ){
      return SQLITE_ERROR;
    }
  }
  if( processOrderGroupBy(pParse, p, pGroupBy, 0, &sNC.hasAgg) ){
    return SQLITE_ERROR;
  }








|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.368 2007/12/13 07:58:51 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  */
  sNC.pEList = p->pEList;
  if( sqlite3ExprResolveNames(&sNC, p->pWhere) ||
     sqlite3ExprResolveNames(&sNC, p->pHaving) ){
    return SQLITE_ERROR;
  }
  if( p->pPrior==0 ){
    if( processOrderGroupBy(pParse, p, p->pOrderBy, 1, &sNC.hasAgg) ){
      return SQLITE_ERROR;
    }
  }
  if( processOrderGroupBy(pParse, p, pGroupBy, 0, &sNC.hasAgg) ){
    return SQLITE_ERROR;
  }

Changes to test/insert5.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
81
82
83
84
85
86
87




88
89







90
91
92
93
94
95
96
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# The tests in this file ensure that a temporary table is used
# when required by an "INSERT INTO ... SELECT ..." statement.
#
# $Id: insert5.test,v 1.2 2007/12/10 05:03:48 danielk1977 Exp $

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

ifcapable !subquery {
  finish_test
  return
................................................................................
} {0}
do_test insert5-2.8 {
  uses_temp_table { 
    INSERT INTO b 
    SELECT * FROM main WHERE id > 10 AND max(id1, (SELECT id FROM b)) > 10;
  }
} {1}




do_test insert5-2.9 {
  uses_temp_table { 







    INSERT INTO b 
    SELECT * FROM main 
    WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
  }
} {1}

finish_test







|







 







>
>
>
>
|
|
>
>
>
>
>
>
>




|


8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# The tests in this file ensure that a temporary table is used
# when required by an "INSERT INTO ... SELECT ..." statement.
#
# $Id: insert5.test,v 1.3 2007/12/13 07:58:51 danielk1977 Exp $

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

ifcapable !subquery {
  finish_test
  return
................................................................................
} {0}
do_test insert5-2.8 {
  uses_temp_table { 
    INSERT INTO b 
    SELECT * FROM main WHERE id > 10 AND max(id1, (SELECT id FROM b)) > 10;
  }
} {1}

# UPDATE: Using a column from the outer query (main.id) in the GROUP BY
# or ORDER BY of a sub-query is no longer supported.
#
# do_test insert5-2.9 {
#   uses_temp_table { 
#     INSERT INTO b 
#     SELECT * FROM main 
#     WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
#   }
# } {}
do_test insert5-2.9 {
  catchsql { 
    INSERT INTO b 
    SELECT * FROM main 
    WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
  }
} {1 {no such column: main.id}}

finish_test

Changes to test/misc5.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc5.test,v 1.18 2007/11/12 15:29:19 danielk1977 Exp $

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

# Build records using the MakeRecord opcode such that the size of the 
# header is at the transition point in the size of a varint.
#
................................................................................
    }
  } {1 {no such table: blah}}
  do_test misc5-6.2 {
    execsql {
      CREATE TABLE logs(msg TEXT, timestamp INTEGER, dbtime TEXT);
    }
    catchsql {
      SELECT * FROM logs WHERE logs.id >= (SELECT head FROM logs_base) 
      UNION ALL 
      SELECT * FROM logs 
      LIMIT (SELECT lmt FROM logs_base) ;
    }
  } {1 {no such column: logs.id}}
}

# Overflow the lemon parser stack by providing an overly complex
# expression.  Make sure that the overflow is detected and reported.
#
do_test misc5-7.1 {
  execsql {CREATE TABLE t1(x)}







|







 







|




|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc5.test,v 1.19 2007/12/13 07:58:51 danielk1977 Exp $

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

# Build records using the MakeRecord opcode such that the size of the 
# header is at the transition point in the size of a varint.
#
................................................................................
    }
  } {1 {no such table: blah}}
  do_test misc5-6.2 {
    execsql {
      CREATE TABLE logs(msg TEXT, timestamp INTEGER, dbtime TEXT);
    }
    catchsql {
      SELECT * FROM logs WHERE logs.oid >= (SELECT head FROM logs_base) 
      UNION ALL 
      SELECT * FROM logs 
      LIMIT (SELECT lmt FROM logs_base) ;
    }
  } {1 {no such table: logs_base}}
}

# Overflow the lemon parser stack by providing an overly complex
# expression.  Make sure that the overflow is detected and reported.
#
do_test misc5-7.1 {
  execsql {CREATE TABLE t1(x)}

Changes to test/null.test.

176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
  } {{} 0 1}
  do_test null-6.4 {
    execsql {
      select b from t1 union select c from t1 order by main.t1.b;
    }
  } {{} 0 1}
  do_test null-6.5 {
    execsql {
      select b from t1 union select c from t1 order by t1.a;
    }
  } {{} 0 1}
  do_test null-6.6 {
    execsql {
      select b from t1 union select c from t1 order by main.t1.a;
    }
  } {{} 0 1}
} ;# ifcapable compound

# The UNIQUE constraint only applies to non-null values
#
ifcapable conflict {
do_test null-7.1 {
    execsql {







|


|

|


|







176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
  } {{} 0 1}
  do_test null-6.4 {
    execsql {
      select b from t1 union select c from t1 order by main.t1.b;
    }
  } {{} 0 1}
  do_test null-6.5 {
    catchsql {
      select b from t1 union select c from t1 order by t1.a;
    }
  } {1 {1st ORDER BY term does not match any column in the result set}}
  do_test null-6.6 {
    catchsql {
      select b from t1 union select c from t1 order by main.t1.a;
    }
  } {1 {1st ORDER BY term does not match any column in the result set}}
} ;# ifcapable compound

# The UNIQUE constraint only applies to non-null values
#
ifcapable conflict {
do_test null-7.1 {
    execsql {

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
...
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
...
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
#    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.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.56 2007/12/10 05:03:48 danielk1977 Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
do_test select1-4.3 {
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
  lappend v $msg
} {0 {11 33}}
do_test select1-4.4 {
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
  lappend v $msg
} {1 {misuse of aggregate function min()}}

# The restriction not allowing constants in the ORDER BY clause
# has been removed.  See ticket #1768
#do_test select1-4.5 {
#  catchsql {
#    SELECT f1 FROM test1 ORDER BY 8.4;
#  }
................................................................................
    SELECT * FROM t5 ORDER BY +2;
  }
} {2 9 1 10}
do_test select1-4.10.1 {
  catchsql {
    SELECT * FROM t5 ORDER BY 3;
  }
} {1 {ORDER BY column number 3 out of range - should be between 1 and 2}}
do_test select1-4.10.2 {
  catchsql {
    SELECT * FROM t5 ORDER BY -1;
  }
} {1 {ORDER BY column number -1 out of range - should be between 1 and 2}}
do_test select1-4.11 {
  execsql {
    INSERT INTO t5 VALUES(3,10);
    SELECT * FROM t5 ORDER BY 2, 1 DESC;
  }
} {2 9 3 10 1 10}
do_test select1-4.12 {
................................................................................
} {0 {f1 11 f1 22 f1 33 f1 44}}
do_test select1-6.11 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
    ORDER BY f2+101;
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}

# Ticket #2296
ifcapable subquery {
do_test select1-6.20 {
   execsql {
     CREATE TABLE t6(a TEXT, b TEXT);
     INSERT INTO t6 VALUES('a','0');







|







 







|







 







|




|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
...
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
...
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
#    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.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.57 2007/12/13 07:58:51 danielk1977 Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
do_test select1-4.3 {
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
  lappend v $msg
} {0 {11 33}}
do_test select1-4.4 {
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
  lappend v $msg
} {1 {misuse of aggregate: min(f1)}}

# The restriction not allowing constants in the ORDER BY clause
# has been removed.  See ticket #1768
#do_test select1-4.5 {
#  catchsql {
#    SELECT f1 FROM test1 ORDER BY 8.4;
#  }
................................................................................
    SELECT * FROM t5 ORDER BY +2;
  }
} {2 9 1 10}
do_test select1-4.10.1 {
  catchsql {
    SELECT * FROM t5 ORDER BY 3;
  }
} {1 {1st ORDER BY term out of range - should be between 1 and 2}}
do_test select1-4.10.2 {
  catchsql {
    SELECT * FROM t5 ORDER BY -1;
  }
} {1 {1st ORDER BY term out of range - should be between 1 and 2}}
do_test select1-4.11 {
  execsql {
    INSERT INTO t5 VALUES(3,10);
    SELECT * FROM t5 ORDER BY 2, 1 DESC;
  }
} {2 9 3 10 1 10}
do_test select1-4.12 {
................................................................................
} {0 {f1 11 f1 22 f1 33 f1 44}}
do_test select1-6.11 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
    ORDER BY f2+101;
  }} msg]
  lappend v $msg
} {1 {1st ORDER BY term does not match any column in the result set}}

# Ticket #2296
ifcapable subquery {
do_test select1-6.20 {
   execsql {
     CREATE TABLE t6(a TEXT, b TEXT);
     INSERT INTO t6 VALUES('a','0');

Changes to test/select3.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.21 2007/06/20 12:18:31 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
#    SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log;
#  }
#} {1 {GROUP BY terms must not be non-integer constants}}
do_test select3-2.10 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log;
  }
} {1 {GROUP BY column number 0 out of range - should be between 1 and 2}}
do_test select3-2.11 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 3 ORDER BY log;
  }
} {1 {GROUP BY column number 3 out of range - should be between 1 and 2}}
do_test select3-2.12 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log;
  }
} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}

# Cannot have an empty GROUP BY







|







 







|




|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.22 2007/12/13 07:58:51 danielk1977 Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
#    SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log;
#  }
#} {1 {GROUP BY terms must not be non-integer constants}}
do_test select3-2.10 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log;
  }
} {1 {1st GROUP BY term out of range - should be between 1 and 2}}
do_test select3-2.11 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 3 ORDER BY log;
  }
} {1 {1st GROUP BY term out of range - should be between 1 and 2}}
do_test select3-2.12 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log;
  }
} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}

# Cannot have an empty GROUP BY

Changes to test/select4.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
...
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
...
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.23 2007/12/13 03:45:08 drh Exp $

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

# Most tests in this file depend on compound-select. But there are a couple
# right at the end that test DISTINCT, so we cannot omit the entire file.
#
................................................................................
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}
do_test select4-5.2d {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}
do_test select4-5.2e {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY n;
  }} msg]
................................................................................
do_test select4-5.2h {
  catchsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY 2;
  }
} {1 {ORDER BY position 2 should be between 1 and 1}}
do_test select4-5.2i {
  catchsql {
    SELECT DISTINCT 1, log FROM t1
    UNION ALL
    SELECT 2, n FROM t1 WHERE log=3
    ORDER BY 2, 1;
  }
................................................................................
      SELECT 4 AS a, 5 AS b
    ) ORDER BY x LIMIT 1;
  }
} {x 0 y 1}
} ;# ifcapable subquery

do_test select4-9.8 {
  catchsql {
    SELECT 0 AS x, 1 AS y
    UNION
    SELECT 2 AS y, -3 AS x
    ORDER BY x LIMIT 1;
  }
} {1 {ORDER BY term number 1 is ambiguous}}

do_test select4-9.9.1 {
  execsql2 {
    SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a
  }
} {a 1 b 2 a 3 b 4}








|







 







|








|







 







|







 







|





|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
...
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
...
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.24 2007/12/13 07:58:51 danielk1977 Exp $

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

# Most tests in this file depend on compound-select. But there are a couple
# right at the end that test DISTINCT, so we cannot omit the entire file.
#
................................................................................
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {1 {1st ORDER BY term does not match any column in the result set}}
do_test select4-5.2d {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {1 {1st ORDER BY term does not match any column in the result set}}
do_test select4-5.2e {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY n;
  }} msg]
................................................................................
do_test select4-5.2h {
  catchsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY 2;
  }
} {1 {1st ORDER BY term out of range - should be between 1 and 1}}
do_test select4-5.2i {
  catchsql {
    SELECT DISTINCT 1, log FROM t1
    UNION ALL
    SELECT 2, n FROM t1 WHERE log=3
    ORDER BY 2, 1;
  }
................................................................................
      SELECT 4 AS a, 5 AS b
    ) ORDER BY x LIMIT 1;
  }
} {x 0 y 1}
} ;# ifcapable subquery

do_test select4-9.8 {
  execsql {
    SELECT 0 AS x, 1 AS y
    UNION
    SELECT 2 AS y, -3 AS x
    ORDER BY x LIMIT 1;
  }
} {0 1}

do_test select4-9.9.1 {
  execsql2 {
    SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a
  }
} {a 1 b 2 a 3 b 4}

Changes to test/tkt2822.test.

9
10
11
12
13
14
15
16
17
18
19
20





















21
22
23
24
25
26
27
28
29
30
31
32



33


34
35
36
37
38
39
40
41
42
43
44
45


46

47
48

49
50
51

52
53
54
55
56
57

58
59
60
61




62
63




64
65

66


67
68
69




70
71




72

73



74
75
76
77
78

79

80
81




82
83

84
85
86




87
88

89


90
91
92
93
94

95
96
97




98
99

100
101
102
#
#***********************************************************************
#
# This file is to test that the issues surrounding expressions in
# ORDER BY clauses on compound SELECT statements raised by ticket
# #2822 have been dealt with.
#
# $Id: tkt2822.test,v 1.2 2007/12/12 04:38:27 danielk1977 Exp $
#

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






















# Test plan:
#
#   tkt2822-1.* - Simple identifier as ORDER BY expression.
#   tkt2822-2.* - More complex ORDER BY expressions.

do_test tkt2822-1.1 {
  execsql {
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(c, b, a);

    INSERT INTO t1 VALUES(1, 2, 3);



    INSERT INTO t2 VALUES(3, 2, 1);


  }
} {}

# If an ORDER BY expression matches two different columns, it is an error.
#
do_test tkt2822-1.2 {
  catchsql {
    SELECT a, b FROM t1 UNION ALL SELECT b, a FROM t2 ORDER BY a;
  }
} {1 {ORDER BY term number 1 is ambiguous}}
do_test tkt2822-1.3 {
  catchsql {


    SELECT a, b, c FROM t2 UNION ALL SELECT c, b, a FROM t1 ORDER BY a;

  }
} {1 {ORDER BY term number 1 is ambiguous}}


# But not if it matches the same column in two or more of the
# compounded SELECT statements.

#
do_test tkt2822-1.4 {
  execsql {
    SELECT a, b, c FROM t2 UNION ALL SELECT a, b, c FROM t1 ORDER BY a;
  }
} {1 2 3 1 2 3}


do_test tkt2822-1.5 {
  execsql {
    SELECT a, b FROM t2 UNION ALL SELECT c, b FROM t1 ORDER BY c;




  }
} {1 2 3 2}





# If a match cannot be found in any SELECT, return an error.

#


do_test tkt2822-1.6 {
  catchsql {
    SELECT * FROM t2 UNION ALL SELECT * FROM t1 ORDER BY d;




  }
} {1 {ORDER BY term number 1 does not match any result column}}










do_test tkt2822-2.1 {
  execsql {
    SELECT a+1, b+1 FROM t1 UNION ALL SELECT a, c FROM t2 ORDER BY a+1;
  }
} {1 3 2 3}

do_test tkt2822-2.2 {

  catchsql {
    SELECT a+1, b+1 FROM t1 UNION ALL SELECT a, c FROM t2 ORDER BY a+2;




  }
} {1 {ORDER BY term number 1 does not match any result column}}

do_test tkt2822-2.3 {
  catchsql {
    SELECT a+1, b+1 FROM t1 UNION ALL SELECT c, a+1 FROM t2 ORDER BY a+1;




  }
} {1 {ORDER BY term number 1 is ambiguous}}




do_test tkt2822-2.4 {
  execsql {
    SELECT t1.a, b+1 FROM t1 UNION ALL SELECT c, a+1 FROM t2 ORDER BY a;
  }
} {1 3 3 2}

do_test tkt2822-2.5 {
  execsql {
    SELECT t1.a, b+1 FROM t1 UNION ALL SELECT c, a+1 FROM t2 ORDER BY t1.a;




  }
} {1 3 3 2}


finish_test








|




>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>






|


|

|
>
>
>
|
>
>



|

|
|
|

|
|
|
>
>
|
>

<
>

<
<
>

|

|

<
>

|

<
>
>
>
>

<
>
>
>
>
|
<
>
|
>
>
|
|
<
>
>
>
>

<
>
>
>
>
|
>

>
>
>
|

|

<
>
|
>
|
<
>
>
>
>

<
>
|
|
<
>
>
>
>

<
>

>
>
|
|
|

<
>
|
|
<
>
>
>
>

<
>



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

77
78


79
80
81
82
83
84

85
86
87
88

89
90
91
92
93

94
95
96
97
98

99
100
101
102
103
104

105
106
107
108
109

110
111
112
113
114
115
116
117
118
119
120
121
122
123

124
125
126
127

128
129
130
131
132

133
134
135

136
137
138
139
140

141
142
143
144
145
146
147
148

149
150
151

152
153
154
155
156

157
158
159
160
#
#***********************************************************************
#
# This file is to test that the issues surrounding expressions in
# ORDER BY clauses on compound SELECT statements raised by ticket
# #2822 have been dealt with.
#
# $Id: tkt2822.test,v 1.3 2007/12/13 07:58:51 danielk1977 Exp $
#

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

# The ORDER BY matching algorithm is three steps:
# 
#   (1)  If the ORDER BY term is an integer constant i, then
#        sort by the i-th column of the result set.
# 
#   (2)  If the ORDER BY term is an identifier (not x.y or x.y.z
#        but simply x) then look for a column alias with the same
#        name.  If found, then sort by that column.
# 
#   (3)  Evaluate the term as an expression and sort by the
#        value of the expression.
# 
# For a compound SELECT the rules are modified slightly.
# In the third rule, the expression must exactly match one
# of the result columns.  The sequences of three rules is
# attempted first on the left-most SELECT.  If that doesn't
# work, we move to the right, one by one. This is not standard
# SQL, it is an SQLite extension.
#


# Test plan:
#
#   tkt2822-1.* - Simple identifier as ORDER BY expression.
#   tkt2822-2.* - More complex ORDER BY expressions.

do_test tkt2822-0.1 {
  execsql {
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(a, b, c);

    INSERT INTO t1 VALUES(1, 3, 9);
    INSERT INTO t1 VALUES(3, 9, 27);
    INSERT INTO t1 VALUES(5, 15, 45);

    INSERT INTO t2 VALUES(2, 6, 18);
    INSERT INTO t2 VALUES(4, 12, 36);
    INSERT INTO t2 VALUES(6, 18, 54);
  }
} {}

# Test the "ORDER BY <integer>" syntax.
#
do_test tkt2822-1.1 {
  execsql {
    SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY 1;
  }
} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}
do_test tkt2822-1.2 {
  execsql {
    SELECT a, CAST (b AS TEXT), c FROM t1 
      UNION ALL 
    SELECT a, b, c FROM t2 
      ORDER BY 2;
  }

} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}



# Test the "ORDER BY <identifier>" syntax.
#
do_test tkt2822-2.1 {
  execsql {
    SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY a;
  }

} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}

do_test tkt2822-2.2 {
  execsql {

    SELECT a, CAST (b AS TEXT) AS x, c FROM t1 
      UNION ALL 
    SELECT a, b, c FROM t2 
      ORDER BY x;
  }

} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}
do_test tkt2822-2.3 {
  execsql {
    SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY a;
  }

} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}

# Test the "ORDER BY <expression>" syntax.
#
do_test tkt2822-3.1 {
  execsql {

    SELECT a, CAST (b AS TEXT) AS x, c FROM t1 
      UNION ALL 
    SELECT a, b, c FROM t2 
      ORDER BY CAST (b AS TEXT);
  }

} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}
do_test tkt2822-3.2 {
  execsql {
    SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY t1.a;
  }
} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}

# Test that if a match cannot be found in the leftmost SELECT, an
# attempt is made to find a match in subsequent SELECT statements.
#
do_test tkt2822-3.1 {
  execsql {
    SELECT a, b, c FROM t1 UNION ALL SELECT a AS x, b, c FROM t2 ORDER BY x;
  }

} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}
do_test tkt2822-3.2 {
  # But the leftmost SELECT takes precedence.
  execsql {

    SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1 
      UNION ALL 
    SELECT a, b, c FROM t2 
      ORDER BY a;
  }

} {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27}
do_test tkt2822-3.3 {
  execsql {

    SELECT a, b, c FROM t2 
      UNION ALL 
    SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1 
      ORDER BY a;
  }

} {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54}

# Test some error conditions (ORDER BY clauses that match no column).
#
do_test tkt2822-4.1 {
  catchsql {
    SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY x
  }

} {1 {1st ORDER BY term does not match any column in the result set}}
do_test tkt2822-4.2 {
  catchsql {

    SELECT a, CAST (b AS TEXT) AS x, c FROM t1 
      UNION ALL 
    SELECT a, b, c FROM t2 
      ORDER BY CAST (b AS INTEGER);
  }

} {1 {1st ORDER BY term does not match any column in the result set}}

finish_test