diff options
| author | Pavan Deolasee | 2015-06-10 10:48:59 +0000 |
|---|---|---|
| committer | Pavan Deolasee | 2015-06-10 10:48:59 +0000 |
| commit | b2cf967bc4397b7954a02c18d3a8980eb2f16b48 (patch) | |
| tree | 775f106b464f0ec863802e0c2e752954532610e7 /src | |
| parent | 0259e9f0fa7a47750e59b4ea7ae0d56b228826ee (diff) | |
Fix alternate expected output file for testcase 'select_views'
Diffstat (limited to 'src')
| -rw-r--r-- | src/test/regress/expected/select_views_1.out | 352 |
1 files changed, 165 insertions, 187 deletions
diff --git a/src/test/regress/expected/select_views_1.out b/src/test/regress/expected/select_views_1.out index 5275ef0b2d..96b5fbfb06 100644 --- a/src/test/regress/expected/select_views_1.out +++ b/src/test/regress/expected/select_views_1.out @@ -2,9 +2,22 @@ -- SELECT_VIEWS -- test the views defined in CREATE_VIEWS -- -SELECT * FROM street; +SELECT * FROM street ORDER BY name,cname,thepath::text; name | thepath | cname ------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------- + 100th Ave | [(-122.1657,37.429),(-122.1647,37.432)] | Oakland + 107th Ave | [(-122.1555,37.403),(-122.1531,37.41)] | Oakland + 14th St | [(-122.299,37.147),(-122.3,37.148)] | Lafayette + 19th Ave | [(-122.2366,37.897),(-122.2359,37.905)] | Berkeley + 1st St | [(-121.75508,37.89294),(-121.753581,37.90031)] | Oakland + 5th St | [(-122.296,37.615),(-122.2953,37.598)] | Berkeley + 5th St | [(-122.278,37),(-122.2792,37.005),(-122.2803,37.009)] | Lafayette + 82nd Ave | [(-122.1695,37.596),(-122.1681,37.603)] | Berkeley + 85th Ave | [(-122.1877,37.466),(-122.186,37.476)] | Oakland + 89th Ave | [(-122.1822,37.459),(-122.1803,37.471)] | Oakland + 98th Ave | [(-122.2001,37.258),(-122.1974,37.27)] | Lafayette + 98th Ave | [(-122.1568,37.498),(-122.1558,37.502)] | Oakland + 98th Ave | [(-122.1693,37.438),(-122.1682,37.444)] | Oakland Access Rd 25 | [(-121.9283,37.894),(-121.9283,37.9)] | Oakland Ada St | [(-122.2487,37.398),(-122.2496,37.401)] | Lafayette Agua Fria Creek | [(-121.9254,37.922),(-121.9281,37.889)] | Oakland @@ -22,10 +35,10 @@ SELECT * FROM street; Arroyo Las Positas | [(-121.7973,37.997),(-121.7957,37.005)] | Oakland Arroyo Seco | [(-121.7073,37.766),(-121.6997,37.729)] | Oakland Ash St | [(-122.0408,37.31),(-122.04,37.292)] | Oakland - Avenue 134th | [(-122.1823,37.002),(-122.1851,37.992)] | Oakland Avenue 134th | [(-122.1823,37.002),(-122.1851,37.992)] | Berkeley - Avenue 140th | [(-122.1656,37.003),(-122.1691,37.988)] | Oakland + Avenue 134th | [(-122.1823,37.002),(-122.1851,37.992)] | Oakland Avenue 140th | [(-122.1656,37.003),(-122.1691,37.988)] | Berkeley + Avenue 140th | [(-122.1656,37.003),(-122.1691,37.988)] | Oakland Avenue D | [(-122.298,37.848),(-122.3024,37.849)] | Berkeley B St | [(-122.1749,37.451),(-122.1743,37.443)] | Oakland Bancroft Ave | [(-122.15714,37.4242),(-122.156,37.409)] | Oakland @@ -37,9 +50,9 @@ SELECT * FROM street; Broadmore Ave | [(-122.095,37.522),(-122.0936,37.497)] | Oakland Broadway | [(-122.2409,37.586),(-122.2395,37.601)] | Berkeley Buckingham Blvd | [(-122.2231,37.59),(-122.2214,37.606)] | Berkeley + Butterfield Dr | [(-122.0838,37.002),(-122.0834,37.987)] | Berkeley Butterfield Dr | [(-122.0838,37.002),(-122.0834,37.987)] | Oakland Butterfield Dr | [(-122.0838,37.002),(-122.0834,37.987)] | Oakland - Butterfield Dr | [(-122.0838,37.002),(-122.0834,37.987)] | Berkeley C St | [(-122.1768,37.46),(-122.1749,37.435)] | Oakland Calaveras Creek | [(-121.8203,37.035),(-121.8207,37.931)] | Oakland Calaveras Creek | [(-121.8203,37.035),(-121.8207,37.931)] | Oakland @@ -60,11 +73,11 @@ SELECT * FROM street; Chapman Dr | [(-122.0421,37.504),(-122.0414,37.498)] | Oakland Charles St | [(-122.0255,37.505),(-122.0252,37.499)] | Oakland Cherry St | [(-122.0437,37.42),(-122.0434,37.413)] | Oakland + Claremont Pl | [(-122.0542,37.995),(-122.0542,37.008)] | Berkeley Claremont Pl | [(-122.0542,37.995),(-122.0542,37.008)] | Oakland Claremont Pl | [(-122.0542,37.995),(-122.0542,37.008)] | Oakland - Claremont Pl | [(-122.0542,37.995),(-122.0542,37.008)] | Berkeley - Coliseum Way | [(-122.2001,37.47),(-122.1978,37.516)] | Oakland Coliseum Way | [(-122.2113,37.626),(-122.2085,37.592),(-122.2063,37.568)] | Berkeley + Coliseum Way | [(-122.2001,37.47),(-122.1978,37.516)] | Oakland Coolidge Ave | [(-122.2007,37.058),(-122.1992,37.06)] | Lafayette Cornell Ave | [(-122.2956,37.925),(-122.2949,37.906),(-122.2939,37.875)] | Berkeley Corriea Way | [(-121.9501,37.402),(-121.9505,37.398)] | Oakland @@ -77,9 +90,9 @@ SELECT * FROM street; Cull Canyon Road | [(-122.0536,37.435),(-122.0499,37.315)] | Oakland Cull Creek | [(-122.0624,37.875),(-122.0582,37.527)] | Berkeley D St | [(-122.1811,37.505),(-122.1805,37.497)] | Oakland + Decoto Road | [(-122.0159,37.006),(-122.016,37.002),(-122.0164,37.993)] | Berkeley Decoto Road | [(-122.0159,37.006),(-122.016,37.002),(-122.0164,37.993)] | Oakland Decoto Road | [(-122.0159,37.006),(-122.016,37.002),(-122.0164,37.993)] | Oakland - Decoto Road | [(-122.0159,37.006),(-122.016,37.002),(-122.0164,37.993)] | Berkeley Deering St | [(-122.2146,37.904),(-122.2126,37.897)] | Berkeley Dimond Ave | [(-122.2167,37.994),(-122.2162,37.006)] | Berkeley Dimond Ave | [(-122.2167,37.994),(-122.2162,37.006)] | Lafayette @@ -89,9 +102,9 @@ SELECT * FROM street; Driscoll Road | [(-121.9482,37.403),(-121.948451,37.39995)] | Oakland E St | [(-122.1832,37.505),(-122.1826,37.498),(-122.182,37.49)] | Oakland Eden Ave | [(-122.1143,37.505),(-122.1142,37.491)] | Oakland + Eden Creek | [(-122.022037,37.00675),(-122.0221,37.998)] | Berkeley Eden Creek | [(-122.022037,37.00675),(-122.0221,37.998)] | Oakland Eden Creek | [(-122.022037,37.00675),(-122.0221,37.998)] | Oakland - Eden Creek | [(-122.022037,37.00675),(-122.0221,37.998)] | Berkeley Edgewater Dr | [(-122.201,37.379),(-122.2042,37.41)] | Lafayette Enos Way | [(-121.7677,37.896),(-121.7673,37.91)] | Oakland Euclid Ave | [(-122.2671,37.009),(-122.2666,37.987)] | Berkeley @@ -106,23 +119,29 @@ SELECT * FROM street; Harris Road | [(-122.0659,37.372),(-122.0675,37.363)] | Oakland Heartwood Dr | [(-122.2006,37.341),(-122.1992,37.338)] | Lafayette Hegenberger Exwy | [(-122.1946,37.52),(-122.1947,37.497)] | Oakland - Herrier St | [(-122.1943,37.006),(-122.1936,37.998)] | Oakland Herrier St | [(-122.1943,37.006),(-122.1936,37.998)] | Berkeley + Herrier St | [(-122.1943,37.006),(-122.1936,37.998)] | Oakland + Hesperian Blvd | [(-122.1132,37.6),(-122.1123,37.586)] | Berkeley Hesperian Blvd | [(-122.097,37.333),(-122.0956,37.31),(-122.0946,37.293)] | Oakland Hesperian Blvd | [(-122.097,37.333),(-122.0956,37.31),(-122.0946,37.293)] | Oakland - Hesperian Blvd | [(-122.1132,37.6),(-122.1123,37.586)] | Berkeley Hollis St | [(-122.2885,37.397),(-122.289,37.414)] | Lafayette + I- 580 | [(-122.1108,37.023),(-122.1101,37.02),(-122.108103,37.00764),(-122.108,37.007),(-122.1069,37.998),(-122.1064,37.994),(-122.1053,37.982),(-122.1048,37.977),(-122.1032,37.958),(-122.1026,37.953),(-122.1013,37.938),(-122.0989,37.911),(-122.0984,37.91),(-122.098,37.908)] | Berkeley + I- 580 | [(-122.1543,37.703),(-122.1535,37.694),(-122.1512,37.655),(-122.1475,37.603),(-122.1468,37.583),(-122.1472,37.569),(-122.149044,37.54874),(-122.1493,37.546),(-122.1501,37.532),(-122.1506,37.509),(-122.1495,37.482),(-122.1487,37.467),(-122.1477,37.447),(-122.1414,37.383),(-122.1404,37.376),(-122.1398,37.372),(-122.139,37.356),(-122.1388,37.353),(-122.1385,37.34),(-122.1382,37.33),(-122.1378,37.316)] | Berkeley + I- 580 | [(-122.2197,37.99),(-122.22,37.99),(-122.222092,37.99523),(-122.2232,37.998),(-122.224146,37.99963),(-122.2261,37.003),(-122.2278,37.007),(-122.2302,37.026),(-122.2323,37.043),(-122.2344,37.059),(-122.235405,37.06427),(-122.2365,37.07)] | Berkeley + I- 580 | [(-122.2197,37.99),(-122.22,37.99),(-122.222092,37.99523),(-122.2232,37.998),(-122.224146,37.99963),(-122.2261,37.003),(-122.2278,37.007),(-122.2302,37.026),(-122.2323,37.043),(-122.2344,37.059),(-122.235405,37.06427),(-122.2365,37.07)] | Lafayette I- 580 | [(-121.727,37.074),(-121.7229,37.093),(-121.722301,37.09522),(-121.721001,37.10005),(-121.7194,37.106),(-121.7188,37.109),(-121.7168,37.12),(-121.7163,37.123),(-121.7145,37.127),(-121.7096,37.148),(-121.707731,37.1568),(-121.7058,37.166),(-121.7055,37.168),(-121.7044,37.174),(-121.7038,37.172),(-121.7037,37.172),(-121.7027,37.175),(-121.7001,37.181),(-121.6957,37.191),(-121.6948,37.192),(-121.6897,37.204),(-121.6697,37.185)] | Oakland I- 580 | [(-121.9322,37.989),(-121.9243,37.006),(-121.9217,37.014)] | Oakland I- 580 | [(-121.9322,37.989),(-121.9243,37.006),(-121.9217,37.014)] | Oakland I- 580 | [(-122.018,37.019),(-122.0009,37.032),(-121.9787,37.983),(-121.958,37.984),(-121.9571,37.986)] | Oakland I- 580 | [(-122.018,37.019),(-122.0009,37.032),(-121.9787,37.983),(-121.958,37.984),(-121.9571,37.986)] | Oakland I- 580 | [(-122.1108,37.023),(-122.1101,37.02),(-122.108103,37.00764),(-122.108,37.007),(-122.1069,37.998),(-122.1064,37.994),(-122.1053,37.982),(-122.1048,37.977),(-122.1032,37.958),(-122.1026,37.953),(-122.1013,37.938),(-122.0989,37.911),(-122.0984,37.91),(-122.098,37.908)] | Oakland - I- 580 | [(-122.1108,37.023),(-122.1101,37.02),(-122.108103,37.00764),(-122.108,37.007),(-122.1069,37.998),(-122.1064,37.994),(-122.1053,37.982),(-122.1048,37.977),(-122.1032,37.958),(-122.1026,37.953),(-122.1013,37.938),(-122.0989,37.911),(-122.0984,37.91),(-122.098,37.908)] | Berkeley I- 580 | [(-122.1543,37.703),(-122.1535,37.694),(-122.1512,37.655),(-122.1475,37.603),(-122.1468,37.583),(-122.1472,37.569),(-122.149044,37.54874),(-122.1493,37.546),(-122.1501,37.532),(-122.1506,37.509),(-122.1495,37.482),(-122.1487,37.467),(-122.1477,37.447),(-122.1414,37.383),(-122.1404,37.376),(-122.1398,37.372),(-122.139,37.356),(-122.1388,37.353),(-122.1385,37.34),(-122.1382,37.33),(-122.1378,37.316)] | Oakland - I- 580 | [(-122.1543,37.703),(-122.1535,37.694),(-122.1512,37.655),(-122.1475,37.603),(-122.1468,37.583),(-122.1472,37.569),(-122.149044,37.54874),(-122.1493,37.546),(-122.1501,37.532),(-122.1506,37.509),(-122.1495,37.482),(-122.1487,37.467),(-122.1477,37.447),(-122.1414,37.383),(-122.1404,37.376),(-122.1398,37.372),(-122.139,37.356),(-122.1388,37.353),(-122.1385,37.34),(-122.1382,37.33),(-122.1378,37.316)] | Berkeley - I- 580 | [(-122.2197,37.99),(-122.22,37.99),(-122.222092,37.99523),(-122.2232,37.998),(-122.224146,37.99963),(-122.2261,37.003),(-122.2278,37.007),(-122.2302,37.026),(-122.2323,37.043),(-122.2344,37.059),(-122.235405,37.06427),(-122.2365,37.07)] | Berkeley - I- 580 | [(-122.2197,37.99),(-122.22,37.99),(-122.222092,37.99523),(-122.2232,37.998),(-122.224146,37.99963),(-122.2261,37.003),(-122.2278,37.007),(-122.2302,37.026),(-122.2323,37.043),(-122.2344,37.059),(-122.235405,37.06427),(-122.2365,37.07)] | Lafayette + I- 580 Ramp | [(-122.093241,37.90351),(-122.09364,37.89634),(-122.093788,37.89212)] | Berkeley + I- 580 Ramp | [(-122.0934,37.896),(-122.09257,37.89961),(-122.0911,37.906)] | Berkeley + I- 580 Ramp | [(-122.0941,37.897),(-122.0943,37.902)] | Berkeley + I- 580 Ramp | [(-122.096,37.888),(-122.0962,37.891),(-122.0964,37.9)] | Berkeley + I- 580 Ramp | [(-122.101,37.898),(-122.1005,37.902),(-122.0989,37.911)] | Berkeley + I- 580 Ramp | [(-122.1086,37.003),(-122.1068,37.993),(-122.1066,37.992),(-122.1053,37.982)] | Berkeley I- 580 Ramp | [(-121.8521,37.011),(-121.8479,37.999),(-121.8476,37.999),(-121.8456,37.01),(-121.8455,37.011)] | Oakland I- 580 Ramp | [(-121.8521,37.011),(-121.8479,37.999),(-121.8476,37.999),(-121.8456,37.01),(-121.8455,37.011)] | Oakland I- 580 Ramp | [(-121.8743,37.014),(-121.8722,37.999),(-121.8714,37.999)] | Oakland @@ -131,13 +150,7 @@ SELECT * FROM street; I- 580 Ramp | [(-121.9043,37.998),(-121.9036,37.013),(-121.902632,37.0174),(-121.9025,37.018)] | Oakland I- 580 Ramp | [(-121.9368,37.986),(-121.936483,37.98832),(-121.9353,37.997),(-121.93504,37.00035),(-121.9346,37.006),(-121.933764,37.00031),(-121.9333,37.997),(-121.9322,37.989)] | Oakland I- 580 Ramp | [(-121.9368,37.986),(-121.936483,37.98832),(-121.9353,37.997),(-121.93504,37.00035),(-121.9346,37.006),(-121.933764,37.00031),(-121.9333,37.997),(-121.9322,37.989)] | Oakland - I- 580 Ramp | [(-122.093241,37.90351),(-122.09364,37.89634),(-122.093788,37.89212)] | Berkeley - I- 580 Ramp | [(-122.0934,37.896),(-122.09257,37.89961),(-122.0911,37.906)] | Berkeley - I- 580 Ramp | [(-122.0941,37.897),(-122.0943,37.902)] | Berkeley - I- 580 Ramp | [(-122.096,37.888),(-122.0962,37.891),(-122.0964,37.9)] | Berkeley - I- 580 Ramp | [(-122.101,37.898),(-122.1005,37.902),(-122.0989,37.911)] | Berkeley I- 580 Ramp | [(-122.1086,37.003),(-122.1068,37.993),(-122.1066,37.992),(-122.1053,37.982)] | Oakland - I- 580 Ramp | [(-122.1086,37.003),(-122.1068,37.993),(-122.1066,37.992),(-122.1053,37.982)] | Berkeley I- 580 Ramp | [(-122.1414,37.383),(-122.1407,37.376),(-122.1403,37.372),(-122.139,37.356)] | Oakland I- 580/I-680 Ramp | ((-121.9207,37.988),(-121.9192,37.016)) | Oakland I- 580/I-680 Ramp | ((-121.9207,37.988),(-121.9192,37.016)) | Oakland @@ -154,22 +167,29 @@ SELECT * FROM street; I- 80 | ((-122.2937,37.277),(-122.3016,37.262)) | Lafayette I- 80 | ((-122.2962,37.273),(-122.3004,37.264)) | Lafayette I- 80 Ramp | [(-122.2962,37.413),(-122.2959,37.382),(-122.2951,37.372)] | Lafayette + I- 880 | [(-122.0375,37.632),(-122.0359,37.619),(-122.0358,37.616),(-122.034514,37.60409),(-122.031876,37.57965),(-122.031193,37.57332),(-122.03016,37.56375),(-122.02943,37.55698),(-122.028689,37.54929),(-122.027833,37.53908),(-122.025979,37.51698),(-122.0238,37.491)] | Berkeley + I- 880 | [(-122.0612,37.003),(-122.0604,37.991),(-122.0596,37.982),(-122.0585,37.967),(-122.0583,37.961),(-122.0553,37.918),(-122.053635,37.89475),(-122.050759,37.8546),(-122.05,37.844),(-122.0485,37.817),(-122.0483,37.813),(-122.0482,37.811)] | Berkeley + I- 880 | [(-122.1365,37.902),(-122.1358,37.898),(-122.1333,37.881),(-122.1323,37.874),(-122.1311,37.866),(-122.1308,37.865),(-122.1307,37.864),(-122.1289,37.851),(-122.1277,37.843),(-122.1264,37.834),(-122.1231,37.812),(-122.1165,37.766),(-122.1104,37.72),(-122.109695,37.71094),(-122.109,37.702),(-122.108312,37.69168),(-122.1076,37.681)] | Berkeley + I- 880 | [(-122.1755,37.185),(-122.1747,37.178),(-122.1742,37.173),(-122.1692,37.126),(-122.167792,37.11594),(-122.16757,37.11435),(-122.1671,37.111),(-122.1655,37.1),(-122.165169,37.09811),(-122.1641,37.092),(-122.1596,37.061),(-122.158381,37.05275),(-122.155991,37.03657),(-122.1531,37.017),(-122.1478,37.98),(-122.1407,37.932),(-122.1394,37.924),(-122.1389,37.92),(-122.1376,37.91)] | Berkeley + I- 880 | [(-122.2214,37.711),(-122.2202,37.699),(-122.2199,37.695),(-122.219,37.682),(-122.2184,37.672),(-122.2173,37.652),(-122.2159,37.638),(-122.2144,37.616),(-122.2138,37.612),(-122.2135,37.609),(-122.212,37.592),(-122.2116,37.586),(-122.2111,37.581)] | Berkeley + I- 880 | [(-122.2707,37.975),(-122.2693,37.972),(-122.2681,37.966),(-122.267,37.962),(-122.2659,37.957),(-122.2648,37.952),(-122.2636,37.946),(-122.2625,37.935),(-122.2617,37.927),(-122.2607,37.921),(-122.2593,37.916),(-122.258,37.911),(-122.2536,37.898),(-122.2432,37.858),(-122.2408,37.845),(-122.2386,37.827),(-122.2374,37.811)] | Berkeley I- 880 | ((-121.9669,37.075),(-121.9663,37.071),(-121.9656,37.065),(-121.9618,37.037),(-121.95689,37),(-121.948,37.933)) | Oakland I- 880 | ((-121.9669,37.075),(-121.9663,37.071),(-121.9656,37.065),(-121.9618,37.037),(-121.95689,37),(-121.948,37.933)) | Oakland I- 880 | [(-121.948,37.933),(-121.9471,37.925),(-121.9467,37.923),(-121.946,37.918),(-121.9452,37.912),(-121.937,37.852)] | Oakland I- 880 | [(-122.0219,37.466),(-122.0205,37.447),(-122.020331,37.44447),(-122.020008,37.43962),(-122.0195,37.432),(-122.0193,37.429),(-122.0164,37.393),(-122.010219,37.34771),(-122.0041,37.313)] | Oakland I- 880 | [(-122.0375,37.632),(-122.0359,37.619),(-122.0358,37.616),(-122.034514,37.60409),(-122.031876,37.57965),(-122.031193,37.57332),(-122.03016,37.56375),(-122.02943,37.55698),(-122.028689,37.54929),(-122.027833,37.53908),(-122.025979,37.51698),(-122.0238,37.491)] | Oakland - I- 880 | [(-122.0375,37.632),(-122.0359,37.619),(-122.0358,37.616),(-122.034514,37.60409),(-122.031876,37.57965),(-122.031193,37.57332),(-122.03016,37.56375),(-122.02943,37.55698),(-122.028689,37.54929),(-122.027833,37.53908),(-122.025979,37.51698),(-122.0238,37.491)] | Berkeley I- 880 | [(-122.0612,37.003),(-122.0604,37.991),(-122.0596,37.982),(-122.0585,37.967),(-122.0583,37.961),(-122.0553,37.918),(-122.053635,37.89475),(-122.050759,37.8546),(-122.05,37.844),(-122.0485,37.817),(-122.0483,37.813),(-122.0482,37.811)] | Oakland I- 880 | [(-122.0612,37.003),(-122.0604,37.991),(-122.0596,37.982),(-122.0585,37.967),(-122.0583,37.961),(-122.0553,37.918),(-122.053635,37.89475),(-122.050759,37.8546),(-122.05,37.844),(-122.0485,37.817),(-122.0483,37.813),(-122.0482,37.811)] | Oakland - I- 880 | [(-122.0612,37.003),(-122.0604,37.991),(-122.0596,37.982),(-122.0585,37.967),(-122.0583,37.961),(-122.0553,37.918),(-122.053635,37.89475),(-122.050759,37.8546),(-122.05,37.844),(-122.0485,37.817),(-122.0483,37.813),(-122.0482,37.811)] | Berkeley I- 880 | [(-122.0831,37.312),(-122.0819,37.296),(-122.081,37.285),(-122.0786,37.248),(-122.078,37.24),(-122.077642,37.23496),(-122.076983,37.22567),(-122.076599,37.22026),(-122.076229,37.21505),(-122.0758,37.209)] | Oakland I- 880 | [(-122.0978,37.528),(-122.096,37.496),(-122.0931,37.453),(-122.09277,37.4496),(-122.090189,37.41442),(-122.0896,37.405),(-122.085,37.34)] | Oakland - I- 880 | [(-122.1365,37.902),(-122.1358,37.898),(-122.1333,37.881),(-122.1323,37.874),(-122.1311,37.866),(-122.1308,37.865),(-122.1307,37.864),(-122.1289,37.851),(-122.1277,37.843),(-122.1264,37.834),(-122.1231,37.812),(-122.1165,37.766),(-122.1104,37.72),(-122.109695,37.71094),(-122.109,37.702),(-122.108312,37.69168),(-122.1076,37.681)] | Berkeley I- 880 | [(-122.1755,37.185),(-122.1747,37.178),(-122.1742,37.173),(-122.1692,37.126),(-122.167792,37.11594),(-122.16757,37.11435),(-122.1671,37.111),(-122.1655,37.1),(-122.165169,37.09811),(-122.1641,37.092),(-122.1596,37.061),(-122.158381,37.05275),(-122.155991,37.03657),(-122.1531,37.017),(-122.1478,37.98),(-122.1407,37.932),(-122.1394,37.924),(-122.1389,37.92),(-122.1376,37.91)] | Oakland - I- 880 | [(-122.1755,37.185),(-122.1747,37.178),(-122.1742,37.173),(-122.1692,37.126),(-122.167792,37.11594),(-122.16757,37.11435),(-122.1671,37.111),(-122.1655,37.1),(-122.165169,37.09811),(-122.1641,37.092),(-122.1596,37.061),(-122.158381,37.05275),(-122.155991,37.03657),(-122.1531,37.017),(-122.1478,37.98),(-122.1407,37.932),(-122.1394,37.924),(-122.1389,37.92),(-122.1376,37.91)] | Berkeley - I- 880 | [(-122.2214,37.711),(-122.2202,37.699),(-122.2199,37.695),(-122.219,37.682),(-122.2184,37.672),(-122.2173,37.652),(-122.2159,37.638),(-122.2144,37.616),(-122.2138,37.612),(-122.2135,37.609),(-122.212,37.592),(-122.2116,37.586),(-122.2111,37.581)] | Berkeley - I- 880 | [(-122.2707,37.975),(-122.2693,37.972),(-122.2681,37.966),(-122.267,37.962),(-122.2659,37.957),(-122.2648,37.952),(-122.2636,37.946),(-122.2625,37.935),(-122.2617,37.927),(-122.2607,37.921),(-122.2593,37.916),(-122.258,37.911),(-122.2536,37.898),(-122.2432,37.858),(-122.2408,37.845),(-122.2386,37.827),(-122.2374,37.811)] | Berkeley + I- 880 Ramp | [(-122.059,37.982),(-122.0577,37.984),(-122.0612,37.003)] | Berkeley + I- 880 Ramp | [(-122.0618,37.011),(-122.0631,37.982),(-122.0585,37.967)] | Berkeley + I- 880 Ramp | [(-122.1029,37.61),(-122.1013,37.587),(-122.0999,37.569)] | Berkeley + I- 880 Ramp | [(-122.1379,37.891),(-122.1383,37.897),(-122.1377,37.902)] | Berkeley + I- 880 Ramp | [(-122.1379,37.931),(-122.137597,37.92736),(-122.1374,37.925),(-122.1373,37.924),(-122.1369,37.914),(-122.1358,37.905),(-122.1365,37.908),(-122.1358,37.898)] | Berkeley + I- 880 Ramp | [(-122.2536,37.898),(-122.254,37.902)] | Berkeley + I- 880 Ramp | [(-122.2771,37.002),(-122.278,37)] | Lafayette I- 880 Ramp | [(-122.0019,37.301),(-122.002,37.293)] | Oakland I- 880 Ramp | [(-122.0041,37.313),(-122.0018,37.315),(-122.0007,37.315),(-122.0005,37.313),(-122.0002,37.308),(-121.9995,37.289)] | Oakland I- 880 Ramp | [(-122.0041,37.313),(-122.0038,37.308),(-122.0039,37.284),(-122.0013,37.287),(-121.9995,37.289)] | Oakland @@ -177,19 +197,12 @@ SELECT * FROM street; I- 880 Ramp | [(-122.0238,37.491),(-122.0215,37.483),(-122.0211,37.477),(-122.0205,37.447)] | Oakland I- 880 Ramp | [(-122.059,37.982),(-122.0577,37.984),(-122.0612,37.003)] | Oakland I- 880 Ramp | [(-122.059,37.982),(-122.0577,37.984),(-122.0612,37.003)] | Oakland - I- 880 Ramp | [(-122.059,37.982),(-122.0577,37.984),(-122.0612,37.003)] | Berkeley I- 880 Ramp | [(-122.0618,37.011),(-122.0631,37.982),(-122.0585,37.967)] | Oakland I- 880 Ramp | [(-122.0618,37.011),(-122.0631,37.982),(-122.0585,37.967)] | Oakland - I- 880 Ramp | [(-122.0618,37.011),(-122.0631,37.982),(-122.0585,37.967)] | Berkeley I- 880 Ramp | [(-122.085,37.34),(-122.0801,37.316),(-122.081,37.285)] | Oakland I- 880 Ramp | [(-122.085,37.34),(-122.0801,37.316),(-122.081,37.285)] | Oakland I- 880 Ramp | [(-122.085,37.34),(-122.0866,37.316),(-122.0819,37.296)] | Oakland I- 880 Ramp | [(-122.085,37.34),(-122.0866,37.316),(-122.0819,37.296)] | Oakland - I- 880 Ramp | [(-122.1029,37.61),(-122.1013,37.587),(-122.0999,37.569)] | Berkeley - I- 880 Ramp | [(-122.1379,37.891),(-122.1383,37.897),(-122.1377,37.902)] | Berkeley - I- 880 Ramp | [(-122.1379,37.931),(-122.137597,37.92736),(-122.1374,37.925),(-122.1373,37.924),(-122.1369,37.914),(-122.1358,37.905),(-122.1365,37.908),(-122.1358,37.898)] | Berkeley - I- 880 Ramp | [(-122.2536,37.898),(-122.254,37.902)] | Berkeley - I- 880 Ramp | [(-122.2771,37.002),(-122.278,37)] | Lafayette Indian Way | [(-122.2066,37.398),(-122.2045,37.411)] | Lafayette Jackson St | [(-122.0845,37.6),(-122.0842,37.606)] | Berkeley Johnson Dr | [(-121.9145,37.901),(-121.915,37.877)] | Oakland @@ -212,26 +225,26 @@ SELECT * FROM street; Livermore Ave | [(-121.7687,37.448),(-121.769,37.375)] | Oakland Livermore Ave | [(-121.772719,37.99085),(-121.7728,37.001)] | Oakland Livermore Ave | [(-121.772719,37.99085),(-121.7728,37.001)] | Oakland - Locust St | [(-122.1606,37.007),(-122.1593,37.987)] | Oakland Locust St | [(-122.1606,37.007),(-122.1593,37.987)] | Berkeley + Locust St | [(-122.1606,37.007),(-122.1593,37.987)] | Oakland Logan Ct | [(-122.0053,37.492),(-122.0061,37.484)] | Oakland Magnolia St | [(-122.0971,37.5),(-122.0962,37.484)] | Oakland Mandalay Road | [(-122.2322,37.397),(-122.2321,37.403)] | Lafayette Marin Ave | [(-122.2741,37.894),(-122.272,37.901)] | Berkeley Martin Luther King Jr Way | [(-122.2712,37.608),(-122.2711,37.599)] | Berkeley Mattos Dr | [(-122.0005,37.502),(-122.000898,37.49683)] | Oakland - Maubert Ave | [(-122.1114,37.009),(-122.1096,37.995)] | Oakland Maubert Ave | [(-122.1114,37.009),(-122.1096,37.995)] | Berkeley - McClure Ave | [(-122.1431,37.001),(-122.1436,37.998)] | Oakland + Maubert Ave | [(-122.1114,37.009),(-122.1096,37.995)] | Oakland McClure Ave | [(-122.1431,37.001),(-122.1436,37.998)] | Berkeley + McClure Ave | [(-122.1431,37.001),(-122.1436,37.998)] | Oakland Medlar Dr | [(-122.0627,37.378),(-122.0625,37.375)] | Oakland Mildred Ct | [(-122.0002,37.388),(-121.9998,37.386)] | Oakland Miller Road | [(-122.0902,37.645),(-122.0865,37.545)] | Berkeley Miramar Ave | [(-122.1009,37.025),(-122.099089,37.03209)] | Oakland + Mission Blvd | [(-122.0006,37.896),(-121.9989,37.88)] | Berkeley Mission Blvd | [(-121.918886,37),(-121.9194,37.976),(-121.9198,37.975)] | Oakland Mission Blvd | [(-121.918886,37),(-121.9194,37.976),(-121.9198,37.975)] | Oakland Mission Blvd | [(-122.0006,37.896),(-121.9989,37.88)] | Oakland - Mission Blvd | [(-122.0006,37.896),(-121.9989,37.88)] | Berkeley Moores Ave | [(-122.0087,37.301),(-122.0094,37.292)] | Oakland National Ave | [(-122.1192,37.5),(-122.1281,37.489)] | Oakland Navajo Ct | [(-121.8779,37.901),(-121.8783,37.9)] | Oakland @@ -241,50 +254,50 @@ SELECT * FROM street; Oneil Ave | [(-122.076754,37.62476),(-122.0745,37.595)] | Berkeley Parkridge Dr | [(-122.1438,37.884),(-122.1428,37.9)] | Berkeley Parkside Dr | [(-122.0475,37.603),(-122.0443,37.596)] | Berkeley + Paseo Padre Pkwy | [(-122.0021,37.639),(-121.996,37.628)] | Berkeley Paseo Padre Pkwy | [(-121.9143,37.005),(-121.913522,37)] | Oakland Paseo Padre Pkwy | [(-122.0021,37.639),(-121.996,37.628)] | Oakland - Paseo Padre Pkwy | [(-122.0021,37.639),(-121.996,37.628)] | Berkeley Pearl St | [(-122.2383,37.594),(-122.2366,37.615)] | Berkeley Periwinkle Road | [(-122.0451,37.301),(-122.044758,37.29844)] | Oakland Pimlico Dr | [(-121.8616,37.998),(-121.8618,37.008)] | Oakland Pimlico Dr | [(-121.8616,37.998),(-121.8618,37.008)] | Oakland Portsmouth Ave | [(-122.1064,37.315),(-122.1064,37.308)] | Oakland Proctor Ave | [(-122.2267,37.406),(-122.2251,37.386)] | Lafayette + Railroad Ave | [(-122.0245,37.013),(-122.0234,37.003),(-122.0223,37.993)] | Berkeley Railroad Ave | [(-122.0245,37.013),(-122.0234,37.003),(-122.0223,37.993)] | Oakland Railroad Ave | [(-122.0245,37.013),(-122.0234,37.003),(-122.0223,37.993)] | Oakland - Railroad Ave | [(-122.0245,37.013),(-122.0234,37.003),(-122.0223,37.993)] | Berkeley + Ranspot Dr | [(-122.0972,37.999),(-122.0959,37)] | Berkeley Ranspot Dr | [(-122.0972,37.999),(-122.0959,37)] | Oakland Ranspot Dr | [(-122.0972,37.999),(-122.0959,37)] | Oakland - Ranspot Dr | [(-122.0972,37.999),(-122.0959,37)] | Berkeley Redding St | [(-122.1978,37.901),(-122.1975,37.895)] | Berkeley - Redwood Road | [(-122.1493,37.98),(-122.1437,37.001)] | Oakland Redwood Road | [(-122.1493,37.98),(-122.1437,37.001)] | Berkeley + Redwood Road | [(-122.1493,37.98),(-122.1437,37.001)] | Oakland Roca Dr | [(-122.0335,37.609),(-122.0314,37.599)] | Berkeley Rosedale Ct | [(-121.9232,37.9),(-121.924,37.897)] | Oakland Sacramento St | [(-122.2799,37.606),(-122.2797,37.597)] | Berkeley Saddle Brook Dr | [(-122.1478,37.909),(-122.1454,37.904),(-122.1451,37.888)] | Berkeley Saginaw Ct | [(-121.8803,37.898),(-121.8806,37.901)] | Oakland San Andreas Dr | [(-122.0609,37.9),(-122.0614,37.895)] | Berkeley + Santa Maria Ave | [(-122.0773,37),(-122.0773,37.98)] | Berkeley Santa Maria Ave | [(-122.0773,37),(-122.0773,37.98)] | Oakland Santa Maria Ave | [(-122.0773,37),(-122.0773,37.98)] | Oakland - Santa Maria Ave | [(-122.0773,37),(-122.0773,37.98)] | Berkeley Shattuck Ave | [(-122.2686,37.904),(-122.2686,37.897)] | Berkeley Sheridan Road | [(-122.2279,37.425),(-122.2253,37.411),(-122.2223,37.377)] | Lafayette Shoreline Dr | [(-122.2657,37.603),(-122.2648,37.6)] | Berkeley - Skyline Blvd | [(-122.1738,37.01),(-122.1714,37.996)] | Oakland Skyline Blvd | [(-122.1738,37.01),(-122.1714,37.996)] | Berkeley + Skyline Blvd | [(-122.1738,37.01),(-122.1714,37.996)] | Oakland Skyline Dr | [(-122.0277,37.5),(-122.0284,37.498)] | Oakland Skywest Dr | [(-122.1161,37.62),(-122.1123,37.586)] | Berkeley Southern Pacific Railroad | [(-122.3002,37.674),(-122.2999,37.661)] | Berkeley + Sp Railroad | [(-122.0734,37.001),(-122.0734,37.997)] | Berkeley + Sp Railroad | [(-122.0914,37.601),(-122.087,37.56),(-122.086408,37.5551)] | Berkeley + Sp Railroad | [(-122.137792,37.003),(-122.1365,37.992),(-122.131257,37.94612)] | Berkeley Sp Railroad | [(-121.893564,37.99009),(-121.897,37.016)] | Oakland Sp Railroad | [(-121.893564,37.99009),(-121.897,37.016)] | Oakland Sp Railroad | [(-121.9565,37.898),(-121.9562,37.9)] | Oakland Sp Railroad | [(-122.0734,37.001),(-122.0734,37.997)] | Oakland Sp Railroad | [(-122.0734,37.001),(-122.0734,37.997)] | Oakland - Sp Railroad | [(-122.0734,37.001),(-122.0734,37.997)] | Berkeley - Sp Railroad | [(-122.0914,37.601),(-122.087,37.56),(-122.086408,37.5551)] | Berkeley Sp Railroad | [(-122.137792,37.003),(-122.1365,37.992),(-122.131257,37.94612)] | Oakland - Sp Railroad | [(-122.137792,37.003),(-122.1365,37.992),(-122.131257,37.94612)] | Berkeley Sp Railroad | [(-122.1947,37.497),(-122.193328,37.4848)] | Oakland Stanton Ave | [(-122.100392,37.0697),(-122.099513,37.06052)] | Oakland State Hwy 123 | [(-122.3004,37.986),(-122.2998,37.969),(-122.2995,37.962),(-122.2992,37.952),(-122.299,37.942),(-122.2987,37.935),(-122.2984,37.924),(-122.2982,37.92),(-122.2976,37.904),(-122.297,37.88),(-122.2966,37.869),(-122.2959,37.848),(-122.2961,37.843)] | Berkeley @@ -316,28 +329,15 @@ SELECT * FROM street; Welch Creek Road | [(-121.7695,37.386),(-121.7737,37.413)] | Oakland Welch Creek Road | [(-121.7695,37.386),(-121.7737,37.413)] | Oakland West Loop Road | [(-122.0576,37.604),(-122.0602,37.586)] | Berkeley + Western Pacific Railroad Spur | [(-122.0394,37.018),(-122.0394,37.961)] | Berkeley Western Pacific Railroad Spur | [(-122.0394,37.018),(-122.0394,37.961)] | Oakland Western Pacific Railroad Spur | [(-122.0394,37.018),(-122.0394,37.961)] | Oakland - Western Pacific Railroad Spur | [(-122.0394,37.018),(-122.0394,37.961)] | Berkeley Whitlock Creek | [(-121.74683,37.91276),(-121.733107,37)] | Oakland Whitlock Creek | [(-121.74683,37.91276),(-121.733107,37)] | Oakland Willimet Way | [(-122.0964,37.517),(-122.0949,37.493)] | Oakland - Wisconsin St | [(-122.1994,37.017),(-122.1975,37.998),(-122.1971,37.994)] | Oakland Wisconsin St | [(-122.1994,37.017),(-122.1975,37.998),(-122.1971,37.994)] | Berkeley + Wisconsin St | [(-122.1994,37.017),(-122.1975,37.998),(-122.1971,37.994)] | Oakland Wp Railroad | [(-122.254,37.902),(-122.2506,37.891)] | Berkeley - 100th Ave | [(-122.1657,37.429),(-122.1647,37.432)] | Oakland - 107th Ave | [(-122.1555,37.403),(-122.1531,37.41)] | Oakland - 14th St | [(-122.299,37.147),(-122.3,37.148)] | Lafayette - 19th Ave | [(-122.2366,37.897),(-122.2359,37.905)] | Berkeley - 1st St | [(-121.75508,37.89294),(-121.753581,37.90031)] | Oakland - 5th St | [(-122.278,37),(-122.2792,37.005),(-122.2803,37.009)] | Lafayette - 5th St | [(-122.296,37.615),(-122.2953,37.598)] | Berkeley - 82nd Ave | [(-122.1695,37.596),(-122.1681,37.603)] | Berkeley - 85th Ave | [(-122.1877,37.466),(-122.186,37.476)] | Oakland - 89th Ave | [(-122.1822,37.459),(-122.1803,37.471)] | Oakland - 98th Ave | [(-122.1568,37.498),(-122.1558,37.502)] | Oakland - 98th Ave | [(-122.1693,37.438),(-122.1682,37.444)] | Oakland - 98th Ave | [(-122.2001,37.258),(-122.1974,37.27)] | Lafayette (333 rows) SELECT name, #thepath FROM iexit ORDER BY 1, 2; @@ -467,20 +467,6 @@ SELECT name, #thepath FROM iexit ORDER BY 1, 2; I- 580 | 21 I- 580 | 22 I- 580 | 22 - I- 580/I-680 Ramp | 2 - I- 580/I-680 Ramp | 2 - I- 580/I-680 Ramp | 2 - I- 580/I-680 Ramp | 2 - I- 580/I-680 Ramp | 2 - I- 580/I-680 Ramp | 2 - I- 580/I-680 Ramp | 4 - I- 580/I-680 Ramp | 4 - I- 580/I-680 Ramp | 4 - I- 580/I-680 Ramp | 4 - I- 580/I-680 Ramp | 5 - I- 580/I-680 Ramp | 6 - I- 580/I-680 Ramp | 6 - I- 580/I-680 Ramp | 6 I- 580 Ramp | 2 I- 580 Ramp | 2 I- 580 Ramp | 2 @@ -731,6 +717,20 @@ SELECT name, #thepath FROM iexit ORDER BY 1, 2; I- 580 Ramp | 8 I- 580 Ramp | 8 I- 580 Ramp | 8 + I- 580/I-680 Ramp | 2 + I- 580/I-680 Ramp | 2 + I- 580/I-680 Ramp | 2 + I- 580/I-680 Ramp | 2 + I- 580/I-680 Ramp | 2 + I- 580/I-680 Ramp | 2 + I- 580/I-680 Ramp | 4 + I- 580/I-680 Ramp | 4 + I- 580/I-680 Ramp | 4 + I- 580/I-680 Ramp | 4 + I- 580/I-680 Ramp | 5 + I- 580/I-680 Ramp | 6 + I- 580/I-680 Ramp | 6 + I- 580/I-680 Ramp | 6 I- 680 | 2 I- 680 | 2 I- 680 | 2 @@ -1317,36 +1317,34 @@ SET SESSION AUTHORIZATION regress_alice; -- prior to the security policy of the view. -- SELECT * FROM my_property_normal WHERE f_leak(passwd); -NOTICE: f_leak => passwd123 -NOTICE: f_leak => beafsteak -NOTICE: f_leak => hamburger cid | name | tel | passwd -----+---------------+------------------+----------- 101 | regress_alice | +81-12-3456-7890 | passwd123 (1 row) EXPLAIN (COSTS OFF) SELECT * FROM my_property_normal WHERE f_leak(passwd); - QUERY PLAN ------------------------------------------------------------------- - Seq Scan on customer - Filter: (f_leak(passwd) AND (name = ("current_user"())::text)) -(2 rows) + QUERY PLAN +------------------------------------------------------------------------ + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Seq Scan on customer + Filter: (f_leak(passwd) AND (name = ("current_user"())::text)) +(3 rows) SELECT * FROM my_property_secure WHERE f_leak(passwd); -NOTICE: f_leak => passwd123 cid | name | tel | passwd -----+---------------+------------------+----------- 101 | regress_alice | +81-12-3456-7890 | passwd123 (1 row) EXPLAIN (COSTS OFF) SELECT * FROM my_property_secure WHERE f_leak(passwd); - QUERY PLAN ---------------------------------------------------- - Subquery Scan on my_property_secure - Filter: f_leak(my_property_secure.passwd) - -> Seq Scan on customer - Filter: (name = ("current_user"())::text) -(4 rows) + QUERY PLAN +--------------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Subquery Scan on my_property_secure + Filter: f_leak(my_property_secure.passwd) + -> Seq Scan on customer + Filter: (name = ("current_user"())::text) +(5 rows) -- -- scenario: qualifiers can be pushed down if they contain leaky functions, @@ -1354,12 +1352,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM my_property_secure WHERE f_leak(passwd); -- SELECT * FROM my_property_normal v WHERE f_leak('passwd') AND f_leak(passwd); -NOTICE: f_leak => passwd -NOTICE: f_leak => passwd123 -NOTICE: f_leak => passwd -NOTICE: f_leak => beafsteak -NOTICE: f_leak => passwd -NOTICE: f_leak => hamburger cid | name | tel | passwd -----+---------------+------------------+----------- 101 | regress_alice | +81-12-3456-7890 | passwd123 @@ -1367,18 +1359,15 @@ NOTICE: f_leak => hamburger EXPLAIN (COSTS OFF) SELECT * FROM my_property_normal v WHERE f_leak('passwd') AND f_leak(passwd); - QUERY PLAN ---------------------------------------------------------------------------------------------- - Seq Scan on customer - Filter: (f_leak('passwd'::text) AND f_leak(passwd) AND (name = ("current_user"())::text)) -(2 rows) + QUERY PLAN +--------------------------------------------------------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Seq Scan on customer + Filter: (f_leak('passwd'::text) AND f_leak(passwd) AND (name = ("current_user"())::text)) +(3 rows) SELECT * FROM my_property_secure v WHERE f_leak('passwd') AND f_leak(passwd); -NOTICE: f_leak => passwd -NOTICE: f_leak => passwd123 -NOTICE: f_leak => passwd -NOTICE: f_leak => passwd cid | name | tel | passwd -----+---------------+------------------+----------- 101 | regress_alice | +81-12-3456-7890 | passwd123 @@ -1386,13 +1375,14 @@ NOTICE: f_leak => passwd EXPLAIN (COSTS OFF) SELECT * FROM my_property_secure v WHERE f_leak('passwd') AND f_leak(passwd); - QUERY PLAN --------------------------------------------------------------------------------- - Subquery Scan on v - Filter: f_leak(v.passwd) - -> Seq Scan on customer - Filter: (f_leak('passwd'::text) AND (name = ("current_user"())::text)) -(4 rows) + QUERY PLAN +-------------------------------------------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Subquery Scan on v + Filter: f_leak(v.passwd) + -> Seq Scan on customer + Filter: (f_leak('passwd'::text) AND (name = ("current_user"())::text)) +(5 rows) -- -- scenario: if a qualifier references only one-side of a particular join- @@ -1400,46 +1390,44 @@ EXPLAIN (COSTS OFF) SELECT * FROM my_property_secure v -- possible as we can. -- SELECT * FROM my_credit_card_normal WHERE f_leak(cnum); -NOTICE: f_leak => 1111-2222-3333-4444 -NOTICE: f_leak => 5555-6666-7777-8888 -NOTICE: f_leak => 9801-2345-6789-0123 cid | name | tel | passwd | cnum | climit -----+---------------+------------------+-----------+---------------------+-------- 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 (1 row) EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_normal WHERE f_leak(cnum); - QUERY PLAN ---------------------------------------------------------- - Hash Join - Hash Cond: (r.cid = l.cid) - -> Seq Scan on credit_card r - Filter: f_leak(cnum) - -> Hash - -> Seq Scan on customer l - Filter: (name = ("current_user"())::text) -(7 rows) - -SELECT * FROM my_credit_card_secure WHERE f_leak(cnum); -NOTICE: f_leak => 1111-2222-3333-4444 - cid | name | tel | passwd | cnum | climit ------+---------------+------------------+-----------+---------------------+-------- - 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 -(1 row) - -EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_secure WHERE f_leak(cnum); QUERY PLAN --------------------------------------------------------------- - Subquery Scan on my_credit_card_secure - Filter: f_leak(my_credit_card_secure.cnum) + Remote Subquery Scan on all (datanode_1,datanode_2) -> Hash Join Hash Cond: (r.cid = l.cid) -> Seq Scan on credit_card r + Filter: f_leak(cnum) -> Hash -> Seq Scan on customer l Filter: (name = ("current_user"())::text) (8 rows) +SELECT * FROM my_credit_card_secure WHERE f_leak(cnum); + cid | name | tel | passwd | cnum | climit +-----+---------------+------------------+-----------+---------------------+-------- + 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_secure WHERE f_leak(cnum); + QUERY PLAN +--------------------------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Subquery Scan on my_credit_card_secure + Filter: f_leak(my_credit_card_secure.cnum) + -> Hash Join + Hash Cond: (r.cid = l.cid) + -> Seq Scan on credit_card r + -> Hash + -> Seq Scan on customer l + Filter: (name = ("current_user"())::text) +(9 rows) + -- -- scenario: an external qualifier can be pushed-down by in-front-of the -- views with "security_barrier" attribute, except for operators @@ -1447,63 +1435,61 @@ EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_secure WHERE f_leak(cnum); -- SELECT * FROM my_credit_card_usage_normal WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01'; -NOTICE: f_leak => 1111-2222-3333-4444 cid | name | tel | passwd | cnum | climit | ymd | usage -----+---------------+------------------+-----------+---------------------+--------+------------+------- - 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-05-2011 | 90 - 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-18-2011 | 110 - 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-21-2011 | 200 + 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 2011-10-05 | 90 + 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 2011-10-18 | 110 + 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 2011-10-21 | 200 (3 rows) EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_normal WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01'; - QUERY PLAN ------------------------------------------------------------------------------- - Nested Loop - Join Filter: (l.cid = r.cid) - -> Seq Scan on credit_usage r - Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) - -> Materialize - -> Subquery Scan on l - Filter: f_leak(l.cnum) - -> Hash Join - Hash Cond: (r_1.cid = l_1.cid) - -> Seq Scan on credit_card r_1 - -> Hash - -> Seq Scan on customer l_1 - Filter: (name = ("current_user"())::text) -(13 rows) + QUERY PLAN +------------------------------------------------------------------------------------ + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Nested Loop + Join Filter: (l.cid = r.cid) + -> Seq Scan on credit_usage r + Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) + -> Materialize + -> Subquery Scan on l + Filter: f_leak(l.cnum) + -> Hash Join + Hash Cond: (r_1.cid = l_1.cid) + -> Seq Scan on credit_card r_1 + -> Hash + -> Seq Scan on customer l_1 + Filter: (name = ("current_user"())::text) +(14 rows) SELECT * FROM my_credit_card_usage_secure WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01'; -NOTICE: f_leak => 1111-2222-3333-4444 -NOTICE: f_leak => 1111-2222-3333-4444 -NOTICE: f_leak => 1111-2222-3333-4444 cid | name | tel | passwd | cnum | climit | ymd | usage -----+---------------+------------------+-----------+---------------------+--------+------------+------- - 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-05-2011 | 90 - 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-18-2011 | 110 - 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 10-21-2011 | 200 + 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 2011-10-05 | 90 + 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 2011-10-18 | 110 + 101 | regress_alice | +81-12-3456-7890 | passwd123 | 1111-2222-3333-4444 | 4000 | 2011-10-21 | 200 (3 rows) EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_secure WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01'; - QUERY PLAN ------------------------------------------------------------------------------------- - Subquery Scan on my_credit_card_usage_secure - Filter: f_leak(my_credit_card_usage_secure.cnum) - -> Nested Loop - Join Filter: (l.cid = r.cid) - -> Seq Scan on credit_usage r - Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) - -> Materialize - -> Hash Join - Hash Cond: (r_1.cid = l.cid) - -> Seq Scan on credit_card r_1 - -> Hash - -> Seq Scan on customer l - Filter: (name = ("current_user"())::text) -(13 rows) + QUERY PLAN +------------------------------------------------------------------------------------------ + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Subquery Scan on my_credit_card_usage_secure + Filter: f_leak(my_credit_card_usage_secure.cnum) + -> Nested Loop + Join Filter: (l.cid = r.cid) + -> Seq Scan on credit_usage r + Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) + -> Materialize + -> Hash Join + Hash Cond: (r_1.cid = l.cid) + -> Seq Scan on credit_card r_1 + -> Hash + -> Seq Scan on customer l + Filter: (name = ("current_user"())::text) +(14 rows) -- -- Test for the case when security_barrier gets changed between rewriter @@ -1512,16 +1498,12 @@ EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_secure PREPARE p1 AS SELECT * FROM my_property_normal WHERE f_leak(passwd); PREPARE p2 AS SELECT * FROM my_property_secure WHERE f_leak(passwd); EXECUTE p1; -NOTICE: f_leak => passwd123 -NOTICE: f_leak => beafsteak -NOTICE: f_leak => hamburger cid | name | tel | passwd -----+---------------+------------------+----------- 101 | regress_alice | +81-12-3456-7890 | passwd123 (1 row) EXECUTE p2; -NOTICE: f_leak => passwd123 cid | name | tel | passwd -----+---------------+------------------+----------- 101 | regress_alice | +81-12-3456-7890 | passwd123 @@ -1532,16 +1514,12 @@ ALTER VIEW my_property_normal SET (security_barrier=true); ALTER VIEW my_property_secure SET (security_barrier=false); SET SESSION AUTHORIZATION regress_alice; EXECUTE p1; -- To be perform as a view with security-barrier -NOTICE: f_leak => passwd123 cid | name | tel | passwd -----+---------------+------------------+----------- 101 | regress_alice | +81-12-3456-7890 | passwd123 (1 row) EXECUTE p2; -- To be perform as a view without security-barrier -NOTICE: f_leak => passwd123 -NOTICE: f_leak => beafsteak -NOTICE: f_leak => hamburger cid | name | tel | passwd -----+---------------+------------------+----------- 101 | regress_alice | +81-12-3456-7890 | passwd123 |