CrateDB is a distributed SQL database that makes it simple to store and analyze massive amounts of machine data in real-time.

Overview
CrateDB
https://github.com/crate/crate/workflows/CrateDB%20SQL/badge.svg?branch=master

About

CrateDB is a distributed SQL database that makes it simple to store and analyze massive amounts of machine data in real-time.

CrateDB offers the scalability and flexibility typically associated with a NoSQL database, is designed to run on inexpensive commodity servers and can be deployed and run on any sort of network - from personal computers to multi-region hybrid clouds.

The smallest CrateDB clusters can easily ingest tens of thousands of records per second. The data can be queried, ad-hoc, in parallel across the whole cluster in real time.

Features

  • Standard SQL interface available via HTTP API and PostgreSQL wire protocol.
  • Dynamic schemas, queryable objects, geospatial features, time series data support, and realtime full-text search providing functionality for handling both relational and document oriented nested data structures.
  • Horizontally scalable, highly available and fault tolerant clusters that run very well in virtualized and containerised environments.
  • Extremely fast distributed query execution.
  • Auto-partitioning, auto-sharding, and auto-replication.
  • Self-healing and auto-rebalancing.

Screenshots

CrateDB provides an Admin UI:

Screenshots of the CrateDB admin UI

Try CrateDB

The fastest way to try CrateDB out is by running:

$ bash -c "$(curl -L try.crate.io)"

Or spin up the official Docker image:

$ docker run --publish 4200:4200 --publish 5432:5432 crate -Cdiscovery.type=single-node

Visit the getting started page to see all the available download and install options.

Once you're up and running, head over to the introductory docs. In order to connect to CrateDB from applications, see our list of CrateDB clients and tools or use the CrateDB shell in order to invoke ad-hoc commands.

For running CrateDB on Kubernetes, the CrateDB Kubernetes Operator and its CrateDB Kubernetes Operator Documentation might be of interest.

Contributing

This project is primarily maintained by Crate.io, but we welcome community contributions!

See the developer docs and the contribution docs for more information.

Help

Looking for more help?

