Always 0 for unexisting @@variables #3109

donhardman · 2025-02-20T04:02:33Z

Bug Description:

There is an issue that blocks a task to be done: manticoresoftware/manticoresearch-buddy#388

When we do a select of any variable that does not exist at all, we return 0, which looks incorrect.

Let's return an error that this variable does not exist, because setting 0 as int for everything is not a good approach. So when we return an error here, we will be able to process such cases with Buddy.

mysql> SELECT @@tx_isolation_eahhahfefhehfehf;
+---------------------------------+
| @@tx_isolation_eahhahfefhehfehf |
+---------------------------------+
|                               0 |
+---------------------------------+
mysql>

Manticore Search Version:

Latest dev

Operating System Version:

Ubuntu

Have you tried the latest development version?

Yes

Internal Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

The text was updated successfully, but these errors were encountered:

tomatolog · 2025-02-20T14:59:25Z

the related ticket is #1273

daemon always returns valid response for unknown @@variable.

Then the buddy present the response looks like

mysql -h 127.0.0.1 -P 9306 -e "SELECT @@tx_isolation_eahhahfefhehfehf;"
+---------------------------------+
| @@tx_isolation_eahhahfefhehfehf |
+---------------------------------+
|                               0 |
+---------------------------------+

then the buddy disable the response looks like

mysql -h 127.0.0.1 -P 9306 -e "SELECT @@tx_isolation_eahhahfefhehfehf;"
+---------------------------------+
| @@tx_isolation_eahhahfefhehfehf |
+---------------------------------+
| <empty>                         |
+---------------------------------+

The supported variables are: @@session.auto_increment_increment, @@character_set_client, @@character_set_connection, @@max_allowed_packet, @@version_comment, @@lower_case_table_names, @@session.last_insert_id, @@autocommit

However all other variables just returns 0 or <empty>.

In case we are going to change this behavior it worth to add new searchd.dummy_select_list_@@ option there user could enumerate all variables these should not fail or maybe provide instruction of how it could be fixed via Buddy.

For it should have format: variable name;variable type ( string \ long ); variable value

like

searchd.dummy_select_list_@@ = name1,string;yes
searchd.dummy_select_list_@@ = name15,long;15

That need as we do not have list of clients these depends on this behavior.

sanikolaev · 2025-02-20T18:43:30Z

Another related issue is https://gitlab.com/manticoresearch/dev/-/issues/3009 and the commit as the result of it - 8240fce05e . It doesn't explain why not NULL and why not an error.

sanikolaev · 2025-02-20T18:52:53Z

why not an error

Probably because in MySQL it's not considered an error either.

I suggest we do the following:

Daemon: Instead of showing 0/<empty> in the daemon, let's return an error so it's routed to Buddy if the variable is unknown.
Buddy: Let's show NULL by default, as in MySQL, and display other values when necessary.

Let's implement these changes in a branch, build the daemon and Buddy packages, and then:

Test it with all mysqldump / mariadb-dump versions.
Test the MySQL-related integrations we support, such as Grafana, Superset, UIs, etc.

…quested; fixed #3109

tomatolog · 2025-02-21T09:56:33Z

I've just pushed the change into #3117

There I keep the current behavior for daemon runs without buddy and while buddy running daemon should fail query with any unknown sysvar and route these into buddy.

the new behavior

mysql -h 127.0.0.1 -P 9306 -e "SELECT @@autocomm;"
ERROR 1064 (42000) at line 1: unknown sysvar @@autocomm

mysql -h 127.0.0.1 -P 9306 -e "SELECT @autocomm;"
ERROR 1064 (42000) at line 1: P09: syntax error, unexpected TOK_USERVAR near ''

mysql -h 127.0.0.1 -P 9306 -e "SELECT autocomm;"
ERROR 1064 (42000) at line 1: unknown column: autocomm

the old behavior

mysql -h 127.0.0.1 -P 9306 -e "SELECT @@autocomm;"
+------------+
| @@autocomm |
+------------+
| <empty>    |
+------------+

