MariaDB Between Operator Inclusive Both Endpoints

Published
Length
3 min read

Luis Felipe Dias

All posts

Yes, the mariadb between operator inclusive both endpoints rule is true by default. A range filter matches the start value, the end value, and everything inside. Teams use this operator to pull date ranges and number sets for reports. Still, broad database roles often let users pull far too many rows at once. When a role has wide read rights, one large range query can expose sensitive data. How the mariadb between operator inclusive both endpoints rule works In MariaDB, the range filter check

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

Yes, the mariadb between operator inclusive both endpoints rule is true by default. A range filter matches the start value, the end value, and everything inside. Teams use this operator to pull date ranges and number sets for reports. Still, broad database roles often let users pull far too many rows at once. When a role has wide read rights, one large range query can expose sensitive data.

How the mariadb between operator inclusive both endpoints rule works

In MariaDB, the range filter checks if a target value sits inside two bounds. The database engine treats the lower bound and the upper bound as valid matches. If a column holds the number ten, a range from ten to twenty matches it. If a column holds twenty, that same range matches it as well.

This logic works the same way as combining two comparison checks in a query. The engine checks if the value is greater than or equal to the start. Then it checks if the value is less than or equal to the end. Both conditions must pass for the database to return the row to the user.

You can negate the filter to exclude a specific range of values instead. When you add a negation, the engine flips the boundary logic right away. A negated range check leaves out both the start value and the end value. Only rows strictly below the start or strictly above the end will come back.

Handling dates, timestamps, and text ranges in queries

Numbers are simple to filter, but dates and timestamps need extra care from developers. A date value without a time part defaults to the very start of midnight. Suppose an analyst runs a range query from January first to January fifth. If the table stores full timestamps, most of January fifth will miss the match.

Only records from the exact stroke of midnight on January fifth will match that query. Any record created at nine in the morning sits past the upper bound. To get the whole final day, teams set the upper bound to the last second. Another clean method is to use a standard less than check for the next day.

Text ranges also follow the inclusive rule based on the column collation settings. The collation decides how the database sorts letters, numbers, and case differences in text. A range from the letter A to the letter C matches the single letter C. Still, a word like Cat sits past the single letter C in sort order.

External Secrets Operator (K8s): architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Why native roles leave a gap for range queries

Native MariaDB grants let admins control who can read a specific database table or column. These grants work well to stop unauthorized users from logging in and reading tables. You can limit a reporting user to read access on a single customer table. This setup blocks the user from dropping tables or changing existing data rows.

Yet native roles only decide who logs in and which tables they can read. Once a user has read rights, the database allows any valid range check. An engineer can run a wide range query that pulls millions of customer rows. Native grants cannot inspect the query in flight to check the size of the range.

Also, native table grants return raw column values for every row that matches the range by default. If the table holds emails, phone numbers, card numbers, or national IDs, everything comes back. Standard roles cannot rewrite matched fields in results when a user runs a broad query. Teams need finer controls to manage what individual statements do after a user logs in.

Add wire guardrails and masking beside MySQL

Database grants and TLS setup decide who can reach MySQL. Even so, an open session can run broad updates or read raw personal data from allowed tables. In practice, hoop.dev runs beside the database as a sidecar for your MySQL. It keeps your login setup and drivers as they are while checking queries on the wire.

With hoop.dev MySQL support, guardrails refuse bad queries before they run and return a readable MySQL error. Data masking rewrites matched sensitive values in result rows in memory. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for GDPR, HIPAA, and PCI DSS programs. Read the docs and source on GitHub.

Frequently asked questions

Does the MariaDB range filter include the start and end values?

Yes, the operator includes both the lower bound and the upper bound in results. A value that equals the start or end point will match the query.

What happens if the start value is higher than the end value?

The query will return zero rows if the first bound is larger than the second. The engine checks for values greater than the first and less than the second.

How do null values behave in a MariaDB range check?

If the column value or either boundary is null, the row will not match. You must use an explicit null check if you want to include empty fields.

The query runs. The emails stay hidden.