Skip to content

MatrixOne Docs

Logical Operators Overview

English
中文简体

Initializing search

matrixorigin/matrixone

MatrixOne

MatrixOne Docs

matrixorigin/matrixone

MatrixOne
MatrixOne
- Home
- Overview
  Overview
  - MatrixOne Introduction
  - MatrixOne Feature List
  - MatrixOne Feature
    MatrixOne Feature
    
    HTAP
    
    Multi-Account
    
    Scalability
    
    Cost-Effective
    
    High Availability
    
    Timing
    
    Streams
    
    User-defined functions
    
    MySQL Compatibility
  - MatrixOne Architecture Design
    MatrixOne Architecture Design
    
    Architecture Design Overview
    
    Transactional Analytical Engine Architecture
    
    Detailed Logservice Architecture
    
    Logtail Protocol Architecture
    
    Transaction and Lock Mechanisms Architecture
    
    Detailed Proxy Architecture
    
    WAL Technology Explained
    
    Detailed Caching and Hot-Cold Data Separation Architecture
    
    Detailed Stream Engine Architecture
    
    MatrixOne-Operator design and implementation
  - MatrixOne vs. other databases
    MatrixOne vs. other databases
    
    MatrixOne Positioning
    
    MatrixOne vs. common OLTP databases
  - What's New
- Getting Started
  Getting Started
  - Deploy standalone MatrixOne
    Deploy standalone MatrixOne
    
    Overview
    
    Deploy on macOS
    Deploy on macOS
    
    Building from source code
    
    Using binary package
    
    Using Docker
    
    Deploy on Linux
    Deploy on Linux
    
    Building from source code
    
    Using binary package
    
    Using Docker
  - Basic SQL
- Developing Guide
  Developing Guide
  - Connect to MatrixOne
    Connect to MatrixOne
    
    Connecting to MatrixOne with Database Client Tool
    
    Java connect to MatrixOne
    Java connect to MatrixOne
    
    Connect MatrixOne with JDBC
    
    Connect MatrixOne with Java ORMs
    
    Python connect to MatrixOne
    
    C# connect to MatrixOne
    
    Connecting to MatrixOne with Golang
    
    MatrixOne SSL connection
  - Schema Design
    Schema Design
    
    Overview
    
    Create Database
    
    Create Table
    
    Replication table
    
    Create View
    
    Create Temporary Table
    
    Create Secondary Index
    
    Vector
    
    Data Integrity
    Data Integrity
    
    Data Integrity Constraints Overview
    
    NOT NULL Constraints
    
    UNIQUE KEY Constraints
    
    PRIMARY KEY Constraints
    
    FOREIGN KEY Constraints
    
    AUTO INCREMENT Constraints
  - Write Data
    Write Data
    
    Import Data
    Import Data
    
    Insert
    
    Bulk Load
    Bulk Load
    
    Bulk Load Overview
    
    Load csv format data
    
    Load jsonlines format data
    
    Load data from S3
    
    Load data by using the `source`
    
    Update Data
    
    Delete Data
    
    Prepared
  - Export Data
    Export Data
    
    Export data by SELECT INTO
    
    Export data by MODUMP
  - Read Data
    Read Data
    
    Query Data from a Single Table
    
    Multi-table Join Queries
    
    Subquery
    
    Views
    
    Common Table Expression
    
    Window Function
    Window Function
    
    Standard Window
    
    Time Window
  - Data de-duplication
    Data de-duplication
    
    COUNT(DISTINCT)
    
    BITMAP
  - Account Design
    Account Design
    
    Multi-Account Overview
    
    Publish-Subscribe
  - Transactions
    Transactions
    
    General Transaction Overview
    
    Transaction by MatrixOne Server
    Transaction by MatrixOne Server
    
    Transaction Overview
    
    Explicit Transaction
    
    Implicit Transaction
    
    Pessimistic Transaction
    
    Optimistic Transaction
    
    Isolation Level
    
    MVCC
    
    How to use transaction
    How to use transaction
    
    User Guide
    
    Scenario
  - User-defined function
    User-defined function
    
    UDF python
    
    UDF python advanced
  - Vector
    Vector
    
    Vector Type
    
    Vector Search
    
    Cluster Centers
  - Application Developing Tutorials
    Application Developing Tutorials
    
    Java CRUD demo
    
    SpringBoot and JPA CRUD demo
    
    SpringBoot and MyBatis CRUD demo
    
    Python CRUD demo
    
    SQLAlchemy CRUD demo
    
    Django CRUD demo
    
    Golang CRUD demo
    
    Gorm CRUD demo
    
    C# CRUD demo
    
    Rag Application demo
    
    Picture(Text)-to-Picture Search Application demo
  - Ecological Tools
    Ecological Tools
    
    Message Queue Tools
    Message Queue Tools
    
    Connecting MatrixOne with Kafka
    
    BI Tools
    BI Tools
    
    Visualizing MatrixOne Data with FineBI
    
    Visualizing MatrixOne Reports with Yonghong BI
    
    Visual Monitoring of MatrixOne with Superset
    
    ETL Tools
    ETL Tools
    
    Write Data to MatrixOne using SeaTunnel
    Write Data to MatrixOne using SeaTunnel
    
    Overview
    
    Writing Data from MySQL to MatrixOne
    
    Writing Data from Oracle to MatrixOne
    
    Using DataX to write data to MatrixOne
    Using DataX to write data to MatrixOne
    
    Overview
    
    Writing Data from MySQL to MatrixOne
    
    Writing Data from Oracle to MatrixOne
    
    Writing Data from PostgreSQL to MatrixOne
    
    Writing Data from SQL Server to MatrixOne
    
    Writing Data from MongoDB to MatrixOne
    
    Writing Data from TiDB to MatrixOne
    
    Writing Data from ClickHouse to MatrixOne
    
    Writing Data from Doris to MatrixOne
    
    Writing Data from InfluxDB to MatrixOne
    
    Writing Data from Elasticsearch to MatrixOne
    
    Computing Engine
    Computing Engine
    
    Using Spark to Write Batch Data to MatrixOne
    Using Spark to Write Batch Data to MatrixOne
    
    Overview
    
    Writing Data from MySQL to MatrixOne
    
    Writing Data from Hive to MatrixOne
    
    Writing Data from Doris to MatrixOne
    
    Using Flink to Write Real-Time Data to MatrixOne
    Using Flink to Write Real-Time Data to MatrixOne
    
    Overview
    
    Writing Data from MySQL to MatrixOne
    
    Writing Data from Oracle to MatrixOne
    
    Writing Data from SQL Server to MatrixOne
    
    Writing Data from PostgreSQL to MatrixOne
    
    Writing Data from MongoDB to MatrixOne
    
    Writing Data from TiDB to MatrixOne
    
    Writing Data from Kafka to MatrixOne
