1. Mike Bayer
  2. sqlalchemy

Source

sqlalchemy / lib / sqlalchemy / sql / selectable.py

   1
   2
   3
   4
   5
   6
   7
   8
   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
 161
 162
 163
 164
 165
 166
 167
 168
 169
 170
 171
 172
 173
 174
 175
 176
 177
 178
 179
 180
 181
 182
 183
 184
 185
 186
 187
 188
 189
 190
 191
 192
 193
 194
 195
 196
 197
 198
 199
 200
 201
 202
 203
 204
 205
 206
 207
 208
 209
 210
 211
 212
 213
 214
 215
 216
 217
 218
 219
 220
 221
 222
 223
 224
 225
 226
 227
 228
 229
 230
 231
 232
 233
 234
 235
 236
 237
 238
 239
 240
 241
 242
 243
 244
 245
 246
 247
 248
 249
 250
 251
 252
 253
 254
 255
 256
 257
 258
 259
 260
 261
 262
 263
 264
 265
 266
 267
 268
 269
 270
 271
 272
 273
 274
 275
 276
 277
 278
 279
 280
 281
 282
 283
 284
 285
 286
 287
 288
 289
 290
 291
 292
 293
 294
 295
 296
 297
 298
 299
 300
 301
 302
 303
 304
 305
 306
 307
 308
 309
 310
 311
 312
 313
 314
 315
 316
 317
 318
 319
 320
 321
 322
 323
 324
 325
 326
 327
 328
 329
 330
 331
 332
 333
 334
 335
 336
 337
 338
 339
 340
 341
 342
 343
 344
 345
 346
 347
 348
 349
 350
 351
 352
 353
 354
 355
 356
 357
 358
 359
 360
 361
 362
 363
 364
 365
 366
 367
 368
 369
 370
 371
 372
 373
 374
 375
 376
 377
 378
 379
 380
 381
 382
 383
 384
 385
 386
 387
 388
 389
 390
 391
 392
 393
 394
 395
 396
 397
 398
 399
 400
 401
 402
 403
 404
 405
 406
 407
 408
 409
 410
 411
 412
 413
 414
 415
 416
 417
 418
 419
 420
 421
 422
 423
 424
 425
 426
 427
 428
 429
 430
 431
 432
 433
 434
 435
 436
 437
 438
 439
 440
 441
 442
 443
 444
 445
 446
 447
 448
 449
 450
 451
 452
 453
 454
 455
 456
 457
 458
 459
 460
 461
 462
 463
 464
 465
 466
 467
 468
 469
 470
 471
 472
 473
 474
 475
 476
 477
 478
 479
 480
 481
 482
 483
 484
 485
 486
 487
 488
 489
 490
 491
 492
 493
 494
 495
 496
 497
 498
 499
 500
 501
 502
 503
 504
 505
 506
 507
 508
 509
 510
 511
 512
 513
 514
 515
 516
 517
 518
 519
 520
 521
 522
 523
 524
 525
 526
 527
 528
 529
 530
 531
 532
 533
 534
 535
 536
 537
 538
 539
 540
 541
 542
 543
 544
 545
 546
 547
 548
 549
 550
 551
 552
 553
 554
 555
 556
 557
 558
 559
 560
 561
 562
 563
 564
 565
 566
 567
 568
 569
 570
 571
 572
 573
 574
 575
 576
 577
 578
 579
 580
 581
 582
 583
 584
 585
 586
 587
 588
 589
 590
 591
 592
 593
 594
 595
 596
 597
 598
 599
 600
 601
 602
 603
 604
 605
 606
 607
 608
 609
 610
 611
 612
 613
 614
 615
 616
 617
 618
 619
 620
 621
 622
 623
 624
 625
 626
 627
 628
 629
 630
 631
 632
 633
 634
 635
 636
 637
 638
 639
 640
 641
 642
 643
 644
 645
 646
 647
 648
 649
 650
 651
 652
 653
 654
 655
 656
 657
 658
 659
 660
 661
 662
 663
 664
 665
 666
 667
 668
 669
 670
 671
 672
 673
 674
 675
 676
 677
 678
 679
 680
 681
 682
 683
 684
 685
 686
 687
 688
 689
 690
 691
 692
 693
 694
 695
 696
 697
 698
 699
 700
 701
 702
 703
 704
 705
 706
 707
 708
 709
 710
 711
 712
 713
 714
 715
 716
 717
 718
 719
 720
 721
 722
 723
 724
 725
 726
 727
 728
 729
 730
 731
 732
 733
 734
 735
 736
 737
 738
 739
 740
 741
 742
 743
 744
 745
 746
 747
 748
 749
 750
 751
 752
 753
 754
 755
 756
 757
 758
 759
 760
 761
 762
 763
 764
 765
 766
 767
 768
 769
 770
 771
 772
 773
 774
 775
 776
 777
 778
 779
 780
 781
 782
 783
 784
 785
 786
 787
 788
 789
 790
 791
 792
 793
 794
 795
 796
 797
 798
 799
 800
 801
 802
 803
 804
 805
 806
 807
 808
 809
 810
 811
 812
 813
 814
 815
 816
 817
 818
 819
 820
 821
 822
 823
 824
 825
 826
 827
 828
 829
 830
 831
 832
 833
 834
 835
 836
 837
 838
 839
 840
 841
 842
 843
 844
 845
 846
 847
 848
 849
 850
 851
 852
 853
 854
 855
 856
 857
 858
 859
 860
 861
 862
 863
 864
 865
 866
 867
 868
 869
 870
 871
 872
 873
 874
 875
 876
 877
 878
 879
 880
 881
 882
 883
 884
 885
 886
 887
 888
 889
 890
 891
 892
 893
 894
 895
 896
 897
 898
 899
 900
 901
 902
 903
 904
 905
 906
 907
 908
 909
 910
 911
 912
 913
 914
 915
 916
 917
 918
 919
 920
 921
 922
 923
 924
 925
 926
 927
 928
 929
 930
 931
 932
 933
 934
 935
 936
 937
 938
 939
 940
 941
 942
 943
 944
 945
 946
 947
 948
 949
 950
 951
 952
 953
 954
 955
 956
 957
 958
 959
 960
 961
 962
 963
 964
 965
 966
 967
 968
 969
 970
 971
 972
 973
 974
 975
 976
 977
 978
 979
 980
 981
 982
 983
 984
 985
 986
 987
 988
 989
 990
 991
 992
 993
 994
 995
 996
 997
 998
 999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
# sql/selectable.py
# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
#
# This module is part of SQLAlchemy and is released under
# the MIT License: http://www.opensource.org/licenses/mit-license.php

"""The :class:`.FromClause` class of SQL expression elements, representing
SQL tables and derived rowsets.

"""

from .elements import ClauseElement, TextClause, ClauseList, \
        and_, Grouping, UnaryExpression, literal_column
from .elements import _clone, \
        _literal_as_text, _interpret_as_column_or_from, _expand_cloned,\
        _select_iterables, _anonymous_label, _clause_element_as_expr,\
        _cloned_intersection, _cloned_difference
from .base import Immutable, Executable, _generative, \
            ColumnCollection, ColumnSet, _from_objects, Generative
from . import type_api
from .. import inspection
from .. import util
from .. import exc
from operator import attrgetter
from . import operators
import operator
from .annotation import Annotated
import itertools

def _interpret_as_from(element):
    insp = inspection.inspect(element, raiseerr=False)
    if insp is None:
        if isinstance(element, util.string_types):
            return TextClause(util.text_type(element))
    elif hasattr(insp, "selectable"):
        return insp.selectable
    raise exc.ArgumentError("FROM expression expected")

def _interpret_as_select(element):
    element = _interpret_as_from(element)
    if isinstance(element, Alias):
        element = element.original
    if not isinstance(element, Select):
        element = element.select()
    return element

def subquery(alias, *args, **kwargs):
    """Return an :class:`.Alias` object derived
    from a :class:`.Select`.

    name
      alias name

    \*args, \**kwargs

      all other arguments are delivered to the
      :func:`select` function.

    """
    return Select(*args, **kwargs).alias(alias)



def alias(selectable, name=None, flat=False):
    """Return an :class:`.Alias` object.

    An :class:`.Alias` represents any :class:`.FromClause`
    with an alternate name assigned within SQL, typically using the ``AS``
    clause when generated, e.g. ``SELECT * FROM table AS aliasname``.

    Similar functionality is available via the
    :meth:`~.FromClause.alias` method
    available on all :class:`.FromClause` subclasses.

    When an :class:`.Alias` is created from a :class:`.Table` object,
    this has the effect of the table being rendered
    as ``tablename AS aliasname`` in a SELECT statement.

    For :func:`.select` objects, the effect is that of creating a named
    subquery, i.e. ``(select ...) AS aliasname``.

    The ``name`` parameter is optional, and provides the name
    to use in the rendered SQL.  If blank, an "anonymous" name
    will be deterministically generated at compile time.
    Deterministic means the name is guaranteed to be unique against
    other constructs used in the same statement, and will also be the
    same name for each successive compilation of the same statement
    object.

    :param selectable: any :class:`.FromClause` subclass,
        such as a table, select statement, etc.

    :param name: string name to be assigned as the alias.
        If ``None``, a name will be deterministically generated
        at compile time.

    :param flat: Will be passed through to if the given selectable
     is an instance of :class:`.Join` - see :meth:`.Join.alias`
     for details.

     .. versionadded:: 0.9.0

    """
    return selectable.alias(name=name, flat=flat)


class Selectable(ClauseElement):
    """mark a class as being selectable"""
    __visit_name__ = 'selectable'

    is_selectable = True

    @property
    def selectable(self):
        return self


