Test subquery2.sql (id 0177627dc2e1) (err)
subquery2.stable.out.FILTERED 2019-09-20 19:46:47.000000000 +0200subquery2.test.out.FILTERED 2019-09-20 19:46:47.000000000 +0200
1 Statistics about stdout of test 'subquery2` in directory 'sql/test/subquery`: 1 1 Statistics about stdout of test 'subquery2` in directory 'sql/test/subquery`: 1
2 26 lines, 130 words, 582 chars in lines not matching '^$|^(\| |)#|^=` 2 ! 2 92 lines, 383 words, 1332 chars in lines not matching '^$|^(\| |)#|^=` 2
3 = 26 lines, 130 words, 582 chars in lines not matching '^$|^(\| |)#` 3 ! 3 = 92 lines, 383 words, 1332 chars in lines not matching '^$|^(\| |)#` 3
4 # 67 lines, 433 words, 2904 chars in all lines 4 ! 4 # 194 lines, 1133 words, 6439 chars in all lines 4
5 5 5 5
..................
#drop table students;
67 #drop table exams; 67 67 #drop table exams; 67
68 68 ! 68 #CREATE TABLE tbl_ProductSales (ColID int, Product_Category varchar(64), Product_Name varchar(64), TotalSales int); 68
69 # 18:55:46 > 69 ! 69 #INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100); 69
70 # 18:55:46 > "Done." 70 ! 70 [ 4 ] 70
71 # 18:55:46 > 71 ! 71 #CREATE TABLE another_T (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6 INT, col7 INT, col8 INT); 71
+ 72 #INSERT INTO another_T VALUES (1,2,3,4,5,6,7,8), (11,22,33,44,55,66,77,88), (111,222,333,444,555,666,777,888), (1111,2222,3333,4444,5555,6666,7777,8888); 72
+ 73 [ 4 ] 73
+ 74 #SELECT 74
+ 75 # -col1 IN (SELECT ColID FROM tbl_ProductSales), 75
+ 76 # col5 = ALL (SELECT 1 FROM tbl_ProductSales HAVING MIN(col8) IS NULL) 76
+ 77 #FROM another_T GROUP BY col1, col2, col5, col8; 77
+ 78 % ., . # table_name 78
+ 79 # .L10, .L14 # table_name 79
+ 80 % L, L # name 80
+ 81 # L10, L14 # name 81
+ 82 % boolean, boolean # type 82
+ 83 % 5, 5 # length 83
+ 84 #SELECT 84
+ 85 # DISTINCT 85
+ 86 # NOT col1 * col5 = ALL (SELECT 1 FROM tbl_ProductSales HAVING MAX(col2) > 2), 86
+ 87 # NOT AVG(col2) * col1 <> ANY (SELECT 20 FROM tbl_ProductSales HAVING MAX(col1) IS NOT NULL OR MIN(col1) < MIN(col2)), 87
+ 88 # CAST (NOT col1 IN (SELECT col2 FROM another_T GROUP BY col2) AS INTEGER) | CAST (col2 IN (SELECT col2 FROM another_T GROUP BY col2) AS INTEGER), 88
+ 89 # CAST (EXISTS (SELECT MAX(col5) * MAX(col4) FROM another_T GROUP BY col5, col4) AS INTEGER) & CAST (AVG(col1) IN (SELECT DISTINCT col2 FROM another_T GROUP BY col2) AS INTEGER) 89
+ 90 % ., ., ., . # table_name 90
+ 91 # .L4, .L13, .L42, .L64 # table_name 91
+ 92 % L, L, L, L # name 92
+ 93 # L4, L13, L42, L64 # name 93
+ 94 % boolean, boolean, int, int # type 94
+ 95 % 5, 5, 1, 1 # length 95
+ 96 [ true, false, 1, 0 ] 96
+ 97 #SELECT 97
+ 98 # SUM(CAST(t1.col1 IN (SELECT t1.col1 FROM another_T) AS INTEGER)) 98
+ 99 #FROM another_T t1 99
+ 100 #GROUP BY t1.col2; 100
+ 101 % . # table_name 101
+ 102 # .L2 # table_name 102
+ 103 % L # name 103
+ 104 # L2 # name 104
+ 105 % hugeint # type 105
+ 106 % 1 # length 106
+ 107 [ 4 ] 107
+ 108 [ 4 ] 108
+ 109 [ 4 ] 109
+ 110 [ 4 ] 110
+ 111 #SELECT 111
+ 112 # DISTINCT 112
+ 113 # NOT MIN(col1 + 7) OVER (PARTITION BY SUM(DISTINCT col7 * col1) - AVG(col8 * NULL) ORDER BY MIN(col5 - col1) - MAX(DISTINCT col6) ROWS UNBOUNDED PRECEDING) 113
+ 114 # NOT IN (SELECT MAX(col1 - 1) * MIN(DISTINCT col2) FROM another_T) 114
+ 115 #FROM another_T 115
+ 116 #GROUP BY col1; --this query is right, but I'm leaving it here 116
+ 117 % . # table_name 117
+ 118 # .L27 # table_name 118
+ 119 % L # name 119
+ 120 # L27 # name 120
+ 121 % boolean # type 121
+ 122 % 5 # length 122
+ 123 [ false ] 123
+ 124 #SELECT 124
+ 125 # SUM(col3 + col2) 125
+ 126 #FROM another_T 126
+ 127 #GROUP BY col1 127
+ 128 #HAVING NOT col1 = ANY (SELECT 0 FROM tbl_ProductSales GROUP BY ColID HAVING NOT MAX(col1) <> AVG(col1)); 128
+ 129 % sys. # table_name 129
+ 130 # sys.L1 # table_name 130
+ 131 % L # name 131
+ 132 # L1 # name 132
+ 133 % bigint # type 133
+ 134 % 4 # length 134
+ 135 [ 5 ] 135
+ 136 [ 55 ] 136
+ 137 [ 555 ] 137
+ 138 [ 5555 ] 138
+ 139 [ 5 ] 139
+ 140 [ 55 ] 140
+ 141 [ 555 ] 141
+ 142 [ 5555 ] 142
+ 143 [ 5 ] 143
+ 144 [ 55 ] 144
+ 145 [ 555 ] 145
+ 146 [ 5555 ] 146
+ 147 [ 5 ] 147
+ 148 [ 55 ] 148
+ 149 [ 555 ] 149
+ 150 [ 5555 ] 150
+ 151 #SELECT 151
+ 152 # SUM(col3) * col1 152
+ 153 #FROM another_T 153
+ 154 #GROUP BY col1 154
+ 155 #HAVING NOT col1 <> ANY (SELECT 0 FROM tbl_ProductSales GROUP BY ColID HAVING NOT MAX(col1) <> col1 * AVG(col1 + ColID) * ColID); 155
+ 156 % sys. # table_name 156
+ 157 # sys.L2 # table_name 157
+ 158 % L # name 158
+ 159 # L2 # name 159
+ 160 % hugeint # type 160
+ 161 % 1 # length 161
+ 162 #SELECT 162
+ 163 # (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 ON t1.col5 = t2.col1) 163
+ 164 #FROM another_T t1; 164
+ 165 % sys. # table_name 165
+ 166 # sys.L4 # table_name 166
+ 167 % L # name 167
+ 168 # L4 # name 168
+ 169 % int # type 169
+ 170 % 1 # length 170
+ 171 [ 1 ] 171
+ 172 [ 1 ] 172
+ 173 [ 1 ] 173
+ 174 [ 1 ] 174
+ 175 #SELECT 175
+ 176 # (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 ON t1.col7 <> SOME(SELECT MAX(t1.col1 + t3.col4) FROM another_T t3)) 176
+ 177 #FROM another_T t1; 177
+ 178 % . # table_name 178
+ 179 # .L6 # table_name 179
+ 180 % L # name 180
+ 181 # L6 # name 181
+ 182 % int # type 182
+ 183 % 1 # length 183
+ 184 #SELECT 184
+ 185 # CASE WHEN 1 IN (SELECT (SELECT MAX(col7)) UNION ALL (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 ON t2.col5 = t2.col1)) THEN 2 ELSE NULL END 185
+ 186 #FROM another_T t1; 186
+ 187 % . # table_name 187
+ 188 # .L22 # table_name 188
+ 189 % L # name 189
+ 190 # L22 # name 190
+ 191 % tinyint # type 191
+ 192 % 1 # length 192
+ 193 [ NULL ] 193
+ 194 [ NULL ] 194
+ 195 195
+ 196 # 19:46:47 > 196
+ 197 # 19:46:47 > "Done." 197
+ 198 # 19:46:47 > 198
72 72 199 199