[sqlite] Ambiguous column name error

[FlorineChen113]

Description of erroneous behavior

Nodejs version: 20.18.1, cds-dk version: 8.5.0
We are switching from sqlite3 to @cap-js/sqlite. When deploying a table with command cds deploy that contains the column name 'value', the deployment failed and gave an ambiguous column name error.

The cds model is like:

entity Status {
    key ID          : String;
        value       : String;
        criticality : Integer;
        text        : String;
}

The csv data is:

ID,value,criticality,text
1,active,3,Active
2,inactive,2,Inactive
3,deleted,1,Deleted

When deploying to sqlite we get the following errors:

SqliteError: in cds.deploy(): ambiguous column name: value in:
INSERT INTO ld_prodManagement_Status (ID,value,criticality,text) SELECT value->>'$[0]' as ID,CASE WHEN OLD.ID IS NULL THEN value->>'$[1]' ELSE (CASE WHEN json_type(value,'$[1]') IS NULL THEN OLD.value ELSE value->>'$[1]' END) END as value,CASE WHEN OLD.ID IS NULL THEN value->>'$[2]' ELSE (CASE WHEN json_type(value,'$[2]') IS NULL THEN OLD.criticality ELSE value->>'$[2]' END) END as criticality,CASE WHEN OLD.ID IS NULL THEN value->>'$[3]' ELSE (CASE WHEN json_type(value,'$[3]') IS NULL THEN OLD.text ELSE value->>'$[3]' END) END as text FROM (SELECT value, value->>'$[0]' as ID from json_each(?)) as NEW LEFT JOIN ld_prodManagement_Status AS OLD ON NEW.ID=OLD.ID WHERE TRUE ON CONFLICT(ID) DO UPDATE SET value = excluded.value,criticality = excluded.criticality,text = excluded.text
Query {
  UPSERT: {
    into: { ref: [ 'ld.prodManagement.Status' ] },
    columns: [ 'ID', 'value', 'criticality', 'text' ],
    rows: [
      [ '1', 'active', '3', 'Active' ],
      [ '2', 'inactive', '2', 'Inactive' ],
      [ '3', 'deleted', '1', 'Deleted' ]
    ]
  }
}

Details about your project

Your Project Name	…
OData version	v4
Node.js version	v20.18.1
@sap/cds	8.5.0
@sap/cds-compiler	5.5.2
@sap/cds-dk	8.5.0

[aschmidt93]

This also applies to postgres.

[patricebender]

Hi,

I was not able to reproduce the issue on my end. I did the following:

1. cds init sample
2. cds sample && npm i
3. add your model as schema.cds & sample data as Status.csv

I deployed the data successfully with cds deploy and also during server start:

❯ cds deploy
  > init from db/data/Status.csv 
/> successfully deployed to in-memory database. 

❯ cds r
Welcome to cds repl v 8.5.0 
> var server = await cds.test()
[cds] - loaded model from 1 file(s):

  db/schema.cds

[cds] - connect to db > sqlite { url: ':memory:' }
  > init from db/data/Status.csv 
/> successfully deployed to in-memory database. 


[cds] - server listening on { url: 'http://localhost:54560' }
[cds] - launched at 12/6/2024, 9:53:00 AM, version: 8.5.0, in: 216.926ms
[cds] - [ terminate with ^C ]

> { Status } = cds.entities
[object Function]
> await SELECT.from(Status)
[
  { ID: '1', value: 'active', criticality: 3, text: 'Active' },
  { ID: '2', value: 'inactive', criticality: 2, text: 'Inactive' },
  { ID: '3', value: 'deleted', criticality: 1, text: 'Deleted' }
]

here are my versions:

❯ cds v

@cap-js/asyncapi: 1.0.2
@cap-js/cds-types: 0.6.5
@cap-js/db-service: 1.16.0
@cap-js/hana: 1.5.0
@cap-js/openapi: 1.0.5
@cap-js/sqlite: 1.7.7
@sap/cds: 8.5.0
@sap/cds-compiler: 5.5.0
@sap/cds-dk: 8.5.0
@sap/cds-fiori: 1.2.8
@sap/cds-foss: 5.0.1
@sap/cds-mtxs: 2.0.0
@sap/eslint-plugin-cds: 3.0.4
Node.js: v20.11.0

Please provide a sample project, with detailed steps to reproduce the issue.

[FlorineChen113]

Hi @patricebender ,

Steps to reproduce:

• Repo:
• Branch name: bug-fix-PHARMANETWORK-46120-upgrade-sqlite
• Install the npm packages
• Run cds deploy
• Got following error

> init from test\data\ld.prodManagement.Products.csv 
  > init from test\data\ld.prodManagement.Organizations.csv 
  > init from test\data\ld.prodManagement.GtinSyncLogs.csv 
  > init from test\data\ld.prodManagement.GlobalCompanyPrefixes.csv 
  > init from test\data\ld.prodManagement.GCPContent.csv 
  > init from test\data\ld.prodManagement.Endpoints.csv 
  > init from db\data\ld.prodManagement.Status.csv 
