Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[YCQL] Failed to SELECT from TABLE with Secondary INDEX when upgrading to 2.0.5.4 #3281

Closed
nocaway opened this issue Jan 7, 2020 · 2 comments
Closed

[YCQL] Failed to SELECT from TABLE with Secondary INDEX when upgrading to 2.0.5.4 #3281

nocaway opened this issue Jan 7, 2020 · 2 comments
Assignees
@nocaway
Labels
kind/bug This issue is a bug priority/critical Critical issue
Milestone
v2.0

Comments

@nocaway
Copy link
Contributor

nocaway commented Jan 7, 2020

After upgrading to 2.0.5.4, a column is missing when selecting from a table that has secondary index.

describe a;
CREATE TABLE a (
    a_hash text PRIMARY KEY,
    a_time int,
    a_height bigint
) WITH default_time_to_live = 0;
CREATE INDEX a_idx ON a (a_height, a_hash);
select * from a WHERE a_height=1;
 a_hash                                                       | a_heigh
--------------------------------------------------------+--------------
@nocaway nocaway added kind/bug This issue is a bug priority/critical Critical issue labels Jan 7, 2020
@nocaway nocaway added this to the v2.0 milestone Jan 7, 2020
@nocaway nocaway self-assigned this Jan 7, 2020
nocaway added a commit that referenced this issue Jan 8, 2020
@nocaway
[YCQL] #3281 Fixed the Issue in Upgrade for SELECT from Indexed Table
a4f68ce 
Summary:
After upgrade server of 2.0 and later versions did not process the existing pre-2.0.5 indexes in the database correctly.

- INDEXes that were created before 2.0 releases does not contain column_names.
- Newer server uses column names to determine if an expression is covered by the index.

The fix
- For existing INDEXes, we continue to use column IDs when checking for covering.
- Also, when checking for covering, to be safe, the predicate `column_name.empty()` is used. The function "string::find(str, substr)" will return TRUE if "substr" is empty.

Test Plan:
Test manually.
```
CREATE TABLE test.bbb (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};
CREATE INDEX bbb_height ON test.bbb (height, k)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'false', 'consistency_level': 'user_enforced'};

cqlsh:test> EXPLAIN SELECT * FROM bbb WHERE height = 1;

 QUERY PLAN
----------------------------------------------
 Index Scan using test.bbb_height on test.bbb
   Key Conditions: (height = 1)

cqlsh:test> describe bbb1;

CREATE TABLE test.bbb1 (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};
CREATE INDEX bbb_height1 ON test.bbb1 (height, k) INCLUDE (time)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'false', 'consistency_level': 'user_enforced'};

cqlsh:test> EXPLAIN SELECT * FROM bbb1 WHERE height = 1;

 QUERY PLAN
-----------------------------------------------------
 Index Only Scan using test.bbb_height1 on test.bbb1
   Key Conditions: (height = 1)

cqlsh:test> describe bbb2;

CREATE TABLE test.bbb2 (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'true'};
CREATE INDEX bbb_height2 ON test.bbb2 (height, k)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'true'};

cqlsh:test> EXPLAIN SELECT * FROM bbb2 WHERE height = 1;

 QUERY PLAN
------------------------------------------------
 Index Scan using test.bbb_height2 on test.bbb2
   Key Conditions: (height = 1)

cqlsh:test> describe bbb3;

CREATE TABLE test.bbb3 (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'true'};
CREATE INDEX bbb_height3 ON test.bbb3 (height, k) INCLUDE (time)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'true'};

cqlsh:test> EXPLAIN SELECT * FROM bbb3 WHERE height = 1;

 QUERY PLAN
-----------------------------------------------------
 Index Only Scan using test.bbb_height3 on test.bbb3
   Key Conditions: (height = 1)
```

Reviewers: amitanand, mihnea

Reviewed By: mihnea

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D7768
@nocaway
Copy link
Contributor Author

nocaway commented Jan 8, 2020

This has been fixed in the master branch.

nocaway added a commit that referenced this issue Jan 9, 2020
@nocaway
[YCQL] #3281 Fixed the Issue in Upgrade for SELECT from Indexed Table
c25e3d9 
Summary:
After upgrade server of 2.0 and later versions did not process the existing pre-2.0.5 indexes in the database correctly.

- INDEXes that were created before 2.0 releases does not contain column_names.
- Newer server uses column names to determine if an expression is covered by the index.

The fix
- For existing INDEXes, we continue to use column IDs when checking for covering.
- Also, when checking for covering, to be safe, the predicate `column_name.empty()` is used. The function "string::find(str, substr)" will return TRUE if "substr" is empty.

