/ Hex Artifact Content
Login

Artifact 33abf37ff8335e6bf98f2b45a0af3e06996ccd9a:


0000: 23 20 32 30 31 33 2d 30 34 2d 31 33 0a 23 0a 23  # 2013-04-13.#.#
0010: 20 54 68 65 20 61 75 74 68 6f 72 20 64 69 73 63   The author disc
0020: 6c 61 69 6d 73 20 63 6f 70 79 72 69 67 68 74 20  laims copyright 
0030: 74 6f 20 74 68 69 73 20 73 6f 75 72 63 65 20 63  to this source c
0040: 6f 64 65 2e 20 49 6e 20 70 6c 61 63 65 20 6f 66  ode. In place of
0050: 0a 23 20 61 20 6c 65 67 61 6c 20 6e 6f 74 69 63  .# a legal notic
0060: 65 2c 20 68 65 72 65 20 69 73 20 61 20 62 6c 65  e, here is a ble
0070: 73 73 69 6e 67 3a 0a 23 0a 23 20 20 20 20 4d 61  ssing:.#.#    Ma
0080: 79 20 79 6f 75 20 64 6f 20 67 6f 6f 64 20 61 6e  y you do good an
0090: 64 20 6e 6f 74 20 65 76 69 6c 2e 0a 23 20 20 20  d not evil..#   
00a0: 20 4d 61 79 20 79 6f 75 20 66 69 6e 64 20 66 6f   May you find fo
00b0: 72 67 69 76 65 6e 65 73 73 20 66 6f 72 20 79 6f  rgiveness for yo
00c0: 75 72 73 65 6c 66 20 61 6e 64 20 66 6f 72 67 69  urself and forgi
00d0: 76 65 20 6f 74 68 65 72 73 2e 0a 23 20 20 20 20  ve others..#    
00e0: 4d 61 79 20 79 6f 75 20 73 68 61 72 65 20 66 72  May you share fr
00f0: 65 65 6c 79 2c 20 6e 65 76 65 72 20 74 61 6b 69  eely, never taki
0100: 6e 67 20 6d 6f 72 65 20 74 68 61 6e 20 79 6f 75  ng more than you
0110: 20 67 69 76 65 2e 0a 23 0a 23 2a 2a 2a 2a 2a 2a   give..#.#******
0120: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0130: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0140: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0150: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0160: 2a 0a 23 0a 23 20 54 68 69 73 20 66 69 6c 65 20  *.#.# This file 
0170: 74 65 73 74 73 20 66 65 61 74 75 72 65 73 20 6f  tests features o
0180: 66 20 74 68 65 20 6e 61 6d 65 20 72 65 73 6f 6c  f the name resol
0190: 76 65 72 20 28 74 68 65 20 63 6f 6d 70 6f 6e 65  ver (the compone
01a0: 6e 74 20 74 68 61 74 0a 23 20 66 69 67 75 72 65  nt that.# figure
01b0: 73 20 6f 75 74 20 77 68 61 74 20 69 64 65 6e 74  s out what ident
01c0: 69 66 69 65 72 73 20 69 6e 20 74 68 65 20 53 51  ifiers in the SQ
01d0: 4c 20 73 74 61 74 65 6d 65 6e 74 20 72 65 66 65  L statement refe
01e0: 72 20 74 6f 29 20 74 68 61 74 0a 23 20 77 65 72  r to) that.# wer
01f0: 65 20 66 69 78 65 64 20 62 79 20 74 69 63 6b 65  e fixed by ticke
0200: 74 20 5b 32 35 30 30 63 64 62 39 62 65 5d 0a 23  t [2500cdb9be].#
0210: 0a 23 20 53 65 65 20 61 6c 73 6f 20 74 69 63 6b  .# See also tick
0220: 65 74 73 20 5b 31 63 36 39 62 65 32 64 61 66 5d  ets [1c69be2daf]
0230: 20 61 6e 64 20 5b 66 36 31 37 65 61 33 31 32 35   and [f617ea3125
0240: 5d 20 66 72 6f 6d 20 32 30 31 33 2d 30 38 2d 31  ] from 2013-08-1
0250: 34 2e 0a 23 0a 0a 73 65 74 20 74 65 73 74 64 69  4..#..set testdi
0260: 72 20 5b 66 69 6c 65 20 64 69 72 6e 61 6d 65 20  r [file dirname 
0270: 24 61 72 67 76 30 5d 0a 73 6f 75 72 63 65 20 24  $argv0].source $
0280: 74 65 73 74 64 69 72 2f 74 65 73 74 65 72 2e 74  testdir/tester.t
0290: 63 6c 0a 0a 23 20 22 4f 52 44 45 52 20 42 59 20  cl..# "ORDER BY 
02a0: 79 22 20 62 69 6e 64 73 20 74 6f 20 74 68 65 20  y" binds to the 
02b0: 6f 75 74 70 75 74 20 72 65 73 75 6c 74 2d 73 65  output result-se
02c0: 74 20 63 6f 6c 75 6d 6e 20 6e 61 6d 65 64 20 22  t column named "
02d0: 79 22 0a 23 20 69 66 20 61 76 61 69 6c 61 62 6c  y".# if availabl
02e0: 65 2e 20 20 49 66 20 6e 6f 20 6f 75 74 70 75 74  e.  If no output
02f0: 20 63 6f 6c 75 6d 6e 20 69 73 20 6e 61 6d 65 64   column is named
0300: 20 22 79 22 2c 20 74 68 65 6e 20 74 72 79 20 74   "y", then try t
0310: 6f 0a 23 20 62 69 6e 64 20 61 67 61 69 6e 73 74  o.# bind against
0320: 20 61 6e 20 69 6e 70 75 74 20 63 6f 6c 75 6d 6e   an input column
0330: 20 6e 61 6d 65 64 20 22 79 22 2e 0a 23 0a 23 20   named "y"..#.# 
0340: 54 68 69 73 20 69 73 20 63 6c 61 73 73 69 63 61  This is classica
0350: 6c 20 53 51 4c 39 32 20 62 65 68 61 76 69 6f 72  l SQL92 behavior
0360: 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 72 65 73 6f  ..#.do_test reso
0370: 6c 76 65 72 30 31 2d 31 2e 31 20 7b 0a 20 20 63  lver01-1.1 {.  c
0380: 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 43 52  atchsql {.    CR
0390: 45 41 54 45 20 54 41 42 4c 45 20 74 31 28 78 2c  EATE TABLE t1(x,
03a0: 20 79 29 3b 20 49 4e 53 45 52 54 20 49 4e 54 4f   y); INSERT INTO
03b0: 20 74 31 20 56 41 4c 55 45 53 28 31 31 2c 32 32   t1 VALUES(11,22
03c0: 29 3b 0a 20 20 20 20 43 52 45 41 54 45 20 54 41  );.    CREATE TA
03d0: 42 4c 45 20 74 32 28 79 2c 20 7a 29 3b 20 49 4e  BLE t2(y, z); IN
03e0: 53 45 52 54 20 49 4e 54 4f 20 74 32 20 56 41 4c  SERT INTO t2 VAL
03f0: 55 45 53 28 33 33 2c 34 34 29 3b 0a 20 20 20 20  UES(33,44);.    
0400: 53 45 4c 45 43 54 20 31 20 41 53 20 79 20 46 52  SELECT 1 AS y FR
0410: 4f 4d 20 74 31 2c 20 74 32 20 4f 52 44 45 52 20  OM t1, t2 ORDER 
0420: 42 59 20 79 3b 0a 20 20 7d 0a 7d 20 7b 30 20 31  BY y;.  }.} {0 1
0430: 7d 0a 64 6f 5f 74 65 73 74 20 72 65 73 6f 6c 76  }.do_test resolv
0440: 65 72 30 31 2d 31 2e 32 20 7b 0a 20 20 63 61 74  er01-1.2 {.  cat
0450: 63 68 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45  chsql {.    SELE
0460: 43 54 20 31 20 41 53 20 79 79 20 46 52 4f 4d 20  CT 1 AS yy FROM 
0470: 74 31 2c 20 74 32 20 4f 52 44 45 52 20 42 59 20  t1, t2 ORDER BY 
0480: 79 3b 0a 20 20 7d 0a 7d 20 7b 31 20 7b 61 6d 62  y;.  }.} {1 {amb
0490: 69 67 75 6f 75 73 20 63 6f 6c 75 6d 6e 20 6e 61  iguous column na
04a0: 6d 65 3a 20 79 7d 7d 0a 64 6f 5f 74 65 73 74 20  me: y}}.do_test 
04b0: 72 65 73 6f 6c 76 65 72 30 31 2d 31 2e 33 20 7b  resolver01-1.3 {
04c0: 0a 20 20 63 61 74 63 68 73 71 6c 20 7b 0a 20 20  .  catchsql {.  
04d0: 20 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 74    CREATE TABLE t
04e0: 33 28 78 2c 79 29 3b 20 49 4e 53 45 52 54 20 49  3(x,y); INSERT I
04f0: 4e 54 4f 20 74 33 20 56 41 4c 55 45 53 28 31 31  NTO t3 VALUES(11
0500: 2c 34 34 29 2c 28 33 33 2c 32 32 29 3b 0a 20 20  ,44),(33,22);.  
0510: 20 20 53 45 4c 45 43 54 20 78 20 41 53 20 79 20    SELECT x AS y 
0520: 46 52 4f 4d 20 74 33 20 4f 52 44 45 52 20 42 59  FROM t3 ORDER BY
0530: 20 79 3b 0a 20 20 7d 0a 7d 20 7b 30 20 7b 31 31   y;.  }.} {0 {11
0540: 20 33 33 7d 7d 0a 64 6f 5f 74 65 73 74 20 72 65   33}}.do_test re
0550: 73 6f 6c 76 65 72 30 31 2d 31 2e 34 20 7b 0a 20  solver01-1.4 {. 
0560: 20 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20   catchsql {.    
0570: 53 45 4c 45 43 54 20 78 20 41 53 20 79 79 20 46  SELECT x AS yy F
0580: 52 4f 4d 20 74 33 20 4f 52 44 45 52 20 42 59 20  ROM t3 ORDER BY 
0590: 79 3b 0a 20 20 7d 0a 7d 20 7b 30 20 7b 33 33 20  y;.  }.} {0 {33 
05a0: 31 31 7d 7d 0a 0a 23 20 53 51 4c 69 74 65 20 61  11}}..# SQLite a
05b0: 6c 6c 6f 77 73 20 74 68 65 20 57 48 45 52 45 20  llows the WHERE 
05c0: 63 6c 61 75 73 65 20 74 6f 20 72 65 66 65 72 65  clause to refere
05d0: 6e 63 65 20 6f 75 74 70 75 74 20 63 6f 6c 75 6d  nce output colum
05e0: 6e 73 20 69 66 20 74 68 65 72 65 20 69 73 0a 23  ns if there is.#
05f0: 20 6e 6f 20 6f 74 68 65 72 20 77 61 79 20 74 6f   no other way to
0600: 20 72 65 73 6f 6c 76 65 20 74 68 65 20 6e 61 6d   resolve the nam
0610: 65 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 72 65 73  e..#.do_test res
0620: 6f 6c 76 65 72 30 31 2d 31 2e 35 20 7b 0a 20 20  olver01-1.5 {.  
0630: 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 53  catchsql {.    S
0640: 45 4c 45 43 54 20 78 20 41 53 20 79 79 20 46 52  ELECT x AS yy FR
0650: 4f 4d 20 74 33 20 4f 52 44 45 52 20 42 59 20 79  OM t3 ORDER BY y
0660: 79 3b 0a 20 20 7d 0a 7d 20 7b 30 20 7b 31 31 20  y;.  }.} {0 {11 
0670: 33 33 7d 7d 0a 64 6f 5f 74 65 73 74 20 72 65 73  33}}.do_test res
0680: 6f 6c 76 65 72 30 31 2d 31 2e 36 20 7b 0a 20 20  olver01-1.6 {.  
0690: 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 53  catchsql {.    S
06a0: 45 4c 45 43 54 20 78 20 41 53 20 79 79 20 46 52  ELECT x AS yy FR
06b0: 4f 4d 20 74 33 20 4f 52 44 45 52 20 42 59 20 31  OM t3 ORDER BY 1
06c0: 3b 0a 20 20 7d 0a 7d 20 7b 30 20 7b 31 31 20 33  ;.  }.} {0 {11 3
06d0: 33 7d 7d 0a 0a 23 20 54 68 65 20 22 4f 52 44 45  3}}..# The "ORDE
06e0: 52 20 42 59 20 79 20 43 4f 4c 4c 41 54 45 20 6e  R BY y COLLATE n
06f0: 6f 63 61 73 65 22 20 66 6f 72 6d 20 77 6f 72 6b  ocase" form work
0700: 73 20 74 68 65 20 73 61 6d 65 20 61 73 20 22 4f  s the same as "O
0710: 52 44 45 52 20 42 59 20 79 22 2e 0a 23 20 54 68  RDER BY y"..# Th
0720: 65 20 22 79 22 20 62 69 6e 64 73 20 6d 6f 72 65  e "y" binds more
0730: 20 74 69 67 68 74 6c 79 20 74 6f 20 6f 75 74 70   tightly to outp
0740: 75 74 20 63 6f 6c 75 6d 6e 73 20 74 68 61 6e 20  ut columns than 
0750: 74 6f 20 69 6e 70 75 74 20 63 6f 6c 75 6d 6e 73  to input columns
0760: 2e 0a 23 0a 23 20 54 68 69 73 20 69 73 20 66 6f  ..#.# This is fo
0770: 72 20 63 6f 6d 70 61 74 69 62 69 6c 69 74 79 20  r compatibility 
0780: 77 69 74 68 20 53 51 4c 39 32 20 61 6e 64 20 77  with SQL92 and w
0790: 69 74 68 20 68 69 73 74 6f 72 69 63 61 6c 20 53  ith historical S
07a0: 51 4c 69 74 65 20 62 65 68 61 76 69 6f 72 2e 0a  QLite behavior..
07b0: 23 20 4e 6f 74 65 20 74 68 61 74 20 50 6f 73 74  # Note that Post
07c0: 67 72 65 53 51 4c 20 63 6f 6e 73 69 64 65 72 73  greSQL considers
07d0: 20 22 79 20 43 4f 4c 4c 41 54 45 20 6e 6f 63 61   "y COLLATE noca
07e0: 73 65 22 20 74 6f 20 62 65 20 61 6e 20 65 78 70  se" to be an exp
07f0: 72 65 73 73 69 6f 6e 0a 23 20 61 6e 64 20 74 68  ression.# and th
0800: 75 73 20 50 6f 73 74 67 72 65 53 51 4c 20 74 72  us PostgreSQL tr
0810: 65 61 74 73 20 74 68 69 73 20 63 61 73 65 20 61  eats this case a
0820: 73 20 69 66 20 69 74 20 77 68 65 72 65 20 74 68  s if it where th
0830: 65 20 33 2e 78 20 63 61 73 65 20 62 65 6c 6f 77  e 3.x case below
0840: 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 72 65 73 6f  ..#.do_test reso
0850: 6c 76 65 72 30 31 2d 32 2e 31 20 7b 0a 20 20 63  lver01-2.1 {.  c
0860: 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 53 45  atchsql {.    SE
0870: 4c 45 43 54 20 32 20 41 53 20 79 20 46 52 4f 4d  LECT 2 AS y FROM
0880: 20 74 31 2c 20 74 32 20 4f 52 44 45 52 20 42 59   t1, t2 ORDER BY
0890: 20 79 20 43 4f 4c 4c 41 54 45 20 6e 6f 63 61 73   y COLLATE nocas
08a0: 65 3b 0a 20 20 7d 0a 7d 20 7b 30 20 32 7d 0a 64  e;.  }.} {0 2}.d
08b0: 6f 5f 74 65 73 74 20 72 65 73 6f 6c 76 65 72 30  o_test resolver0
08c0: 31 2d 32 2e 32 20 7b 0a 20 20 63 61 74 63 68 73  1-2.2 {.  catchs
08d0: 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  ql {.    SELECT 
08e0: 32 20 41 53 20 79 79 20 46 52 4f 4d 20 74 31 2c  2 AS yy FROM t1,
08f0: 20 74 32 20 4f 52 44 45 52 20 42 59 20 79 20 43   t2 ORDER BY y C
0900: 4f 4c 4c 41 54 45 20 6e 6f 63 61 73 65 3b 0a 20  OLLATE nocase;. 
0910: 20 7d 0a 7d 20 7b 31 20 7b 61 6d 62 69 67 75 6f   }.} {1 {ambiguo
0920: 75 73 20 63 6f 6c 75 6d 6e 20 6e 61 6d 65 3a 20  us column name: 
0930: 79 7d 7d 0a 64 6f 5f 74 65 73 74 20 72 65 73 6f  y}}.do_test reso
0940: 6c 76 65 72 30 31 2d 32 2e 33 20 7b 0a 20 20 63  lver01-2.3 {.  c
0950: 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 53 45  atchsql {.    SE
0960: 4c 45 43 54 20 78 20 41 53 20 79 20 46 52 4f 4d  LECT x AS y FROM
0970: 20 74 33 20 4f 52 44 45 52 20 42 59 20 79 20 43   t3 ORDER BY y C
0980: 4f 4c 4c 41 54 45 20 6e 6f 63 61 73 65 3b 0a 20  OLLATE nocase;. 
0990: 20 7d 0a 7d 20 7b 30 20 7b 31 31 20 33 33 7d 7d   }.} {0 {11 33}}
09a0: 0a 64 6f 5f 74 65 73 74 20 72 65 73 6f 6c 76 65  .do_test resolve
09b0: 72 30 31 2d 32 2e 34 20 7b 0a 20 20 63 61 74 63  r01-2.4 {.  catc
09c0: 68 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43  hsql {.    SELEC
09d0: 54 20 78 20 41 53 20 79 79 20 46 52 4f 4d 20 74  T x AS yy FROM t
09e0: 33 20 4f 52 44 45 52 20 42 59 20 79 20 43 4f 4c  3 ORDER BY y COL
09f0: 4c 41 54 45 20 6e 6f 63 61 73 65 3b 0a 20 20 7d  LATE nocase;.  }
0a00: 0a 7d 20 7b 30 20 7b 33 33 20 31 31 7d 7d 0a 64  .} {0 {33 11}}.d
0a10: 6f 5f 74 65 73 74 20 72 65 73 6f 6c 76 65 72 30  o_test resolver0
0a20: 31 2d 32 2e 35 20 7b 0a 20 20 63 61 74 63 68 73  1-2.5 {.  catchs
0a30: 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  ql {.    SELECT 
0a40: 78 20 41 53 20 79 79 20 46 52 4f 4d 20 74 33 20  x AS yy FROM t3 
0a50: 4f 52 44 45 52 20 42 59 20 79 79 20 43 4f 4c 4c  ORDER BY yy COLL
0a60: 41 54 45 20 6e 6f 63 61 73 65 3b 0a 20 20 7d 0a  ATE nocase;.  }.
0a70: 7d 20 7b 30 20 7b 31 31 20 33 33 7d 7d 0a 64 6f  } {0 {11 33}}.do
0a80: 5f 74 65 73 74 20 72 65 73 6f 6c 76 65 72 30 31  _test resolver01
0a90: 2d 32 2e 36 20 7b 0a 20 20 63 61 74 63 68 73 71  -2.6 {.  catchsq
0aa0: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 78  l {.    SELECT x
0ab0: 20 41 53 20 79 79 20 46 52 4f 4d 20 74 33 20 4f   AS yy FROM t3 O
0ac0: 52 44 45 52 20 42 59 20 31 20 43 4f 4c 4c 41 54  RDER BY 1 COLLAT
0ad0: 45 20 6e 6f 63 61 73 65 3b 0a 20 20 7d 0a 7d 20  E nocase;.  }.} 
0ae0: 7b 30 20 7b 31 31 20 33 33 7d 7d 0a 0a 23 20 42  {0 {11 33}}..# B
0af0: 75 74 20 69 66 20 74 68 65 20 66 6f 72 6d 20 69  ut if the form i
0b00: 73 20 22 4f 52 44 45 52 20 42 59 20 65 78 70 72  s "ORDER BY expr
0b10: 22 20 74 68 65 6e 20 62 69 6e 64 20 6d 6f 72 65  " then bind more
0b20: 20 74 69 67 68 74 6c 79 20 74 6f 20 74 68 65 0a   tightly to the.
0b30: 23 20 74 68 65 20 69 6e 70 75 74 20 63 6f 6c 75  # the input colu
0b40: 6d 6e 20 6e 61 6d 65 73 20 61 6e 64 20 6f 6e 6c  mn names and onl
0b50: 79 20 75 73 65 20 74 68 65 20 6f 75 74 70 75 74  y use the output
0b60: 20 63 6f 6c 75 6d 6e 20 6e 61 6d 65 73 20 69 66   column names if
0b70: 20 6e 6f 0a 23 20 69 6e 70 75 74 20 63 6f 6c 75   no.# input colu
0b80: 6d 6e 20 6e 61 6d 65 20 6d 61 74 63 68 65 73 2e  mn name matches.
0b90: 0a 23 0a 23 20 54 68 69 73 20 69 73 20 53 51 4c  .#.# This is SQL
0ba0: 39 39 20 62 65 68 61 76 69 6f 72 2c 20 61 73 20  99 behavior, as 
0bb0: 69 6d 70 6c 65 6d 65 6e 74 65 64 20 62 79 20 50  implemented by P
0bc0: 6f 73 74 67 72 65 53 51 4c 20 61 6e 64 20 4d 53  ostgreSQL and MS
0bd0: 2d 53 51 4c 2e 0a 23 20 4e 6f 74 65 20 74 68 61  -SQL..# Note tha
0be0: 74 20 4f 72 61 63 6c 65 20 77 6f 72 6b 73 20 64  t Oracle works d
0bf0: 69 66 66 65 72 65 6e 74 6c 79 2e 0a 23 0a 64 6f  ifferently..#.do
0c00: 5f 74 65 73 74 20 72 65 73 6f 6c 76 65 72 30 31  _test resolver01
0c10: 2d 33 2e 31 20 7b 0a 20 20 63 61 74 63 68 73 71  -3.1 {.  catchsq
0c20: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 33  l {.    SELECT 3
0c30: 20 41 53 20 79 20 46 52 4f 4d 20 74 31 2c 20 74   AS y FROM t1, t
0c40: 32 20 4f 52 44 45 52 20 42 59 20 2b 79 3b 0a 20  2 ORDER BY +y;. 
0c50: 20 7d 0a 7d 20 7b 31 20 7b 61 6d 62 69 67 75 6f   }.} {1 {ambiguo
0c60: 75 73 20 63 6f 6c 75 6d 6e 20 6e 61 6d 65 3a 20  us column name: 
0c70: 79 7d 7d 0a 64 6f 5f 74 65 73 74 20 72 65 73 6f  y}}.do_test reso
0c80: 6c 76 65 72 30 31 2d 33 2e 32 20 7b 0a 20 20 63  lver01-3.2 {.  c
0c90: 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 53 45  atchsql {.    SE
0ca0: 4c 45 43 54 20 32 20 41 53 20 79 79 20 46 52 4f  LECT 2 AS yy FRO
0cb0: 4d 20 74 31 2c 20 74 32 20 4f 52 44 45 52 20 42  M t1, t2 ORDER B
0cc0: 59 20 2b 79 3b 0a 20 20 7d 0a 7d 20 7b 31 20 7b  Y +y;.  }.} {1 {
0cd0: 61 6d 62 69 67 75 6f 75 73 20 63 6f 6c 75 6d 6e  ambiguous column
0ce0: 20 6e 61 6d 65 3a 20 79 7d 7d 0a 64 6f 5f 74 65   name: y}}.do_te
0cf0: 73 74 20 72 65 73 6f 6c 76 65 72 30 31 2d 33 2e  st resolver01-3.
0d00: 33 20 7b 0a 20 20 63 61 74 63 68 73 71 6c 20 7b  3 {.  catchsql {
0d10: 0a 20 20 20 20 53 45 4c 45 43 54 20 78 20 41 53  .    SELECT x AS
0d20: 20 79 20 46 52 4f 4d 20 74 33 20 4f 52 44 45 52   y FROM t3 ORDER
0d30: 20 42 59 20 2b 79 3b 0a 20 20 7d 0a 7d 20 7b 30   BY +y;.  }.} {0
0d40: 20 7b 33 33 20 31 31 7d 7d 0a 64 6f 5f 74 65 73   {33 11}}.do_tes
0d50: 74 20 72 65 73 6f 6c 76 65 72 30 31 2d 33 2e 34  t resolver01-3.4
0d60: 20 7b 0a 20 20 63 61 74 63 68 73 71 6c 20 7b 0a   {.  catchsql {.
0d70: 20 20 20 20 53 45 4c 45 43 54 20 78 20 41 53 20      SELECT x AS 
0d80: 79 79 20 46 52 4f 4d 20 74 33 20 4f 52 44 45 52  yy FROM t3 ORDER
0d90: 20 42 59 20 2b 79 3b 0a 20 20 7d 0a 7d 20 7b 30   BY +y;.  }.} {0
0da0: 20 7b 33 33 20 31 31 7d 7d 0a 64 6f 5f 74 65 73   {33 11}}.do_tes
0db0: 74 20 72 65 73 6f 6c 76 65 72 30 31 2d 33 2e 35  t resolver01-3.5
0dc0: 20 7b 0a 20 20 63 61 74 63 68 73 71 6c 20 7b 0a   {.  catchsql {.
0dd0: 20 20 20 20 53 45 4c 45 43 54 20 78 20 41 53 20      SELECT x AS 
0de0: 79 79 20 46 52 4f 4d 20 74 33 20 4f 52 44 45 52  yy FROM t3 ORDER
0df0: 20 42 59 20 2b 79 79 0a 20 20 7d 0a 7d 20 7b 30   BY +yy.  }.} {0
0e00: 20 7b 31 31 20 33 33 7d 7d 0a 0a 23 20 54 68 69   {11 33}}..# Thi
0e10: 73 20 69 73 20 74 68 65 20 74 65 73 74 20 63 61  s is the test ca
0e20: 73 65 20 67 69 76 65 6e 20 69 6e 20 74 69 63 6b  se given in tick
0e30: 65 74 20 5b 66 36 31 37 65 61 33 31 32 35 65 39  et [f617ea3125e9
0e40: 5d 20 28 77 69 74 68 20 74 61 62 6c 65 20 6e 61  ] (with table na
0e50: 6d 65 0a 23 20 63 68 61 6e 67 65 64 20 66 72 6f  me.# changed fro
0e60: 6d 20 22 74 31 22 20 74 6f 20 22 74 34 22 2e 20  m "t1" to "t4". 
0e70: 20 54 68 65 20 62 65 68 61 76 69 6f 72 20 6f 66   The behavior of
0e80: 20 28 31 29 20 61 6e 64 20 28 33 29 20 6d 61 74   (1) and (3) mat
0e90: 63 68 20 77 69 74 68 0a 23 20 50 6f 73 74 67 72  ch with.# Postgr
0ea0: 65 53 51 4c 2c 20 62 75 74 20 77 65 20 69 6e 74  eSQL, but we int
0eb0: 65 6e 74 69 6f 6e 61 6c 6c 79 20 62 72 65 61 6b  entionally break
0ec0: 20 77 69 74 68 20 50 6f 73 74 67 72 65 53 51 4c   with PostgreSQL
0ed0: 20 74 6f 20 70 72 6f 76 69 64 65 0a 23 20 53 51   to provide.# SQ
0ee0: 4c 39 32 20 62 65 68 61 76 69 6f 72 20 66 6f 72  L92 behavior for
0ef0: 20 63 61 73 65 20 28 32 29 2e 0a 23 0a 64 6f 5f   case (2)..#.do_
0f00: 65 78 65 63 73 71 6c 5f 74 65 73 74 20 72 65 73  execsql_test res
0f10: 6f 6c 76 65 72 30 31 2d 34 2e 31 20 7b 0a 20 20  olver01-4.1 {.  
0f20: 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 34 28  CREATE TABLE t4(
0f30: 6d 20 43 48 41 52 28 32 29 29 3b 0a 20 20 49 4e  m CHAR(2));.  IN
0f40: 53 45 52 54 20 49 4e 54 4f 20 74 34 20 56 41 4c  SERT INTO t4 VAL
0f50: 55 45 53 28 27 61 7a 27 29 3b 0a 20 20 49 4e 53  UES('az');.  INS
0f60: 45 52 54 20 49 4e 54 4f 20 74 34 20 56 41 4c 55  ERT INTO t4 VALU
0f70: 45 53 28 27 62 79 27 29 3b 0a 20 20 49 4e 53 45  ES('by');.  INSE
0f80: 52 54 20 49 4e 54 4f 20 74 34 20 56 41 4c 55 45  RT INTO t4 VALUE
0f90: 53 28 27 63 78 27 29 3b 0a 20 20 53 45 4c 45 43  S('cx');.  SELEC
0fa0: 54 20 27 31 27 2c 20 73 75 62 73 74 72 28 6d 2c  T '1', substr(m,
0fb0: 32 29 20 41 53 20 6d 20 46 52 4f 4d 20 74 34 20  2) AS m FROM t4 
0fc0: 4f 52 44 45 52 20 42 59 20 6d 3b 0a 20 20 53 45  ORDER BY m;.  SE
0fd0: 4c 45 43 54 20 27 32 27 2c 20 73 75 62 73 74 72  LECT '2', substr
0fe0: 28 6d 2c 32 29 20 41 53 20 6d 20 46 52 4f 4d 20  (m,2) AS m FROM 
0ff0: 74 34 20 4f 52 44 45 52 20 42 59 20 6d 20 43 4f  t4 ORDER BY m CO
1000: 4c 4c 41 54 45 20 62 69 6e 61 72 79 3b 0a 20 20  LLATE binary;.  
1010: 53 45 4c 45 43 54 20 27 33 27 2c 20 73 75 62 73  SELECT '3', subs
1020: 74 72 28 6d 2c 32 29 20 41 53 20 6d 20 46 52 4f  tr(m,2) AS m FRO
1030: 4d 20 74 34 20 4f 52 44 45 52 20 42 59 20 6c 6f  M t4 ORDER BY lo
1040: 77 65 72 28 6d 29 3b 0a 7d 20 7b 31 20 78 20 31  wer(m);.} {1 x 1
1050: 20 79 20 31 20 7a 20 32 20 78 20 32 20 79 20 32   y 1 z 2 x 2 y 2
1060: 20 7a 20 33 20 7a 20 33 20 79 20 33 20 78 7d 0a   z 3 z 3 y 3 x}.
1070: 0a 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23  .###############
1080: 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23  ################
1090: 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23  ################
10a0: 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23  ################
10b0: 23 23 23 23 23 23 23 23 23 23 23 0a 23 20 54 65  ###########.# Te
10c0: 73 74 20 63 61 73 65 73 20 66 6f 72 20 74 69 63  st cases for tic
10d0: 6b 65 74 20 5b 31 63 36 39 62 65 32 64 61 66 63  ket [1c69be2dafc
10e0: 32 38 5d 3a 20 20 4d 61 6b 65 20 73 75 72 65 20  28]:  Make sure 
10f0: 74 68 65 20 47 52 4f 55 50 20 42 59 20 62 69 6e  the GROUP BY bin
1100: 64 73 0a 23 20 6d 6f 72 65 20 74 69 67 68 74 6c  ds.# more tightl
1110: 79 20 74 6f 20 74 68 65 20 69 6e 70 75 74 20 74  y to the input t
1120: 61 62 6c 65 73 20 69 6e 20 61 6c 6c 20 63 61 73  ables in all cas
1130: 65 73 2e 0a 23 0a 23 20 54 68 69 73 20 66 69 72  es..#.# This fir
1140: 73 74 20 63 61 73 65 20 63 61 73 65 20 68 61 73  st case case has
1150: 20 62 65 65 6e 20 77 72 6f 6e 67 20 69 6e 20 53   been wrong in S
1160: 51 4c 69 74 65 20 66 6f 72 20 74 69 6d 65 20 6f  QLite for time o
1170: 75 74 20 6f 66 20 6d 69 6e 64 2e 0a 23 20 46 6f  ut of mind..# Fo
1180: 72 20 53 51 4c 69 74 65 20 76 65 72 73 69 6f 6e  r SQLite version
1190: 20 33 2e 37 2e 31 37 20 74 68 65 20 61 6e 73 77   3.7.17 the answ
11a0: 65 72 20 77 61 73 20 74 77 6f 20 72 6f 77 73 2c  er was two rows,
11b0: 20 77 68 69 63 68 20 69 73 20 77 72 6f 6e 67 2e   which is wrong.
11c0: 0a 23 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65  .#.do_execsql_te
11d0: 73 74 20 72 65 73 6f 6c 76 65 72 30 31 2d 35 2e  st resolver01-5.
11e0: 31 20 7b 0a 20 20 43 52 45 41 54 45 20 54 41 42  1 {.  CREATE TAB
11f0: 4c 45 20 74 35 28 6d 20 43 48 41 52 28 32 29 29  LE t5(m CHAR(2))
1200: 3b 0a 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20  ;.  INSERT INTO 
1210: 74 35 20 56 41 4c 55 45 53 28 27 61 78 27 29 3b  t5 VALUES('ax');
1220: 0a 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74  .  INSERT INTO t
1230: 35 20 56 41 4c 55 45 53 28 27 62 78 27 29 3b 0a  5 VALUES('bx');.
1240: 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 35    INSERT INTO t5
1250: 20 56 41 4c 55 45 53 28 27 63 79 27 29 3b 0a 20   VALUES('cy');. 
1260: 20 53 45 4c 45 43 54 20 63 6f 75 6e 74 28 2a 29   SELECT count(*)
1270: 2c 20 73 75 62 73 74 72 28 6d 2c 32 2c 31 29 20  , substr(m,2,1) 
1280: 41 53 20 6d 20 46 52 4f 4d 20 74 35 20 47 52 4f  AS m FROM t5 GRO
1290: 55 50 20 42 59 20 6d 20 4f 52 44 45 52 20 42 59  UP BY m ORDER BY
12a0: 20 31 2c 20 32 3b 0a 7d 20 7b 31 20 78 20 31 20   1, 2;.} {1 x 1 
12b0: 78 20 31 20 79 7d 0a 0a 23 20 54 68 69 73 20 63  x 1 y}..# This c
12c0: 61 73 65 20 69 73 20 75 6e 61 6d 62 69 67 75 6f  ase is unambiguo
12d0: 75 73 20 61 6e 64 20 68 61 73 20 61 6c 77 61 79  us and has alway
12e0: 73 20 62 65 65 6e 20 63 6f 72 72 65 63 74 2e 0a  s been correct..
12f0: 23 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73  #.do_execsql_tes
1300: 74 20 72 65 73 6f 6c 76 65 72 30 31 2d 35 2e 32  t resolver01-5.2
1310: 20 7b 0a 20 20 53 45 4c 45 43 54 20 63 6f 75 6e   {.  SELECT coun
1320: 74 28 2a 29 2c 20 73 75 62 73 74 72 28 6d 2c 32  t(*), substr(m,2
1330: 2c 31 29 20 41 53 20 6d 78 20 46 52 4f 4d 20 74  ,1) AS mx FROM t
1340: 35 20 47 52 4f 55 50 20 42 59 20 6d 20 4f 52 44  5 GROUP BY m ORD
1350: 45 52 20 42 59 20 31 2c 20 32 3b 0a 7d 20 7b 31  ER BY 1, 2;.} {1
1360: 20 78 20 31 20 78 20 31 20 79 7d 0a 0a 23 20 54   x 1 x 1 y}..# T
1370: 68 69 73 20 63 61 73 65 20 69 73 20 6e 6f 74 20  his case is not 
1380: 61 6c 6c 6f 77 65 64 20 69 6e 20 73 74 61 6e 64  allowed in stand
1390: 61 72 64 20 53 51 4c 2c 20 62 75 74 20 53 51 4c  ard SQL, but SQL
13a0: 69 74 65 20 61 6c 6c 6f 77 73 20 61 6e 64 20 64  ite allows and d
13b0: 6f 65 73 0a 23 20 74 68 65 20 73 65 6e 73 69 62  oes.# the sensib
13c0: 6c 65 20 74 68 69 6e 67 2e 0a 23 0a 64 6f 5f 65  le thing..#.do_e
13d0: 78 65 63 73 71 6c 5f 74 65 73 74 20 72 65 73 6f  xecsql_test reso
13e0: 6c 76 65 72 30 31 2d 35 2e 33 20 7b 0a 20 20 53  lver01-5.3 {.  S
13f0: 45 4c 45 43 54 20 63 6f 75 6e 74 28 2a 29 2c 20  ELECT count(*), 
1400: 73 75 62 73 74 72 28 6d 2c 32 2c 31 29 20 41 53  substr(m,2,1) AS
1410: 20 6d 78 20 46 52 4f 4d 20 74 35 20 47 52 4f 55   mx FROM t5 GROU
1420: 50 20 42 59 20 6d 78 20 4f 52 44 45 52 20 42 59  P BY mx ORDER BY
1430: 20 31 2c 20 32 3b 0a 7d 20 7b 31 20 79 20 32 20   1, 2;.} {1 y 2 
1440: 78 7d 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65  x}.do_execsql_te
1450: 73 74 20 72 65 73 6f 6c 76 65 72 30 31 2d 35 2e  st resolver01-5.
1460: 34 20 7b 0a 20 20 53 45 4c 45 43 54 20 63 6f 75  4 {.  SELECT cou
1470: 6e 74 28 2a 29 2c 20 73 75 62 73 74 72 28 6d 2c  nt(*), substr(m,
1480: 32 2c 31 29 20 41 53 20 6d 78 20 46 52 4f 4d 20  2,1) AS mx FROM 
1490: 74 35 0a 20 20 20 47 52 4f 55 50 20 42 59 20 73  t5.   GROUP BY s
14a0: 75 62 73 74 72 28 6d 2c 32 2c 31 29 20 4f 52 44  ubstr(m,2,1) ORD
14b0: 45 52 20 42 59 20 31 2c 20 32 3b 0a 7d 20 7b 31  ER BY 1, 2;.} {1
14c0: 20 79 20 32 20 78 7d 0a 0a 23 20 54 68 65 73 65   y 2 x}..# These
14d0: 20 74 65 73 74 20 63 61 73 65 20 77 65 65 72 65   test case weere
14e0: 20 70 72 6f 76 69 64 65 64 20 69 6e 20 74 68 65   provided in the
14f0: 20 32 30 31 33 2d 30 38 2d 31 34 20 65 6d 61 69   2013-08-14 emai
1500: 6c 20 66 72 6f 6d 20 52 6f 62 20 47 6f 6c 73 74  l from Rob Golst
1510: 65 69 6a 6e 0a 23 20 74 68 61 74 20 6f 72 69 67  eijn.# that orig
1520: 69 6e 61 6c 6c 79 20 72 65 70 6f 72 74 65 64 20  inally reported 
1530: 74 68 65 20 70 72 6f 62 6c 65 6d 20 6f 66 20 74  the problem of t
1540: 69 63 6b 65 74 20 5b 31 63 36 39 62 65 32 64 61  icket [1c69be2da
1550: 66 63 32 38 5d 2e 0a 23 0a 64 6f 5f 65 78 65 63  fc28]..#.do_exec
1560: 73 71 6c 5f 74 65 73 74 20 72 65 73 6f 6c 76 65  sql_test resolve
1570: 72 30 31 2d 36 2e 31 20 7b 0a 20 20 43 52 45 41  r01-6.1 {.  CREA
1580: 54 45 20 54 41 42 4c 45 20 74 36 31 28 6e 61 6d  TE TABLE t61(nam
1590: 65 29 3b 0a 20 20 53 45 4c 45 43 54 20 6d 69 6e  e);.  SELECT min
15a0: 28 6e 61 6d 65 29 20 46 52 4f 4d 20 74 36 31 20  (name) FROM t61 
15b0: 47 52 4f 55 50 20 42 59 20 6c 6f 77 65 72 28 6e  GROUP BY lower(n
15c0: 61 6d 65 29 3b 0a 7d 20 7b 7d 0a 64 6f 5f 65 78  ame);.} {}.do_ex
15d0: 65 63 73 71 6c 5f 74 65 73 74 20 72 65 73 6f 6c  ecsql_test resol
15e0: 76 65 72 30 31 2d 36 2e 32 20 7b 0a 20 20 53 45  ver01-6.2 {.  SE
15f0: 4c 45 43 54 20 6d 69 6e 28 6e 61 6d 65 29 20 41  LECT min(name) A
1600: 53 20 6e 61 6d 65 20 46 52 4f 4d 20 74 36 31 20  S name FROM t61 
1610: 47 52 4f 55 50 20 42 59 20 6c 6f 77 65 72 28 6e  GROUP BY lower(n
1620: 61 6d 65 29 3b 20 0a 7d 20 7b 7d 0a 64 6f 5f 65  ame); .} {}.do_e
1630: 78 65 63 73 71 6c 5f 74 65 73 74 20 72 65 73 6f  xecsql_test reso
1640: 6c 76 65 72 30 31 2d 36 2e 33 20 7b 0a 20 20 43  lver01-6.3 {.  C
1650: 52 45 41 54 45 20 54 41 42 4c 45 20 74 36 33 28  REATE TABLE t63(
1660: 6e 61 6d 65 29 3b 0a 20 20 49 4e 53 45 52 54 20  name);.  INSERT 
1670: 49 4e 54 4f 20 74 36 33 20 56 41 4c 55 45 53 20  INTO t63 VALUES 
1680: 28 4e 55 4c 4c 29 3b 0a 20 20 49 4e 53 45 52 54  (NULL);.  INSERT
1690: 20 49 4e 54 4f 20 74 36 33 20 56 41 4c 55 45 53   INTO t63 VALUES
16a0: 20 28 27 61 62 63 27 29 3b 0a 20 20 53 45 4c 45   ('abc');.  SELE
16b0: 43 54 20 63 6f 75 6e 74 28 29 2c 0a 20 20 20 20  CT count(),.    
16c0: 20 20 20 4e 55 4c 4c 49 46 28 6e 61 6d 65 2c 27     NULLIF(name,'
16d0: 61 62 63 27 29 20 41 53 20 6e 61 6d 65 0a 20 20  abc') AS name.  
16e0: 20 20 46 52 4f 4d 20 74 36 33 0a 20 20 20 47 52    FROM t63.   GR
16f0: 4f 55 50 20 42 59 20 6c 6f 77 65 72 28 6e 61 6d  OUP BY lower(nam
1700: 65 29 3b 0a 7d 20 7b 31 20 7b 7d 20 31 20 7b 7d  e);.} {1 {} 1 {}
1710: 7d 0a 0a 0a 0a 0a 0a 66 69 6e 69 73 68 5f 74 65  }......finish_te
1720: 73 74 0a                                         st.