/> deployment to db\productDb.sqlite failed

SqliteError: in cds.deploy(): ambiguous column name: value in:
INSERT INTO ld_prodManagement_Status (ID,value,criticality,text) SELECT value->>'$[0]' as ID,CASE WHEN OLD.ID IS NULL THEN value->>'$[1]' ELSE (CASE WHEN json_type(value,'$[1]') IS NULL THEN OLD.value ELSE value->>'$[1]' END) END as value,CASE WHEN OLD.ID IS NULL THEN value->>'$[2]' ELSE (CASE WHEN json_type(value,'$[2]') IS NULL THEN OLD.criticality ELSE value->>'$[2]' END) END as criticality,CASE WHEN OLD.ID IS NULL THEN value->>'$[3]' ELSE (CASE WHEN json_type(value,'$[3]') IS NULL THEN OLD.text ELSE value->>'$[3]' END) END as text FROM (SELECT value, value->>'$[0]' as ID from json_each(?)) as NEW LEFT JOIN ld_prodManagement_Status AS OLD ON NEW.ID=OLD.ID WHERE TRUE ON CONFLICT(ID) DO UPDATE SET value = excluded.value,criticality = excluded.criticality,text = excluded.text
Query {
  UPSERT: {
    into: { ref: [ 'ld.prodManagement.Status' ] },
    columns: [ 'ID', 'value', 'criticality', 'text' ],
    rows: [
      [ '1', 'active', '3', 'Active' ],
      [ '2', 'inactive', '2', 'Inactive' ],
      [ '3', 'deleted', '1', 'Deleted' ]
    ]
  }
}
    at Database.prepare (C:\Project\LD\vrs-ld-service-product-mgmt\node_modules\better-sqlite3\lib\methods\wrappers.js:5:21)
    at SQLiteService.prepare (C:\Project\LD\vrs-ld-service-product-mgmt\node_modules\@cap-js\sqlite\lib\SQLiteService.js:75:29)
    at SQLiteService.onUPSERT (C:\Project\LD\vrs-ld-service-product-mgmt\node_modules\@cap-js\db-service\lib\SQLService.js:184:27)
    at next (C:\Project\LD\vrs-ld-service-product-mgmt\node_modules\@sap\cds\lib\srv\srv-dispatch.js:69:36)
    at SQLiteService.onDeep (C:\Project\LD\vrs-ld-service-product-mgmt\node_modules\@cap-js\db-service\lib\deep-queries.js:43:39)
    at next (C:\Project\LD\vrs-ld-service-product-mgmt\node_modules\@sap\cds\lib\srv\srv-dispatch.js:69:36)
    at SQLiteService.handle (C:\Project\LD\vrs-ld-service-product-mgmt\node_modules\@sap\cds\lib\srv\srv-dispatch.js:73:6)
    at SQLiteService.dispatch (C:\Project\LD\vrs-ld-service-product-mgmt\node_modules\@sap\cds\lib\srv\srv-dispatch.js:35:15) {
  code: 'SQLITE_ERROR',
  query: "INSERT INTO ld_prodManagement_Status (ID,value,criticality,text) SELECT value->>'$[0]' as ID,CASE WHEN OLD.ID IS NULL THEN value->>'$[1]' ELSE (CASE WHEN json_type(value,'$[1]') IS NULL THEN OLD.value ELSE value->>'$[1]' END) END as value,CASE WHEN OLD.ID IS NULL THEN value->>'$[2]' ELSE (CASE WHEN json_type(value,'$[2]') IS NULL THEN OLD.criticality ELSE value->>'$[2]' END) END as criticality,CASE WHEN OLD.ID IS NULL THEN value->>'$[3]' ELSE (CASE WHEN json_type(value,'$[3]') IS NULL THEN OLD.text ELSE value->>'$[3]' END) END as text FROM (SELECT value, value->>'$[0]' as ID from json_each(?)) as NEW LEFT JOIN ld_prodManagement_Status AS OLD ON NEW.ID=OLD.ID WHERE TRUE ON CONFLICT(ID) DO UPDATE SET value = excluded.value,criticality = excluded.criticality,text = excluded.text"
}

[patricebender]

Hi, there is no db\data\ld.prodManagement.Status.csv as far as I can see. At least not on the branch you suggested :(

[patricebender]

Moreover, I can't find @cap-js/hana in your package.json as dependency. Could you please run npm add @cap-js/hana and try again?

[FlorineChen113]

Hi @patricebender ,
The ld.prodManagement.Status.csv is under db/data, not test/db/data.

I've tried adding @cap-js/hana and the error is still there. Please kindly pull the change and check again.
Thanks!

[patricebender]

thanks for your patience :) I was able to identify the issue and will now figure out a fix. Stay tuned.