- Deploying
  Deploying
  - Single MatrixOne with S3
  - Plan MatrixOne Cluster Topology
    Plan MatrixOne Cluster Topology
    
    Cluster Topology Planning Overview
    
    Experience Environment Deployment Plan
    
    Minimum Production Environment Deployment Plan
    
    Recommended Production Environment Deployment Plan
  - Cluster Deployment Guide
    Cluster Deployment Guide
    
    Kubernetes and object storage environment not deployed
    
    Deployed Kubernetes and object storage environment
  - Cluster Operations Management
    Cluster Operations Management
    
    Starting and stopping
    
    Updating
    
    Health check and resource monitoring
    
    Scaling
    
    Managing CN Groups with Proxy
    
    Import data from local Minio to MatrixOne
    
    Operator Management
- Maintenance
  Maintenance
  - Backup and Restore
    Backup and Restore
    
    MatrixOne Backup and Recovery Overview
    
    Backup and Recovery Concepts
    
    Backup and Restore by using mo-dump
    
    mo_br Backup and Recovery
    mo_br Backup and Recovery
    
    mo_br guidelines for use
    
    mo_br regular physical backup recovery
    
    mo_br snapshot backup recovery
  - Mount Data
    Mount Data
    
    Mount directory to Docker container
- Migrating
  Migrating
- Testing
  Testing
  - Performance Testing
    Performance Testing
    
    SSB Test with MatrixOne
    
    TPCH Test with MatrixOne
    
    TPCC Test with MatrixOne
  - Testing Tool
    Testing Tool
    
    MO-Tester
    
    MO-Tester Specification
- Performance Tuning
  Performance Tuning
  - SQL Performance Tuning Methods Overview
  - Understanding the Query Execution Plan
    Understanding the Query Execution Plan
    
    Query Execution Plan Overview
    
    Using EXPLAIN to learn the execution plan
    
    Explain Statements Using JOIN
    
    Explain Statements Using Subqueries
    
    Explain Statements Using Aggregation
    
    EXPLAIN Statements Using Views
  - Performance tuning best practices
    Performance tuning best practices
    
    Using Cluster by for performance tuning
    
    Scaling CN for better performance