class FromClause(Selectable):
    """Represent an element that can be used within the ``FROM``
    clause of a ``SELECT`` statement.

    The most common forms of :class:`.FromClause` are the
    :class:`.Table` and the :func:`.select` constructs.  Key
    features common to all :class:`.FromClause` objects include:

    * a :attr:`.c` collection, which provides per-name access to a collection
      of :class:`.ColumnElement` objects.
    * a :attr:`.primary_key` attribute, which is a collection of all those
      :class:`.ColumnElement` objects that indicate the ``primary_key`` flag.
    * Methods to generate various derivations of a "from" clause, including
      :meth:`.FromClause.alias`, :meth:`.FromClause.join`,
      :meth:`.FromClause.select`.


    """
    __visit_name__ = 'fromclause'
    named_with_column = False
    _hide_froms = []
    schema = None
    _memoized_property = util.group_expirable_memoized_property(["_columns"])

    @util.dependencies("sqlalchemy.sql.functions")
    def count(self, functions, whereclause=None, **params):
        """return a SELECT COUNT generated against this
        :class:`.FromClause`."""

        if self.primary_key:
            col = list(self.primary_key)[0]
        else:
            col = list(self.columns)[0]
        return Select(
                    [functions.func.count(col).label('tbl_row_count')],
                    whereclause,
                    from_obj=[self],
                    **params)

    def select(self, whereclause=None, **params):
        """return a SELECT of this :class:`.FromClause`.

        .. seealso::

            :func:`~.sql.expression.select` - general purpose
            method which allows for arbitrary column lists.

        """

        return Select([self], whereclause, **params)

    def join(self, right, onclause=None, isouter=False):
        """return a join of this :class:`.FromClause` against another
        :class:`.FromClause`."""

        return Join(self, right, onclause, isouter)

    def outerjoin(self, right, onclause=None):
        """return an outer join of this :class:`.FromClause` against another
        :class:`.FromClause`."""

        return Join(self, right, onclause, True)

    def alias(self, name=None, flat=False):
        """return an alias of this :class:`.FromClause`.

        This is shorthand for calling::

            from sqlalchemy import alias
            a = alias(self, name=name)

        See :func:`~.expression.alias` for details.

        """

        return Alias(self, name)

    def is_derived_from(self, fromclause):
        """Return True if this FromClause is 'derived' from the given
        FromClause.

        An example would be an Alias of a Table is derived from that Table.

        """
        # this is essentially an "identity" check in the base class.
        # Other constructs override this to traverse through
        # contained elements.
        return fromclause in self._cloned_set

    def _is_lexical_equivalent(self, other):
        """Return True if this FromClause and the other represent
        the same lexical identity.

        This tests if either one is a copy of the other, or
        if they are the same via annotation identity.

        """
        return self._cloned_set.intersection(other._cloned_set)

    @util.dependencies("sqlalchemy.sql.util")
    def replace_selectable(self, sqlutil, old, alias):
        """replace all occurrences of FromClause 'old' with the given Alias
        object, returning a copy of this :class:`.FromClause`.

        """

        return sqlutil.ClauseAdapter(alias).traverse(self)

    def correspond_on_equivalents(self, column, equivalents):
        """Return corresponding_column for the given column, or if None
        search for a match in the given dictionary.

        """
        col = self.corresponding_column(column, require_embedded=True)
        if col is None and col in equivalents:
            for equiv in equivalents[col]:
                nc = self.corresponding_column(equiv, require_embedded=True)
                if nc:
                    return nc
        return col

    def corresponding_column(self, column, require_embedded=False):
        """Given a :class:`.ColumnElement`, return the exported
        :class:`.ColumnElement` object from this :class:`.Selectable`
        which corresponds to that original
        :class:`~sqlalchemy.schema.Column` via a common ancestor
        column.

        :param column: the target :class:`.ColumnElement` to be matched

        :param require_embedded: only return corresponding columns for
        the given :class:`.ColumnElement`, if the given
        :class:`.ColumnElement` is actually present within a sub-element
        of this :class:`.FromClause`.  Normally the column will match if
        it merely shares a common ancestor with one of the exported
        columns of this :class:`.FromClause`.

        """

        def embedded(expanded_proxy_set, target_set):
            for t in target_set.difference(expanded_proxy_set):
                if not set(_expand_cloned([t])
                            ).intersection(expanded_proxy_set):
                    return False
            return True

        # don't dig around if the column is locally present
        if self.c.contains_column(column):
            return column
        col, intersect = None, None
        target_set = column.proxy_set
        cols = self.c
        for c in cols:
            expanded_proxy_set = set(_expand_cloned(c.proxy_set))
            i = target_set.intersection(expanded_proxy_set)
            if i and (not require_embedded
                      or embedded(expanded_proxy_set, target_set)):
                if col is None:

                    # no corresponding column yet, pick this one.

                    col, intersect = c, i
                elif len(i) > len(intersect):

                    # 'c' has a larger field of correspondence than
                    # 'col'. i.e. selectable.c.a1_x->a1.c.x->table.c.x
                    # matches a1.c.x->table.c.x better than
                    # selectable.c.x->table.c.x does.

                    col, intersect = c, i
                elif i == intersect:

                    # they have the same field of correspondence. see
                    # which proxy_set has fewer columns in it, which
                    # indicates a closer relationship with the root
                    # column. Also take into account the "weight"
                    # attribute which CompoundSelect() uses to give
                    # higher precedence to columns based on vertical
                    # position in the compound statement, and discard
                    # columns that have no reference to the target
                    # column (also occurs with CompoundSelect)

                    col_distance = util.reduce(operator.add,
                            [sc._annotations.get('weight', 1) for sc in
                            col.proxy_set if sc.shares_lineage(column)])
                    c_distance = util.reduce(operator.add,
                            [sc._annotations.get('weight', 1) for sc in
                            c.proxy_set if sc.shares_lineage(column)])
                    if c_distance < col_distance:
                        col, intersect = c, i
        return col

    @property
    def description(self):
        """a brief description of this FromClause.

        Used primarily for error message formatting.

        """
        return getattr(self, 'name', self.__class__.__name__ + " object")

    def _reset_exported(self):
        """delete memoized collections when a FromClause is cloned."""

        self._memoized_property.expire_instance(self)

    @_memoized_property
    def columns(self):
        """A named-based collection of :class:`.ColumnElement` objects
        maintained by this :class:`.FromClause`.

        The :attr:`.columns`, or :attr:`.c` collection, is the gateway
        to the construction of SQL expressions using table-bound or
        other selectable-bound columns::

            select([mytable]).where(mytable.c.somecolumn == 5)

        """

        if '_columns' not in self.__dict__:
            self._init_collections()
            self._populate_column_collection()
        return self._columns.as_immutable()

    @_memoized_property
    def primary_key(self):
        """Return the collection of Column objects which comprise the
        primary key of this FromClause."""

        self._init_collections()
        self._populate_column_collection()
        return self.primary_key

    @_memoized_property
    def foreign_keys(self):
        """Return the collection of ForeignKey objects which this
        FromClause references."""

        self._init_collections()
        self._populate_column_collection()
        return self.foreign_keys

    c = property(attrgetter('columns'),
            doc="An alias for the :attr:`.columns` attribute.")
    _select_iterable = property(attrgetter('columns'))

    def _init_collections(self):
        assert '_columns' not in self.__dict__
        assert 'primary_key' not in self.__dict__
        assert 'foreign_keys' not in self.__dict__

        self._columns = ColumnCollection()
        self.primary_key = ColumnSet()
        self.foreign_keys = set()

    @property
    def _cols_populated(self):
        return '_columns' in self.__dict__

    def _populate_column_collection(self):
        """Called on subclasses to establish the .c collection.

        Each implementation has a different way of establishing
        this collection.

        """

    def _refresh_for_new_column(self, column):
        """Given a column added to the .c collection of an underlying
        selectable, produce the local version of that column, assuming this
        selectable ultimately should proxy this column.

        this is used to "ping" a derived selectable to add a new column
        to its .c. collection when a Column has been added to one of the
        Table objects it ultimtely derives from.

        If the given selectable hasn't populated it's .c. collection yet,
        it should at least pass on the message to the contained selectables,
        but it will return None.

        This method is currently used by Declarative to allow Table
        columns to be added to a partially constructed inheritance
        mapping that may have already produced joins.  The method
        isn't public right now, as the full span of implications
        and/or caveats aren't yet clear.

        It's also possible that this functionality could be invoked by
        default via an event, which would require that
        selectables maintain a weak referencing collection of all
        derivations.

        """
        if not self._cols_populated:
            return None
        elif column.key in self.columns and self.columns[column.key] is column:
            return column
        else:
            return None


