[MCOL-256] queries return random data when they have inline comments Created: 2016-07-21  Updated: 2016-09-09  Resolved: 2016-08-26

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.0.1
Fix Version/s: 1.0.3

Type: Bug Priority: Major
Reporter: David Hill (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Attachments: PNG File image-2016-07-21-15-35-42-270.png     PNG File image-2016-07-21-15-37-53-509.png    
Issue Links:
PartOf
is part of MCOL-280 Beta issues Closed
Problem/Incident
causes MCOL-284 Comment parser in MCOL-256 breaks som... Closed
Sprint: 1.0.3

 Description   

Alpha customer reported following issue:

We continue to do testing, actively, with the ColumnStore engine… and our tools place in-line comments in some of the queries.
If you place inline comments in the queries, random data is returned, so I suspect there’s a bug within the parsing of the query strings?

Removing the inline comments results in successful querying of data.

example:

Before:
SELECT
d.*,
c.cause_name
FROM gbd.output_epi_multi_year_v263 d
LEFT JOIN shared.cause_hierarchy ch ON ch.cause_id = d.cause_id
LEFT JOIN shared.cause c ON c.cause_id = d.cause_id
WHERE measure_id = 5 – prevalence
AND year_start_id = 1990
AND year_end_id = 2015
AND location_id = 160
AND metric_id = 2 – percent
AND sex_id = 3 – both sexes
AND age_group_id = 22 – all ages
AND ch.level = 2 – cause hierarchy level 1
ORDER BY val ASC – lowest to highest values
LIMIT 1

After:
SELECT
d.*,
c.cause_name
FROM gbd.output_epi_multi_year_v263 d
LEFT JOIN shared.cause_hierarchy ch ON ch.cause_id = d.cause_id
LEFT JOIN shared.cause c ON c.cause_id = d.cause_id
WHERE measure_id = 5
AND year_start_id = 1990
AND year_end_id = 2015
AND location_id = 160
AND metric_id = 2
AND sex_id = 3
AND age_group_id = 22
AND ch.level = 2
ORDER BY val ASC
LIMIT 1

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2016-08-18 ]

Note that this isn't reproducible on the command line, but I've managed to reproduce it using a PHP script to access MySQL.

Comment by Andrew Hutchings (Inactive) [ 2016-08-18 ]

It is taking everything after the start of the comment including following lines of SQL as the whole comment

Comment by Andrew Hutchings (Inactive) [ 2016-08-18 ]

Progress notes...

In the debug log during my test:

info: Query: create temporary table infinidb_vtable.$vtable_2 engine = aria as select City.name  from City where City.Population > 1000000 -- 1 million limit 1

So the engine is creating a temporary table with the data using the original query, newlines removed. This means that the double dash is commenting out the rest of the query.

This temporary table query is generated in mysql/ha_calpont_execplan.cpp, getSelectPlan() around line 6179 onwards:

create_query = "create temporary table " + vtb.str() + " as select " + sel_cols_in_create + " from ";

Cause found, now for a fix...

Comment by Andrew Hutchings (Inactive) [ 2016-08-18 ]

Right problem, wrong place. It was actually being triggered in similar code in the modified sql/sql_parse.cc.

It was triggered due to the ColumnStore modifications to MariaDB stripping newline characters from the original query which is later used to generate a temporary table query. The following pull request fixes this by also stripping the comments when stripping the newline charaters:

https://github.com/mariadb-corporation/mariadb-columnstore-server/pull/2

Recommend for a test case that you test any query with a comment in the middle and also test using something like:

select "--" as a, name as b from users;

Comment by Andrew Hutchings (Inactive) [ 2016-08-18 ]

Obviously not for review until after the freeze

Comment by Daniel Lee (Inactive) [ 2016-08-26 ]

Verified in 1.0.2.1-1. The order by clause was not skipped due to the inline comment

[root@builder dlee]# cat /tmp/test.php
<?php
$mysql1 = new mysqli('192.168.0.209', 'root', '', 'mytest');

$sql = "select n_name from nation
where n_regionkey > 0 – test comment
order by n_name
";

if (!$result = $mysql1->query($sql))

{ echo "Error: Our query failed to execute and here is why: \n"; echo "Query: " . $sql . "\n"; echo "Errno: " . $mysql1->errno . "\n"; echo "Error: " . $mysql1->error . "\n"; exit; }

for ($i = 0; $i < $result->num_rows; $i++)

{ $row = $result->fetch_row(); echo $row[0] . "\n"; //echo $row[1] . "\n"; //echo $row[2] . "\n"; }

echo $result->num_rows;
?>

Release 1.0.2-1 output:

[root@builder dlee]# php /tmp/test.php
ARGENTINA
BRAZIL
CANADA
EGYPT
FRANCE
GERMANY
INDIA
INDONESIA
IRAN
IRAQ
JAPAN
JORDAN
PERU
CHINA
ROMANIA
SAUDI ARABIA
VIETNAM
RUSSIA
UNITED KINGDOM
UNITED STATES
ARGENTINA
BRAZIL
CANADA
EGYPT
FRANCE
GERMANY
INDIA
INDONESIA
IRAN
IRAQ
JAPAN
JORDAN
PERU
CHINA
ROMANIA
SAUDI ARABIA
VIETNAM
RUSSIA
UNITED KINGDOM
UNITED STATES
40

Release 1.0.2.1-1 output

[root@builder dlee]# php /tmp/test.php
ARGENTINA
ARGENTINA
BRAZIL
BRAZIL
CANADA
CANADA
CHINA
CHINA
EGYPT
EGYPT
FRANCE
FRANCE
GERMANY
GERMANY
INDIA
INDIA
INDONESIA
INDONESIA
IRAN
IRAN
IRAQ
IRAQ
JAPAN
JAPAN
JORDAN
JORDAN
PERU
PERU
ROMANIA
ROMANIA
RUSSIA
RUSSIA
SAUDI ARABIA
SAUDI ARABIA
UNITED KINGDOM
UNITED KINGDOM
UNITED STATES
UNITED STATES
VIETNAM
VIETNAM
40

Comment by Daniel Lee (Inactive) [ 2016-08-26 ]

Closed per last comment.

Generated at Thu Feb 08 02:19:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.