- Privilege
  Privilege
  - Security and Permissions Overview
  - Authentication and Authorization
  - Password Management
  - Access Control
    Access Control
    
    About MatrixOne Privilege Management
    
    Privilege Management Scenario
    
    Best Practices
  - User Guide
    User Guide
    
    Privileges Management Overview
    
    Create accounts, Verify Resource Isolation
    
    Use the new account to creates users, roles, grant the privilege
  - Data Transmission Encryption
  - Security Audit
- Reference
  Reference
  - System Variables
    System Variables
    
    System Variables Overview
    
    System Variables parameters
    System Variables parameters
    
    SQL Mode
    
    Save query result support
    
    Timezone support
    
    Lower case table names support
    
    Foreign key checking support
    
    User-specified case consistency support for query result set column names
    
    Custom variable
  - SQL Language Structure
    SQL Language Structure
    
    Keywords
    
    Comments
  - Data Types
    Data Types
    
    Data Types Overview
    
    Data Type Conversion
    
    Date and Time Types
    Date and Time Types
    
    Timestamp Initialization
    
    YEAR Type
    
    JSON Data Type
    
    BLOB and TEXT Type
    
    ENUM Type
    
    UUID Type
    
    VECTOR Type
    
    Fixed-Point Types (Exact Value) - DECIMAL
  - SQL Statements
    SQL Statements
    
    Type of SQL Statements
    
    Data Definition Language
    Data Definition Language
    
    CREATE DATABASE
    
    CREATE INDEX
    
    CREATE INDEX...USING IVFFLAT
    
    CREATE TABLE
    
    CREATE TABLE AS SELECT
    
    CREATE TABLE ... LIKE
    
    CREATE EXTERNAL TABLE
    
    CREATE CLUSTER TABLE
    
    CREATE PUBLICATION
    
    CREATE SEQUENCE
    
    CREATE STAGE
    
    CREATE...FROM...PUBLICATION...
    
    CREATE VIEW
    
    CREATE FUNCTION...LANGUAGE SQL AS
    
    CREATE FUNCTION...LANGUAGE PYTHON AS
    
    CREATE SOURCE
    
    CREATE DYNAMIC TABLE
    
    CREATE SNAPSHOT
    
    ALTER TABLE
    
    ALTER TABLE ... ALTER REINDEX
    
    ALTER PUBLICATION
    
    ALTER SEQUENCE
    
    ALTER STAGE
    
    ALTER VIEW
    
    DROP DATABASE
    
    DROP INDEX
    
    DROP TABLE
    
    DROP PUBLICATION
    
    DROP SEQUENCE
    
    DROP STAGE
    
    DROP SNAPSHOT
    
    DROP VIEW
    
    DROP FUNCTION
    
    TRUNCATE TABLE
    
    RESTORE ACCOUNT
    
    Data Manipulation Language
    Data Manipulation Language
    
    INSERT
    
    INSERT INTO SELECT
    
    DELETE
    
    UPDATE
    
    LOAD DATA INFILE
    
    LOAD DATA INLINE
    
    UPSERT
    UPSERT
    
    UPSERT Overview
    
    INSERT ON DUPLICATE KEY UPDATE
    
    INSERT IGNORE
    
    REPLACE
    
    Information Functions
    Information Functions
    
    LAST_QUERY_ID()
    
    LAST_INSERT_ID()
    
    Data Query Language
    Data Query Language
    
    SELECT
    
    SUBQUERY
    SUBQUERY
    
    SUBQUERY Overview
    
    Derived Tables
    
    Comparisons Using Subqueries
    
    SUBQUERY with ANY or SOME
    
    SUBQUERY with ALL
    
    SUBQUERY with EXISTS
    
    SUBQUERY with IN
    
    JOIN
    JOIN
    
    JOIN Overview
    
    CROSS JOIN
    
    CROSS JOIN
    
    LEFT JOIN
    
    RIGHT JOIN
    
    FULL JOIN
    
    OUTER JOIN
    
    NATURAL JOIN
    
    With CTE
    
    Combining Queries
    Combining Queries
    
    Combining Queries Overview
    
    UNION
    
    INTERSECT
    
    MINUS
    
    Data Control Language
    Data Control Language
    
    CREATE ACCOUNT
    
    ALTER ACCOUNT
    
    CREATE ROLE
    
    CREATE USER
    
    ALTER USER
    
    DROP ACCOUNT
    
    DROP USER
    
    DROP ROLE
    
    GRANT
    
    REVOKE
    
    Other
    Other
    
    SHOW
    SHOW
    
    SHOW ACCOUNTS
    
    SHOW DATABASES
    
    SHOW CREATE TABLE
    
    SHOW CREATE VIEW
    
    SHOW CREATE PUBLICATION
    
    SHOW TABLES
    
    SHOW INDEX
    
    SHOW COLLATION
    
    SHOW COLUMNS
    
    SHOW FUNCTION STATUS
    
    SHOW GRANT
    
    SHOW PROCESSLIST
    
    SHOW PUBLICATIONS
    
    SHOW ROLES
    
    SHOW SEQUENCES
    
    SHOW STAGE
    
    SHOW SUBSCRIPTIONS
    
    SHOW VARIABLES
    
    SET
    SET
    
    SET ROLE
    
    USE
    USE
    
    USE DATABASE
    
    KILL
    
    Prepared Statements
    Prepared Statements
    
    PREPARE
    
    EXECUTE
    
    DEALLOCATE
    
    Explain
    Explain
    
    EXPLAIN
    
    EXPLAIN Output Format
    
    Explain Analyze
    
    Explain Prepared
  - Functions and Operators
    Functions and Operators
    
    Operators
    Operators
    
    INTERVAL
    
    OPERATORS
    OPERATORS
    
    OPERATORS Overview
    
    OPERATORS Precedence
    
    Arithmetic Operators
    Arithmetic Operators
    
    Arithmetic Operators Overview
    
    %,MOD
    
    *
    
    +
    
    -
    
    -
    
    /
    
    DIV
    
    Assignment Operators
    Assignment Operators
    
    Assignment Operators Overview
    
    =
    
    Bit Functions and Operators
    Bit Functions and Operators
    
    Bit Functions and Operators Overview
    
    &
    
    >>
    
    <<
    
    ^
    
    |
    
    ~
    
    Cast Functions and Operators
    Cast Functions and Operators
    
    Cast Functions and Operators Overview
    
    BINARY
    
    CAST
    
    CONVERT
    
    SERIAL
    
    SERIAL_FULL
    
    Comparison Functions and Operators
    Comparison Functions and Operators
    
    Comparison Functions and Operators Overview
    
    >
    
    >=
    
    <
    
    <>,!=
    
    <=
    
    =
    
    BETWEEN ... AND ...
    
    IN
    
    IS
    
    IS NOT
    
    IS NOT NULL
    
    IS NULL
    
    ILIKE
    
    ISNULL
    
    LIKE
    
    NOT BETWEEN ... AND ...
    
    NOT IN
    
    NOT LIKE
    
    COALESCE
    
    Flow Control Functions
    Flow Control Functions
    
    Flow Control Functions Overview
    
    CASE WHEN
    
    IF
    
    IFNULL
    
    NULLIF
    
    Logical Operators
    Logical Operators
    
    Logical Operators Overview
    
    AND,&&
    
    NOT,!
    
    OR
    
    XOR
    
    Functions
    Functions
    
    Summary Table of Functions
    
    Aggregate Functions
    Aggregate Functions
    
    ANY_VALUE
    
    AVG
    
    BITMAP
    
    BIT_AND
    
    BIT_OR
    
    BIT_XOR
    
    COUNT
    
    GROUP_CONCAT
    
    MAX
    
    MEDIAN
    
    MIN
    
    STDDEV_POP
    
    SUM
    
    VARIANCE
    
    VAR_POP
    
    Datetime
    Datetime
    
    CONVERT_TZ()
    
    CURDATE()
    
    CURRENT_TIMESTAMP()
    
    DATE()
    
    DATE_ADD()
    
    DATE_FORMAT()
    
    DATE_SUB()
    
    DATEDIFF()
    
    DAY()
    
    DAYOFYEAR()
    
    EXTRACT()
    
    HOUR()
    
    FROM_UNIXTIME
    
    MINUTE()
    
    MONTH()
    
    NOW()
    
    SECOND()
    
    STR_TO_DATE()
    
    SYSDATE()
    
    TIME()
    
    TIMEDIFF()
    
    TIMESTAMP()
    
    TIMESTAMPDIFF()
    
    TO_DATE()
    
    TO_DAYS()
    
    TO_SECONDS()
    
    UNIX_TIMESTAMP
    
    UTC_TIMESTAMP()
    
    WEEK()
    
    WEEKDAY()
    
    YEAR()
    
    Mathematical
    Mathematical
    
    ABS()
    
    ACOS()
    
    ATAN()
    
    CEIL()
    
    CEILING()
    
    COS()
    
    COT()
    
    EXP()
    
    FLOOR()
    
    LN()
    
    LOG()
    
    LOG2()
    
    LOG10()
    
    PI()
    
    POWER()
    
    ROUND()
    
    RAND()
    
    SIN()
    
    SINH()
    
    TAN()
    
    String
    String
    
    BIN()
    
    BIT_LENGTH()
    
    CHAR_LENGTH()
    
    CONCAT()
    
    CONCAT_WS()
    
    EMPTY()
    
    ENDSWITH()
    
    FIELD()
    
    FIND_IN_SET()
    
    FORMAT()
    
    FROM_BASE64()
    
    HEX()
    
    INSTR()
    
    LCASE()
    
    LEFT()
    
    LENGTH()
    
    LOCATE()
    
    LOWER()
    
    LPAD()
    
    LTRIM()
    
    MD5()
    
    OCT()
    
    REPEAT()
    
    REVERSE()
    
    RPAD()
    
    RTRIM()
    
    SHA1()/SHA()
    
    SHA2()
    
    SPACE()
    
    SPLIT_PART()
    
    STARTSWITH()
    
    SUBSTRING()
    
    SUBSTRING_INDEX()
    
    TO_BASE64()
    
    TRIM()
    
    UCASE()
    
    UNHEX()
    
    UPPER()
    
    Regular Expressions
    Regular Expressions
    
    Regular Expressions Overview
    
    NOT REGEXP
    
    REGEXP_INSTR()
    
    REGEXP_LIKE()
    
    REGEXP_REPLACE()
    
    REGEXP_SUBSTR()
    
    Vector
    Vector
    
    Basic Operator
    
    Mathematical Calculations
    
    CLUSTER_CENTERS()
    
    COSINE_SIMILARITY()
    
    COSINE_DISTANCE()
    
    INNER_PRODUCT()
    
    L1_NORM()
    
    L2_NORM()
    
    L2_DISTANCE()
    
    NORMALIZE_L2()
    
    SUBVECTOR()
    
    VECTOR_DIMS()
    
    Table
    Table
    
    UNNEST()
    
    Vector
    Vector
    
    Basic Operator
    
    COSINE_SIMILARITY()
    
    COSINE_DISTANCE()
    
    INNER_PRODUCT()
    
    L1_NORM()
    
    L2_NORM()
    
    L2_DISTANCE()
    
    Mathematical Calculations
    
    VECTOR_DIMS()
    
    NORMALIZE_L2()
    
    Window Functions
    Window Functions
    
    DENSE_RANK()
    
    RANK()
    
    ROW_NUMBER()
    
    JSON Functions
    
    Other Functions
    Other Functions
    
    SAMPLE
    
    SERIAL_EXTRACT
    
    SLEEP
    
    UUID()
    
    System OPS Functions
    System OPS Functions
    
    CURRENT_ROLE_NAME()
    
    CURRENT_ROLE()
    
    CURRENT_USER_NAME()
    
    CURRENT_USER()
    
    PURGE_LOG()
  - System Paramaters
    System Paramaters
    
    System Parameters Overview
    
    Standalone Common Parameters Configuration
    
    Distributed Common Parameters Configuration
  - MatrixOne Catalog
  - Privilege Control Types
  - Limitations
    Limitations
    
    JDBC supported features list
    
    Partitioning supported features list
  - MatrixOne Directory Structure
  - MatrixOne Tools
    MatrixOne Tools
    
    mo_ctl stand-alone tool
    
    mo_ctl distributed Tools
    
    mo_datax_writer tool
    
    mo_ssb_open tool
    
    mo_tpch_open tool
    
    mo_ts_perf_test tool