class Join(FromClause):
    """represent a ``JOIN`` construct between two :class:`.FromClause`
    elements.

    The public constructor function for :class:`.Join` is the module-level
    :func:`join()` function, as well as the :func:`join()` method available
    off all :class:`.FromClause` subclasses.

    """
    __visit_name__ = 'join'

    def __init__(self, left, right, onclause=None, isouter=False):
        """Construct a new :class:`.Join`.

        The usual entrypoint here is the :func:`~.expression.join`
        function or the :meth:`.FromClause.join` method of any
        :class:`.FromClause` object.

        """
        self.left = _interpret_as_from(left)
        self.right = _interpret_as_from(right).self_group()

        if onclause is None:
            self.onclause = self._match_primaries(self.left, self.right)
        else:
            self.onclause = onclause

        self.isouter = isouter

    @classmethod
    def _create_outerjoin(cls, left, right, onclause=None):
        """Return an ``OUTER JOIN`` clause element.

        The returned object is an instance of :class:`.Join`.

        Similar functionality is also available via the
        :meth:`~.FromClause.outerjoin()` method on any
        :class:`.FromClause`.

        :param left: The left side of the join.

        :param right: The right side of the join.

        :param onclause:  Optional criterion for the ``ON`` clause, is
          derived from foreign key relationships established between
          left and right otherwise.

        To chain joins together, use the :meth:`.FromClause.join` or
        :meth:`.FromClause.outerjoin` methods on the resulting
        :class:`.Join` object.

        """
        return cls(left, right, onclause, isouter=True)


    @classmethod
    def _create_join(cls, left, right, onclause=None, isouter=False):
        """Return a ``JOIN`` clause element (regular inner join).

        The returned object is an instance of :class:`.Join`.

        Similar functionality is also available via the
        :meth:`~.FromClause.join()` method on any
        :class:`.FromClause`.

        :param left: The left side of the join.

        :param right: The right side of the join.

        :param onclause:  Optional criterion for the ``ON`` clause, is
          derived from foreign key relationships established between
          left and right otherwise.

        :param isouter: if True, produce an outer join; synonymous
         with :func:`.outerjoin`.

        To chain joins together, use the :meth:`.FromClause.join` or
        :meth:`.FromClause.outerjoin` methods on the resulting
        :class:`.Join` object.


        """
        return cls(left, right, onclause, isouter)


    @property
    def description(self):
        return "Join object on %s(%d) and %s(%d)" % (
            self.left.description,
            id(self.left),
            self.right.description,
            id(self.right))

    def is_derived_from(self, fromclause):
        return fromclause is self or \
                self.left.is_derived_from(fromclause) or \
                self.right.is_derived_from(fromclause)

    def self_group(self, against=None):
        return FromGrouping(self)

    @util.dependencies("sqlalchemy.sql.util")
    def _populate_column_collection(self, sqlutil):
        columns = [c for c in self.left.columns] + \
                        [c for c in self.right.columns]

        self.primary_key.extend(sqlutil.reduce_columns(
                (c for c in columns if c.primary_key), self.onclause))
        self._columns.update((col._label, col) for col in columns)
        self.foreign_keys.update(itertools.chain(
                        *[col.foreign_keys for col in columns]))

    def _refresh_for_new_column(self, column):
        col = self.left._refresh_for_new_column(column)
        if col is None:
            col = self.right._refresh_for_new_column(column)
        if col is not None:
            if self._cols_populated:
                self._columns[col._label] = col
                self.foreign_keys.add(col)
                if col.primary_key:
                    self.primary_key.add(col)
                return col
        return None

    def _copy_internals(self, clone=_clone, **kw):
        self._reset_exported()
        self.left = clone(self.left, **kw)
        self.right = clone(self.right, **kw)
        self.onclause = clone(self.onclause, **kw)

    def get_children(self, **kwargs):
        return self.left, self.right, self.onclause

    def _match_primaries(self, left, right):
        if isinstance(left, Join):
            left_right = left.right
        else:
            left_right = None
        return self._join_condition(left, right, a_subset=left_right)

    @classmethod
    def _join_condition(cls, a, b, ignore_nonexistent_tables=False,
                                a_subset=None,
                                consider_as_foreign_keys=None):
        """create a join condition between two tables or selectables.

        e.g.::

            join_condition(tablea, tableb)

        would produce an expression along the lines of::

            tablea.c.id==tableb.c.tablea_id

        The join is determined based on the foreign key relationships
        between the two selectables.   If there are multiple ways
        to join, or no way to join, an error is raised.

        :param ignore_nonexistent_tables:  Deprecated - this
        flag is no longer used.  Only resolution errors regarding
        the two given tables are propagated.

        :param a_subset: An optional expression that is a sub-component
        of ``a``.  An attempt will be made to join to just this sub-component
        first before looking at the full ``a`` construct, and if found
        will be successful even if there are other ways to join to ``a``.
        This allows the "right side" of a join to be passed thereby
        providing a "natural join".

        """
        crit = []
        constraints = set()

        for left in (a_subset, a):
            if left is None:
                continue
            for fk in sorted(
                        b.foreign_keys,
                        key=lambda fk: fk.parent._creation_order):
                if consider_as_foreign_keys is not None and \
                    fk.parent not in consider_as_foreign_keys:
                    continue
                try:
                    col = fk.get_referent(left)
                except exc.NoReferenceError as nrte:
                    if nrte.table_name == left.name:
                        raise
                    else:
                        continue

                if col is not None:
                    crit.append(col == fk.parent)
                    constraints.add(fk.constraint)
            if left is not b:
                for fk in sorted(
                            left.foreign_keys,
                            key=lambda fk: fk.parent._creation_order):
                    if consider_as_foreign_keys is not None and \
                        fk.parent not in consider_as_foreign_keys:
                        continue
                    try:
                        col = fk.get_referent(b)
                    except exc.NoReferenceError as nrte:
                        if nrte.table_name == b.name:
                            raise
                        else:
                            # this is totally covered.  can't get
                            # coverage to mark it.
                            continue

                    if col is not None:
                        crit.append(col == fk.parent)
                        constraints.add(fk.constraint)
            if crit:
                break

        if len(crit) == 0:
            if isinstance(b, FromGrouping):
                hint = " Perhaps you meant to convert the right side to a "\
                                    "subquery using alias()?"
            else:
                hint = ""
            raise exc.NoForeignKeysError(
                "Can't find any foreign key relationships "
                "between '%s' and '%s'.%s" % (a.description, b.description, hint))
        elif len(constraints) > 1:
            raise exc.AmbiguousForeignKeysError(
                "Can't determine join between '%s' and '%s'; "
                "tables have more than one foreign key "
                "constraint relationship between them. "
                "Please specify the 'onclause' of this "
                "join explicitly." % (a.description, b.description))
        elif len(crit) == 1:
            return (crit[0])
        else:
            return and_(*crit)


    def select(self, whereclause=None, **kwargs):
        """Create a :class:`.Select` from this :class:`.Join`.

        The equivalent long-hand form, given a :class:`.Join` object
        ``j``, is::

            from sqlalchemy import select
            j = select([j.left, j.right], **kw).\\
                        where(whereclause).\\
                        select_from(j)

        :param whereclause: the WHERE criterion that will be sent to
          the :func:`select()` function

        :param \**kwargs: all other kwargs are sent to the
          underlying :func:`select()` function.

        """
        collist = [self.left, self.right]

        return Select(collist, whereclause, from_obj=[self], **kwargs)

    @property
    def bind(self):
        return self.left.bind or self.right.bind

    @util.dependencies("sqlalchemy.sql.util")
    def alias(self, sqlutil, name=None, flat=False):
        """return an alias of this :class:`.Join`.

        The default behavior here is to first produce a SELECT
        construct from this :class:`.Join`, then to produce a
        :class:`.Alias` from that.  So given a join of the form::

            j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)

        The JOIN by itself would look like::

            table_a JOIN table_b ON table_a.id = table_b.a_id

        Whereas the alias of the above, ``j.alias()``, would in a
        SELECT context look like::

            (SELECT table_a.id AS table_a_id, table_b.id AS table_b_id,
                table_b.a_id AS table_b_a_id
                FROM table_a
                JOIN table_b ON table_a.id = table_b.a_id) AS anon_1

        The equivalent long-hand form, given a :class:`.Join` object
        ``j``, is::

            from sqlalchemy import select, alias
            j = alias(
                select([j.left, j.right]).\\
                    select_from(j).\\
                    with_labels(True).\\
                    correlate(False),
                name=name
            )

        The selectable produced by :meth:`.Join.alias` features the same
        columns as that of the two individual selectables presented under
        a single name - the individual columns are "auto-labeled", meaning
        the ``.c.`` collection of the resulting :class:`.Alias` represents
        the names of the individual columns using a ``<tablename>_<columname>``
        scheme::

            j.c.table_a_id
            j.c.table_b_a_id

        :meth:`.Join.alias` also features an alternate
        option for aliasing joins which produces no enclosing SELECT and
        does not normally apply labels to the column names.  The
        ``flat=True`` option will call :meth:`.FromClause.alias`
        against the left and right sides individually.
        Using this option, no new ``SELECT`` is produced;
        we instead, from a construct as below::

            j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
            j = j.alias(flat=True)

        we get a result like this::

            table_a AS table_a_1 JOIN table_b AS table_b_1 ON
            table_a_1.id = table_b_1.a_id

        The ``flat=True`` argument is also propagated to the contained
        selectables, so that a composite join such as::

            j = table_a.join(
                    table_b.join(table_c,
                            table_b.c.id == table_c.c.b_id),
                    table_b.c.a_id == table_a.c.id
                ).alias(flat=True)

        Will produce an expression like::

            table_a AS table_a_1 JOIN (
                    table_b AS table_b_1 JOIN table_c AS table_c_1
                    ON table_b_1.id = table_c_1.b_id
            ) ON table_a_1.id = table_b_1.a_id

        The standalone :func:`experssion.alias` function as well as the
        base :meth:`.FromClause.alias` method also support the ``flat=True``
        argument as a no-op, so that the argument can be passed to the
        ``alias()`` method of any selectable.

        .. versionadded:: 0.9.0 Added the ``flat=True`` option to create
          "aliases" of joins without enclosing inside of a SELECT
          subquery.

        :param name: name given to the alias.

        :param flat: if True, produce an alias of the left and right
         sides of this :class:`.Join` and return the join of those
         two selectables.   This produces join expression that does not
         include an enclosing SELECT.

         .. versionadded:: 0.9.0

        .. seealso::

            :func:`~.expression.alias`

        """
        if flat:
            assert name is None, "Can't send name argument with flat"
            left_a, right_a = self.left.alias(flat=True), \
                                self.right.alias(flat=True)
            adapter = sqlutil.ClauseAdapter(left_a).\
                        chain(sqlutil.ClauseAdapter(right_a))

            return left_a.join(right_a,
                        adapter.traverse(self.onclause), isouter=self.isouter)
        else:
            return self.select(use_labels=True, correlate=False).alias(name)

    @property
    def _hide_froms(self):
        return itertools.chain(*[_from_objects(x.left, x.right)
                               for x in self._cloned_set])

    @property
    def _from_objects(self):
        return [self] + \
                self.onclause._from_objects + \
                self.left._from_objects + \
                self.right._from_objects


class Alias(FromClause):
    """Represents an table or selectable alias (AS).

    Represents an alias, as typically applied to any table or
    sub-select within a SQL statement using the ``AS`` keyword (or
    without the keyword on certain databases such as Oracle).

    This object is constructed from the :func:`~.expression.alias` module level
    function as well as the :meth:`.FromClause.alias` method available on all
    :class:`.FromClause` subclasses.

    """

    __visit_name__ = 'alias'
    named_with_column = True

    def __init__(self, selectable, name=None):
        baseselectable = selectable
        while isinstance(baseselectable, Alias):
            baseselectable = baseselectable.element
        self.original = baseselectable
        self.supports_execution = baseselectable.supports_execution
        if self.supports_execution:
            self._execution_options = baseselectable._execution_options
        self.element = selectable
        if name is None:
            if self.original.named_with_column:
                name = getattr(self.original, 'name', None)
            name = _anonymous_label('%%(%d %s)s' % (id(self), name
                    or 'anon'))
        self.name = name

    @property
    def description(self):
        if util.py3k:
            return self.name
        else:
            return self.name.encode('ascii', 'backslashreplace')

    def as_scalar(self):
        try:
            return self.element.as_scalar()
        except AttributeError:
            raise AttributeError("Element %s does not support "
                                 "'as_scalar()'" % self.element)

    def is_derived_from(self, fromclause):
        if fromclause in self._cloned_set:
            return True
        return self.element.is_derived_from(fromclause)

    def _populate_column_collection(self):
        for col in self.element.columns:
            col._make_proxy(self)

    def _refresh_for_new_column(self, column):
        col = self.element._refresh_for_new_column(column)
        if col is not None:
            if not self._cols_populated:
                return None
            else:
                return col._make_proxy(self)
        else:
            return None

    def _copy_internals(self, clone=_clone, **kw):
        # don't apply anything to an aliased Table
        # for now.   May want to drive this from
        # the given **kw.
        if isinstance(self.element, TableClause):
            return
        self._reset_exported()
        self.element = clone(self.element, **kw)
        baseselectable = self.element
        while isinstance(baseselectable, Alias):
            baseselectable = baseselectable.element
        self.original = baseselectable

    def get_children(self, column_collections=True, **kw):
        if column_collections:
            for c in self.c:
                yield c
        yield self.element

    @property
    def _from_objects(self):
        return [self]

    @property
    def bind(self):
        return self.element.bind