mysql -h 127.0.0.1 -P 9306 -e "SELECT @autocomm;"
ERROR 1064 (42000) at line 1: P09: syntax error, unexpected TOK_USERVAR near ''

mysql -h 127.0.0.1 -P 9306 -e "SELECT autocomm;"
ERROR 1064 (42000) at line 1: unknown column: autocomm

need to check all failed cases at the PR and implement fixes at the buddy.

sanikolaev · 2025-02-25T06:56:23Z

@donhardman pls test the updated version or prepare a clear test plan for @PavelShilin89 .

donhardman · 2025-03-04T04:42:43Z

Test Cases

1. Basic System Variable Tests

1.1 Test Existing System Variables

Purpose: Verify that valid system variables return correct values

Test supported variables:

SELECT @@session.auto_increment_increment;
SELECT @@character_set_client;
SELECT @@character_set_connection;
SELECT @@max_allowed_packet;
SELECT @@version_comment;
SELECT @@lower_case_table_names;
SELECT @@session.last_insert_id;
SELECT @@autocommit;

Expected: Each returns its proper value

1.2 Test Non-existent System Variables

Purpose: Verify error handling for non-existent variables

Test with completely invalid variables:

SELECT @@tx_isolation_eahhahfefhehfehf;
SELECT @@nonexistent_variable;
SELECT @@random_name;

Expected with Buddy: Error message "unknown sysvar @@variable_name"
Expected without Buddy: Returns 0 or <empty> (current behavior)

1.3 Test Partial Matches

Purpose: Verify behavior with variables that partially match valid ones

Test with:

SELECT @@autocomm;
SELECT @@character_set;
SELECT @@version;

Expected with Buddy: Error message for unknown system variables
Expected without Buddy: Returns 0 or <empty>

2. Query Context Tests

2.1 Test in Multi-Select Queries

Purpose: Verify behavior when mixing valid and invalid variables

Test with:

SELECT @@autocommit, @@nonexistent_variable;
SELECT @@character_set_client, @@tx_isolation_fake, @@max_allowed_packet;

Expected with Buddy: Error for the unknown variables
Expected without Buddy: Valid values for known variables, 0 or <empty> for unknown ones

2.2 Test in Complex Queries

Purpose: Verify behavior in more complex query structures

Test with:

SELECT @@autocommit AS auto, @@nonexistent_variable AS nonexistent FROM some_index WHERE id=1;
SELECT * FROM some_index WHERE value > @@nonexistent_variable;

Expected with Buddy: Error for unknown variables
Expected without Buddy: Query executes with 0 or <empty> for unknown variables

3. Client Integration Tests

3.1 Test with mysqldump/mariadb-dump

Purpose: Verify that backup tools work correctly with the new behavior

Run mysqldump against a Manticore instance with both configurations
Verify dumps complete successfully

3.2 Test with MySQL-Related Integrations

Purpose: Verify compatibility with common tools

Test with Grafana
Test with Apache Superset
Test with any MySQL UI tools (e.g., MySQL Workbench, phpMyAdmin)

4. User Variables Tests

4.1 Test User Variables vs System Variables

Purpose: Verify that user variables (@var) are handled differently from system variables (@@var)

Test with:

SELECT @user_variable;
SET @user_variable = 123;
SELECT @user_variable;

Expected: Proper user variable handling regardless of Buddy configuration
Test with:
```
SELECT @nonexistent_user_var;
```
Expected: Appropriate syntax error

5. Edge Cases

5.1 Test Variable Name Edge Cases

Purpose: Verify behavior with unusual variable names

Test with:

SELECT @@123;
SELECT @@'quoted_name';
SELECT @@very_long_variable_name_that_exceeds_normal_length_limits;

Expected with Buddy: Appropriate error messages
Expected without Buddy: Returns 0 or <empty>

5.2 Test Case Sensitivity

Purpose: Verify case sensitivity handling

Test with:

SELECT @@AUTOCOMMIT;
SELECT @@AutoCommit;
SELECT @@autocommit;

Expected: Consistent behavior regardless of case

