Performance Reproduction for DDL Schema

[katieluo88]

Hello, thanks for releasing the model weights! I'm trying to reproduce the results for the XiYanSQL-QwenCoder-32B by running the released Modelscope weights with the Chinese prompt, as per-example. However, appending the SQL schemas in DDL format yields a very low EX performance (14.47 EX over the total dev set):

                     simple               moderate             challenging          total
count                925                  464                  145                  1534
======================================    ACCURACY    =====================================
accuracy             19.68                7.54                 3.45                 14.47
===========================================================================================

Should I be using a different prompt? I'm using the model with vLLM, prompted according to the following prompt:

NL2SQL_TEMPLATE_CN = f"""你是一名{{dialect}}专家，现在需要阅读并理解下面的【数据库schema】描述，以及可能用到的【参考信息】，并运用{{dialect}}知识生成sql语句回答【用户问题】。
【用户问题】
{{question}}

【数据库schema】
{{db_schema}}

【参考信息】
{{evidence}}

【用户问题】
{{question}}

```sql"""

where the db_schema is given as the concatenation of the DDL schemas within the database: "\n\n".join(f"{ddl}" for _, ddl in db_schemas.items()).

I wish to check if this is this the way it should be prompted, or if it needs to be modified to be something different. Many thanks!

[XGenerationLab]

Hello, the results you've reproduced must have encountered a bug to result in such low values.

The DDL results we obtained are based on the original DDL of the corresponding database. Due to the lack of more information, I suspect your problem might be related to your use of vLLM, as similar issues have been observed on Modelscope before. It might also be related to the DDL information you are using.

We are also considering validating the use of vLLM and supplementing more evaluation information.

[katieluo88]

I see; in that case I look forward to seeing the detailed instructions on how to run it with vLLM. To provide a bit more detail, the command I'm using to run the ModelScope weights is to first download the weights/config locally, then running it with vLLM serve, like so:

vllm serve [MODELSCOPE_LOCAL_PATH] --tensor-parallel-size [NUM_GPUS] --served-model-name xiyan-qwencode-32b

[XGenerationLab]

Sure, thank you. We will consider providing instructions for running it with vLLM.

[longbowzhang]

a side question: I notice that the

【用户问题】
{{question}}

section appears twice in the template, is this duplication intentional (for a specific purpose) or is it a mistake?
If it’s intentional (I assume this is the case), could you clarify the reasoning behind this design?

[XGenerationLab]

Yep, this duplication is intentional. It is designed to emphasize the question information, particularly before the database schema.

[longbowzhang]

Follow-up questions
I also explore the https://github.com/XGenerationLab/M-Schema repository and find out the prompt to use

dialect = db_engine.dialect.name
question = ''
evidence = ''
prompt = """You are now a {dialect} data analyst, and you are given a database schema as follows:

【Schema】
{db_schema}

【Question】
{question}

【Evidence】
{evidence}

Please read and understand the database schema carefully, and generate an executable SQL based on the user's question and evidence. The generated SQL is protected by ```sql and ```.
""".format(dialect=dialect, question=question, db_schema=mschema_str, evidence=evidence)

Q1: this seems different from the prompt NL2SQL_TEMPLATE_CN, for example, the order of 【Question】(which comes after Schema) and no duplication. So which prompt template you suggest to use?

Q2: in the above prompt, it emphasize that "The generated SQL is protected by ```sql and ```". However, when i play around the model (14B version) with this prompt, it failed to generate "```sql and ```". Even with the NL2SQL_TEMPLATE_CN prompt template, the model fails to generate the enclosing tag "```". Do you observe similar patterns (i.e., missing the SQL code block formatting ```sql and ```)?

Thanks a lot advance.

[XGenerationLab]

Great exploration. However, please do not confuse these different contents. When using our XiYanSQL series models, please follow the prompt format we provide. The prompt in the m-schema repository is just a general example.

[longbowzhang]

Thanks for clarifying the usage of prompts.
But, how about the reason of missing of SQL code block formatting ```sql and ``` with both prompts? have you observed similar problem? any insights on this?

[XGenerationLab]

Our XiYanSQL models does not require the use of the sql and format for output; the ```sql in the prompt is merely a guide.