class CTE(Alias):
    """Represent a Common Table Expression.

    The :class:`.CTE` object is obtained using the
    :meth:`.SelectBase.cte` method from any selectable.
    See that method for complete examples.

    .. versionadded:: 0.7.6

    """
    __visit_name__ = 'cte'

    def __init__(self, selectable,
                        name=None,
                        recursive=False,
                        _cte_alias=None,
                        _restates=frozenset()):
        self.recursive = recursive
        self._cte_alias = _cte_alias
        self._restates = _restates
        super(CTE, self).__init__(selectable, name=name)

    def alias(self, name=None, flat=False):
        return CTE(
            self.original,
            name=name,
            recursive=self.recursive,
            _cte_alias=self,
          )

    def union(self, other):
        return CTE(
            self.original.union(other),
            name=self.name,
            recursive=self.recursive,
            _restates=self._restates.union([self])
        )

    def union_all(self, other):
        return CTE(
            self.original.union_all(other),
            name=self.name,
            recursive=self.recursive,
            _restates=self._restates.union([self])
        )




class FromGrouping(FromClause):
    """Represent a grouping of a FROM clause"""
    __visit_name__ = 'grouping'

    def __init__(self, element):
        self.element = element

    def _init_collections(self):
        pass

    @property
    def columns(self):
        return self.element.columns

    @property
    def primary_key(self):
        return self.element.primary_key

    @property
    def foreign_keys(self):
        return self.element.foreign_keys

    def is_derived_from(self, element):
        return self.element.is_derived_from(element)

    def alias(self, **kw):
        return FromGrouping(self.element.alias(**kw))

    @property
    def _hide_froms(self):
        return self.element._hide_froms

    def get_children(self, **kwargs):
        return self.element,

    def _copy_internals(self, clone=_clone, **kw):
        self.element = clone(self.element, **kw)

    @property
    def _from_objects(self):
        return self.element._from_objects

    def __getattr__(self, attr):
        return getattr(self.element, attr)

    def __getstate__(self):
        return {'element': self.element}

    def __setstate__(self, state):
        self.element = state['element']

class TableClause(Immutable, FromClause):
    """Represents a minimal "table" construct.

    This is a lightweight table object that has only a name and a
    collection of columns, which are typically produced
    by the :func:`.expression.column` function::

        from sqlalchemy.sql import table, column

        user = table("user",
                column("id"),
                column("name"),
                column("description"),
        )

    The :class:`.TableClause` construct serves as the base for
    the more commonly used :class:`~.schema.Table` object, providing
    the usual set of :class:`~.expression.FromClause` services including
    the ``.c.`` collection and statement generation methods.

    It does **not** provide all the additional schema-level services
    of :class:`~.schema.Table`, including constraints, references to other
    tables, or support for :class:`.MetaData`-level services.  It's useful
    on its own as an ad-hoc construct used to generate quick SQL
    statements when a more fully fledged :class:`~.schema.Table`
    is not on hand.

    """

    __visit_name__ = 'table'

    named_with_column = True

    implicit_returning = False
    """:class:`.TableClause` doesn't support having a primary key or column
    -level defaults, so implicit returning doesn't apply."""

    _autoincrement_column = None
    """No PK or default support so no autoincrement column."""

    def __init__(self, name, *columns):
        """Produce a new :class:`.TableClause`.

        The object returned is an instance of :class:`.TableClause`, which
        represents the "syntactical" portion of the schema-level
        :class:`~.schema.Table` object.
        It may be used to construct lightweight table constructs.

        Note that the :func:`.expression.table` function is not part of
        the ``sqlalchemy`` namespace.  It must be imported from the
        ``sql`` package::

            from sqlalchemy.sql import table, column

        :param name: Name of the table.

        :param columns: A collection of :func:`.expression.column` constructs.

        """

        super(TableClause, self).__init__()
        self.name = self.fullname = name
        self._columns = ColumnCollection()
        self.primary_key = ColumnSet()
        self.foreign_keys = set()
        for c in columns:
            self.append_column(c)

    def _init_collections(self):
        pass

    @util.memoized_property
    def description(self):
        if util.py3k:
            return self.name
        else:
            return self.name.encode('ascii', 'backslashreplace')

    def append_column(self, c):
        self._columns[c.key] = c
        c.table = self

    def get_children(self, column_collections=True, **kwargs):
        if column_collections:
            return [c for c in self.c]
        else:
            return []

    @util.dependencies("sqlalchemy.sql.functions")
    def count(self, functions, whereclause=None, **params):
        """return a SELECT COUNT generated against this
        :class:`.TableClause`."""

        if self.primary_key:
            col = list(self.primary_key)[0]
        else:
            col = list(self.columns)[0]
        return Select(
                    [functions.func.count(col).label('tbl_row_count')],
                    whereclause,
                    from_obj=[self],
                    **params)

    @util.dependencies("sqlalchemy.sql.dml")
    def insert(self, dml, values=None, inline=False, **kwargs):
        """Generate an :func:`.insert` construct against this
        :class:`.TableClause`.

        E.g.::

            table.insert().values(name='foo')

        See :func:`.insert` for argument and usage information.

        """

        return dml.Insert(self, values=values, inline=inline, **kwargs)

    @util.dependencies("sqlalchemy.sql.dml")
    def update(self, dml, whereclause=None, values=None, inline=False, **kwargs):
        """Generate an :func:`.update` construct against this
        :class:`.TableClause`.

        E.g.::

            table.update().where(table.c.id==7).values(name='foo')

        See :func:`.update` for argument and usage information.

        """

        return dml.Update(self, whereclause=whereclause,
                            values=values, inline=inline, **kwargs)

    @util.dependencies("sqlalchemy.sql.dml")
    def delete(self, dml, whereclause=None, **kwargs):
        """Generate a :func:`.delete` construct against this
        :class:`.TableClause`.

        E.g.::

            table.delete().where(table.c.id==7)

        See :func:`.delete` for argument and usage information.

        """

        return dml.Delete(self, whereclause, **kwargs)

    @property
    def _from_objects(self):
        return [self]


class SelectBase(Executable, FromClause):
    """Base class for :class:`.Select` and :class:`.CompoundSelect`."""

    _order_by_clause = ClauseList()
    _group_by_clause = ClauseList()
    _limit = None
    _offset = None

    def __init__(self,
            use_labels=False,
            for_update=False,
            limit=None,
            offset=None,
            order_by=None,
            group_by=None,
            bind=None,
            autocommit=None):
        self.use_labels = use_labels
        self.for_update = for_update
        if autocommit is not None:
            util.warn_deprecated('autocommit on select() is '
                                 'deprecated.  Use .execution_options(a'
                                 'utocommit=True)')
            self._execution_options = \
                self._execution_options.union(
                  {'autocommit': autocommit})
        if limit is not None:
            self._limit = util.asint(limit)
        if offset is not None:
            self._offset = util.asint(offset)
        self._bind = bind

        if order_by is not None:
            self._order_by_clause = ClauseList(*util.to_list(order_by))
        if group_by is not None:
            self._group_by_clause = ClauseList(*util.to_list(group_by))

    def as_scalar(self):
        """return a 'scalar' representation of this selectable, which can be
        used as a column expression.

        Typically, a select statement which has only one column in its columns
        clause is eligible to be used as a scalar expression.

        The returned object is an instance of
        :class:`ScalarSelect`.

        """
        return ScalarSelect(self)

    @_generative
    def apply_labels(self):
        """return a new selectable with the 'use_labels' flag set to True.

        This will result in column expressions being generated using labels
        against their table name, such as "SELECT somecolumn AS
        tablename_somecolumn". This allows selectables which contain multiple
        FROM clauses to produce a unique set of column names regardless of
        name conflicts among the individual FROM clauses.

        """
        self.use_labels = True

    def label(self, name):
        """return a 'scalar' representation of this selectable, embedded as a
        subquery with a label.

        .. seealso::

            :meth:`~.SelectBase.as_scalar`.

        """
        return self.as_scalar().label(name)

    def cte(self, name=None, recursive=False):
        """Return a new :class:`.CTE`, or Common Table Expression instance.

        Common table expressions are a SQL standard whereby SELECT
        statements can draw upon secondary statements specified along
        with the primary statement, using a clause called "WITH".
        Special semantics regarding UNION can also be employed to
        allow "recursive" queries, where a SELECT statement can draw
        upon the set of rows that have previously been selected.

        SQLAlchemy detects :class:`.CTE` objects, which are treated
        similarly to :class:`.Alias` objects, as special elements
        to be delivered to the FROM clause of the statement as well
        as to a WITH clause at the top of the statement.

        .. versionadded:: 0.7.6

        :param name: name given to the common table expression.  Like
         :meth:`._FromClause.alias`, the name can be left as ``None``
         in which case an anonymous symbol will be used at query
         compile time.
        :param recursive: if ``True``, will render ``WITH RECURSIVE``.
         A recursive common table expression is intended to be used in
         conjunction with UNION ALL in order to derive rows
         from those already selected.

        The following examples illustrate two examples from
        Postgresql's documentation at
        http://www.postgresql.org/docs/8.4/static/queries-with.html.

        Example 1, non recursive::

            from sqlalchemy import Table, Column, String, Integer, MetaData, \\
                select, func

            metadata = MetaData()

            orders = Table('orders', metadata,
                Column('region', String),
                Column('amount', Integer),
                Column('product', String),
                Column('quantity', Integer)
            )

            regional_sales = select([
                                orders.c.region,
                                func.sum(orders.c.amount).label('total_sales')
                            ]).group_by(orders.c.region).cte("regional_sales")


            top_regions = select([regional_sales.c.region]).\\
                    where(
                        regional_sales.c.total_sales >
                        select([
                            func.sum(regional_sales.c.total_sales)/10
                        ])
                    ).cte("top_regions")

            statement = select([
                        orders.c.region,
                        orders.c.product,
                        func.sum(orders.c.quantity).label("product_units"),
                        func.sum(orders.c.amount).label("product_sales")
                ]).where(orders.c.region.in_(
                    select([top_regions.c.region])
                )).group_by(orders.c.region, orders.c.product)

            result = conn.execute(statement).fetchall()

        Example 2, WITH RECURSIVE::

            from sqlalchemy import Table, Column, String, Integer, MetaData, \\
                select, func

            metadata = MetaData()

            parts = Table('parts', metadata,
                Column('part', String),
                Column('sub_part', String),
                Column('quantity', Integer),
            )

            included_parts = select([
                                parts.c.sub_part,
                                parts.c.part,
                                parts.c.quantity]).\\
                                where(parts.c.part=='our part').\\
                                cte(recursive=True)


            incl_alias = included_parts.alias()
            parts_alias = parts.alias()
            included_parts = included_parts.union_all(
                select([
                    parts_alias.c.part,
                    parts_alias.c.sub_part,
                    parts_alias.c.quantity
                ]).
                    where(parts_alias.c.part==incl_alias.c.sub_part)
            )

            statement = select([
                        included_parts.c.sub_part,
                        func.sum(included_parts.c.quantity).
                          label('total_quantity')
                    ]).\
                    select_from(included_parts.join(parts,
                                included_parts.c.part==parts.c.part)).\\
                    group_by(included_parts.c.sub_part)

            result = conn.execute(statement).fetchall()


        .. seealso::

            :meth:`.orm.query.Query.cte` - ORM version of :meth:`.SelectBase.cte`.

        """
        return CTE(self, name=name, recursive=recursive)

    @_generative
    @util.deprecated('0.6',
                     message=":func:`.autocommit` is deprecated. Use "
                     ":func:`.Executable.execution_options` with the "
                     "'autocommit' flag.")
    def autocommit(self):
        """return a new selectable with the 'autocommit' flag set to
        True."""

        self._execution_options = \
            self._execution_options.union({'autocommit': True})

    def _generate(self):
        """Override the default _generate() method to also clear out
        exported collections."""

        s = self.__class__.__new__(self.__class__)
        s.__dict__ = self.__dict__.copy()
        s._reset_exported()
        return s

    @_generative
    def limit(self, limit):
        """return a new selectable with the given LIMIT criterion
        applied."""

        self._limit = util.asint(limit)

    @_generative
    def offset(self, offset):
        """return a new selectable with the given OFFSET criterion
        applied."""

        self._offset = util.asint(offset)

    @_generative
    def order_by(self, *clauses):
        """return a new selectable with the given list of ORDER BY
        criterion applied.

        The criterion will be appended to any pre-existing ORDER BY
        criterion.

        """

        self.append_order_by(*clauses)

    @_generative
    def group_by(self, *clauses):
        """return a new selectable with the given list of GROUP BY
        criterion applied.

        The criterion will be appended to any pre-existing GROUP BY
        criterion.

        """

        self.append_group_by(*clauses)

    def append_order_by(self, *clauses):
        """Append the given ORDER BY criterion applied to this selectable.

        The criterion will be appended to any pre-existing ORDER BY criterion.

        This is an **in-place** mutation method; the
        :meth:`~.SelectBase.order_by` method is preferred, as it provides standard
        :term:`method chaining`.

        """
        if len(clauses) == 1 and clauses[0] is None:
            self._order_by_clause = ClauseList()
        else:
            if getattr(self, '_order_by_clause', None) is not None:
                clauses = list(self._order_by_clause) + list(clauses)
            self._order_by_clause = ClauseList(*clauses)

    def append_group_by(self, *clauses):
        """Append the given GROUP BY criterion applied to this selectable.

        The criterion will be appended to any pre-existing GROUP BY criterion.

        This is an **in-place** mutation method; the
        :meth:`~.SelectBase.group_by` method is preferred, as it provides standard
        :term:`method chaining`.

        """
        if len(clauses) == 1 and clauses[0] is None:
            self._group_by_clause = ClauseList()
        else:
            if getattr(self, '_group_by_clause', None) is not None:
                clauses = list(self._group_by_clause) + list(clauses)
            self._group_by_clause = ClauseList(*clauses)

    @property
    def _from_objects(self):
        return [self]