Test Plan:
Test manually.
```
CREATE TABLE test.bbb (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};
CREATE INDEX bbb_height ON test.bbb (height, k)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'false', 'consistency_level': 'user_enforced'};

cqlsh:test> EXPLAIN SELECT * FROM bbb WHERE height = 1;

 QUERY PLAN
----------------------------------------------
 Index Scan using test.bbb_height on test.bbb
   Key Conditions: (height = 1)

cqlsh:test> describe bbb1;

CREATE TABLE test.bbb1 (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};
CREATE INDEX bbb_height1 ON test.bbb1 (height, k) INCLUDE (time)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'false', 'consistency_level': 'user_enforced'};

cqlsh:test> EXPLAIN SELECT * FROM bbb1 WHERE height = 1;

 QUERY PLAN
-----------------------------------------------------
 Index Only Scan using test.bbb_height1 on test.bbb1
   Key Conditions: (height = 1)

cqlsh:test> describe bbb2;

CREATE TABLE test.bbb2 (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'true'};
CREATE INDEX bbb_height2 ON test.bbb2 (height, k)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'true'};

cqlsh:test> EXPLAIN SELECT * FROM bbb2 WHERE height = 1;

 QUERY PLAN
------------------------------------------------
 Index Scan using test.bbb_height2 on test.bbb2
   Key Conditions: (height = 1)

cqlsh:test> describe bbb3;

CREATE TABLE test.bbb3 (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'true'};
CREATE INDEX bbb_height3 ON test.bbb3 (height, k) INCLUDE (time)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'true'};

cqlsh:test> EXPLAIN SELECT * FROM bbb3 WHERE height = 1;

 QUERY PLAN
-----------------------------------------------------
 Index Only Scan using test.bbb_height3 on test.bbb3
   Key Conditions: (height = 1)
```

Reviewers: amitanand, mihnea

Reviewed By: mihnea

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D7768
@nocaway
Copy link
Contributor Author

nocaway commented Jan 9, 2020

This fix has been backported to 2.0.5 branch.

@nocaway nocaway closed this as completed Jan 9, 2020
carlos-username pushed a commit to carlos-username/yugabyte-db that referenced this issue Mar 11, 2020
@nocaway
[YCQL] yugabyte#3281 Fixed the Issue in Upgrade for SELECT from Index…
5a2fb37 
…ed Table

Summary:
After upgrade server of 2.0 and later versions did not process the existing pre-2.0.5 indexes in the database correctly.

- INDEXes that were created before 2.0 releases does not contain column_names.
- Newer server uses column names to determine if an expression is covered by the index.

The fix
- For existing INDEXes, we continue to use column IDs when checking for covering.
- Also, when checking for covering, to be safe, the predicate `column_name.empty()` is used. The function "string::find(str, substr)" will return TRUE if "substr" is empty.

Test Plan:
Test manually.
```
CREATE TABLE test.bbb (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};
CREATE INDEX bbb_height ON test.bbb (height, k)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'false', 'consistency_level': 'user_enforced'};

cqlsh:test> EXPLAIN SELECT * FROM bbb WHERE height = 1;

 QUERY PLAN
----------------------------------------------
 Index Scan using test.bbb_height on test.bbb
   Key Conditions: (height = 1)

cqlsh:test> describe bbb1;

CREATE TABLE test.bbb1 (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};
CREATE INDEX bbb_height1 ON test.bbb1 (height, k) INCLUDE (time)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'false', 'consistency_level': 'user_enforced'};

cqlsh:test> EXPLAIN SELECT * FROM bbb1 WHERE height = 1;

 QUERY PLAN
-----------------------------------------------------
 Index Only Scan using test.bbb_height1 on test.bbb1
   Key Conditions: (height = 1)

cqlsh:test> describe bbb2;

CREATE TABLE test.bbb2 (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'true'};
CREATE INDEX bbb_height2 ON test.bbb2 (height, k)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'true'};

cqlsh:test> EXPLAIN SELECT * FROM bbb2 WHERE height = 1;

 QUERY PLAN
------------------------------------------------
 Index Scan using test.bbb_height2 on test.bbb2
   Key Conditions: (height = 1)

cqlsh:test> describe bbb3;

CREATE TABLE test.bbb3 (
    k int PRIMARY KEY,
    time int,
    height bigint
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'true'};
CREATE INDEX bbb_height3 ON test.bbb3 (height, k) INCLUDE (time)
    WITH CLUSTERING ORDER BY (k ASC)
    AND transactions = {'enabled': 'true'};

cqlsh:test> EXPLAIN SELECT * FROM bbb3 WHERE height = 1;

 QUERY PLAN
-----------------------------------------------------
 Index Only Scan using test.bbb_height3 on test.bbb3
   Key Conditions: (height = 1)
```

Reviewers: amitanand, mihnea

Reviewed By: mihnea

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D7768
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@nocaway nocaway

Labels
kind/bug This issue is a bug priority/critical Critical issue
Projects
None yet
Milestone
v2.0
Development

No branches or pull requests
1 participant
@nocaway