Comments
  • Statements stuck in sys.jobs

    Statements stuck in sys.jobs

    Environment: Crate 1.1.6 Amazon Linux AMI 4.9.38-16.33.amzn1.x86_64 6 AWS t2.small nodes, in 3 availability zones

    Problem description: I posted this issue on Slack and was instructed to crate an issue here.

    Seeing quite a few (over 100) entries in sys.jobs for statements with old start dates ... up to a week ago. The application which started the query is definitely not running and I don't seem to be able to KILL the job either (nothing happens).

    Current 102 jobs. Killing a couple of them using KILL didn't change anything (the job still shows up in sys.jobs):

    image

    I've tried running a few of the queries found in sys.jobs and they all run in under a second. Nothing noteworthy is in the logs. Node CPU looks normal: image

    I haven't restarted the nodes in attempt to maintain this state. What else should I look for? Thanks.

    bug v/2.3 
    opened by tellezb 37
  • Under-Replicated Records not recovering

    Under-Replicated Records not recovering

    CrateDB version 4.1.2

    • JVM version: openjdk version "11" 2018-09-25
    • Kernel: 4.15.0-43-generic #46~16.04.1-Ubuntu SMP Fri Dec 7 13:31:08 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
    • Distribution: Ubuntu 16.04.5 LTS
    • Number of nodes: 4

    Problem description: For the last 72h or for one of the tables with characteristics as below:

    • Configured Replicas: 0-1
    • Configured Shards: 8
    • Started Shards: 15
    • Missing Shards: 0
    • Total Records: 747.5 Million
    • Unavailable Records: 0
    • Size (Sum of primary shards): 981.7 GB

    Status shows:

    • Health: warning
    • Under-Replicated Records: 93.4 Million
    • Under-Replicated Shards: 1
    • Recovery: 93.8%

    and there is no progress in recovery at all for last 3 days or so.

    What I would expect is CrateDB to make automated recovery within sensible period of time (several hours).

    Please let me know if there is any additional diagnostic logs / information I can provide to help understand root cause of the issue and fix it.

    Regards, Chris

    opened by l7s 30
  • 9651 scalar current date

    9651 scalar current date

    Summary of the changes / Why this improves CrateDB

    • Added date type
    • Added curdate scalar function returning current date in UTC

    Checklist

    • [x] Added an entry in CHANGES.txt for user facing changes
    • [x] Updated documentation & sql_features table for user facing changes
    • [x] Touched code is covered by tests
    • [x] CLA is signed
    • [x] This does not contain breaking changes, or if it does:
      • It is released within a major release
      • It is recorded in CHANGES.txt
      • It was marked as deprecated in an earlier release if possible
      • You've thought about the consequences and other components are adapted (E.g. AdminUI)
    ready-to-merge 
    opened by BaurzhanSakhariev 26
  • Stalled and unkillable COPY FROM

    Stalled and unkillable COPY FROM

    Hello,

    • Crate 0.54.5

    I launched a COPY FROM with one set of json files (generated by a COPY TO) of ~200M documents

    COPY events_2015_10 FROM '/tmp/backup_crate/events_2015_10/*' WITH(compression='gzip')
    
    1. It imported about ~100M documents (in few hours) then stopped: the load decreased and no documents were added.
    2. The query is still in sys.jobs
    3. A kill id or kill all does nothing

    It looks like it did one shard then freezed.

    Ben

    bug 
    opened by benjaminrigaud 25
  • Big partitioned table isn't recovering on 4.6.3 on k8s cluster.

    Big partitioned table isn't recovering on 4.6.3 on k8s cluster.

    CrateDB version

    4.6.3

    CrateDB setup information

    3 node crate 4.6.3 cluster on k8s with 6g heap size and a table with 900 million records partitioned per week with 12 shards and 1 replica.

    Steps to Reproduce

    I upgraded from 4.6.1 to 4.6.3 by bringing the 3 pods down. Then adjusting the yaml and bringing the cluster up again. That went fine, all is starting and most tables in db are recovering fine. But the big table isn't. The table is partly recovering. The oldest partitions are stated critical and won't recovering, while others turn green. Crate is saying the total of the amount of records in the table are 760 million instead of the 910 million it should have. I don't understand, because there must be at least 1 replica on one of the nodes, but it seams to be lost because its staying critical. The only thing I can do is restore the whole table from a snapshot now. This is happend me before also on 4.6.1. So I think somehow big tables are hard to recover. Didn't have problem this on older cratedb versions.

    Expected Result

    I expect it to rebuild the table fine. because it was green before the cluster was downnscaled to 0 and there was a replica in place.

    Actual Result

    Not sure where to find the logs.

    opened by rjbaat 23
  • Implement

    Implement "CREATE TABLE AS"

    …with column constraints then inserts the subquery results

    Summary of the changes / Why this improves CrateDB

    This is regarding #9999

    documentation remaining

    Checklist

    • [x] Added an entry in CHANGES.txt for user facing changes
    • [x] Updated documentation & sql_features table for user facing changes
    • [x] Touched code is covered by tests
    • [x] CLA is signed
    • [x] This does not contain breaking changes, or if it does:
      • It is released within a major release
      • It is recorded in CHANGES.txt
      • It was marked as deprecated in an earlier release if possible
      • You've thought about the consequences and other components are adapted (E.g. AdminUI)
    ready-to-merge 
    opened by jeeminso 22
  • JDBC support for Object array

    JDBC support for Object array

    CrateDB version: 0.57.8

    JVM version: 1.8.0_74 OS version / environment description: MAC-OS X 10.11.6 Problem description: Not able to insert a column (using JDBC preparedStatement) of Type -> Array of Object

    [2017-05-23 16:22:15,738][WARN ][io.crate.analyze.SetStatementAnalyzer] SET STATEMENT WITH SESSION OR LOCAL WILL BE IGNORED: SetStatement{scope=SESSION, assignments=[Assignment{column="extra_float_digits", expressions=[3]}], settingType=TRANSIENT}
    [2017-05-23 16:22:16,078][WARN ][io.crate.protocols.postgres.types.PGType] decodeUTF8Text failed. input= ..... type=_json
    

    Steps to reproduce: A simple code shown below will show this error.

    List<PGobject> entities = new ArrayList<>();
        PGobject entityObject = new PGobject();
        entityObject.setType("json");
        entityObject.setValue(gson.toJson(customObject.getEntity().get(0)));
        entities.add(entityObject);
        statement.setArray(1, connection.createArrayOf("object", entities.toArray()));
    

    relevant part of the schema

    entity array(object as (
       id string,
       type string,
       name string INDEX using fulltext with (type = 'standard'),
       attr1 string,
       info string,
       count int
    )),
    

    Use case:

    Feature description:

    opened by ramach 22
  • New snapshots have PARTIAL state on healthy cluster

    New snapshots have PARTIAL state on healthy cluster

    CrateDB version: 2.0.4

    JVM version: openjdk version "1.8.0_131" OpenJDK Runtime Environment (build 1.8.0_131-8u131-b11-0ubuntu1.16.04.2-b11) OpenJDK 64-Bit Server VM (build 25.131-b11, mixed mode)

    OS version / environment description: Ubuntu 16.04.2 LTS (GNU/Linux 4.4.0-47-generic x86_64)

    Problem description: New snapshots suddenly finish with PARTIAL state, despite the cluster being healthy (health: good, available data: 100%, replicated data: 100%, unrerepl. records: 0, unavailable records: 0).

    Creating a new repository and creating snapshots there seems to work though (state SUCCESS). Maybe repository got corrupted ?

    select repository, name, state, started, finished, version from sys.snapshots limit 100;
    

    repository | name | state | started | finished | version -- | -- | -- | -- | -- | -- snapshots | 26-04-2017-20-21 | SUCCESS | 1493227309376 (Wed, 26 Apr 2017 17:21:49 GMT) | 1493227471359 (Wed, 26 Apr 2017 17:24:31 GMT) | 2.4.2 snapshots | 25.05.2017-09:55 | SUCCESS | 1495695306492 (Thu, 25 May 2017 06:55:06 GMT) | 1495695522147 (Thu, 25 May 2017 06:58:42 GMT) | 2.4.2 snapshots | 30.05.2017-14:35 | SUCCESS | 1496144095789 (Tue, 30 May 2017 11:34:55 GMT) | 1496144283098 (Tue, 30 May 2017 11:38:03 GMT) | 2.4.2 snapshots | 01.06.2017-13:02 | SUCCESS | 1496311351877 (Thu, 01 Jun 2017 10:02:31 GMT) | 1496312124287 (Thu, 01 Jun 2017 10:15:24 GMT) | 2.4.2 snapshots | 06.06.2017-13:07 | SUCCESS | 1496743679331 (Tue, 06 Jun 2017 10:07:59 GMT) | 1496744036015 (Tue, 06 Jun 2017 10:13:56 GMT) | 2.4.2 ... snapshots | 10-07-2017-18-42 | SUCCESS | 1499701368996 (Mon, 10 Jul 2017 15:42:48 GMT) | 1499701637805 (Mon, 10 Jul 2017 15:47:17 GMT) | 2.4.2 snapshots | 10-07-2017-18-47 | SUCCESS | 1499701669194 (Mon, 10 Jul 2017 15:47:49 GMT) | 1499701753663 (Mon, 10 Jul 2017 15:49:13 GMT) | 2.4.2 snapshots | 10-07-2017-23-47 | SUCCESS | 1499719663445 (Mon, 10 Jul 2017 20:47:43 GMT) | 1499722686019 (Mon, 10 Jul 2017 21:38:06 GMT) | 5.0.2 snapshots | 12-07-2017-22-06 | PARTIAL | 1499886417891 (Wed, 12 Jul 2017 19:06:57 GMT) | 1499887327666 (Wed, 12 Jul 2017 19:22:07 GMT) | 5.0.2 snapshots | 12-07-2017-22-26 | PARTIAL | 1499887582649 (Wed, 12 Jul 2017 19:26:22 GMT) | 1499887764824 (Wed, 12 Jul 2017 19:29:24 GMT) | 5.0.2 snapshots | 12-07-2017-22-37-route | PARTIAL | 1499888273737 (Wed, 12 Jul 2017 19:37:53 GMT) | 1499888342670 (Wed, 12 Jul 2017 19:39:02 GMT) | 5.0.2 test | route | SUCCESS | 1499891748611 (Wed, 12 Jul 2017 20:35:48 GMT) | 1499892863318 (Wed, 12 Jul 2017 20:54:23 GMT) | 5.0.2

    Steps to reproduce:

    Started with fresh 2.0.4. Restored from a previous snapshot (name: 10-07-2017-18-47, created on 1.1.3). Created new snapshot after restore and optimize (worked, name: 10-07-2017-23-47, state: SUCCESS) Every snapshot after that has state PARTIAL (name: 12-07-2017-22-06, 12-07-2017-22-26, etc.)

    Create new repository Create snapshot (worked, name: route, state: SUCCESS)

    Tried snapshot of same table on both repositories (12-07-2017-22-37-route vs route).

    Use case:

    Feature description:

    opened by rps-v 21
  • Sql: Allow array counting

    Sql: Allow array counting

    Hi,

    it would be really great if we could do some basic arrays operations like array length, or array intersect (like the && operator in postgresql ie) ...

    Thanks a lot for your work

    feature: sql: operators feature: sql: scalars v/3.2 
    opened by eMerzh 21
  • constant amount of underreplicated records

    constant amount of underreplicated records

    CrateDB version: 2.2.7 docker centos 7.4 12 Nodes docker 10G heap size crate-python 0.21.1 Problem description:

    constant amount of underreplicated records that never decreases. image

    [email protected]    | org.elasticsearch.env.ShardLockObtainFailedException: [.partitioned.events_schelling.04732d1p6cqjidho60o30c1g][2]: obtaining shard lock timed out after 5000ms
    [email protected]    | 	at org.elasticsearch.env.NodeEnvironment$InternalShardLock.acquire(NodeEnvironment.java:726) ~[crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.env.NodeEnvironment.shardLock(NodeEnvironment.java:645) ~[crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.index.store.Store.readMetadataSnapshot(Store.java:414) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.indices.store.TransportNodesListShardStoreMetaData.listStoreMetaData(TransportNodesListShardStoreMetaData.java:153) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.indices.store.TransportNodesListShardStoreMetaData.nodeOperation(TransportNodesListShardStoreMetaData.java:112) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.indices.store.TransportNodesListShardStoreMetaData.nodeOperation(TransportNodesListShardStoreMetaData.java:64) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.action.support.nodes.TransportNodesAction.nodeOperation(TransportNodesAction.java:140) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.action.support.nodes.TransportNodesAction$NodeTransportHandler.messageReceived(TransportNodesAction.java:262) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.action.support.nodes.TransportNodesAction$NodeTransportHandler.messageReceived(TransportNodesAction.java:258) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.transport.RequestHandlerRegistry.processMessageReceived(RequestHandlerRegistry.java:69) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.transport.TcpTransport$RequestHandler.doRun(TcpTransport.java:1544) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:638) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_151]
    [email protected]    | 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_151]
    [email protected]    | 	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_151]
    
    
    

    today I also found this in the log

    [email protected]    | [2018-02-09T11:12:33,542][INFO ][o.e.i.s.TransportNodesListShardStoreMetaData] [hephaistos1crate] [.partitioned.events_schelling.04732d1n6ssjae1k60o30c1g][2]: failed to obtain shard lock
    [email protected]    | org.elasticsearch.env.ShardLockObtainFailedException: [.partitioned.events_schelling.04732d1n6ssjae1k60o30c1g][2]: obtaining shard lock timed out after 5000ms
    [email protected]    | 	at org.elasticsearch.env.NodeEnvironment$InternalShardLock.acquire(NodeEnvironment.java:726) ~[crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.env.NodeEnvironment.shardLock(NodeEnvironment.java:645) ~[crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.index.store.Store.readMetadataSnapshot(Store.java:414) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.indices.store.TransportNodesListShardStoreMetaData.listStoreMetaData(TransportNodesListShardStoreMetaData.java:153) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.indices.store.TransportNodesListShardStoreMetaData.nodeOperation(TransportNodesListShardStoreMetaData.java:112) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.indices.store.TransportNodesListShardStoreMetaData.nodeOperation(TransportNodesListShardStoreMetaData.java:64) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.action.support.nodes.TransportNodesAction.nodeOperation(TransportNodesAction.java:140) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.action.support.nodes.TransportNodesAction$NodeTransportHandler.messageReceived(TransportNodesAction.java:262) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.action.support.nodes.TransportNodesAction$NodeTransportHandler.messageReceived(TransportNodesAction.java:258) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.transport.RequestHandlerRegistry.processMessageReceived(RequestHandlerRegistry.java:69) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.transport.TcpTransport$RequestHandler.doRun(TcpTransport.java:1544) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:638) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) [crate-app-2.2.7.jar:2.2.7]
    [email protected]    | 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_151]
    [email protected]    | 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_151]
    [email protected]    | 	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_151]
    
    

    Steps to reproduce: possible reason: upgraded from 2.2.4 to 2.2.7

    bug v/2.3 
    opened by sonix07 20
  • Failed to open output when running COPY TO

    Failed to open output when running COPY TO

    CrateDB Version: 2.17

    JVM: OpenJDK 1.8.0_131

    OS: Ubuntu 16.04.3 LTS Linux 4.4.38-std-1 x86_64

    Problem description:

    When attempting to copy a table to JSON, some of the files are created, and one fails, giving this error. SQLActionException[NotSerializableExceptionWrapper: unhandled_server_exception: Failed to open output: '/mnt/data/crate-copies/table_name_2_.json (No such file or directory)'] If the command is reran, sometimes the same file fails, sometimes a different file fails. Our setup is a three node cluster, all running the same version of cratedb and ubuntu. The table we are attempting to copy has 2.3 million records in it, so it's not expected to be very quick, but other much smaller tables also get the same error. The COPY TO command has been ran on this table before, and succeeded without error, although I think this was before we switched from 2.0.7 to 2.1.7.

    Steps to reproduce:

    COPY table_name TO DIRECTORY '/mnt/data/crate-copies'; Where the table_name is any table.

    opened by nocturnaltortoise 19
  • Fix function resolution for bit types with different lengths

    Fix function resolution for bit types with different lengths

    Similar to STRING and CHARACTER types, need to resolve compatibility of BIT strings with different length.

    See https://github.com/crate/crate/commit/0312972713071214ee27af408989a32f6cb58fe1#diff-2f38770d54ce83a675bffd39094bb90580effb7cc9e2213ded1237e90f4c0daaR104 and

    https://github.com/crate/crate/commit/1866a331f90aaaeabc6a789a2c11cee2aac5c611#diff-2f38770d54ce83a675bffd39094bb90580effb7cc9e2213ded1237e90f4c0daaR104

    We compare BIT strings lexicographically, so it's fine to compare bit strings with different length.

    For operations requiring same length (bitwise functions), we have an explicit check.

    Originally issue was spot on queries with bit operators of the same length:

    CREATE TABLE tbl (b BIT(3));
    SELECT * FROM tbl WHERE b & B'100' =  B'100'
    

    We register bitwise functions with the default instance (len=1) and then allow base name match for them.

    without this change, b & B'100' becames smth with return type of length 1 and then skips compatibility resolution (which is added now) and fails in https://github.com/crate/crate/blob/master/server/src/main/java/io/crate/types/TypeCompatibility.java#L122

    v/5.1 
    opened by BaurzhanSakhariev 2
  • Add optimizer rule to push down a Filter beneath a CorrelatedJoin

    Add optimizer rule to push down a Filter beneath a CorrelatedJoin

    Summary of the changes / Why this improves CrateDB

    If a Filter is not pushed down, other rules to push down the query or used references to a beneath Collect operation won't kick in, resulting in a planner error.

    Fixes the failing query described here https://github.com/crate/crate/issues/7335#issuecomment-1369752183.

    Checklist

    • [x] Added an entry in CHANGES.txt for user facing changes
    • [x] Updated documentation & sql_features table for user facing changes
    • [x] Touched code is covered by tests
    • [x] CLA is signed
    • [x] This does not contain breaking changes, or if it does:
      • It is released within a major release
      • It is recorded in CHANGES.txt
      • It was marked as deprecated in an earlier release if possible
      • You've thought about the consequences and other components are adapted (E.g. AdminUI)
    v/5.1 
    opened by seut 0
  • Upgraded PostgreSQL Wire Protocol to 14

    Upgraded PostgreSQL Wire Protocol to 14

    https://github.com/crate/crate/issues/13418

    Summary of the changes / Why this improves CrateDB

    Fix for ticket https://github.com/crate/crate/issues/13418

    Checklist

    • [X] Added an entry in CHANGES.txt for user facing changes
    • [X] Updated documentation & sql_features table for user facing changes
    • [X] Touched code is covered by tests
    • [X] CLA is signed
    • [X] This does not contain breaking changes, or if it does:
      • It is released within a major release
      • It is recorded in CHANGES.txt
      • It was marked as deprecated in an earlier release if possible
      • You've thought about the consequences and other components are adapted (E.g. AdminUI)
    opened by robd003 2
  • Support to compare tuples in (equal) operator(s)

    Support to compare tuples in (equal) operator(s)

    Problem Statement

    Currently, CrateDB's comparison operators do not support to compare tuples. Following query doesn't work:

    SELECT 1 WHERE (1,2) = (1,2)

    Possible Solutions

    Comparing tuples with (equal) operator(s) is supported.

    Considered Alternatives

    This can be of course done by comparing each tuple value dedicated combined by an AND operator:

    SELECT 1 WHERE 1 = 1 AND 2 = 2

    But when considering the usage inside a sub-query for example like this:

    SELECT 1 
    FROM t1 a
    WHERE (a.x, a.y) IN (
        SELECT
          b.x,
          b.y
        FROM t1 b
    )
    

    the only workaround is to:

    a) repeat the sub-query in each comparison which may result in poor performance due to repeated execution (depends on the internal optimizer/implementation)

    SELECT 1 
    FROM t1 a
    WHERE 
      a.x IN (
        SELECT
          b.x
        FROM t1 b
      )
      AND
      a.y IN (
        SELECT
          b.y
        FROM t1 b
      )
    

    b) or re-using a CTE which may not result in repeated execution but may not be comfortable to use

    WITH c(x, y) AS (
        SELECT
          b.x,
          b.y
        FROM t1 b
    )
    SELECT 1 
    FROM t1 a
    WHERE 
      a.x IN (SELECT x FROM c)
      AND 
      a.y IN (SELECT y FROM c)
    
    feature: sql: operators complexity: no estimate leverage: low 
    opened by seut 0
  • Bump Postgres Wire Protocol version to 14 or 15

    Bump Postgres Wire Protocol version to 14 or 15

    Problem Statement

    With https://github.com/crate/crate/pull/13399 the reported PostgreSQL version has been bumped to 11. However also PostgreSQL version 11 will be EOL by the November 2023 and therefore not supported by some 3rd party tools.

    from https://www.postgresql.org/support/versioning/

    Version | Current minor | Supported | First Release | Final Release -- | -- | -- | -- | -- 15 | 15.1 | Yes | October 13, 2022 | November 11, 2027 14 | 14.6 | Yes | September 30, 2021 | November 12, 2026 13 | 13.9 | Yes | September 24, 2020 | November 13, 2025 12 | 12.13 | Yes | October 3, 2019 | November 14, 2024 11 | 11.18 | Yes | October 18, 2018 | November 9, 2023

    Therefore I would suggest to go with a newer version (e.g. 14 or 15) straight away. Being aware that, PG 15 is "only" 3 months old, and some tools might not support to be discussed.

    • [ ] check / update the pg_catalog tables to adhere to the new post v10 schemas
    • [ ] check for regression in compatibility test suites

    Possible Solutions

    No response

    Considered Alternatives

    No response

    triage 
    opened by proddata 1