Expected Results Summary

With Buddy running:
- Known system variables return their proper values
- Unknown system variables produce "unknown sysvar @@variable_name" error
- User variables (@var) produce the expected syntax error if not defined
Without Buddy running:
- Known system variables return their proper values
- Unknown system variables return 0 or <empty> (maintaining backward compatibility)
- User variables (@var) produce the expected syntax error if not defined

PavelShilin89 · 2025-05-13T07:55:27Z

Testing is done in this PR - #3361.

Only the points and conditions that do not meet expectations are highlighted:

Point 1.1:
@@session.last_insert_id: Empty string instead of 0.
@@version_comment: Uninformative git branch HEAD (no branch) instead of an explicit version.

Point 2.2:
With Buddy: Syntax errors instead of unknown sysvar for non-existent variables.
Without Buddy: Syntax errors instead of query execution with replacement to 0 or <empty>.

Point 3.1:
With Buddy: Error unknown sysvar @@collation_database instead of successful dump.

Point 4.1:
SET @user_variable = 123; SELECT @user_variable;: Error due to boolean value restriction instead of returning 123.

Point 5.1:
Without Buddy: Syntax errors for @@123 and @@quoted_name instead of 0 or <empty>.

PavelShilin89 · 2025-05-18T14:20:16Z

@tomatolog @donhardman A list of all problematic requests:
1. @@session.last_insert_id - empty string instead of 0

mysql -h0 -P9306 -e "SELECT @@session.last_insert_id;"

Expectation: 0
Actual: empty string

2. @@version_comment - uninformative value , shows the branch not the manticore version. It will vary depending on the environment in which the test is run. It may be worth changing the output, or excluding it from the test.

mysql -h0 -P9306 -e "SELECT @@version_comment;"

Expectation: readable information, e.g. Manticore
Fact: git branch HEAD (no branch)

3. Buddy: parser error instead of unknown sysvar
bash

mysql -h0 -P9306 -e "SELECT @@autocommit AS auto, @@nonexistent_variable AS nonexistent FROM tbl WHERE id=1;"

Expectation: ERROR 1064 (42000): unknown sysvar @@nonexistent_variable
Fact:

ERROR 1064 (42000) at line 1: table tbl: parse error: P09: syntax error, unexpected TOK_USERVAR, expecting $end near ''

4. Buddy: синтаксическая ошибка вместо unknown sysvar в WHERE

mysql -h0 -P9306 -e "SELECT * FROM tbl WHERE value > @@nonexistent_variable;"

Expectation: ERROR 1064 (42000): unknown sysvar @@nonexistent_variable
Fact:

ERROR 1064 (42000) at line 1: P01: syntax error, unexpected @@sysvar near '@@nonexistent_variable'

5. Buddy: mysqldump aborts due to unknown variable

mysqldump -h0 -P9306 --no-data --skip-opt --no-tablespaces tbl > /tmp/dump.sql

Expectation: successful dump
Fact:

mysqldump: Couldn't execute 'select @@collation_database': unknown sysvar @@collation_database (1064)

6. Error when setting a user variable

mysql -h0 -P9306 -e "SET @user_variable = 123; SELECT @user_variable;"

Expectation:

+----------------+
| @user_variable |
+----------------+
|            123 |
+----------------+

Fact:

ERROR 1064 (42000) at line 1: sphinxql: only 0 and 1 could be used as boolean values near '123'

7. Without Buddy: syntax error for @@@123

mysql -h0 -P9306 -e "SELECT @@123;"

Expectation:

+--------+
| @@123  |
+--------+
| <empty> |
+--------+

Fact:

ERROR 1064 (42000) at line 1: P09: syntax error, unexpected $end near ''

8. Without Buddy: syntax error for @@'quoted_name'

mysql -h0 -P9306 -e "SELECT @@'quoted_name';"

Expectation:

+--------------------+
| @@'quoted_name'  |
+--------------------+
|  <empty>                   |
+--------------------+

Fact:

