Case sensitivity in SQL query engine while using Where condition

[mraguth]

In SQL query engine, I have to call exactly same field name in the where condtion. It is painful to know how the data is in the database fields for every field. Is there way I can mitigate? Thank you.

https://docs.llamaindex.ai/en/latest/examples/query_engine/SQLAutoVectorQueryEngine/

[dosubot[bot]]

To address case sensitivity issues in the SQL query engine when using the WHERE condition, you can modify your SQL queries to use case-insensitive comparisons. This can be done by using functions like LOWER() or UPPER() in your SQL queries.

Here is an example of how you can modify your SQL query to be case-insensitive:

# Example SQL query with case-insensitive WHERE condition
sql_query = """
SELECT * FROM your_table
WHERE LOWER(your_column) = LOWER('your_value')
"""

In the context of using the SQLAutoVectorQueryEngine, you would ensure that your SQL queries are constructed in this manner before they are executed by the query engine. The SQLAutoVectorQueryEngine itself does not directly handle case sensitivity, so the responsibility lies in how you construct your SQL queries.

For a practical implementation, you can refer to the SQL Auto Vector Query Engine tutorial provided by LlamaIndex ^[1].

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu I have encountered a similar problem with SQL and pandas query pipelines due to case sensitivity. For instance, if I query for 'phone' instead of 'Phone', my query engine returns no data because pandas is case sensitive. My question is, can we implement a mechanism to closely match user queries with data fields, such as 'Response Synthesis' or 'Prompt Customization'?

This issue becomes particularly challenging when deploying applications to end users who may not be fully aware of how values are stored in the structured database. While this approach works well with unstructured data like PDFs, it is cumbersome when dealing with structured databases using SQL or pandas. Any advice on how to make this process more user-friendly would be greatly appreciated. Thank you!"

[dosubot[bot]]

To handle case sensitivity in SQL and pandas query pipelines and make the process more user-friendly for end users, you can implement mechanisms such as response synthesis and prompt customization. Here are some approaches:

SQL

In SQL, you can use the LOWER function to convert both the column values and the search values to lowercase, ensuring case-insensitive comparisons.

Example:

SELECT * FROM your_table WHERE LOWER(your_column) = LOWER('your_value');

Pandas

In pandas, you can use the str.lower() method to convert both the column values and the search values to lowercase.

Example:

import pandas as pd

# Sample DataFrame
df = pd.DataFrame({
    'Phone': ['iPhone', 'Samsung', 'Nokia'],
    'Price': [999, 799, 499]
})

# Case-insensitive search
search_value = 'iphone'
result = df[df['Phone'].str.lower() == search_value.lower()]

print(result)

Prompt Customization in LlamaIndex

LlamaIndex allows you to customize query prompts to handle case sensitivity issues. You can modify the auto-retrieval prompt to include instructions for handling case sensitivity. Here is an example:

from llama_index.core.prompts import display_prompt_dict
from llama_index.core import PromptTemplate

# Retrieve the current prompts
prompts_dict = retriever.get_prompts()

# Display the current prompt template variables
display_prompt_dict(prompts_dict)

# Define a new prompt template with additional instructions for case sensitivity
prompt_tmpl_str = """\
Your goal is to structure the user's query to match the request schema provided below.

<< Structured Request Schema >>
When responding use a markdown code snippet with a JSON object formatted in the following schema:

{schema_str}

The query string should contain only text that is expected to match the contents of documents. Any conditions in the filter should not be mentioned in the query as well.

Make sure that filters only refer to attributes that exist in the data source.
Make sure that filters take into account the descriptions of attributes.
Make sure that filters are only used as needed. If there are no filters that should be applied return [] for the filter value.
If the user's query explicitly mentions number of documents to retrieve, set top_k to that number, otherwise do not set top_k.
Do NOT EVER infer a null value for a filter. This will break the downstream program. Instead, don't include the filter.

<< Example 1. >>
Data Source:
```json
{{
    "metadata_info": [
        {{
            "name": "author",
            "type": "str",
            "description": "Author name"
        }},
        {{
            "name": "book_title",
            "type": "str",
            "description": "Book title"
        }},
        {{
            "name": "year",
            "type": "int",
            "description": "Year Published"
        }},
        {{
            "name": "pages",
            "type": "int",
            "description": "Number of pages"
        }},
        {{
            "name": "summary",
            "type": "str",
            "description": "A short summary of the book"
        }}
    ],
    "content_info": "Classic literature"
}}

User Query:
What are some books by Jane Austen published after 1813 that explore the theme of marriage for social standing?

Additional Instructions:
None

Structured Request:

{{"query": "Books related to theme of marriage for social standing", "filters": [{{"key": "year", "value": "1813", "operator": ">"}}, {{"key": "author", "value": "Jane Austen", "operator": "=="}}], "top_k": null}}

<< Example 2. >>
Data Source:
{info_str}

User Query:
{query_str}

Additional Instructions:
{additional_instructions}

Structured Request:
"""