- Troubleshooting
  Troubleshooting
- FAQs
  FAQs
- Release Notes
  Release Notes
  - Release Timeline
  - v24.1.2.4
  - v24.1.2.3
  - v24.1.2.2
  - v24.1.2.1
  - v24.1.2.0
  - v24.1.1.3
  - v24.1.1.2
  - v24.1.1.1
  - v23.1.1.0
  - v23.1.0.2
  - v23.1.0.1
  - v23.1.0.0
  - v23.1.0.0-rc2
  - v23.1.0.0-rc1
  - v23.0.8.0
  - v23.0.7.0
  - v22.0.6.0
  - v22.0.5.1
  - v22.0.5.0
  - v22.0.4.0
  - v22.0.3.0
  - v22.0.2.0
  - v21.0.1.0
- Glossary
- Contribution Guide
  Contribution Guide
  - Make Your First Contribution
  - How to Contribute
    How to Contribute
    
    Types of Contributions
    
    Preparation
    
    Report an Issue
    
    Contribute Code
    
    Review a Pull Request
    
    Contribute Documentation
    
    Make a Design
  - Code Style
    Code Style
    
    Code of Conduct
    
    Code Comment Style
    
    Commit & Pull Request Style

Logical Operators

Name	Description
AND,&&	Logical AND
NOT,!	Negates value
OR	Logical OR
XOR	Logical XOR

Made with Material for MkDocs