ERROR 1064 (42000) at line 1: P01: syntax error, unexpected string, expecting $end near ''quoted_name''

tomatolog · 2025-05-22T14:15:34Z

I've pushed the fixes of the points 1,7,8 into branch at #3117

for point 1 - now SELECT @@session.last_insert_id returns 0 if last_insert_id is empty
for point 7 - I added the fix that @@sys_var could start with number
for point 8 - I added the fix that @@sys_var could be quoted however the quotation uses another char and looks like

mysql -h0 -P9306 -e "SELECT @@`quoted_name`;"

for all other points I suggest to create another separate tickets as these are not related to the original ticket.

for point 2

@@version_comment - uninformative value , shows the branch not the manticore version. It will vary depending on the environment in which the test is run. It may be worth changing the output, or excluding it from the test.

this seems generated at sphinxrev.cmake and output of the git status -s -b command. It seems like it should be like this and code at sphDumpGdb expects such output format.

for points 3. 4

I'd move all issues (point 3 and 4) related to select ... from table_name into separate issue as it not related to original SELECT @@var; and got handled by different code at the regular table search or filter creation at the table search

Buddy: parser error instead of unknown sysvar
mysql -h0 -P9306 -e "SELECT @@autocommit AS auto, @@nonexistent_variable AS nonexistent FROM tbl WHERE id=1;"

Buddy: синтаксическая ошибка вместо unknown sysvar в WHERE
mysql -h0 -P9306 -e "SELECT * FROM tbl WHERE value > @@nonexistent_variable;"

for points 5

I'd move issue (point 5) related to mysqldump into separate issue as it not related to original SELECT @@var; and got handled by different code related to mysqldump

Buddy: mysqldump aborts due to unknown variable
mysqldump -h0 -P9306 --no-data --skip-opt --no-tablespaces tbl > /tmp/dump.sql

for points 6

I'd move issue (point 6) related to set uservar into separate issue as it not related to original SELECT @@var; and got handled by different code related to parser and set uservar

Error when setting a user variable
mysql -h0 -P9306 -e "SET @user_variable = 123

PavelShilin89 · 2025-05-26T23:35:42Z

@tomatolog @donhardman I confirm the fixes mentioned in the comment.
For other problems created issue - #3405
Blocked by #3405

sanikolaev · 2025-05-27T10:03:56Z

@donhardman Let's simplify the test case so it only covers what's been fixed in this issue, so this is not blocked because of the other issues of lower priority with no significant reason.

PavelShilin89 · 2025-05-28T10:01:43Z

@donhardman Testing done in this PR - #3361
Please give feedback or approve the merge.
Removed cases moved to another issue - #3405

sanikolaev · 2025-05-29T08:39:26Z

@tomatolog pls merge "master" into #3117 and reassign back to @PavelShilin89 to finalize.

tomatolog · 2025-05-29T09:54:16Z

I've rolled back the latest commit at the branch and merged master into https://github.com/manticoresoftware/manticoresearch/tree/sysvar_buddy but the CLT still failed at CLT / CLT (Buddy, test/clt-tests/buddy/)

github-actions · 2025-05-30T12:36:25Z

Thanks for the update! However, the internal checklist is not yet complete. A Manticore team member will close this issue once everything is checked off. 🛠️

donhardman added the bug label Feb 20, 2025

donhardman mentioned this issue Feb 20, 2025

Apache Superset 4.x support manticoresoftware/manticoresearch-buddy#388

Open

6 tasks

sanikolaev assigned tomatolog Feb 20, 2025

tomatolog added the waiting Waiting for the original poster (in most cases) or something else label Feb 20, 2025

sanikolaev removed the waiting Waiting for the original poster (in most cases) or something else label Feb 20, 2025

tomatolog added a commit that referenced this issue Feb 21, 2025

added failure if the buddy running and any unknown system variable re…

f4bd54e

…quested; fixed #3109

tomatolog assigned sanikolaev and unassigned tomatolog Feb 21, 2025

sanikolaev assigned donhardman and unassigned sanikolaev Feb 25, 2025

