blob: 8b5732f287f7a1d25555a3b531de097b40b1f85a (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
require 'spec_helper'
module Arel
module Visitors
describe 'the oracle visitor' do
before do
@visitor = Oracle.new Table.engine
end
it 'modifies order when there is distinct and first value' do
# *sigh*
select = "DISTINCT foo.id, FIRST_VALUE(projects.name) OVER (foo) AS alias_0__"
stmt = Nodes::SelectStatement.new
stmt.cores.first.projections << Nodes::SqlLiteral.new(select)
stmt.orders << Nodes::SqlLiteral.new('foo')
sql = @visitor.accept(stmt)
sql.must_be_like %{
SELECT #{select} ORDER BY alias_0__
}
end
it 'is idempotent with crazy query' do
# *sigh*
select = "DISTINCT foo.id, FIRST_VALUE(projects.name) OVER (foo) AS alias_0__"
stmt = Nodes::SelectStatement.new
stmt.cores.first.projections << Nodes::SqlLiteral.new(select)
stmt.orders << Nodes::SqlLiteral.new('foo')
sql = @visitor.accept(stmt)
sql2 = @visitor.accept(stmt)
check sql.must_equal sql2
end
it 'splits orders with commas' do
# *sigh*
select = "DISTINCT foo.id, FIRST_VALUE(projects.name) OVER (foo) AS alias_0__"
stmt = Nodes::SelectStatement.new
stmt.cores.first.projections << Nodes::SqlLiteral.new(select)
stmt.orders << Nodes::SqlLiteral.new('foo, bar')
sql = @visitor.accept(stmt)
sql.must_be_like %{
SELECT #{select} ORDER BY alias_0__, alias_1__
}
end
describe 'Nodes::SelectStatement' do
describe 'limit' do
it 'adds a rownum clause' do
stmt = Nodes::SelectStatement.new
stmt.limit = 10
sql = @visitor.accept stmt
sql.must_be_like %{ SELECT WHERE ROWNUM <= 10 }
end
it 'is idempotent' do
stmt = Nodes::SelectStatement.new
stmt.orders << Nodes::SqlLiteral.new('foo')
stmt.limit = 10
sql = @visitor.accept stmt
sql2 = @visitor.accept stmt
check sql.must_equal sql2
end
it 'creates a subquery when there is order_by' do
stmt = Nodes::SelectStatement.new
stmt.orders << Nodes::SqlLiteral.new('foo')
stmt.limit = 10
sql = @visitor.accept stmt
sql.must_be_like %{
SELECT * FROM (SELECT ORDER BY foo) WHERE ROWNUM <= 10
}
end
it 'creates a subquery when there is DISTINCT' do
stmt = Nodes::SelectStatement.new
stmt.cores.first.projections << Nodes::SqlLiteral.new('DISTINCT id')
stmt.limit = 10
sql = @visitor.accept stmt
sql.must_be_like %{
SELECT * FROM (SELECT DISTINCT id) WHERE ROWNUM <= 10
}
end
it 'creates a different subquery when there is an offset' do
stmt = Nodes::SelectStatement.new
stmt.limit = 10
stmt.offset = Nodes::Offset.new(10)
sql = @visitor.accept stmt
sql.must_be_like %{
SELECT * FROM (
SELECT raw_sql_.*, rownum raw_rnum_
FROM (SELECT ) raw_sql_
WHERE rownum <= 20
)
WHERE raw_rnum_ > 10
}
end
it 'is idempotent with different subquery' do
stmt = Nodes::SelectStatement.new
stmt.limit = 10
stmt.offset = Nodes::Offset.new(10)
sql = @visitor.accept stmt
sql2 = @visitor.accept stmt
check sql.must_equal sql2
end
end
end
end
end
end
|