# Create a new prompt template
prompt_tmpl = PromptTemplate(prompt_tmpl_str)

# Add additional instructions for case sensitivity
add_instrs = """\
If one of the filters is 'theme', please make sure that the first letter of the inferred value is capitalized. Only words that are capitalized are valid values for "theme". \
"""
prompt_tmpl = prompt_tmpl.partial_format(additional_instructions=add_instrs)

# Update the retriever with the new prompt template
retriever.update_prompts({"prompt": prompt_tmpl})

This customization ensures that the LLM will capitalize the first letter of the inferred value for the "theme" attribute, addressing case sensitivity issues ^[1][2].

By implementing these mechanisms, you can make the querying process more user-friendly for end users who may not be aware of how values are stored in the structured database.

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu This is not clear. I am using pandas query engine https://docs.llamaindex.ai/en/stable/examples/query_engine/pandas_query_engine/

I am encountering an issue with case sensitivity in the query_engine.query function. For example, if my CSV column "fruit" contains the value "Apple" and I query for all fruits like "apple," the pandas query engine returns zero rows. Because human asked 'apple' but dataframe has 'Apple'... so LLM or pandas query found zero rows. How can I resolve this?

I understand that the pandas query engine operates based on pandas syntax, and I am aware that pandas is case-sensitive. Is it possible to vectorize the data within the pandas DataFrame and perform a case-insensitive query? I am open to any suggestions. I have tried to ignore case sensitivity using pandas instructions, but it is not working. Thank you for your help.

[dosubot[bot]]

To resolve the issue of case sensitivity in the PandasQueryEngine when querying a DataFrame, you can modify the query to convert both the query string and the DataFrame column values to lowercase. This ensures that the comparison is case-insensitive.

Here is an example of how you can modify the query to achieve this:

import pandas as pd
from llama_index.experimental.query_engine.pandas import PandasQueryEngine

# Sample DataFrame
df = pd.DataFrame({
    "fruit": ["Apple", "Banana", "Cherry"]
})

# Initialize the PandasQueryEngine
query_engine = PandasQueryEngine(df=df, verbose=True)

# Modify the query to be case-insensitive
query = 'fruit.str.lower() == "apple".lower()'

# Execute the query
response = query_engine.query(query)
print(response)

In this example, the query converts both the column values (fruit.str.lower()) and the search string ("apple".lower()) to lowercase before performing the comparison. This ensures that the query is case-insensitive ^[1].

Additionally, you can customize or extend the PandasQueryEngine to preprocess DataFrame columns to standardize case before performing queries. Here is an example:

import pandas as pd
from llama_index.experimental.query_engine.pandas import PandasQueryEngine

class CustomPandasQueryEngine(PandasQueryEngine):
    def __init__(self, df: pd.DataFrame, **kwargs: Any) -> None:
        # Preprocess DataFrame to standardize case
        df.columns = [col.lower() for col in df.columns]
        df = df.applymap(lambda x: x.lower() if isinstance(x, str) else x)
        super().__init__(df, **kwargs)

# Example usage
df = pd.DataFrame(
    {
        "City": ["Toronto", "Tokyo", "Berlin"],
        "Population": [2930000, 13960000, 3645000]
    }
)

query_engine = CustomPandasQueryEngine(df=df, verbose=True)
response = query_engine.query("city == 'tokyo'")
print(response)

In this example, the CustomPandasQueryEngine class preprocesses the DataFrame columns and their values to lowercase before calling the parent class's __init__ method, ensuring case-insensitive querying ^[1].

To continue talking to Dosu, mention @dosu.