SQL query syntax alignment with MySQL

[etcd]

Is your feature request related to a problem? Please describe.

Manticore implements a variant of SQL syntax that's close to MySQL, but some basic differences make it difficult to use Manticore with ORMs. With a little bit of work, the Manticore integration experience across different platforms could be vastly improved.

For context, I recently tried to use Drizzle (a Typescript ORM) to generate SQL for Manticore to use. Aside from small syntax differences in generated SQL, Drizzle's mysql connectors are actually pretty close to providing a nice Manticore ORM experience!

Describe the solution you'd like Ideally Manticore could broaden its SQL syntax in small ways to allow for compatibility with some ORMs.

Given the table:

create table mytable(s string, t text);

1. Manticore does not support default keyword for inserted values.

insert into `mytable` (`id`, `s`, `t`) values (0, '123', 'this is sample text'); -- works
insert into `mytable` (`id`, `s`, `t`) values (default, '123', 'this is sample text'); -- fails

2. ~~Manticore does not support parentheses around where clauses that have match functions.~~ Edit: I was just writing my match queries wrong.

3. Manticore does not support qualifying column names with table names.

select `s` from `mytable` order by `s` desc; -- works
select `s` from `mytable` order by `mytable`.`s` desc; -- fails

[virtadpt]

It definitely doesn't seem to work with the mysql-connector-python module (the official MySQL Python library). I spent a couple of weeks trying to get it to work (ref) and it errors out every time. I wasn't able to pry very much information out when debugging, so I'm not sure what's happening there.

[sanikolaev]

@etcd can you please provide a script which reproduces the mentioned problems, so we can use it for tests after implementation?

[etcd]

@sanikolaev sure! Is there a specific format you'd like me to use for this test script? What does one look like?

I've updated the example SQL statements in the issue body above to include the table schema / creation. Hopefully it is straightforward enough - the lines can be run in order.

[sanikolaev]

Is there a specific format you'd like me to use for this test script? What does one look like?

Just a sample typescript script which we can run to ensure it doesn't fail and an instruction how to run it (whether anything should be installed prior to running the script etc.)

The point is that none of the core team writes in Typescript and we never heard about Drizzle, so it will take us some time to prepare such a script.

[etcd]

Oh, I brought up Drizzle as just an example ORM that produces incompatible SQL. I didn't expect anything like full Drizzle support. However, I'd be happy to provide some sample code that demonstrates Drizzle + Manticore usage.

[sanikolaev]

I'd be happy to provide some sample code that demonstrates Drizzle + Manticore usage.

Please do. We have to solve this task based on something, it's better to support one more mysql client/ORM/etc than to apply random workarounds since if at least one command is not supported, it can ruin the whole integration.

[etcd]

@sanikolaev I've created an example repo for using Drizzle and Manticore together: https://github.com/etcd/drizzle-manticore-example

[sanikolaev]

@sanikolaev I've created an example repo for using Drizzle and Manticore together: https://github.com/etcd/drizzle-manticore-example

Thanks! It will be very helpful when we get to this task.

[etcd]

I've noticed that this is tagged as a Buddy candidate. What's the rationale behind writing this in PHP instead of changing the parser in C++ directly? It seems like extra work if a parser does not already exist in PHP (but maybe one already does?). Btw I'm just asking for context, and haven't really looked into how Buddy works.

[etcd]

Btw, I updated the issue to reflect a new finding. Parentheses around where clauses are accepted in simple cases, but not in other cases such as if a match function is used:

select `s` from `mytable` where (`s` = '123' and `s` = '123'); -- works
select `s` from `mytable` where (`s` = '123' and match('sample')); -- fails

(EDIT: removed previous incorrect statement. The above is still true though.)

[tomatolog]

I sure the issue here is that match always should come first then could come filters combined ONLY via AND with the match

ie if query has a match it should come right after where if the query has a match and the filters these combine like where match and (any filters withe the different combinations)

[etcd]

I sure the issue here is that match always should come first then could come filters combined ONLY via AND with the match

Ah, I see. These work:

select * from my_table where match('foo') and (`title`='a title'); -- works
select * from my_table where (`title`='a title') and match('foo'); -- works

Perhaps the documentation should be updated to reflect these more complex examples with compound where clauses, because this is the only example provided:

SELECT * FROM index WHERE MATCH('cats|birds'); 

I've made a PR to demonstrate this usage: https://github.com/manticoresoftware/manticoresearch/pull/1863

[sanikolaev]

I've noticed that this is tagged as a Buddy candidate. What's the rationale behind writing this in PHP instead of changing the parser in C++ directly? It seems like extra work if a parser does not already exist in PHP (but maybe one already does?).

A parser does exists for Buddy and it's much easier to use it than changing the Bison parser generator which is used in the C++ daemon. For cases like this when performance is not paramount, Buddy is helpful since it allows implementing support for such non-documented queries needed for a specific integration sooner.

[KlimTodrik]

As I can see, the drizzle has its own support for different databases with different syntax. Our syntax is similar to MySQL but not the same. That's why I suggest you create issue about the support of ManticoreSearch in the Drizzle project

[sanikolaev]

My two cents: If someone from the @drizzle-team is interested in integrating Manticore, we would be glad to assist. Indeed, it seems to me more appropriate to approach this integration from Drizzle's side rather than mimicking mysql (which makes sense for purely mysql IDEs / clients etc.)