fix and regression test for RT #62642
[dbsrgits/DBIx-Class.git] / t / sqlmaker / limit_dialects / toplimit.t
CommitLineData
65c2b042 1use strict;
2use warnings;
3
4use Test::More;
65c2b042 5use lib qw(t/lib);
e606d0ce 6use DBICTest;
65c2b042 7use DBIC::SqlMakerTest;
ed0648ee 8
e606d0ce 9my $schema = DBICTest->init_schema;
65c2b042 10
e606d0ce 11# Trick the sqlite DB to use Top limit emulation
8f6dbee9 12# We could test all of this via $sq->$op directly,
ed0648ee 13# but some conditions need a $rsrc
20f44a33 14delete $schema->storage->_sql_maker->{_cached_syntax};
e606d0ce 15$schema->storage->_sql_maker->limit_dialect ('Top');
16
20f44a33 17my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, { prefetch => 'owner', rows => 2, offset => 3 });
e606d0ce 18
20f44a33 19for my $null_order (
20 undef,
21 '',
22 {},
23 [],
24 [{}],
25) {
26 my $rs = $books_45_and_owners->search ({}, {order_by => $null_order });
e606d0ce 27 is_same_sql_bind(
20f44a33 28 $rs->as_query,
29 '(SELECT TOP 2
30 id, source, owner, title, price, owner__id, owner__name
31 FROM (
32 SELECT TOP 5
33 me.id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name AS owner__name
34 FROM books me
35 JOIN owners owner ON owner.id = me.owner
36 WHERE ( source = ? )
37 ORDER BY me.id
38 ) me
39 ORDER BY me.id DESC
40 )',
07dc2055 41 [ [ source => 'Library' ] ],
65c2b042 42 );
43}
44
20f44a33 45
46for my $ord_set (
e606d0ce 47 {
83dee2e9 48 order_by => \'foo DESC',
e606d0ce 49 order_inner => 'foo DESC',
94fa8410 50 order_outer => 'ORDER__BY__1 ASC',
51 order_req => 'ORDER__BY__1 DESC',
52 exselect_outer => 'ORDER__BY__1',
53 exselect_inner => 'foo AS ORDER__BY__1',
e606d0ce 54 },
55 {
56 order_by => { -asc => 'foo' },
e606d0ce 57 order_inner => 'foo ASC',
94fa8410 58 order_outer => 'ORDER__BY__1 DESC',
59 order_req => 'ORDER__BY__1 ASC',
60 exselect_outer => 'ORDER__BY__1',
61 exselect_inner => 'foo AS ORDER__BY__1',
e606d0ce 62 },
63 {
20f44a33 64 order_by => { -desc => 'foo' },
65 order_inner => 'foo DESC',
94fa8410 66 order_outer => 'ORDER__BY__1 ASC',
67 order_req => 'ORDER__BY__1 DESC',
68 exselect_outer => 'ORDER__BY__1',
69 exselect_inner => 'foo AS ORDER__BY__1',
e606d0ce 70 },
71 {
20f44a33 72 order_by => 'foo',
73 order_inner => 'foo',
94fa8410 74 order_outer => 'ORDER__BY__1 DESC',
75 order_req => 'ORDER__BY__1',
76 exselect_outer => 'ORDER__BY__1',
77 exselect_inner => 'foo AS ORDER__BY__1',
e606d0ce 78 },
79 {
20f44a33 80 order_by => [ qw{ foo me.owner} ],
81 order_inner => 'foo, me.owner',
94fa8410 82 order_outer => 'ORDER__BY__1 DESC, me.owner DESC',
83 order_req => 'ORDER__BY__1, me.owner',
84 exselect_outer => 'ORDER__BY__1',
85 exselect_inner => 'foo AS ORDER__BY__1',
e606d0ce 86 },
87 {
88 order_by => ['foo', { -desc => 'bar' } ],
20f44a33 89 order_inner => 'foo, bar DESC',
94fa8410 90 order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 ASC',
91 order_req => 'ORDER__BY__1, ORDER__BY__2 DESC',
92 exselect_outer => 'ORDER__BY__1, ORDER__BY__2',
93 exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2',
e606d0ce 94 },
95 {
96 order_by => { -asc => [qw{ foo bar }] },
e606d0ce 97 order_inner => 'foo ASC, bar ASC',
94fa8410 98 order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 DESC',
99 order_req => 'ORDER__BY__1 ASC, ORDER__BY__2 ASC',
100 exselect_outer => 'ORDER__BY__1, ORDER__BY__2',
101 exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2',
e606d0ce 102 },
103 {
104 order_by => [
20f44a33 105 'foo',
e606d0ce 106 { -desc => [qw{bar}] },
20f44a33 107 { -asc => [qw{me.owner sensors}]},
e606d0ce 108 ],
20f44a33 109 order_inner => 'foo, bar DESC, me.owner ASC, sensors ASC',
94fa8410 110 order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 ASC, me.owner DESC, ORDER__BY__3 DESC',
111 order_req => 'ORDER__BY__1, ORDER__BY__2 DESC, me.owner ASC, ORDER__BY__3 ASC',
112 exselect_outer => 'ORDER__BY__1, ORDER__BY__2, ORDER__BY__3',
113 exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2, sensors AS ORDER__BY__3',
e606d0ce 114 },
20f44a33 115) {
116 my $o_sel = $ord_set->{exselect_outer}
117 ? ', ' . $ord_set->{exselect_outer}
118 : ''
119 ;
120 my $i_sel = $ord_set->{exselect_inner}
121 ? ', ' . $ord_set->{exselect_inner}
122 : ''
123 ;
83dee2e9 124
20f44a33 125 is_same_sql_bind(
126 $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}})->as_query,
127 "(SELECT TOP 2
128 id, source, owner, title, price, owner__id, owner__name
129 FROM (
130 SELECT TOP 2
131 id, source, owner, title, price, owner__id, owner__name$o_sel
132 FROM (
133 SELECT TOP 5
134 me.id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
135 FROM books me
136 JOIN owners owner ON owner.id = me.owner
137 WHERE ( source = ? )
138 ORDER BY $ord_set->{order_inner}
139 ) me
140 ORDER BY $ord_set->{order_outer}
141 ) me
142 ORDER BY $ord_set->{order_req}
143 )",
144 [ [ source => 'Library' ] ],
94fa8410 145 );
20f44a33 146}
8f6dbee9 147
20f44a33 148# with groupby
8f6dbee9 149is_same_sql_bind (
20f44a33 150 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
151 '(SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name
152 FROM (
153 SELECT TOP 2 id, source, owner, title, price
154 FROM (
155 SELECT TOP 2
75f025cf 156 id, source, owner, title, price
20f44a33 157 FROM (
158 SELECT TOP 5
75f025cf 159 me.id, me.source, me.owner, me.title, me.price
20f44a33 160 FROM books me
161 JOIN owners owner ON owner.id = me.owner
162 WHERE ( source = ? )
163 GROUP BY title
164 ORDER BY title
165 ) me
75f025cf 166 ORDER BY title DESC
20f44a33 167 ) me
75f025cf 168 ORDER BY title
83dee2e9 169 ) me
20f44a33 170 JOIN owners owner ON owner.id = me.owner
171 WHERE ( source = ? )
172 ORDER BY title
173 )',
83dee2e9 174 [ [ source => 'Library' ], [ source => 'Library' ] ],
8f6dbee9 175);
a5f843e3 176
20f44a33 177# test deprecated column mixing over join boundaries
41eac664 178my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
179 '+select' => ['owner.name'],
180 '+as' => ['owner_name'],
181 join => 'owner',
182 rows => 1
183});
a5f843e3 184
185is_same_sql_bind( $rs_selectas_top->search({})->as_query,
41eac664 186 '(SELECT
187 TOP 1 me.id, me.source, me.owner, me.title, me.price,
20f44a33 188 owner.name AS owner_name
41eac664 189 FROM books me
190 JOIN owners owner ON owner.id = me.owner
191 WHERE ( source = ? )
20f44a33 192 ORDER BY me.id
41eac664 193 )',
a5f843e3 194 [ [ 'source', 'Library' ] ],
195 );
196
197done_testing;