donhardman assigned PavelShilin89 and unassigned donhardman Mar 4, 2025

PavelShilin89 linked a pull request May 6, 2025 that will close this issue

Add CLT test for system variables error handling (Buddy ON/OFF) #3361

Open

PavelShilin89 assigned donhardman and unassigned PavelShilin89 May 14, 2025

PavelShilin89 assigned tomatolog and unassigned donhardman May 22, 2025

tomatolog assigned PavelShilin89 and unassigned tomatolog May 22, 2025

PavelShilin89 mentioned this issue May 26, 2025

System Variables and Query Parsing Inconsistencies in Manticore Search #3405

Open

4 tasks

PavelShilin89 assigned tomatolog and unassigned PavelShilin89 May 26, 2025

sanikolaev assigned donhardman and unassigned tomatolog May 27, 2025

sanikolaev assigned tomatolog and unassigned donhardman May 29, 2025

tomatolog assigned PavelShilin89 and unassigned tomatolog May 29, 2025

PavelShilin89 closed this as completed in 4fcebf3 May 30, 2025

github-actions bot reopened this May 30, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

Always 0 for unexisting @@variables #3109

Always 0 for unexisting @@variables #3109

donhardman commented Feb 20, 2025

tomatolog commented Feb 20, 2025

Uh oh!

sanikolaev commented Feb 20, 2025

Uh oh!

sanikolaev commented Feb 20, 2025

Uh oh!

tomatolog commented Feb 21, 2025

Uh oh!

sanikolaev commented Feb 25, 2025

Uh oh!

donhardman commented Mar 4, 2025

Uh oh!

PavelShilin89 commented May 13, 2025

Uh oh!

PavelShilin89 commented May 18, 2025

Uh oh!

tomatolog commented May 22, 2025

Uh oh!

PavelShilin89 commented May 26, 2025

Uh oh!

sanikolaev commented May 27, 2025

Uh oh!

PavelShilin89 commented May 28, 2025 •

edited

Loading

Uh oh!

sanikolaev commented May 29, 2025

Uh oh!

tomatolog commented May 29, 2025

Uh oh!

github-actions bot commented May 30, 2025

Uh oh!

Uh oh!

Always 0 for unexisting @@variables #3109

Always 0 for unexisting @@variables #3109

Comments

donhardman commented Feb 20, 2025

Bug Description:

Manticore Search Version:

Operating System Version:

Have you tried the latest development version?

Internal Checklist:

tomatolog commented Feb 20, 2025

Uh oh!

sanikolaev commented Feb 20, 2025

Uh oh!

sanikolaev commented Feb 20, 2025

Uh oh!

tomatolog commented Feb 21, 2025

Uh oh!

sanikolaev commented Feb 25, 2025

Uh oh!

donhardman commented Mar 4, 2025

Test Cases

1. Basic System Variable Tests

1.1 Test Existing System Variables

1.2 Test Non-existent System Variables

1.3 Test Partial Matches

2. Query Context Tests

2.1 Test in Multi-Select Queries

2.2 Test in Complex Queries

3. Client Integration Tests

3.1 Test with mysqldump/mariadb-dump

3.2 Test with MySQL-Related Integrations

4. User Variables Tests

4.1 Test User Variables vs System Variables

5. Edge Cases

5.1 Test Variable Name Edge Cases

5.2 Test Case Sensitivity

Expected Results Summary

Uh oh!

PavelShilin89 commented May 13, 2025

Uh oh!

PavelShilin89 commented May 18, 2025

Uh oh!

tomatolog commented May 22, 2025

for point 2

for points 3. 4

for points 5

for points 6

Uh oh!

PavelShilin89 commented May 26, 2025

Uh oh!

sanikolaev commented May 27, 2025

Uh oh!

PavelShilin89 commented May 28, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

sanikolaev commented May 29, 2025

Uh oh!

tomatolog commented May 29, 2025

Uh oh!

github-actions bot commented May 30, 2025

Uh oh!

PavelShilin89 commented May 28, 2025 •

edited

Loading