mirror of https://github.com/apache/cassandra
342 lines
12 KiB
SQL
342 lines
12 KiB
SQL
/*
|
||
# Licensed to the Apache Software Foundation (ASF) under one
|
||
# or more contributor license agreements. See the NOTICE file
|
||
# distributed with this work for additional information
|
||
# regarding copyright ownership. The ASF licenses this file
|
||
# to you under the Apache License, Version 2.0 (the
|
||
# "License"); you may not use this file except in compliance
|
||
# with the License. You may obtain a copy of the License at
|
||
#
|
||
# http://www.apache.org/licenses/LICENSE-2.0
|
||
#
|
||
# Unless required by applicable law or agreed to in writing, software
|
||
# distributed under the License is distributed on an "AS IS" BASIS,
|
||
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
|
||
# See the License for the specific language governing permissions and
|
||
# limitations under the License.
|
||
*/
|
||
|
||
CREATE TABLE has_all_types (
|
||
num int PRIMARY KEY,
|
||
intcol int,
|
||
asciicol ascii,
|
||
bigintcol bigint,
|
||
blobcol blob,
|
||
booleancol boolean,
|
||
decimalcol decimal,
|
||
doublecol double,
|
||
durationcol duration,
|
||
floatcol float,
|
||
smallintcol smallint,
|
||
textcol text,
|
||
timestampcol timestamp,
|
||
tinyintcol tinyint,
|
||
uuidcol uuid,
|
||
varcharcol varchar,
|
||
varintcol varint,
|
||
listcol list<text>,
|
||
setcol set<text>,
|
||
mapcol map<text, int>,
|
||
vectorcol vector<float, 3>,
|
||
) WITH compression = {'class':'LZ4Compressor'};
|
||
|
||
INSERT INTO has_all_types (num, intcol, asciicol, bigintcol, blobcol, booleancol,
|
||
decimalcol, doublecol, durationcol, floatcol, smallintcol, textcol,
|
||
timestampcol, tinyintcol, uuidcol, varcharcol, varintcol,
|
||
listcol, setcol, mapcol, vectorcol)
|
||
VALUES (0, -12, 'abcdefg', 1234567890123456789, 0x000102030405fffefd, true,
|
||
19952.11882, 1.0, 12h, -2.1, 32767, 'Voilá!',
|
||
'2012-05-14 12:53:20+0000', 127, bd1924e1-6af8-44ae-b5e1-f24131dbd460, '"', 10000000000000000000000000,
|
||
['a', 'b', 'c'], {'a', 'b', 'c'}, {'a': 1, 'b': 2, 'c': 3}, [1.0, 2.0, 3.0]);
|
||
|
||
INSERT INTO has_all_types (num, intcol, asciicol, bigintcol, blobcol, booleancol,
|
||
decimalcol, doublecol, durationcol, floatcol, smallintcol, textcol,
|
||
timestampcol, tinyintcol, uuidcol, varcharcol, varintcol,
|
||
listcol, setcol, mapcol, vectorcol)
|
||
VALUES (1, 2147483647, '__!''$#@!~"', 9223372036854775807, 0xffffffffffffffffff, true,
|
||
0.00000000000001, 9999999.999, 12h30m, 99999.999, 32767, '∭Ƕ⑮ฑ➳❏''',
|
||
'1950-01-01+0000', 127, ffffffff-ffff-ffff-ffff-ffffffffffff, 'newline->
|
||
<-', 9,
|
||
['__!''$#@!~"'], {'__!''$#@!~"'}, {'__!''$#@!~"': 2147483647}, [1, -2.0, NaN]);
|
||
|
||
INSERT INTO has_all_types (num, intcol, asciicol, bigintcol, blobcol, booleancol,
|
||
decimalcol, doublecol, durationcol, floatcol, smallintcol, textcol,
|
||
timestampcol, tinyintcol, uuidcol, varcharcol, varintcol,
|
||
listcol, setcol, mapcol, vectorcol)
|
||
VALUES (2, 0, '', 0, 0x, false,
|
||
0.0, 0.0, 12h30m30s, 0.0, 0, '',
|
||
0, 0, 00000000-0000-0000-0000-000000000000, '', 0,
|
||
[], {}, {}, [0.0, 0.0, 0.0]);
|
||
|
||
INSERT INTO has_all_types (num, intcol, asciicol, bigintcol, blobcol, booleancol,
|
||
decimalcol, doublecol, durationcol, floatcol, smallintcol, textcol,
|
||
timestampcol, tinyintcol, uuidcol, varcharcol, varintcol,
|
||
listcol, setcol, mapcol, vectorcol)
|
||
VALUES (3, -2147483648, '''''''', -9223372036854775808, 0x80, false,
|
||
10.0000000000000, -1004.10, 12h30m30s250ms, 100000000.9, 32767, '龍馭鬱',
|
||
'2038-01-19T03:14-1200', 127, ffffffff-ffff-1fff-8fff-ffffffffffff,
|
||
'''', -10000000000000000000000000,
|
||
['龍馭鬱'], {'龍馭鬱'}, {'龍馭鬱': -2147483648}, [100000000.9, 100000000.9, 100000000.9]);
|
||
|
||
INSERT INTO has_all_types (num, intcol, asciicol, bigintcol, blobcol, booleancol,
|
||
decimalcol, doublecol, floatcol, smallintcol, textcol,
|
||
timestampcol, tinyintcol, uuidcol, varcharcol, varintcol)
|
||
VALUES (4, blob_as_int(0x), '', blob_as_bigint(0x), 0x, blob_as_boolean(0x),
|
||
blob_as_decimal(0x), blob_as_double(0x), blob_as_float(0x), blob_as_smallInt(0x0000), '',
|
||
blob_as_timestamp(0x), blob_as_tinyint(0x00), blob_as_uuid(0x), '', blob_as_varint(0x));
|
||
|
||
|
||
|
||
CREATE TABLE empty_table (
|
||
lonelykey float primary key,
|
||
lonelycol text
|
||
);
|
||
|
||
|
||
|
||
CREATE TABLE dynamic_columns (
|
||
somekey int,
|
||
column1 float,
|
||
value text,
|
||
PRIMARY KEY(somekey, column1)
|
||
);
|
||
|
||
INSERT INTO dynamic_columns (somekey, column1, value) VALUES (1, 1.2, 'one point two');
|
||
INSERT INTO dynamic_columns (somekey, column1, value) VALUES (2, 2.3, 'two point three');
|
||
INSERT INTO dynamic_columns (somekey, column1, value) VALUES (3, 3.46, 'three point four six');
|
||
INSERT INTO dynamic_columns (somekey, column1, value) VALUES (3, 99.0, 'ninety-nine point oh');
|
||
INSERT INTO dynamic_columns (somekey, column1, value) VALUES (3, -0.0001, 'negative ten thousandth');
|
||
|
||
|
||
|
||
CREATE TABLE twenty_rows_table (
|
||
a text primary key,
|
||
b text
|
||
);
|
||
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('1', '1');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('2', '2');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('3', '3');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('4', '4');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('5', '5');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('6', '6');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('7', '7');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('8', '8');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('9', '9');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('10', '10');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('11', '11');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('12', '12');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('13', '13');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('14', '14');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('15', '15');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('16', '16');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('17', '17');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('18', '18');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('19', '19');
|
||
INSERT INTO twenty_rows_table (a, b) VALUES ('20', '20');
|
||
|
||
|
||
CREATE TABLE undefined_values_table (
|
||
k text PRIMARY KEY,
|
||
c text,
|
||
notthere text
|
||
);
|
||
|
||
INSERT INTO undefined_values_table (k, c) VALUES ('k1', 'c1');
|
||
INSERT INTO undefined_values_table (k, c) VALUES ('k2', 'c2');
|
||
|
||
|
||
|
||
CREATE TABLE ascii_with_special_chars (
|
||
k int PRIMARY KEY,
|
||
val ascii
|
||
);
|
||
|
||
-- "newline:\n"
|
||
INSERT INTO ascii_with_special_chars (k, val) VALUES (0, blob_as_ascii(0x6e65776c696e653a0a));
|
||
-- "return\rand null\0!"
|
||
INSERT INTO ascii_with_special_chars (k, val) VALUES (1, blob_as_ascii(0x72657475726e0d616e64206e756c6c0021));
|
||
-- "\x00\x01\x02\x03\x04\x05control chars\x06\x07"
|
||
INSERT INTO ascii_with_special_chars (k, val) VALUES (2, blob_as_ascii(0x000102030405636f6e74726f6c2063686172730607));
|
||
-- "fake special chars\\x00\\n"
|
||
INSERT INTO ascii_with_special_chars (k, val) VALUES (3, blob_as_ascii(0x66616b65207370656369616c2063686172735c7830305c6e));
|
||
|
||
|
||
|
||
CREATE TABLE utf8_with_special_chars (
|
||
k int PRIMARY KEY,
|
||
val text
|
||
);
|
||
|
||
INSERT INTO utf8_with_special_chars (k, val) VALUES (0, 'Normal string');
|
||
INSERT INTO utf8_with_special_chars (k, val) VALUES (1, 'Text with
|
||
newlines
|
||
');
|
||
INSERT INTO utf8_with_special_chars (k, val) VALUES (2, 'Text with embedded char');
|
||
INSERT INTO utf8_with_special_chars (k, val) VALUES (3, 'ⓈⓅⒺⒸⒾⒶⓁ ⒞⒣⒜⒭⒮ and normal ones');
|
||
INSERT INTO utf8_with_special_chars (k, val) VALUES (4, 'double wides: ⾑⾤⾚');
|
||
INSERT INTO utf8_with_special_chars (k, val) VALUES (5, 'zero widthspace');
|
||
INSERT INTO utf8_with_special_chars (k, val) VALUES (6, 'fake special chars\x00\n');
|
||
|
||
|
||
CREATE TABLE empty_composite_table (
|
||
lonelykey float,
|
||
lonelycol text,
|
||
lonelyval int,
|
||
primary key (lonelykey, lonelycol)
|
||
);
|
||
|
||
CREATE TABLE twenty_rows_composite_table (
|
||
a text,
|
||
b text,
|
||
c text,
|
||
primary key (a, b)
|
||
);
|
||
|
||
-- all in the same storage engine row:
|
||
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '1', '1');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '2', '2');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '3', '3');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '4', '4');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '5', '5');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '6', '6');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '7', '7');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '8', '8');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '9', '9');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '10', '10');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '11', '11');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '12', '12');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '13', '13');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '14', '14');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '15', '15');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '16', '16');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '17', '17');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '18', '18');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '19', '19');
|
||
INSERT INTO twenty_rows_composite_table (a, b, c) VALUES ('A', '20', '20');
|
||
|
||
CREATE TYPE address (
|
||
city text,
|
||
address text,
|
||
zip text
|
||
);
|
||
|
||
CREATE TYPE phone_number (
|
||
country text,
|
||
number text
|
||
);
|
||
|
||
CREATE TABLE users (
|
||
login text PRIMARY KEY,
|
||
name text,
|
||
addresses set<frozen<address>>,
|
||
phone_numbers set<frozen<phone_number>>
|
||
);
|
||
|
||
insert into users (login, name, addresses, phone_numbers)
|
||
values ('jbellis',
|
||
'jonathan ellis',
|
||
{{city: 'Austin', address: '902 East 5th St. #202', zip: '78702'},
|
||
{city: 'Sunnyvale', address: '292 Gibraltar Drive #107', zip: '94089'}},
|
||
{{country: '+44', number: '208 622 3021'},
|
||
{country: '+1', number: '512-537-7809'}});
|
||
|
||
insert into users (login, name, addresses, phone_numbers)
|
||
values ('vpupkin',
|
||
'vasya pupkin',
|
||
{{city: 'Chelyabinsk', address: '3rd street', zip: null},
|
||
{city: 'Chigirinsk', address: null, zip: '676722'}},
|
||
{{country: '+7', number: null},
|
||
{country: null, number: '03'}});
|
||
|
||
CREATE TYPE band_info_type (
|
||
founded varint,
|
||
members set<text>,
|
||
description text
|
||
);
|
||
|
||
CREATE TYPE tags (
|
||
tags map<text, text>
|
||
);
|
||
|
||
CREATE TABLE songs (
|
||
title text PRIMARY KEY,
|
||
band text,
|
||
info frozen<band_info_type>,
|
||
tags frozen<tags>
|
||
);
|
||
|
||
insert into songs (title, band, info, tags)
|
||
values (
|
||
'The trooper',
|
||
'Iron Maiden',
|
||
{
|
||
founded:188694000,
|
||
members: {
|
||
'Bruce Dickinson',
|
||
'Dave Murray',
|
||
'Adrian Smith',
|
||
'Janick Gers',
|
||
'Steve Harris',
|
||
'Nicko McBrain'
|
||
},
|
||
description: 'Pure evil metal'
|
||
},
|
||
{
|
||
tags: {
|
||
'genre':'metal',
|
||
'origin':'england'
|
||
}
|
||
});
|
||
|
||
CREATE FUNCTION fBestband ( input double )
|
||
RETURNS NULL ON NULL INPUT
|
||
RETURNS text
|
||
LANGUAGE java
|
||
AS 'return "Iron Maiden";';
|
||
|
||
CREATE FUNCTION fBestsong ( input double )
|
||
RETURNS NULL ON NULL INPUT
|
||
RETURNS text
|
||
LANGUAGE java
|
||
AS 'return "Revelations";';
|
||
|
||
CREATE FUNCTION fMax(current int, candidate int)
|
||
CALLED ON NULL INPUT
|
||
RETURNS int
|
||
LANGUAGE java
|
||
AS 'if (current == null) return candidate; else return Math.max(current, candidate);' ;
|
||
|
||
CREATE FUNCTION fMin(current int, candidate int)
|
||
CALLED ON NULL INPUT
|
||
RETURNS int
|
||
LANGUAGE java
|
||
AS 'if (current == null) return candidate; else return Math.min(current, candidate);' ;
|
||
|
||
CREATE AGGREGATE aggMax(int)
|
||
SFUNC fMax
|
||
STYPE int
|
||
INITCOND null;
|
||
|
||
CREATE AGGREGATE aggMin(int)
|
||
SFUNC fMin
|
||
STYPE int
|
||
INITCOND null;
|
||
|
||
CREATE TYPE quote_udt (
|
||
data text
|
||
);
|
||
|
||
CREATE TABLE escape_quotes (
|
||
id int PRIMARY KEY,
|
||
text_data text,
|
||
map_data map<int, text>,
|
||
set_data set<text>,
|
||
list_data list<text>,
|
||
tuple_data tuple<int, text>,
|
||
udt_data frozen<quote_udt>
|
||
);
|
||
|
||
INSERT INTO escape_quotes (id, text_data, map_data, set_data, list_data, tuple_data, udt_data) values(1, 'I''m newb', {1:'I''m newb'}, {'I''m newb'}, ['I''m newb'], (1, 'I''m newb'), {data: 'I''m newb'});
|