class CompoundSelect(SelectBase):
    """Forms the basis of ``UNION``, ``UNION ALL``, and other
        SELECT-based set operations.


    .. seealso::

        :func:`.union`

        :func:`.union_all`

        :func:`.intersect`

        :func:`.intersect_all`

        :func:`.except`

        :func:`.except_all`

    """

    __visit_name__ = 'compound_select'

    UNION = util.symbol('UNION')
    UNION_ALL = util.symbol('UNION ALL')
    EXCEPT = util.symbol('EXCEPT')
    EXCEPT_ALL = util.symbol('EXCEPT ALL')
    INTERSECT = util.symbol('INTERSECT')
    INTERSECT_ALL = util.symbol('INTERSECT ALL')

    def __init__(self, keyword, *selects, **kwargs):
        self._auto_correlate = kwargs.pop('correlate', False)
        self.keyword = keyword
        self.selects = []

        numcols = None

        # some DBs do not like ORDER BY in the inner queries of a UNION, etc.
        for n, s in enumerate(selects):
            s = _clause_element_as_expr(s)

            if not numcols:
                numcols = len(s.c)
            elif len(s.c) != numcols:
                raise exc.ArgumentError('All selectables passed to '
                        'CompoundSelect must have identical numbers of '
                        'columns; select #%d has %d columns, select '
                        '#%d has %d' % (1, len(self.selects[0].c), n
                        + 1, len(s.c)))

            self.selects.append(s.self_group(self))

        SelectBase.__init__(self, **kwargs)

    @classmethod
    def _create_union(cls, *selects, **kwargs):
        """Return a ``UNION`` of multiple selectables.

        The returned object is an instance of
        :class:`.CompoundSelect`.

        A similar :func:`union()` method is available on all
        :class:`.FromClause` subclasses.

        \*selects
          a list of :class:`.Select` instances.

        \**kwargs
           available keyword arguments are the same as those of
           :func:`select`.

        """
        return CompoundSelect(CompoundSelect.UNION, *selects, **kwargs)

    @classmethod
    def _create_union_all(cls, *selects, **kwargs):
        """Return a ``UNION ALL`` of multiple selectables.

        The returned object is an instance of
        :class:`.CompoundSelect`.

        A similar :func:`union_all()` method is available on all
        :class:`.FromClause` subclasses.

        \*selects
          a list of :class:`.Select` instances.

        \**kwargs
          available keyword arguments are the same as those of
          :func:`select`.

        """
        return CompoundSelect(CompoundSelect.UNION_ALL, *selects, **kwargs)


    @classmethod
    def _create_except(cls, *selects, **kwargs):
        """Return an ``EXCEPT`` of multiple selectables.

        The returned object is an instance of
        :class:`.CompoundSelect`.

        \*selects
          a list of :class:`.Select` instances.

        \**kwargs
          available keyword arguments are the same as those of
          :func:`select`.

        """
        return CompoundSelect(CompoundSelect.EXCEPT, *selects, **kwargs)


    @classmethod
    def _create_except_all(cls, *selects, **kwargs):
        """Return an ``EXCEPT ALL`` of multiple selectables.

        The returned object is an instance of
        :class:`.CompoundSelect`.

        \*selects
          a list of :class:`.Select` instances.

        \**kwargs
          available keyword arguments are the same as those of
          :func:`select`.

        """
        return CompoundSelect(CompoundSelect.EXCEPT_ALL, *selects, **kwargs)


    @classmethod
    def _create_intersect(cls, *selects, **kwargs):
        """Return an ``INTERSECT`` of multiple selectables.

        The returned object is an instance of
        :class:`.CompoundSelect`.

        \*selects
          a list of :class:`.Select` instances.

        \**kwargs
          available keyword arguments are the same as those of
          :func:`select`.

        """
        return CompoundSelect(CompoundSelect.INTERSECT, *selects, **kwargs)


    @classmethod
    def _create_intersect_all(cls, *selects, **kwargs):
        """Return an ``INTERSECT ALL`` of multiple selectables.

        The returned object is an instance of
        :class:`.CompoundSelect`.

        \*selects
          a list of :class:`.Select` instances.

        \**kwargs
          available keyword arguments are the same as those of
          :func:`select`.

        """
        return CompoundSelect(CompoundSelect.INTERSECT_ALL, *selects, **kwargs)


    def _scalar_type(self):
        return self.selects[0]._scalar_type()

    def self_group(self, against=None):
        return FromGrouping(self)

    def is_derived_from(self, fromclause):
        for s in self.selects:
            if s.is_derived_from(fromclause):
                return True
        return False

    def _populate_column_collection(self):
        for cols in zip(*[s.c for s in self.selects]):

            # this is a slightly hacky thing - the union exports a
            # column that resembles just that of the *first* selectable.
            # to get at a "composite" column, particularly foreign keys,
            # you have to dig through the proxies collection which we
            # generate below.  We may want to improve upon this, such as
            # perhaps _make_proxy can accept a list of other columns
            # that are "shared" - schema.column can then copy all the
            # ForeignKeys in. this would allow the union() to have all
            # those fks too.

            proxy = cols[0]._make_proxy(self,
                    name=cols[0]._label if self.use_labels else None,
                    key=cols[0]._key_label if self.use_labels else None)

            # hand-construct the "_proxies" collection to include all
            # derived columns place a 'weight' annotation corresponding
            # to how low in the list of select()s the column occurs, so
            # that the corresponding_column() operation can resolve
            # conflicts

            proxy._proxies = [c._annotate({'weight': i + 1}) for (i,
                             c) in enumerate(cols)]

    def _refresh_for_new_column(self, column):
        for s in self.selects:
            s._refresh_for_new_column(column)

        if not self._cols_populated:
            return None

        raise NotImplementedError("CompoundSelect constructs don't support "
                "addition of columns to underlying selectables")

    def _copy_internals(self, clone=_clone, **kw):
        self._reset_exported()
        self.selects = [clone(s, **kw) for s in self.selects]
        if hasattr(self, '_col_map'):
            del self._col_map
        for attr in ('_order_by_clause', '_group_by_clause'):
            if getattr(self, attr) is not None:
                setattr(self, attr, clone(getattr(self, attr), **kw))

    def get_children(self, column_collections=True, **kwargs):
        return (column_collections and list(self.c) or []) \
            + [self._order_by_clause, self._group_by_clause] \
            + list(self.selects)

    def bind(self):
        if self._bind:
            return self._bind
        for s in self.selects:
            e = s.bind
            if e:
                return e
        else:
            return None

    def _set_bind(self, bind):
        self._bind = bind
    bind = property(bind, _set_bind)


class HasPrefixes(object):
    _prefixes = ()

    @_generative
    def prefix_with(self, *expr, **kw):
        """Add one or more expressions following the statement keyword, i.e.
        SELECT, INSERT, UPDATE, or DELETE. Generative.

        This is used to support backend-specific prefix keywords such as those
        provided by MySQL.

        E.g.::

            stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")

        Multiple prefixes can be specified by multiple calls
        to :meth:`.prefix_with`.

        :param \*expr: textual or :class:`.ClauseElement` construct which
         will be rendered following the INSERT, UPDATE, or DELETE
         keyword.
        :param \**kw: A single keyword 'dialect' is accepted.  This is an
         optional string dialect name which will
         limit rendering of this prefix to only that dialect.

        """
        dialect = kw.pop('dialect', None)
        if kw:
            raise exc.ArgumentError("Unsupported argument(s): %s" %
                            ",".join(kw))
        self._setup_prefixes(expr, dialect)

    def _setup_prefixes(self, prefixes, dialect=None):
        self._prefixes = self._prefixes + tuple(
                            [(_literal_as_text(p), dialect) for p in prefixes])

