manticoresoftware / manticoresoftware/manticoresearch

now() cannot be used in WHERE

Open
#665 7 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

in backlog
Dominant language
C++
Stars
12k
Forks
642
Avg merge
3d 12h
Merged PRs (30d)
47

Description

Describe the bug

The function now() (and possibly others) cannot be used in the WHERE part of a query.

To Reproduce
Steps to reproduce the behavior:

  1. Create a table with a timestamp column:
    create table temp (d timestamp, t text)
  2. Insert some data into it:
    insert into temp (d) values (1637769590)
  3. Try to select all records with a date before or after the current instant:
    select * from temp where d >= now()
  4. You get this error:

sphinxql: syntax error, unexpected identifier, expecting integer or float or .number or '-' near 'now()'

Expected behavior
I would expect the engine to filter all records whose date came before the time when the query is executed.

Describe the environment:

  • Manticore Search version 3.6.0 96d61d8bf@210504 release
  • OS version: Linux 5c298eb422d2 5.4.0-80-generic #90-Ubuntu SMP Fri Jul 9 22:49:44 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the reported CREATE TABLE, INSERT, and SELECT query against the stated Manticore Search version, then trace the SQL expression parser and function handling for WHERE predicates. Add or update a regression test covering now() in a timestamp comparison; done means the query parses and filters rows relative to query execution time.

Written by the indexing model from the issue text.

Assessment

Tech stack
cpp, sql
Domain
databases, search
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.