Union and limit in one query in MonetDB

Question

How can I write following query in MonetDB:

CREATE TABLE "sampled" AS (
 SELECT *
 FROM   "base"
 WHERE  "target" = 'B'
 LIMIT  30
) UNION ALL (
 SELECT * 
 FROM "base" 
 WHERE "target" = 'A' 
 LIMIT 30 
) WITH DATA

?

When I attempt to execute the above query on:

MonetDB Database Server Toolkit v1.1 (Oct2014-SP2)

OS X 10.9.5

I am getting:

Error: syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION or ')' in: "create table "sampled" as (
SQLState:  42000
ErrorCode: 0 
Error:      select * 
SQLState:  22000 
ErrorCode: 0 
Error: from   "predictor_factory"."base"" 
SQLState:  22000 
ErrorCode: 0

Union alone works as expected:

SELECT *
FROM   "base"
WHERE  "target" = 'B'
UNION ALL
SELECT * 
FROM   "base"
WHERE  "target" = 'A'

Limit alone also works well:

SELECT * 
FROM   "base"
WHERE  "target" = 'A'
LIMIT  30

It's the combination that is troublesome for me.

Answer 1

MonetDB does not support LIMIT in subqueries. You could do the following

CREATE TABLE "sampled" AS SELECT * FROM "base" WHERE "target" = 'B' LIMIT 30 WITH DATA;
INSERT INTO "sampled" SELECT * FROM "base" WHERE "target" = 'A' LIMIT 30;