Major Description
Build: 6.5.0-4349
Standard `travel-sample` bucket
Steps to reproduce:
- Load sample `travel-sample` bucket
- drop primary and all secondary indexes.
- try to run the following query:
WITH CTE_DTXFU AS (
|
SELECT BUCKET_BNCUM.free_breakfast,
|
BUCKET_BNCUM.id,
|
BUCKET_BNCUM.address
|
FROM `travel-sample` AS BUCKET_BNCUM
|
WHERE BUCKET_BNCUM.free_breakfast=TRUE)
|
SELECT `travel-sample`.id,
|
CTE_DTXFU.address,
|
(WITH CTE_WAOGJ AS (
|
SELECT BUCKET_HPZIP.id
|
FROM `travel-sample` AS BUCKET_HPZIP
|
WHERE BUCKET_HPZIP.free_breakfast=TRUE)
|
SELECT RAW AVG(CTE_WAOGJ.id) AS AVG_KKXQF
|
FROM CTE_WAOGJ
|
WHERE CTE_WAOGJ.id < 9036787 |
OR CTE_WAOGJ.id = 97036787 )[0] AS SUBQUERY_GXPDE |
FROM `travel-sample`
|
INNER JOIN CTE_DTXFU ON (`travel-sample`.id == CTE_DTXFU.id)
|
WHERE CTE_DTXFU.id IN (WITH CTE_KDCLK AS (
|
SELECT BUCKET_VYBDB.free_breakfast,
|
BUCKET_VYBDB.id
|
FROM `travel-sample` AS BUCKET_VYBDB
|
WHERE BUCKET_VYBDB.free_breakfast=TRUE)
|
SELECT RAW BUCKET_FRKVL.id
|
FROM `travel-sample` AS BUCKET_FRKVL
|
WHERE BUCKET_FRKVL.id = 7436 ) |
The following error message appears:
[
|
{
|
"code": 4000, |
"msg": "No index available on keyspace travel-sample that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.", |
"query": "WITH CTE_DTXFU AS (\n SELECT BUCKET_BNCUM.free_breakfast,\n BUCKET_BNCUM.id,\n BUCKET_BNCUM.address\n FROM `travel-sample` AS BUCKET_BNCUM\n WHERE BUCKET_BNCUM.free_breakfast=TRUE)\nSELECT `travel-sample`.id,\n CTE_DTXFU.address,\n (WITH CTE_WAOGJ AS (\n SELECT BUCKET_HPZIP.id\n FROM `travel-sample` AS BUCKET_HPZIP\n WHERE BUCKET_HPZIP.free_breakfast=TRUE)\nSELECT RAW AVG(CTE_WAOGJ.id) AS AVG_KKXQF\nFROM CTE_WAOGJ\nWHERE CTE_WAOGJ.id < 9036787\n OR CTE_WAOGJ.id = 97036787 )[0] AS SUBQUERY_GXPDE\nFROM `travel-sample`\nINNER JOIN CTE_DTXFU ON (`travel-sample`.id == CTE_DTXFU.id)\nWHERE CTE_DTXFU.id IN (WITH CTE_KDCLK AS (\n SELECT BUCKET_VYBDB.free_breakfast,\n BUCKET_VYBDB.id\n FROM `travel-sample` AS BUCKET_VYBDB\n WHERE BUCKET_VYBDB.free_breakfast=TRUE)\nSELECT RAW BUCKET_FRKVL.id\nFROM `travel-sample` AS BUCKET_FRKVL\nWHERE BUCKET_FRKVL.id = 7436 )" |
}
|
]
|
Which is totally OK.
then use advise function for this query, it will give you this:
Covered Index Recommendations
|
CREATE INDEX adv_id ON `travel-sample`(`id`)
|
Create suggested index, run query once again
Error as a result:
[
|
{
|
"code": 5370, |
"msg": "Unable to run subquery - cause: No index available on keyspace travel-sample that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.", |
"query": "WITH CTE_DTXFU AS (\n SELECT BUCKET_BNCUM.free_breakfast,\n BUCKET_BNCUM.id,\n BUCKET_BNCUM.address\n FROM `travel-sample` AS BUCKET_BNCUM\n WHERE BUCKET_BNCUM.free_breakfast=TRUE)\nSELECT `travel-sample`.id,\n CTE_DTXFU.address,\n (WITH CTE_WAOGJ AS (\n SELECT BUCKET_HPZIP.id\n FROM `travel-sample` AS BUCKET_HPZIP\n WHERE BUCKET_HPZIP.free_breakfast=TRUE)\nSELECT RAW AVG(CTE_WAOGJ.id) AS AVG_KKXQF\nFROM CTE_WAOGJ\nWHERE CTE_WAOGJ.id < 9036787\n OR CTE_WAOGJ.id = 97036787 )[0] AS SUBQUERY_GXPDE\nFROM `travel-sample`\nINNER JOIN CTE_DTXFU ON (`travel-sample`.id == CTE_DTXFU.id)\nWHERE CTE_DTXFU.id IN (WITH CTE_KDCLK AS (\n SELECT BUCKET_VYBDB.free_breakfast,\n BUCKET_VYBDB.id\n FROM `travel-sample` AS BUCKET_VYBDB\n WHERE BUCKET_VYBDB.free_breakfast=TRUE)\nSELECT RAW BUCKET_FRKVL.id\nFROM `travel-sample` AS BUCKET_FRKVL\nWHERE BUCKET_FRKVL.id = 7436 )" |
},
|
{
|
"code": 5010, |
"msg": "Error evaluating WITH. - cause: No index available on keyspace travel-sample that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online." |
}
|
]
|
Advise function usage gives nothing, but it is enough to create the following index:
CREATE INDEX adv_breakfast ON `travel-sample`(`free_breakfast`)
|
and query will be executed successfully.