class Select(HasPrefixes, SelectBase):
    """Represents a ``SELECT`` statement.

    """

    __visit_name__ = 'select'

    _prefixes = ()
    _hints = util.immutabledict()
    _distinct = False
    _from_cloned = None
    _correlate = ()
    _correlate_except = None
    _memoized_property = SelectBase._memoized_property

    def __init__(self,
                columns=None,
                whereclause=None,
                from_obj=None,
                distinct=False,
                having=None,
                correlate=True,
                prefixes=None,
                **kwargs):
        """Construct a new :class:`.Select`.

        Similar functionality is also available via the :meth:`.FromClause.select`
        method on any :class:`.FromClause`.

        All arguments which accept :class:`.ClauseElement` arguments also accept
        string arguments, which will be converted as appropriate into
        either :func:`text()` or :func:`literal_column()` constructs.

        .. seealso::

            :ref:`coretutorial_selecting` - Core Tutorial description of
            :func:`.select`.

        :param columns:
          A list of :class:`.ClauseElement` objects, typically
          :class:`.ColumnElement` objects or subclasses, which will form the
          columns clause of the resulting statement. For all members which are
          instances of :class:`.Selectable`, the individual :class:`.ColumnElement`
          members of the :class:`.Selectable` will be added individually to the
          columns clause. For example, specifying a
          :class:`~sqlalchemy.schema.Table` instance will result in all the
          contained :class:`~sqlalchemy.schema.Column` objects within to be added
          to the columns clause.

          This argument is not present on the form of :func:`select()`
          available on :class:`~sqlalchemy.schema.Table`.

        :param whereclause:
          A :class:`.ClauseElement` expression which will be used to form the
          ``WHERE`` clause.

        :param from_obj:
          A list of :class:`.ClauseElement` objects which will be added to the
          ``FROM`` clause of the resulting statement. Note that "from" objects are
          automatically located within the columns and whereclause ClauseElements.
          Use this parameter to explicitly specify "from" objects which are not
          automatically locatable. This could include
          :class:`~sqlalchemy.schema.Table` objects that aren't otherwise present,
          or :class:`.Join` objects whose presence will supercede that of the
          :class:`~sqlalchemy.schema.Table` objects already located in the other
          clauses.

        :param autocommit:
          Deprecated.  Use .execution_options(autocommit=<True|False>)
          to set the autocommit option.

        :param bind=None:
          an :class:`~.base.Engine` or :class:`~.base.Connection` instance
          to which the
          resulting :class:`.Select` object will be bound.  The :class:`.Select`
          object will otherwise automatically bind to whatever
          :class:`~.base.Connectable` instances can be located within its contained
          :class:`.ClauseElement` members.

        :param correlate=True:
          indicates that this :class:`.Select` object should have its
          contained :class:`.FromClause` elements "correlated" to an enclosing
          :class:`.Select` object.  This means that any :class:`.ClauseElement`
          instance within the "froms" collection of this :class:`.Select`
          which is also present in the "froms" collection of an
          enclosing select will not be rendered in the ``FROM`` clause
          of this select statement.

        :param distinct=False:
          when ``True``, applies a ``DISTINCT`` qualifier to the columns
          clause of the resulting statement.

          The boolean argument may also be a column expression or list
          of column expressions - this is a special calling form which
          is understood by the Postgresql dialect to render the
          ``DISTINCT ON (<columns>)`` syntax.

          ``distinct`` is also available via the :meth:`~.Select.distinct`
          generative method.

        :param for_update=False:
          when ``True``, applies ``FOR UPDATE`` to the end of the
          resulting statement.

          Certain database dialects also support
          alternate values for this parameter:

          * With the MySQL dialect, the value ``"read"`` translates to
            ``LOCK IN SHARE MODE``.
          * With the Oracle and Postgresql dialects, the value ``"nowait"``
            translates to ``FOR UPDATE NOWAIT``.
          * With the Postgresql dialect, the values "read" and ``"read_nowait"``
            translate to ``FOR SHARE`` and ``FOR SHARE NOWAIT``, respectively.

            .. versionadded:: 0.7.7

        :param group_by:
          a list of :class:`.ClauseElement` objects which will comprise the
          ``GROUP BY`` clause of the resulting select.

        :param having:
          a :class:`.ClauseElement` that will comprise the ``HAVING`` clause
          of the resulting select when ``GROUP BY`` is used.

        :param limit=None:
          a numerical value which usually compiles to a ``LIMIT``
          expression in the resulting select.  Databases that don't
          support ``LIMIT`` will attempt to provide similar
          functionality.

        :param offset=None:
          a numeric value which usually compiles to an ``OFFSET``
          expression in the resulting select.  Databases that don't
          support ``OFFSET`` will attempt to provide similar
          functionality.

        :param order_by:
          a scalar or list of :class:`.ClauseElement` objects which will
          comprise the ``ORDER BY`` clause of the resulting select.

        :param use_labels=False:
          when ``True``, the statement will be generated using labels
          for each column in the columns clause, which qualify each
          column with its parent table's (or aliases) name so that name
          conflicts between columns in different tables don't occur.
          The format of the label is <tablename>_<column>.  The "c"
          collection of the resulting :class:`.Select` object will use these
          names as well for targeting column members.

          use_labels is also available via the :meth:`~.SelectBase.apply_labels`
          generative method.

        """
        self._auto_correlate = correlate
        if distinct is not False:
            if distinct is True:
                self._distinct = True
            else:
                self._distinct = [
                                _literal_as_text(e)
                                for e in util.to_list(distinct)
                            ]

        if from_obj is not None:
            self._from_obj = util.OrderedSet(
                                _interpret_as_from(f)
                                for f in util.to_list(from_obj))
        else:
            self._from_obj = util.OrderedSet()

        try:
            cols_present = bool(columns)
        except TypeError:
            raise exc.ArgumentError("columns argument to select() must "
                                "be a Python list or other iterable")

        if cols_present:
            self._raw_columns = []
            for c in columns:
                c = _interpret_as_column_or_from(c)
                if isinstance(c, ScalarSelect):
                    c = c.self_group(against=operators.comma_op)
                self._raw_columns.append(c)
        else:
            self._raw_columns = []

        if whereclause is not None:
            self._whereclause = _literal_as_text(whereclause)
        else:
            self._whereclause = None

        if having is not None:
            self._having = _literal_as_text(having)
        else:
            self._having = None

        if prefixes:
            self._setup_prefixes(prefixes)

        SelectBase.__init__(self, **kwargs)

    @property
    def _froms(self):
        # would love to cache this,
        # but there's just enough edge cases, particularly now that
        # declarative encourages construction of SQL expressions
        # without tables present, to just regen this each time.
        froms = []
        seen = set()
        translate = self._from_cloned

        def add(items):
            for item in items:
                if translate and item in translate:
                    item = translate[item]
                if not seen.intersection(item._cloned_set):
                    froms.append(item)
                seen.update(item._cloned_set)

        add(_from_objects(*self._raw_columns))
        if self._whereclause is not None:
            add(_from_objects(self._whereclause))
        add(self._from_obj)

        return froms

    def _get_display_froms(self, explicit_correlate_froms=None,
                                    implicit_correlate_froms=None):
        """Return the full list of 'from' clauses to be displayed.

        Takes into account a set of existing froms which may be
        rendered in the FROM clause of enclosing selects; this Select
        may want to leave those absent if it is automatically
        correlating.

        """
        froms = self._froms

        toremove = set(itertools.chain(*[
                            _expand_cloned(f._hide_froms)
                            for f in froms]))
        if toremove:
            # if we're maintaining clones of froms,
            # add the copies out to the toremove list.  only include
            # clones that are lexical equivalents.
            if self._from_cloned:
                toremove.update(
                    self._from_cloned[f] for f in
                    toremove.intersection(self._from_cloned)
                    if self._from_cloned[f]._is_lexical_equivalent(f)
                )
            # filter out to FROM clauses not in the list,
            # using a list to maintain ordering
            froms = [f for f in froms if f not in toremove]

        if self._correlate:
            to_correlate = self._correlate
            if to_correlate:
                froms = [
                    f for f in froms if f not in
                    _cloned_intersection(
                        _cloned_intersection(froms, explicit_correlate_froms or ()),
                        to_correlate
                    )
                ]

        if self._correlate_except is not None:

            froms = [
                f for f in froms if f not in
                _cloned_difference(
                    _cloned_intersection(froms, explicit_correlate_froms or ()),
                    self._correlate_except
                )
            ]

        if self._auto_correlate and \
            implicit_correlate_froms and \
            len(froms) > 1:

            froms = [
                f for f in froms if f not in
                _cloned_intersection(froms, implicit_correlate_froms)
            ]

            if not len(froms):
                raise exc.InvalidRequestError("Select statement '%s"
                        "' returned no FROM clauses due to "
                        "auto-correlation; specify "
                        "correlate(<tables>) to control "
                        "correlation manually." % self)

        return froms

    def _scalar_type(self):
        elem = self._raw_columns[0]
        cols = list(elem._select_iterable)
        return cols[0].type

    @property
    def froms(self):
        """Return the displayed list of FromClause elements."""

        return self._get_display_froms()

    @_generative
    def with_hint(self, selectable, text, dialect_name='*'):
        """Add an indexing hint for the given selectable to this
        :class:`.Select`.

        The text of the hint is rendered in the appropriate
        location for the database backend in use, relative
        to the given :class:`.Table` or :class:`.Alias` passed as the
        ``selectable`` argument. The dialect implementation
        typically uses Python string substitution syntax
        with the token ``%(name)s`` to render the name of
        the table or alias. E.g. when using Oracle, the
        following::

            select([mytable]).\\
                with_hint(mytable, "+ index(%(name)s ix_mytable)")

        Would render SQL as::

            select /*+ index(mytable ix_mytable) */ ... from mytable

        The ``dialect_name`` option will limit the rendering of a particular
        hint to a particular backend. Such as, to add hints for both Oracle
        and Sybase simultaneously::

            select([mytable]).\\
                with_hint(mytable, "+ index(%(name)s ix_mytable)", 'oracle').\\
                with_hint(mytable, "WITH INDEX ix_mytable", 'sybase')

        """
        self._hints = self._hints.union(
                    {(selectable, dialect_name): text})

    @property
    def type(self):
        raise exc.InvalidRequestError("Select objects don't have a type.  "
                    "Call as_scalar() on this Select object "
                    "to return a 'scalar' version of this Select.")

    @_memoized_property.method
    def locate_all_froms(self):
        """return a Set of all FromClause elements referenced by this Select.

        This set is a superset of that returned by the ``froms`` property,
        which is specifically for those FromClause elements that would
        actually be rendered.

        """
        froms = self._froms
        return froms + list(_from_objects(*froms))

    @property
    def inner_columns(self):
        """an iterator of all ColumnElement expressions which would
        be rendered into the columns clause of the resulting SELECT statement.

        """
        return _select_iterables(self._raw_columns)

    def is_derived_from(self, fromclause):
        if self in fromclause._cloned_set:
            return True

        for f in self.locate_all_froms():
            if f.is_derived_from(fromclause):
                return True
        return False

    def _copy_internals(self, clone=_clone, **kw):

        # Select() object has been cloned and probably adapted by the
        # given clone function.  Apply the cloning function to internal
        # objects

        # 1. keep a dictionary of the froms we've cloned, and what
        # they've become.  This is consulted later when we derive
        # additional froms from "whereclause" and the columns clause,
        # which may still reference the uncloned parent table.
        # as of 0.7.4 we also put the current version of _froms, which
        # gets cleared on each generation.  previously we were "baking"
        # _froms into self._from_obj.
        self._from_cloned = from_cloned = dict((f, clone(f, **kw))
                for f in self._from_obj.union(self._froms))

        # 3. update persistent _from_obj with the cloned versions.
        self._from_obj = util.OrderedSet(from_cloned[f] for f in
                self._from_obj)

        # the _correlate collection is done separately, what can happen
        # here is the same item is _correlate as in _from_obj but the
        # _correlate version has an annotation on it - (specifically
        # RelationshipProperty.Comparator._criterion_exists() does
        # this). Also keep _correlate liberally open with it's previous
        # contents, as this set is used for matching, not rendering.
        self._correlate = set(clone(f) for f in
                              self._correlate).union(self._correlate)

        # 4. clone other things.   The difficulty here is that Column
        # objects are not actually cloned, and refer to their original
        # .table, resulting in the wrong "from" parent after a clone
        # operation.  Hence _from_cloned and _from_obj supercede what is
        # present here.
        self._raw_columns = [clone(c, **kw) for c in self._raw_columns]
        for attr in '_whereclause', '_having', '_order_by_clause', \
            '_group_by_clause':
            if getattr(self, attr) is not None:
                setattr(self, attr, clone(getattr(self, attr), **kw))

        # erase exported column list, _froms collection,
        # etc.
        self._reset_exported()

    def get_children(self, column_collections=True, **kwargs):
        """return child elements as per the ClauseElement specification."""

        return (column_collections and list(self.columns) or []) + \
            self._raw_columns + list(self._froms) + \
            [x for x in
                (self._whereclause, self._having,
                    self._order_by_clause, self._group_by_clause)
            if x is not None]

    @_generative
    def column(self, column):
        """return a new select() construct with the given column expression
            added to its columns clause.

        """
        self.append_column(column)

    @util.dependencies("sqlalchemy.sql.util")
    def reduce_columns(self, sqlutil, only_synonyms=True):
        """Return a new :func`.select` construct with redundantly
        named, equivalently-valued columns removed from the columns clause.

        "Redundant" here means two columns where one refers to the
        other either based on foreign key, or via a simple equality
        comparison in the WHERE clause of the statement.   The primary purpose
        of this method is to automatically construct a select statement
        with all uniquely-named columns, without the need to use
        table-qualified labels as :meth:`.apply_labels` does.

        When columns are omitted based on foreign key, the referred-to
        column is the one that's kept.  When columns are omitted based on
        WHERE eqivalence, the first column in the columns clause is the
        one that's kept.

        :param only_synonyms: when True, limit the removal of columns
         to those which have the same name as the equivalent.   Otherwise,
         all columns that are equivalent to another are removed.

        .. versionadded:: 0.8

        """
        return self.with_only_columns(
                sqlutil.reduce_columns(
                        self.inner_columns,
                        only_synonyms=only_synonyms,
                        *(self._whereclause, ) + tuple(self._from_obj)
                )
            )

    @_generative
    def with_only_columns(self, columns):
        """Return a new :func:`.select` construct with its columns
        clause replaced with the given columns.

        .. versionchanged:: 0.7.3
            Due to a bug fix, this method has a slight
            behavioral change as of version 0.7.3.
            Prior to version 0.7.3, the FROM clause of
            a :func:`.select` was calculated upfront and as new columns
            were added; in 0.7.3 and later it's calculated
            at compile time, fixing an issue regarding late binding
            of columns to parent tables.  This changes the behavior of
            :meth:`.Select.with_only_columns` in that FROM clauses no
            longer represented in the new list are dropped,
            but this behavior is more consistent in
            that the FROM clauses are consistently derived from the
            current columns clause.  The original intent of this method
            is to allow trimming of the existing columns list to be fewer
            columns than originally present; the use case of replacing
            the columns list with an entirely different one hadn't
            been anticipated until 0.7.3 was released; the usage
            guidelines below illustrate how this should be done.

        This method is exactly equivalent to as if the original
        :func:`.select` had been called with the given columns
        clause.   I.e. a statement::

            s = select([table1.c.a, table1.c.b])
            s = s.with_only_columns([table1.c.b])

        should be exactly equivalent to::

            s = select([table1.c.b])

        This means that FROM clauses which are only derived
        from the column list will be discarded if the new column
        list no longer contains that FROM::

            >>> table1 = table('t1', column('a'), column('b'))
            >>> table2 = table('t2', column('a'), column('b'))
            >>> s1 = select([table1.c.a, table2.c.b])
            >>> print s1
            SELECT t1.a, t2.b FROM t1, t2
            >>> s2 = s1.with_only_columns([table2.c.b])
            >>> print s2
            SELECT t2.b FROM t1

        The preferred way to maintain a specific FROM clause
        in the construct, assuming it won't be represented anywhere
        else (i.e. not in the WHERE clause, etc.) is to set it using
        :meth:`.Select.select_from`::

            >>> s1 = select([table1.c.a, table2.c.b]).\\
            ...         select_from(table1.join(table2,
            ...                 table1.c.a==table2.c.a))
            >>> s2 = s1.with_only_columns([table2.c.b])
            >>> print s2
            SELECT t2.b FROM t1 JOIN t2 ON t1.a=t2.a

        Care should also be taken to use the correct
        set of column objects passed to :meth:`.Select.with_only_columns`.
        Since the method is essentially equivalent to calling the
        :func:`.select` construct in the first place with the given
        columns, the columns passed to :meth:`.Select.with_only_columns`
        should usually be a subset of those which were passed
        to the :func:`.select` construct, not those which are available
        from the ``.c`` collection of that :func:`.select`.  That
        is::

            s = select([table1.c.a, table1.c.b]).select_from(table1)
            s = s.with_only_columns([table1.c.b])

        and **not**::

            # usually incorrect
            s = s.with_only_columns([s.c.b])

        The latter would produce the SQL::

            SELECT b
            FROM (SELECT t1.a AS a, t1.b AS b
            FROM t1), t1

        Since the :func:`.select` construct is essentially being
        asked to select both from ``table1`` as well as itself.

        """
        self._reset_exported()
        rc = []
        for c in columns:
            c = _interpret_as_column_or_from(c)
            if isinstance(c, ScalarSelect):
                c = c.self_group(against=operators.comma_op)
            rc.append(c)
        self._raw_columns = rc

    @_generative
    def where(self, whereclause):
        """return a new select() construct with the given expression added to
        its WHERE clause, joined to the existing clause via AND, if any.

        """

        self.append_whereclause(whereclause)

    @_generative
    def having(self, having):
        """return a new select() construct with the given expression added to
        its HAVING clause, joined to the existing clause via AND, if any.

        """
        self.append_having(having)

    @_generative
    def distinct(self, *expr):
        """Return a new select() construct which will apply DISTINCT to its
        columns clause.

        :param \*expr: optional column expressions.  When present,
         the Postgresql dialect will render a ``DISTINCT ON (<expressions>>)``
         construct.

        """
        if expr:
            expr = [_literal_as_text(e) for e in expr]
            if isinstance(self._distinct, list):
                self._distinct = self._distinct + expr
            else:
                self._distinct = expr
        else:
            self._distinct = True

    @_generative
    def select_from(self, fromclause):
        """return a new :func:`.select` construct with the
        given FROM expression
        merged into its list of FROM objects.

        E.g.::

            table1 = table('t1', column('a'))
            table2 = table('t2', column('b'))
            s = select([table1.c.a]).\\
                select_from(
                    table1.join(table2, table1.c.a==table2.c.b)
                )

        The "from" list is a unique set on the identity of each element,
        so adding an already present :class:`.Table` or other selectable
        will have no effect.   Passing a :class:`.Join` that refers
        to an already present :class:`.Table` or other selectable will have
        the effect of concealing the presence of that selectable as
        an individual element in the rendered FROM list, instead
        rendering it into a JOIN clause.

        While the typical purpose of :meth:`.Select.select_from` is to
        replace the default, derived FROM clause with a join, it can
        also be called with individual table elements, multiple times
        if desired, in the case that the FROM clause cannot be fully
        derived from the columns clause::

            select([func.count('*')]).select_from(table1)

        """
        self.append_from(fromclause)

    @_generative
    def correlate(self, *fromclauses):
        """return a new :class:`.Select` which will correlate the given FROM
        clauses to that of an enclosing :class:`.Select`.

        Calling this method turns off the :class:`.Select` object's
        default behavior of "auto-correlation".  Normally, FROM elements
        which appear in a :class:`.Select` that encloses this one via
        its :term:`WHERE clause`, ORDER BY, HAVING or
        :term:`columns clause` will be omitted from this :class:`.Select`
        object's :term:`FROM clause`.
        Setting an explicit correlation collection using the
        :meth:`.Select.correlate` method provides a fixed list of FROM objects
        that can potentially take place in this process.

        When :meth:`.Select.correlate` is used to apply specific FROM clauses
        for correlation, the FROM elements become candidates for
        correlation regardless of how deeply nested this :class:`.Select`
        object is, relative to an enclosing :class:`.Select` which refers to
        the same FROM object.  This is in contrast to the behavior of
        "auto-correlation" which only correlates to an immediate enclosing
        :class:`.Select`.   Multi-level correlation ensures that the link
        between enclosed and enclosing :class:`.Select` is always via
        at least one WHERE/ORDER BY/HAVING/columns clause in order for
        correlation to take place.

        If ``None`` is passed, the :class:`.Select` object will correlate
        none of its FROM entries, and all will render unconditionally
        in the local FROM clause.

        :param \*fromclauses: a list of one or more :class:`.FromClause`
         constructs, or other compatible constructs (i.e. ORM-mapped
         classes) to become part of the correlate collection.

         .. versionchanged:: 0.8.0 ORM-mapped classes are accepted by
            :meth:`.Select.correlate`.

        .. versionchanged:: 0.8.0 The :meth:`.Select.correlate` method no
           longer unconditionally removes entries from the FROM clause; instead,
           the candidate FROM entries must also be matched by a FROM entry
           located in an enclosing :class:`.Select`, which ultimately encloses
           this one as present in the WHERE clause, ORDER BY clause, HAVING
           clause, or columns clause of an enclosing :meth:`.Select`.

        .. versionchanged:: 0.8.2 explicit correlation takes place
           via any level of nesting of :class:`.Select` objects; in previous
           0.8 versions, correlation would only occur relative to the immediate
           enclosing :class:`.Select` construct.

        .. seealso::

            :meth:`.Select.correlate_except`

            :ref:`correlated_subqueries`

        """
        self._auto_correlate = False
        if fromclauses and fromclauses[0] is None:
            self._correlate = ()
        else:
            self._correlate = set(self._correlate).union(
                    _interpret_as_from(f) for f in fromclauses)

    @_generative
    def correlate_except(self, *fromclauses):
        """return a new :class:`.Select` which will omit the given FROM
        clauses from the auto-correlation process.

        Calling :meth:`.Select.correlate_except` turns off the
        :class:`.Select` object's default behavior of
        "auto-correlation" for the given FROM elements.  An element
        specified here will unconditionally appear in the FROM list, while
        all other FROM elements remain subject to normal auto-correlation
        behaviors.

        .. versionchanged:: 0.8.2 The :meth:`.Select.correlate_except`
           method was improved to fully prevent FROM clauses specified here
           from being omitted from the immediate FROM clause of this
           :class:`.Select`.

        If ``None`` is passed, the :class:`.Select` object will correlate
        all of its FROM entries.

        .. versionchanged:: 0.8.2 calling ``correlate_except(None)`` will
           correctly auto-correlate all FROM clauses.

        :param \*fromclauses: a list of one or more :class:`.FromClause`
         constructs, or other compatible constructs (i.e. ORM-mapped
         classes) to become part of the correlate-exception collection.

        .. seealso::

            :meth:`.Select.correlate`

            :ref:`correlated_subqueries`

        """

        self._auto_correlate = False
        if fromclauses and fromclauses[0] is None:
            self._correlate_except = ()
        else:
            self._correlate_except = set(self._correlate_except or ()).union(
                    _interpret_as_from(f) for f in fromclauses)

    def append_correlation(self, fromclause):
        """append the given correlation expression to this select()
        construct.

        This is an **in-place** mutation method; the
        :meth:`~.Select.correlate` method is preferred, as it provides standard
        :term:`method chaining`.

        """

        self._auto_correlate = False
        self._correlate = set(self._correlate).union(
                _interpret_as_from(f) for f in fromclause)

    def append_column(self, column):
        """append the given column expression to the columns clause of this
        select() construct.

        This is an **in-place** mutation method; the
        :meth:`~.Select.column` method is preferred, as it provides standard
        :term:`method chaining`.

        """
        self._reset_exported()
        column = _interpret_as_column_or_from(column)

        if isinstance(column, ScalarSelect):
            column = column.self_group(against=operators.comma_op)

        self._raw_columns = self._raw_columns + [column]

    def append_prefix(self, clause):
        """append the given columns clause prefix expression to this select()
        construct.

        This is an **in-place** mutation method; the
        :meth:`~.Select.prefix_with` method is preferred, as it provides standard
        :term:`method chaining`.

        """
        clause = _literal_as_text(clause)
        self._prefixes = self._prefixes + (clause,)

    def append_whereclause(self, whereclause):
        """append the given expression to this select() construct's WHERE
        criterion.

        The expression will be joined to existing WHERE criterion via AND.

        This is an **in-place** mutation method; the
        :meth:`~.Select.where` method is preferred, as it provides standard
        :term:`method chaining`.

        """
        self._reset_exported()
        whereclause = _literal_as_text(whereclause)

        if self._whereclause is not None:
            self._whereclause = and_(self._whereclause, whereclause)
        else:
            self._whereclause = whereclause

    def append_having(self, having):
        """append the given expression to this select() construct's HAVING
        criterion.

        The expression will be joined to existing HAVING criterion via AND.

        This is an **in-place** mutation method; the
        :meth:`~.Select.having` method is preferred, as it provides standard
        :term:`method chaining`.

        """
        if self._having is not None:
            self._having = and_(self._having, _literal_as_text(having))
        else:
            self._having = _literal_as_text(having)

    def append_from(self, fromclause):
        """append the given FromClause expression to this select() construct's
        FROM clause.

        This is an **in-place** mutation method; the
        :meth:`~.Select.select_from` method is preferred, as it provides standard
        :term:`method chaining`.

        """
        self._reset_exported()
        fromclause = _interpret_as_from(fromclause)
        self._from_obj = self._from_obj.union([fromclause])


    @_memoized_property
    def _columns_plus_names(self):
        if self.use_labels:
            names = set()
            def name_for_col(c):
                if c._label is None:
                    return (None, c)
                name = c._label
                if name in names:
                    name = c.anon_label
                else:
                    names.add(name)
                return name, c

            return [
                name_for_col(c)
                for c in util.unique_list(_select_iterables(self._raw_columns))
            ]
        else:
            return [
                (None, c)
                for c in util.unique_list(_select_iterables(self._raw_columns))
            ]

    def _populate_column_collection(self):
        for name, c in self._columns_plus_names:
            if not hasattr(c, '_make_proxy'):
                continue
            if name is None:
                key = None
            elif self.use_labels:
                key = c._key_label
                if key is not None and key in self.c:
                    key = c.anon_label
            else:
                key = None

            c._make_proxy(self, key=key,
                    name=name,
                    name_is_truncatable=True)

    def _refresh_for_new_column(self, column):
        for fromclause in self._froms:
            col = fromclause._refresh_for_new_column(column)
            if col is not None:
                if col in self.inner_columns and self._cols_populated:
                    our_label = col._key_label if self.use_labels else col.key
                    if our_label not in self.c:
                        return col._make_proxy(self,
                            name=col._label if self.use_labels else None,
                            key=col._key_label if self.use_labels else None,
                            name_is_truncatable=True)
                return None
        return None

    def self_group(self, against=None):
        """return a 'grouping' construct as per the ClauseElement
        specification.

        This produces an element that can be embedded in an expression. Note
        that this method is called automatically as needed when constructing
        expressions and should not require explicit use.

        """
        if isinstance(against, CompoundSelect):
            return self
        return FromGrouping(self)

    def union(self, other, **kwargs):
        """return a SQL UNION of this select() construct against the given
        selectable."""

        return CompoundSelect._create_union(self, other, **kwargs)

    def union_all(self, other, **kwargs):
        """return a SQL UNION ALL of this select() construct against the given
        selectable.

        """
        return CompoundSelect._create_union_all(self, other, **kwargs)

    def except_(self, other, **kwargs):
        """return a SQL EXCEPT of this select() construct against the given
        selectable."""

        return CompoundSelect._create_except(self, other, **kwargs)

    def except_all(self, other, **kwargs):
        """return a SQL EXCEPT ALL of this select() construct against the
        given selectable.

        """
        return CompoundSelect._create_except_all(self, other, **kwargs)

    def intersect(self, other, **kwargs):
        """return a SQL INTERSECT of this select() construct against the given
        selectable.

        """
        return CompoundSelect._create_intersect(self, other, **kwargs)

    def intersect_all(self, other, **kwargs):
        """return a SQL INTERSECT ALL of this select() construct against the
        given selectable.

        """
        return CompoundSelect._create_intersect_all(self, other, **kwargs)

    def bind(self):
        if self._bind:
            return self._bind
        froms = self._froms
        if not froms:
            for c in self._raw_columns:
                e = c.bind
                if e:
                    self._bind = e
                    return e
        else:
            e = list(froms)[0].bind
            if e:
                self._bind = e
                return e

        return None

    def _set_bind(self, bind):
        self._bind = bind
    bind = property(bind, _set_bind)


