Applied patch from Zbigniew Lukasiak (with slight modifications) to accept "col ...
[dbsrgits/SQL-Abstract.git] / t / 01generate.t
CommitLineData
41751122 1#!/usr/bin/perl
32eab2da 2
3use strict;
41751122 4use warnings;
5use Test::More;
7fb57243 6use Test::Warn;
32eab2da 7
5aad8cf3 8use SQL::Abstract::Test import => ['is_same_sql_bind'];
32eab2da 9
10use SQL::Abstract;
11
12my @tests = (
13 #1
14 {
15 func => 'select',
16 args => ['test', '*'],
17 stmt => 'SELECT * FROM test',
18 stmt_q => 'SELECT * FROM `test`',
19 bind => []
20 },
21 #2
22 {
23 func => 'select',
24 args => ['test', [qw(one two three)]],
25 stmt => 'SELECT one, two, three FROM test',
26 stmt_q => 'SELECT `one`, `two`, `three` FROM `test`',
27 bind => []
28 },
29 #3
30 {
31 func => 'select',
32 args => ['test', '*', { a => 0 }, [qw/boom bada bing/]],
33 stmt => 'SELECT * FROM test WHERE ( a = ? ) ORDER BY boom, bada, bing',
34 stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? ) ORDER BY `boom`, `bada`, `bing`',
35 bind => [0]
36 },
37 #4
38 {
39 func => 'select',
40 args => ['test', '*', [ { a => 5 }, { b => 6 } ]],
41 stmt => 'SELECT * FROM test WHERE ( ( a = ? ) OR ( b = ? ) )',
42 stmt_q => 'SELECT * FROM `test` WHERE ( ( `a` = ? ) OR ( `b` = ? ) )',
43 bind => [5,6]
44 },
45 #5
46 {
47 func => 'select',
48 args => ['test', '*', undef, ['id']],
49 stmt => 'SELECT * FROM test ORDER BY id',
50 stmt_q => 'SELECT * FROM `test` ORDER BY `id`',
51 bind => []
52 },
53 #6
54 {
55 func => 'select',
56 args => ['test', '*', { a => 'boom' } , ['id']],
57 stmt => 'SELECT * FROM test WHERE ( a = ? ) ORDER BY id',
58 stmt_q => 'SELECT * FROM `test` WHERE ( `a` = ? ) ORDER BY `id`',
59 bind => ['boom']
60 },
61 #7
62 {
63 func => 'select',
64 args => ['test', '*', { a => ['boom', 'bang'] }],
65 stmt => 'SELECT * FROM test WHERE ( ( ( a = ? ) OR ( a = ? ) ) )',
66 stmt_q => 'SELECT * FROM `test` WHERE ( ( ( `a` = ? ) OR ( `a` = ? ) ) )',
67 bind => ['boom', 'bang']
68 },
69 #8
70 {
71 func => 'select',
72 args => [[qw/test1 test2/], '*', { 'test1.a' => { 'In', ['boom', 'bang'] } }],
73 stmt => 'SELECT * FROM test1, test2 WHERE ( test1.a IN ( ?, ? ) )',
74 stmt_q => 'SELECT * FROM `test1`, `test2` WHERE ( `test1`.`a` IN ( ?, ? ) )',
75 bind => ['boom', 'bang']
76 },
77 #9
78 {
79 func => 'select',
80 args => ['test', '*', { a => { 'between', ['boom', 'bang'] } }],
81 stmt => 'SELECT * FROM test WHERE ( a BETWEEN ? AND ? )',
82 stmt_q => 'SELECT * FROM `test` WHERE ( `a` BETWEEN ? AND ? )',
83 bind => ['boom', 'bang']
84 },
85 #10
86 {
87 func => 'select',
88 args => ['test', '*', { a => { '!=', 'boom' } }],
89 stmt => 'SELECT * FROM test WHERE ( a != ? )',
90 stmt_q => 'SELECT * FROM `test` WHERE ( `a` != ? )',
91 bind => ['boom']
92 },
93 #11
94 {
95 func => 'update',
96 args => ['test', {a => 'boom'}, {a => undef}],
97 stmt => 'UPDATE test SET a = ? WHERE ( a IS NULL )',
98 stmt_q => 'UPDATE `test` SET `a` = ? WHERE ( `a` IS NULL )',
99 bind => ['boom']
100 },
101 #12
102 {
103 func => 'update',
104 args => ['test', {a => 'boom'}, { a => {'!=', "bang" }} ],
105 stmt => 'UPDATE test SET a = ? WHERE ( a != ? )',
106 stmt_q => 'UPDATE `test` SET `a` = ? WHERE ( `a` != ? )',
107 bind => ['boom', 'bang']
108 },
109 #13
110 {
111 func => 'update',
112 args => ['test', {'a-funny-flavored-candy' => 'yummy', b => 'oops'}, { a42 => "bang" }],
113 stmt => 'UPDATE test SET a-funny-flavored-candy = ?, b = ? WHERE ( a42 = ? )',
114 stmt_q => 'UPDATE `test` SET `a-funny-flavored-candy` = ?, `b` = ? WHERE ( `a42` = ? )',
115 bind => ['yummy', 'oops', 'bang']
116 },
117 #14
118 {
119 func => 'delete',
120 args => ['test', {requestor => undef}],
121 stmt => 'DELETE FROM test WHERE ( requestor IS NULL )',
122 stmt_q => 'DELETE FROM `test` WHERE ( `requestor` IS NULL )',
123 bind => []
124 },
125 #15
126 {
127 func => 'delete',
128 args => [[qw/test1 test2 test3/],
129 { 'test1.field' => \'!= test2.field',
130 user => {'!=','nwiger'} },
131 ],
132 stmt => 'DELETE FROM test1, test2, test3 WHERE ( test1.field != test2.field AND user != ? )',
133 stmt_q => 'DELETE FROM `test1`, `test2`, `test3` WHERE ( `test1`.`field` != test2.field AND `user` != ? )', # test2.field is a literal value, cannnot be quoted.
134 bind => ['nwiger']
135 },
136 #16
137 {
138 func => 'insert',
139 args => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}],
140 stmt => 'INSERT INTO test (a, b, c, d, e) VALUES (?, ?, ?, ?, ?)',
141 stmt_q => 'INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES (?, ?, ?, ?, ?)',
142 bind => [qw/1 2 3 4 5/],
143 },
144 #17
145 {
146 func => 'insert',
147 args => ['test', [qw/1 2 3 4 5/]],
148 stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?)',
149 stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?)',
150 bind => [qw/1 2 3 4 5/],
151 },
152 #18
153 {
154 func => 'insert',
155 args => ['test', [qw/1 2 3 4 5/, undef]],
156 stmt => 'INSERT INTO test VALUES (?, ?, ?, ?, ?, ?)',
157 stmt_q => 'INSERT INTO `test` VALUES (?, ?, ?, ?, ?, ?)',
158 bind => [qw/1 2 3 4 5/, undef],
159 },
160 #19
161 {
162 func => 'update',
163 args => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}],
164 stmt => 'UPDATE test SET a = ?, b = ?, c = ?, d = ?, e = ?',
165 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?, `c` = ?, `d` = ?, `e` = ?',
166 bind => [qw/1 2 3 4 5/],
167 },
168 #20
169 {
170 func => 'update',
171 args => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}, {a => {'in', [1..5]}}],
172 stmt => 'UPDATE test SET a = ?, b = ?, c = ?, d = ?, e = ? WHERE ( a IN ( ?, ?, ?, ?, ? ) )',
173 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?, `c` = ?, `d` = ?, `e` = ? WHERE ( `a` IN ( ?, ?, ?, ?, ? ) )',
174 bind => [qw/1 2 3 4 5 1 2 3 4 5/],
175 },
176 #21
177 {
178 func => 'update',
96449e8e 179 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, {a => {'between', [1,2]}}],
32eab2da 180 stmt => 'UPDATE test SET a = ?, b = to_date(?, \'MM/DD/YY\') WHERE ( a BETWEEN ? AND ? )',
181 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = to_date(?, \'MM/DD/YY\') WHERE ( `a` BETWEEN ? AND ? )',
182 bind => [qw(1 02/02/02 1 2)],
183 },
184 #22
185 {
186 func => 'insert',
187 args => ['test.table', {high_limit => \'max(all_limits)', low_limit => 4} ],
188 stmt => 'INSERT INTO test.table (high_limit, low_limit) VALUES (max(all_limits), ?)',
189 stmt_q => 'INSERT INTO `test`.`table` (`high_limit`, `low_limit`) VALUES (max(all_limits), ?)',
190 bind => ['4'],
191 },
192 #23
193 {
194 func => 'insert',
195 new => {bindtype => 'columns'},
196 args => ['test.table', {one => 2, three => 4, five => 6} ],
197 stmt => 'INSERT INTO test.table (five, one, three) VALUES (?, ?, ?)',
198 stmt_q => 'INSERT INTO `test`.`table` (`five`, `one`, `three`) VALUES (?, ?, ?)',
199 bind => [['five', 6], ['one', 2], ['three', 4]], # alpha order, man...
200 },
201 #24
202 {
203 func => 'select',
204 new => {bindtype => 'columns', case => 'lower'},
205 args => ['test.table', [qw/one two three/], {one => 2, three => 4, five => 6} ],
206 stmt => 'select one, two, three from test.table where ( five = ? and one = ? and three = ? )',
207 stmt_q => 'select `one`, `two`, `three` from `test`.`table` where ( `five` = ? and `one` = ? and `three` = ? )',
208 bind => [['five', 6], ['one', 2], ['three', 4]], # alpha order, man...
209 },
210 #25
211 {
212 func => 'update',
213 new => {bindtype => 'columns', cmp => 'like'},
214 args => ['testin.table2', {One => 22, Three => 44, FIVE => 66},
215 {Beer => 'is', Yummy => '%YES%', IT => ['IS','REALLY','GOOD']}],
216 stmt => 'UPDATE testin.table2 SET FIVE = ?, One = ?, Three = ? WHERE '
217 . '( Beer LIKE ? AND ( ( IT LIKE ? ) OR ( IT LIKE ? ) OR ( IT LIKE ? ) ) AND Yummy LIKE ? )',
218 stmt_q => 'UPDATE `testin`.`table2` SET `FIVE` = ?, `One` = ?, `Three` = ? WHERE '
219 . '( `Beer` LIKE ? AND ( ( `IT` LIKE ? ) OR ( `IT` LIKE ? ) OR ( `IT` LIKE ? ) ) AND `Yummy` LIKE ? )',
220 bind => [['FIVE', 66], ['One', 22], ['Three', 44], ['Beer','is'],
221 ['IT','IS'], ['IT','REALLY'], ['IT','GOOD'], ['Yummy','%YES%']],
222 },
223 #26
224 {
225 func => 'select',
226 args => ['test', '*', {priority => [ -and => {'!=', 2}, {'!=', 1} ]}],
227 stmt => 'SELECT * FROM test WHERE ( ( ( priority != ? ) AND ( priority != ? ) ) )',
228 stmt_q => 'SELECT * FROM `test` WHERE ( ( ( `priority` != ? ) AND ( `priority` != ? ) ) )',
229 bind => [qw(2 1)],
230 },
231 #27
232 {
233 func => 'select',
234 args => ['Yo Momma', '*', { user => 'nwiger',
235 -nest => [ workhrs => {'>', 20}, geo => 'ASIA' ] }],
236 stmt => 'SELECT * FROM Yo Momma WHERE ( ( ( workhrs > ? ) OR ( geo = ? ) ) AND user = ? )',
237 stmt_q => 'SELECT * FROM `Yo Momma` WHERE ( ( ( `workhrs` > ? ) OR ( `geo` = ? ) ) AND `user` = ? )',
238 bind => [qw(20 ASIA nwiger)],
239 },
240 #28
241 {
242 func => 'update',
243 args => ['taco_punches', { one => 2, three => 4 },
244 { bland => [ -and => {'!=', 'yes'}, {'!=', 'YES'} ],
245 tasty => { '!=', [qw(yes YES)] },
246 -nest => [ face => [ -or => {'=', 'mr.happy'}, {'=', undef} ] ] },
247 ],
96449e8e 248# LDNOTE : modified the test below, same reasons as #14 in 00where.t
32eab2da 249 stmt => 'UPDATE taco_punches SET one = ?, three = ? WHERE ( ( ( ( ( face = ? ) OR ( face IS NULL ) ) ) )'
96449e8e 250# . ' AND ( ( bland != ? ) AND ( bland != ? ) ) AND ( ( tasty != ? ) OR ( tasty != ? ) ) )',
251 . ' AND ( ( bland != ? ) AND ( bland != ? ) ) AND ( ( tasty != ? ) AND ( tasty != ? ) ) )',
32eab2da 252 stmt_q => 'UPDATE `taco_punches` SET `one` = ?, `three` = ? WHERE ( ( ( ( ( `face` = ? ) OR ( `face` IS NULL ) ) ) )'
96449e8e 253# . ' AND ( ( `bland` != ? ) AND ( `bland` != ? ) ) AND ( ( `tasty` != ? ) OR ( `tasty` != ? ) ) )',
254 . ' AND ( ( `bland` != ? ) AND ( `bland` != ? ) ) AND ( ( `tasty` != ? ) AND ( `tasty` != ? ) ) )',
32eab2da 255 bind => [qw(2 4 mr.happy yes YES yes YES)],
256 },
257 #29
258 {
259 func => 'select',
260 args => ['jeff', '*', { name => {'like', '%smith%', -not_in => ['Nate','Jim','Bob','Sally']},
261 -nest => [ -or => [ -and => [age => { -between => [20,30] }, age => {'!=', 25} ],
96449e8e 262 yob => {'<', 1976} ] ] } ],
263# LDNOTE : original test below was WRONG with respect to the doc.
264# [-and, [cond1, cond2], cond3] should mean (cond1 OR cond2) AND cond3
265# instead of (cond1 AND cond2) OR cond3.
266# Probably a misconception because of '=>' notation
267# in [-and => [cond1, cond2], cond3].
268# Also some differences in parentheses, but without impact on semantics.
269# stmt => 'SELECT * FROM jeff WHERE ( ( ( ( ( ( ( age BETWEEN ? AND ? ) AND ( age != ? ) ) ) OR ( yob < ? ) ) ) )'
270# . ' AND name NOT IN ( ?, ?, ?, ? ) AND name LIKE ? )',
271# stmt_q => 'SELECT * FROM `jeff` WHERE ( ( ( ( ( ( ( `age` BETWEEN ? AND ? ) AND ( `age` != ? ) ) ) OR ( `yob` < ? ) ) ) )'
272# . ' AND `name` NOT IN ( ?, ?, ?, ? ) AND `name` LIKE ? )',
273 stmt => 'SELECT * FROM jeff WHERE ( ( ( ( ( age BETWEEN ? AND ? ) OR ( age != ? ) ) AND ( yob < ? ) ) )'
274 . ' AND ( name NOT IN ( ?, ?, ?, ? ) AND name LIKE ? ) )',
275 stmt_q => 'SELECT * FROM `jeff` WHERE ( ( ( ( ( `age` BETWEEN ? AND ? ) OR ( `age` != ? ) ) AND ( `yob` < ? ) ) )'
276 . ' AND ( `name` NOT IN ( ?, ?, ?, ? ) AND `name` LIKE ? ) )',
32eab2da 277 bind => [qw(20 30 25 1976 Nate Jim Bob Sally %smith%)]
278 },
279 #30
280 {
32eab2da 281 func => 'update',
96449e8e 282# LDNOTE : removed the "-maybe", because we no longer admit unknown ops
283# args => ['fhole', {fpoles => 4}, [-maybe => {race => [-and => [qw(black white asian)]]},
284 args => ['fhole', {fpoles => 4}, [ {race => [-and => [qw(black white asian)]]},
32eab2da 285 {-nest => {firsttime => [-or => {'=','yes'}, undef]}},
286 [ -and => {firstname => {-not_like => 'candace'}}, {lastname => {-in => [qw(jugs canyon towers)]}} ] ] ],
287 stmt => 'UPDATE fhole SET fpoles = ? WHERE ( ( ( ( ( ( ( race = ? ) OR ( race = ? ) OR ( race = ? ) ) ) ) ) )'
288 . ' OR ( ( ( ( firsttime = ? ) OR ( firsttime IS NULL ) ) ) ) OR ( ( ( firstname NOT LIKE ? ) ) AND ( lastname IN ( ?, ?, ? ) ) ) )',
289 stmt_q => 'UPDATE `fhole` SET `fpoles` = ? WHERE ( ( ( ( ( ( ( `race` = ? ) OR ( `race` = ? ) OR ( `race` = ? ) ) ) ) ) )'
290 . ' OR ( ( ( ( `firsttime` = ? ) OR ( `firsttime` IS NULL ) ) ) ) OR ( ( ( `firstname` NOT LIKE ? ) ) AND ( `lastname` IN ( ?, ?, ? ) ) ) )',
291 bind => [qw(4 black white asian yes candace jugs canyon towers)]
292 },
96449e8e 293 #31
294 {
295 func => 'insert',
296 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}],
297 stmt => 'INSERT INTO test (a, b) VALUES (?, to_date(?, \'MM/DD/YY\'))',
298 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, to_date(?, \'MM/DD/YY\'))',
299 bind => [qw(1 02/02/02)],
300 },
301 #32
302 {
303 func => 'select',
304# LDNOTE: modified test below because we agreed with MST that literal SQL
305# should not automatically insert a '='; the user has to do it
306# args => ['test', '*', { a => \["to_date(?, 'MM/DD/YY')", '02/02/02']}],
307 args => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", '02/02/02']}],
308 stmt => q{SELECT * FROM test WHERE ( a = to_date(?, 'MM/DD/YY') )},
309 stmt_q => q{SELECT * FROM `test` WHERE ( `a` = to_date(?, 'MM/DD/YY') )},
310 bind => ['02/02/02'],
d82b8afb 311 },
312 #33
313 {
314 func => 'insert',
315 new => {array_datatypes => 1},
316 args => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}],
317 stmt => 'INSERT INTO test (a, b) VALUES (?, ?)',
318 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, ?)',
319 bind => [1, [1, 1, 2, 3, 5, 8]],
320 },
321 #34
322 {
323 func => 'insert',
324 new => {bindtype => 'columns', array_datatypes => 1},
325 args => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}],
326 stmt => 'INSERT INTO test (a, b) VALUES (?, ?)',
327 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, ?)',
328 bind => [[a => 1], [b => [1, 1, 2, 3, 5, 8]]],
329 },
330 #35
331 {
332 func => 'update',
333 new => {array_datatypes => 1},
334 args => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}],
335 stmt => 'UPDATE test SET a = ?, b = ?',
336 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?',
337 bind => [1, [1, 1, 2, 3, 5, 8]],
338 },
339 #36
340 {
341 func => 'update',
342 new => {bindtype => 'columns', array_datatypes => 1},
343 args => ['test', {a => 1, b => [1, 1, 2, 3, 5, 8]}],
344 stmt => 'UPDATE test SET a = ?, b = ?',
345 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = ?',
346 bind => [[a => 1], [b => [1, 1, 2, 3, 5, 8]]],
347 },
145fbfc8 348 #37
349 {
350 func => 'select',
351 args => ['test', '*', { a => {'>', \'1 + 1'}, b => 8 }],
352 stmt => 'SELECT * FROM test WHERE ( a > 1 + 1 AND b = ? )',
353 stmt_q => 'SELECT * FROM `test` WHERE ( `a` > 1 + 1 AND `b` = ? )',
354 bind => [8],
355 },
b3be7bd0 356 #38
357 {
358 func => 'select',
359 args => ['test', '*', { a => {'<' => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, b => 8 }],
360 stmt => 'SELECT * FROM test WHERE ( a < to_date(?, \'MM/DD/YY\') AND b = ? )',
361 stmt_q => 'SELECT * FROM `test` WHERE ( `a` < to_date(?, \'MM/DD/YY\') AND `b` = ? )',
362 bind => ['02/02/02', 8],
363 },
5db47f9f 364 #39
365 { #TODO in SQLA >= 2.0 it will die instead (we kept this just because old SQLA passed it through)
366 func => 'insert',
367 args => ['test', {a => 1, b => 2, c => 3, d => 4, e => { answer => 42 }}],
368 stmt => 'INSERT INTO test (a, b, c, d, e) VALUES (?, ?, ?, ?, ?)',
369 stmt_q => 'INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES (?, ?, ?, ?, ?)',
370 bind => [qw/1 2 3 4/, { answer => 42}],
7fb57243 371 warning_like => qr/HASH ref as bind value in insert is not supported/i,
5db47f9f 372 },
ef4d99a5 373 #40
374 {
375 func => 'update',
376 args => ['test', {a => 1, b => \["42"]}, {a => {'between', [1,2]}}],
377 stmt => 'UPDATE test SET a = ?, b = 42 WHERE ( a BETWEEN ? AND ? )',
378 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = 42 WHERE ( `a` BETWEEN ? AND ? )',
379 bind => [qw(1 1 2)],
380 },
381 #41
382 {
383 func => 'insert',
384 args => ['test', {a => 1, b => \["42"]}],
385 stmt => 'INSERT INTO test (a, b) VALUES (?, 42)',
386 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, 42)',
387 bind => [qw(1)],
388 },
389 #42
390 {
391 func => 'select',
392 args => ['test', '*', { a => \["= 42"], b => 1}],
393 stmt => q{SELECT * FROM test WHERE ( a = 42 ) AND (b = ? )},
394 stmt_q => q{SELECT * FROM `test` WHERE ( `a` = 42 ) AND ( `b` = ? )},
395 bind => [qw(1)],
396 },
397 #43
398 {
399 func => 'select',
400 args => ['test', '*', { a => {'<' => \["42"]}, b => 8 }],
401 stmt => 'SELECT * FROM test WHERE ( a < 42 AND b = ? )',
402 stmt_q => 'SELECT * FROM `test` WHERE ( `a` < 42 AND `b` = ? )',
403 bind => [qw(8)],
404 },
cd87fd4c 405 #44
406 {
407 func => 'insert',
408 new => {bindtype => 'columns'},
409 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}],
410 stmt => 'INSERT INTO test (a, b) VALUES (?, to_date(?, \'MM/DD/YY\'))',
411 stmt_q => 'INSERT INTO `test` (`a`, `b`) VALUES (?, to_date(?, \'MM/DD/YY\'))',
412 bind => [[a => '1'], [b => '02/02/02']],
413 },
414 #45
415 {
416 func => 'update',
417 new => {bindtype => 'columns'},
418 args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, {a => {'between', [1,2]}}],
419 stmt => 'UPDATE test SET a = ?, b = to_date(?, \'MM/DD/YY\') WHERE ( a BETWEEN ? AND ? )',
420 stmt_q => 'UPDATE `test` SET `a` = ?, `b` = to_date(?, \'MM/DD/YY\') WHERE ( `a` BETWEEN ? AND ? )',
421 bind => [[a => '1'], [b => '02/02/02'], [a => '1'], [a => '2']],
422 },
423 #46
424 {
425 func => 'select',
426 new => {bindtype => 'columns'},
427 args => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", '02/02/02']}],
428 stmt => q{SELECT * FROM test WHERE ( a = to_date(?, 'MM/DD/YY') )},
429 stmt_q => q{SELECT * FROM `test` WHERE ( `a` = to_date(?, 'MM/DD/YY') )},
430 bind => [[a => '02/02/02']],
431 },
432 #47
433 {
434 func => 'select',
435 new => {bindtype => 'columns'},
436 args => ['test', '*', { a => {'<' => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, b => 8 }],
437 stmt => 'SELECT * FROM test WHERE ( a < to_date(?, \'MM/DD/YY\') AND b = ? )',
438 stmt_q => 'SELECT * FROM `test` WHERE ( `a` < to_date(?, \'MM/DD/YY\') AND `b` = ? )',
439 bind => [[a => '02/02/02'], [b => 8]],
440 },
32eab2da 441);
442
6dcf723c 443
7fb57243 444plan tests => scalar(grep { !$_->{warning_like} } @tests) * 2
445 + scalar(grep { $_->{warning_like} } @tests) * 4;
32eab2da 446
447for (@tests) {
96449e8e 448 local $"=', ';
32eab2da 449
96449e8e 450 my $new = $_->{new} || {};
451 $new->{debug} = $ENV{DEBUG} || 0;
32eab2da 452
7fb57243 453 # test without quoting labels
454 {
455 my $sql = SQL::Abstract->new(%$new);
456
457 my $func = $_->{func};
458 my($stmt, @bind);
459 my $test = sub {
460 ($stmt, @bind) = $sql->$func(@{$_->{args}})
461 };
462 if ($_->{warning_like}) {
463 warning_like { &$test } $_->{warning_like}, "throws the expected warning ($_->{warning_like})";
464 } else {
465 &$test;
466 }
467 is_same_sql_bind($stmt, \@bind, $_->{stmt}, $_->{bind});
468 }
32eab2da 469
96449e8e 470 # test with quoted labels
7fb57243 471 {
472 my $sql_q = SQL::Abstract->new(%$new, quote_char => '`', name_sep => '.');
32eab2da 473
7fb57243 474 my $func_q = $_->{func};
475 my($stmt_q, @bind_q);
476 my $test = sub {
477 ($stmt_q, @bind_q) = $sql_q->$func_q(@{$_->{args}})
478 };
479 if ($_->{warning_like}) {
480 warning_like { &$test } $_->{warning_like}, "throws the expected warning ($_->{warning_like})";
481 } else {
482 &$test;
483 }
32eab2da 484
7fb57243 485 is_same_sql_bind($stmt_q, \@bind_q, $_->{stmt_q}, $_->{bind});
486 }
32eab2da 487}