Releases(5.1.2)
  • 5.1.2(Dec 7, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/5.1.2.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 5.1.1(Nov 9, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/5.1.1.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 5.0.3(Oct 18, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/master/appendices/release-notes/5.0.3.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 5.1.0(Oct 11, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/master/appendices/release-notes/5.1.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 5.0.2(Oct 17, 2022)

    Release notes:

    https://crate.io/docs/crate/reference/en/master/appendices/release-notes/5.0.2.html Download on:

    https://crate.io/download

    Source code(tar.gz)
    Source code(zip)
  • 4.8.4(Sep 15, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.8.4.html

    Complete list of changes in 4.8:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.8.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 5.0.1(Sep 6, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/5.0.1.html

    Complete list of changes in 5.0:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/5.0.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.8.3(Sep 6, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.8.3.html

    Complete list of changes in 4.8:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.8.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 5.0.0(Jul 12, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/master/appendices/release-notes/5.0.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.8.2(Jul 11, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.8.2.html

    Complete list of changes in 4.8:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.8.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.8.1(May 25, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.8.1.html

    Complete list of changes in 4.8:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.8.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.7.3(May 25, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.7.3.html

    Complete list of changes in 4.7:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.7.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.8.0(Apr 28, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/master/appendices/release-notes/4.8.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.7.2(Apr 28, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.7.2.html

    Complete list of changes in 4.7:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.7.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.7.1(Mar 10, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.7.1.html

    Complete list of changes in 4.7:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.7.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.6.8(Mar 10, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.8.html

    Complete list of changes in 4.6:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.7.0(Jan 26, 2022)

    Release notes:

    https://crate.io/docs/crate/reference/en/master/appendices/release-notes/4.7.0.html

    Download on:

    https://crate.io/download

    Source code(tar.gz)
    Source code(zip)
  • 4.6.7(Jan 19, 2022)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.7.html

    Complete list of changes in 4.6:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.6.6(Dec 13, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.6.html

    Complete list of changes in 4.6:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.6.5(Nov 12, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.5.html

    Complete list of changes in 4.6:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.6.4(Sep 30, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.4.html

    Complete list of changes in 4.6:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.6.3(Sep 8, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.3.html

    Complete list of changes in 4.6:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.6.2(Aug 27, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.2.html

    Complete list of changes in 4.6:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.6.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.6.1(Jul 21, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/master/appendices/release-notes/4.6.1.html

    Complete list of changes in 4.6:

    • https://crate.io/docs/crate/reference/en/master/appendices/release-notes/4.6.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.5.5(Jul 20, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.5.5.html

    Complete list of changes in 4.5:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.5.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.6.0(Jul 13, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/master/appendices/release-notes/4.6.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.5.4(Jul 13, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.5.4.html

    Complete list of changes in 4.5:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.5.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.5.3(Jun 22, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.5.3.html

    Complete list of changes in 4.5:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.5.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.5.2(Jun 15, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.5.2.html

    Complete list of changes in 4.5:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.5.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
  • 4.5.1(May 3, 2021)

    Release notes:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.5.1.html

    Complete list of changes in 4.5:

    • https://crate.io/docs/crate/reference/en/latest/appendices/release-notes/4.5.0.html

    Download on:

    • https://crate.io/download
    Source code(tar.gz)
    Source code(zip)
Owner
Crate.io
CrateDB: Put machine data to work
Crate.io
sql2o is a small library, which makes it easy to convert the result of your sql-statements into objects. No resultset hacking required. Kind of like an orm, but without the sql-generation capabilities. Supports named parameters.

sql2o Sql2o is a small java library, with the purpose of making database interaction easy. When fetching data from the database, the ResultSet will au

Lars Aaberg 1.1k Dec 28, 2022
An open source SQL database designed to process time series data, faster

English | 简体中文 | العربية QuestDB QuestDB is a high-performance, open-source SQL database for applications in financial services, IoT, machine learning

QuestDB 9.9k Jan 1, 2023
Java implementation of Condensation - a zero-trust distributed database that ensures data ownership and data security

Java implementation of Condensation About Condensation enables to build modern applications while ensuring data ownership and security. It's a one sto

CondensationDB 43 Oct 19, 2022
HurricaneDB a real-time distributed OLAP engine, powered by Apache Pinot

HurricaneDB is a real-time distributed OLAP datastore, built to deliver scalable real-time analytics with low latency. It can ingest from batch data sources (such as Hadoop HDFS, Amazon S3, Azure ADLS, Google Cloud Storage) as well as stream data sources (such as Apache Kafka).

GuinsooLab 4 Dec 28, 2022
A distributed in-memory data store for the cloud

EVCache EVCache is a memcached & spymemcached based caching solution that is mainly used for AWS EC2 infrastructure for caching frequently used data.

Netflix, Inc. 1.9k Jan 2, 2023
Apache Druid: a high performance real-time analytics database.

Website | Documentation | Developer Mailing List | User Mailing List | Slack | Twitter | Download Apache Druid Druid is a high performance real-time a

The Apache Software Foundation 12.3k Jan 1, 2023
Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)

Trino is a fast distributed SQL query engine for big data analytics. See the User Manual for deployment instructions and end user documentation. Devel

Trino 6.9k Dec 31, 2022
The official home of the Presto distributed SQL query engine for big data

Presto Presto is a distributed SQL query engine for big data. See the User Manual for deployment instructions and end user documentation. Requirements

Presto 14.3k Dec 30, 2022
IoTDB (Internet of Things Database) is a data management system for time series data

English | 中文 IoTDB Overview IoTDB (Internet of Things Database) is a data management system for time series data, which can provide users specific ser

The Apache Software Foundation 3k Jan 1, 2023
A scalable, distributed Time Series Database.

___ _____ ____ ____ ____ / _ \ _ __ ___ _ _|_ _/ ___|| _ \| __ ) | | | | '_ \ / _ \ '_ \| | \___ \| | | | _ \

OpenTSDB 4.8k Dec 26, 2022
🚀flink-sql-submit is a custom SQL submission client

??flink-sql-submit is a custom SQL submission client This is a customizable extension of the client, unlike flink's official default client.

ccinn 3 Mar 28, 2022
Free universal database tool and SQL client

DBeaver Free multi-platform database tool for developers, SQL programmers, database administrators and analysts. Supports any database which has JDBC

DBeaver 29.8k Jan 1, 2023
HasorDB is a Full-featured database access tool, Providing object mapping,Richer type handling than Mybatis, Dynamic SQL

HasorDB is a Full-featured database access tool, Providing object mapping,Richer type handling than Mybatis, Dynamic SQL, stored procedures, more dialect 20+, nested transactions, multiple data sources, conditional constructors, INSERT strategies, multiple statements/multiple results. And compatible with Spring and MyBatis usage.

赵永春 17 Oct 27, 2022
jdbi is designed to provide convenient tabular data access in Java; including templated SQL, parameterized and strongly typed queries, and Streams integration

The Jdbi library provides convenient, idiomatic access to relational databases in Java. Jdbi is built on top of JDBC. If your database has a JDBC driv

null 1.7k Dec 27, 2022
New-fangled Timeseries Data Store

Newts Newts is a time-series data store based on Apache Cassandra. Features High throughput Newts is built upon Apache Cassandra, a write-optimized, f

OpenNMS 190 Oct 3, 2022
The Prometheus monitoring system and time series database.

Prometheus Visit prometheus.io for the full documentation, examples and guides. Prometheus, a Cloud Native Computing Foundation project, is a systems

Prometheus 46.3k Jan 10, 2023
Simple but powerful SQL library for tiny projects

KiORM - Simple and powerful MySQL Java library for tiny projects Notice: KiORM is still in SNAPSHOT state. The code is not tested, there is no Javadoc

Rikonardo 2 Sep 13, 2022