class ScalarSelect(Generative, Grouping):
    _from_objects = []

    def __init__(self, element):
        self.element = element
        self.type = element._scalar_type()

    @property
    def columns(self):
        raise exc.InvalidRequestError('Scalar Select expression has no '
                'columns; use this object directly within a '
                'column-level expression.')
    c = columns

    @_generative
    def where(self, crit):
        """Apply a WHERE clause to the SELECT statement referred to
        by this :class:`.ScalarSelect`.

        """
        self.element = self.element.where(crit)

    def self_group(self, **kwargs):
        return self


class Exists(UnaryExpression):
    """Represent an ``EXISTS`` clause.

    """
    __visit_name__ = UnaryExpression.__visit_name__
    _from_objects = []


    def __init__(self, *args, **kwargs):
        """Construct a new :class:`.Exists` against an existing
        :class:`.Select` object.

        Calling styles are of the following forms::

            # use on an existing select()
            s = select([table.c.col1]).where(table.c.col2==5)
            s = exists(s)

            # construct a select() at once
            exists(['*'], **select_arguments).where(criterion)

            # columns argument is optional, generates "EXISTS (SELECT *)"
            # by default.
            exists().where(table.c.col2==5)

        """
        if args and isinstance(args[0], (SelectBase, ScalarSelect)):
            s = args[0]
        else:
            if not args:
                args = ([literal_column('*')],)
            s = Select(*args, **kwargs).as_scalar().self_group()

        UnaryExpression.__init__(self, s, operator=operators.exists,
                                  type_=type_api.BOOLEANTYPE)

    def select(self, whereclause=None, **params):
        return Select([self], whereclause, **params)

    def correlate(self, *fromclause):
        e = self._clone()
        e.element = self.element.correlate(*fromclause).self_group()
        return e

    def correlate_except(self, *fromclause):
        e = self._clone()
        e.element = self.element.correlate_except(*fromclause).self_group()
        return e

    def select_from(self, clause):
        """return a new :class:`.Exists` construct, applying the given
        expression to the :meth:`.Select.select_from` method of the select
        statement contained.

        """
        e = self._clone()
        e.element = self.element.select_from(clause).self_group()
        return e

    def where(self, clause):
        """return a new exists() construct with the given expression added to
        its WHERE clause, joined to the existing clause via AND, if any.

        """
        e = self._clone()
        e.element = self.element.where(clause).self_group()
        return e


class AnnotatedFromClause(Annotated):
    def __init__(self, element, values):
        # force FromClause to generate their internal
        # collections into __dict__
        element.c
        Annotated.__init__(self, element, values)