| 208 | } |
| 209 | |
| 210 | func TestSqlCsvDriverSubQuery(t *testing.T) { |
| 211 | // Sub-Query |
| 212 | sqlText := ` |
| 213 | SELECT |
| 214 | u.user_id, o.item_id, u.reg_date, u.email, o.price, o.order_date |
| 215 | FROM users AS u |
| 216 | INNER JOIN ( |
| 217 | SELECT price, order_date, user_id from ORDERS |
| 218 | WHERE user_id IS NOT NULL AND price > 10 |
| 219 | ) AS o |
| 220 | ON u.user_id = o.user_id |
| 221 | ` |
| 222 | db, err := sql.Open("qlbridge", "mockcsv") |
| 223 | assert.True(t, err == nil, "no error: %v", err) |
| 224 | assert.True(t, db != nil, "has conn: %v", db) |
| 225 | |
| 226 | defer func() { |
| 227 | if err := db.Close(); err != nil { |
| 228 | t.Fatalf("Should not error on close: %v", err) |
| 229 | } |
| 230 | }() |
| 231 | |
| 232 | rows, err := db.Query(sqlText) |
| 233 | assert.True(t, err == nil, "no error: %v", err) |
| 234 | assert.True(t, rows != nil, "has results: %v", rows) |
| 235 | |
| 236 | cols, err := rows.Columns() |
| 237 | assert.True(t, err == nil, "no error: %v", err) |
| 238 | assert.True(t, len(cols) == 6, "6 cols: %v", cols) |
| 239 | userOrders := make([]userorder, 0) |
| 240 | for rows.Next() { |
| 241 | var uo userorder |
| 242 | err = rows.Scan(&uo.UserId, &uo.ItemId, &uo.RegDate, &uo.Email, &uo.Price, &uo.OrderDate) |
| 243 | assert.True(t, err == nil, "no error: %v", err) |
| 244 | //u.Debugf("userorder=%+v", uo) |
| 245 | userOrders = append(userOrders, uo) |
| 246 | } |
| 247 | assert.True(t, rows.Err() == nil, "no error: %v", err) |
| 248 | assert.True(t, len(userOrders) == 2, "want 2 userOrders row: %+v", userOrders) |
| 249 | |
| 250 | uo1 := userOrders[0] |
| 251 | assert.True(t, uo1.Email == "aaron@email.com", "%#v", uo1) |
| 252 | assert.True(t, uo1.Price == 22.5, "? %#v", uo1) |
| 253 | |
| 254 | rows.Close() |
| 255 | |
| 256 | // Really want to test the order_date date conversion detection |
| 257 | |
| 258 | // sqlText := ` |
| 259 | // SELECT |
| 260 | // u.user_id, o.item_id, u.reg_date, u.email, o.price, o.order_date |
| 261 | // FROM users AS u |
| 262 | // INNER JOIN ( |
| 263 | // SELECT price, order_date, user_id from ORDERS |
| 264 | // WHERE user_id IS NOT NULL AND order_date > "2014/01/01" |
| 265 | // ) AS o |
| 266 | // ON u.user_id = o.user_id |
| 267 | // WHERE o.price > 10; |