Virtual column does not exist

[pwasem]

I am facing an issue with a virtual column.

Given

• this simple data model using a virtual field rating (./db/schema.cds):

using {
  cuid,
  managed
} from '@sap/cds/common';

namespace sap.cap.ideas;

entity Ideas : cuid, managed {
  @mandatory
  title          : String(50);

  @mandatory
  description    : String(250);

  virtual rating : Decimal;
}

• this service (./srv/ideas-service.cds):

using {sap.cap.ideas as ideas} from '../db/schema';

service IdeasService {
  entity Ideas as projection on ideas.Ideas;
}

• these fiori annotations (./app/ideas/fiori-service.cds):

using IdeasService from '../../srv/ideas-service';

annotate IdeasService.Ideas with @(
  odata.draft.enabled,
  UI.HeaderInfo          : {
    TypeName       : '{i18n>Ideas.headerInfo.typeName}',
    TypeNamePlural : '{i18n>Ideas.headerInfo.typeNamePlural}',
    Title          : {
      $Type : 'UI.DataField',
      Value : title
    },
    Description    : {
      $Type : 'UI.DataField',
      Value : description
    }
  },
  UI.SelectionFields     : [
    title,
    description,
    rating
  ],
  UI.LineItem            : [
    {
      $Type             : 'UI.DataField',
      Value             : title,
      ![@UI.Importance] : #High
    },
    {
      $Type             : 'UI.DataField',
      Value             : description,
      ![@UI.Importance] : #Medium
    },
    {
      $Type             : 'UI.DataField',
      Value             : rating,
      ![@UI.Importance] : #Medium
    },
    {
      $Type             : 'UI.DataField',
      Value             : createdAt,
      ![@UI.Importance] : #Low
    },
    {
      $Type             : 'UI.DataField',
      Value             : createdBy,
      ![@UI.Importance] : #Low
    },
    {
      $Type             : 'UI.DataField',
      Value             : modifiedAt,
      ![@UI.Importance] : #Low
    },
    {
      $Type             : 'UI.DataField',
      Value             : modifiedBy,
      ![@UI.Importance] : #Low
    }
  ],
  UI.Identification      : [
    {
      $Type : 'UI.DataField',
      Value : title,
    },
    {
      $Type : 'UI.DataField',
      Value : description,
    },
  ],
  UI.FieldGroup #Managed : {Data : [
    {Value : createdBy},
    {Value : createdAt},
    {Value : modifiedBy},
    {Value : modifiedAt}
  ]},
  UI.Facets              : [{
    ID     : 'managed',
    $Type  : 'UI.ReferenceFacet',
    Label  : '{i18n>Ideas.facet.managed}',
    Target : '@UI.FieldGroup#Managed'
  }]
) {
  @UI.Hidden
  ID;

  @title : '{i18n>Ideas.element.title}'
  title;

  @title : '{i18n>Ideas.element.rating}'
  rating;

  @title : '{i18n>Ideas.element.description}'
  @UI.MultiLineText
  description;
}

• this config

  • .cdsrc.json:

{
  "features": {
    "fiori_preview": true
  },
  "requires": {
    "db": {
      "kind": "postgres"
    },
    "postgres": {
      "impl": "cds-pg",
      "model": [
        "app"
      ]
    },
    "cap-ratings": {
      "kind": "odata",
      "credentials": {
        "path": "/ratings",
        "requestTimeout": 30000
      }
    }
  },
  "migrations": {
    "db": {
      "schema": {
        "default": "public",
        "clone": "_cdsdbm_clone",
        "reference": "_cdsdbm_ref"
      },
      "deploy": {
        "tmpFile": "tmp/_autodeploy.json",
        "undeployFile": "db/undeploy.json"
      }
    }
  }
}

• default-env.json

{
  "VCAP_SERVICES": {
    "postgres": [
      {
        "name": "postgres",
        "label": "postgres",
        "tags": [
          "postgres"
        ],
        "credentials": {
          "host": "postgres-svc",
          "port": "5432",
          "user": "***",
          "password": "***",
          "database": "cap",
          "schema": "public"
        }
      }
    ],
    "cap-ratings": [{
      "name": "cap-ratings",
      "label": "cap-ratings",
      "tags": [
        "cap-ratings"
      ],
      "credentials": {
        "url": "http://cap-ratings-svc:5005"
      }
    }]
  },
  "DEBUG": "ideas-service"
}

I am running npx cds-dbm deploy to deploy the schema to the database.

Now when running the fiori app preview and creating a new entity I am receiving an error:

[cds] - POST /ideas/$batch
[cds] - > NEW Ideas 
[cds] - error: column "rating" does not exist
    at Parser.parseErrorMessage (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/workspaces/cap-ideas/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:290:12)
    at readableAddChunk (internal/streams/readable.js:265:9)
    at Socket.Readable.push (internal/streams/readable.js:204:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    at TCP.callbackTrampoline (internal/async_hooks.js:131:17) {
  length: 107,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: '176',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3504',
  routine: 'errorMissingColumn',
  id: '1515520',
  level: 'ERROR',
  timestamp: 1632136335162
}

Any ideas what could be the root cause and why the service tries to persist the virtual column to the database throwing an error?
Running the service with sqlite everything works as expected.

cds -v

node ➜ /workspaces/cap-ideas $ cds -v
@sap/cds: 5.3.2
@sap/cds-compiler: 2.5.2
@sap/cds-dk: 4.4.2
@sap/cds-foss: 2.3.1
@sap/cds-odata-v2-adapter-proxy: 1.7.11
Node.js: v14.17.6
cap-ideas: 0.1.0

Thanks in advance,

Pascal

[vobu]

hi and thanks for the detailed report!
so far, we have neither specifically looked at virtual fields, nor provided any custom postgres-specific, implementation.
so your issue might either be

• a bug in cds^5.3 itself or
• a bug in cds-dbm

to further investigate, could you try hooking up sqlite locally and see whether the issue remains?

[pwasem]

@vobu thx for the quick reply!

With sqlite it works just fine:

cds deploy --to sqlite:my.db

.cdsrc.json

{
  "features": {
    "fiori_preview": true
  },
  "requires": {
    "db": {
      "kind": "sqlite",
      "model": "*",
      "credentials": {
        "database": "my.db"
      }
    },
    "cap-ratings": {
      "kind": "odata",
      "credentials": {
        "path": "/ratings",
        "requestTimeout": 30000
      }
    }
  }
}

[cds] - POST /ideas/$batch
[cds] - > NEW Ideas 
[cds] - POST /ideas/$batch
[cds] - > READ Ideas(ID=9dbbf96f-4d80-4aea-a639-5ddf88075cd5,IsActiveEntity=false) {
  '$select': 'HasActiveEntity,HasDraftEntity,ID,IsActiveEntity,createdAt,createdBy,description,modifiedAt,modifiedBy,title',
  '$expand': 'DraftAdministrativeData($select=DraftIsProcessedByMe,DraftUUID,InProcessByUser)'
}
[ideas-service] - after READ Ideas {
  ID: '9dbbf96f-4d80-4aea-a639-5ddf88075cd5',
  createdAt: '2021-09-20T13:41:35.196Z',
  createdBy: 'anonymous',
  description: null,
  modifiedAt: '2021-09-20T13:41:35.196Z',
  modifiedBy: 'anonymous',
  title: null,
  DraftAdministrativeData: {
    DraftUUID: '836165f5-f2f8-4997-abcb-8e58d78aff26',
    DraftIsProcessedByMe: true,
    InProcessByUser: 'anonymous'
  },
  IsActiveEntity: false,
  HasActiveEntity: false,
  HasDraftEntity: false
}

[vobu]

With sqlite it works just fine:

ok. so let's narrow it further down by deploying to postgres w/o cds-dbm:
npx cds-pg deploy srv --to postgres
note: this will erase all exiting data in the postgres db and re-create the db schema based on the cds definitions.

do things work with the cds-pg-native deploy method?

[pwasem]

@vobu

Same issue.

I have ran

node ➜ /workspaces/cap-ideas $ npx cds-pg deploy app --to postgres
/> successfully deployed to ./cap

Tables get created without the virtual rating column

The issue comes up at runtime only:

node ➜ /workspaces/cap-ideas $ npm run watch

> cap-ideas@0.1.0 watch /workspaces/cap-ideas
> cds watch

 
cds serve all --with-mocks --in-memory? 
watching: cds,csn,csv,ts,mjs,cjs,js,json,properties,edmx,xml,env,css,gif,html,jpg,png,svg... 
live reload enabled for browsers 

[cds] - model loaded from 5 file(s):

  ./db/schema.cds
  ./srv/ideas-service.cds
  ./app/index.cds
  ./node_modules/@sap/cds/common.cds
  ./app/ideas/fiori-service.cds

[cds] - connect using bindings from: { registry: '~/.cds-services.json' }
[cds] - connect to db > postgres {
  host: 'postgres-svc',
  port: '5432',
  user: 'slonik',
  password: '...',
  database: 'cap',
  schema: 'public'
}
[cds] - serving IdeasService { at: '/ideas', impl: './srv/ideas-service.js' }

[cds] - launched in: 5.770s
[cds] - server loaded from { file: './srv/server.js' }
[cds] - server listening on { url: 'http://localhost:4004' }
[ terminate with ^C ]

[cds] - GET /$fiori-preview/IdeasService/Ideas?sap-ui-theme=sap_fiori_3_dark&sap-language=EN
[cds] - GET /appconfig/fioriSandboxConfig.json
[cds] - GET /$fiori-preview/IdeasService/Ideas/app/Component-preload.js
[cds] - GET /$fiori-preview/IdeasService/Ideas/app/Component-preload.js
[cds] - GET /$fiori-preview/IdeasService/Ideas/app/Component.js
[cds] - HEAD /ideas/
[cds] - GET /ideas/$metadata
[cds] - GET /sap/bc/lrep/flex/data/preview.Component?sap-language=EN
[cds] - GET /sap/bc/lrep/flex/settings
[cds] - POST /ideas/$batch
[cds] - > READ Ideas {
  '$count': 'true',
  '$filter': '(IsActiveEntity eq false or SiblingEntity/IsActiveEntity eq null)',
  '$select': 'HasActiveEntity,ID,IsActiveEntity,createdAt,createdBy,description,modifiedAt,modifiedBy,rating,title',
  '$skip': '0',
  '$top': '30'
}
[cds] - GET /sap/opu/odata/sap/ESH_SEARCH_SRV/ServerInfos?$expand=Services/Capabilities
[ideas-service] - after READ Ideas []
[cds] - GET /sap/es/ina/GetServerInfo?_=1632218866265
[cds] - POST /ideas/$batch
[cds] - > NEW Ideas 
[cds] - error: column "rating" does not exist
    at Parser.parseErrorMessage (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/workspaces/cap-ideas/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:290:12)
    at readableAddChunk (internal/streams/readable.js:265:9)
    at Socket.Readable.push (internal/streams/readable.js:204:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    at TCP.callbackTrampoline (internal/async_hooks.js:131:17) {
  length: 107,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: '176',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3504',
  routine: 'errorMissingColumn',
  id: '1132172',
  level: 'ERROR',
  timestamp: 1632218869595
}

The $batch request being sent when pressing the Create button in the fiori preview app does also not contain any payload:

--batch_id-1632219122309-203
Content-Type:application/http
Content-Transfer-Encoding:binary

POST Ideas HTTP/1.1
Accept:application/json;odata.metadata=minimal;IEEE754Compatible=true
Accept-Language:en
Content-Type:application/json;charset=UTF-8;IEEE754Compatible=true

{}
--batch_id-1632219122309-203--
Group ID: $auto

Response:

--batch_id-1632219122309-203
content-type: application/http
content-transfer-encoding: binary

HTTP/1.1 500 Internal Server Error
odata-version: 4.0
content-type: application/json;odata.metadata=minimal;IEEE754Compatible=true

{"error":{"code":"42703","message":"column \"rating\" does not exist"}}
--batch_id-1632219122309-203--

So it really looks like the service tries to create a new table row including the non existing virtual column.

The stack trace hints pg-protocol
So we would need to check where this module is being used.

Bests,

Pascal

[pwasem]

@vobu

After enabling debug logs for cds-pg the root cause becomes clear, it comes from the SELECT statement to retrieve the ID of the newly created entry after the actual insert:

[cds] - POST /ideas/$batch
[cds] - > NEW Ideas 
[cds-pg] - sql >  INSERT INTO DRAFT_DraftAdministrativeData ( DraftUUID, CreationDateTime, CreatedByUser, LastChangeDateTime, LastChangedByUser, DraftIsCreatedByMe, DraftIsProcessedByMe, InProcessByUser ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8 ) Returning *
[cds-pg] - values >  [
  'f80d4376-6d2c-4dc0-8dda-9e6964977df8',
  '2021-09-21T13:32:15.670Z',
  'anonymous',
  '2021-09-21T13:32:15.670Z',
  'anonymous',
  true,
  true,
  'anonymous'
]
[cds-pg] - sql >  INSERT INTO IdeasService_Ideas_drafts ( ID, DraftAdministrativeData_DraftUUID, IsActiveEntity, HasDraftEntity, HasActiveEntity, createdAt, createdBy, modifiedAt, modifiedBy ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9 ) Returning *
[cds-pg] - values >  [
  'f7378d6f-d8d7-478e-a366-45aabe71cb41',
  'f80d4376-6d2c-4dc0-8dda-9e6964977df8',
  false,
  false,
  false,
  'NOW ()',
  'anonymous',
  'NOW ()',
  'anonymous'
]
[cds-pg] - sql >  SELECT ID AS "ID", createdAt AS "createdAt", createdBy AS "createdBy", modifiedAt AS "modifiedAt", modifiedBy AS "modifiedBy", title AS "title", description AS "description", rating AS "rating", IsActiveEntity AS "IsActiveEntity", HasActiveEntity AS "HasActiveEntity", HasDraftEntity AS "HasDraftEntity", DraftAdministrativeData_DraftUUID AS "DraftAdministrativeData_DraftUUID" FROM IdeasService_Ideas_drafts ALIAS_1 WHERE ID = $1
[cds-pg] - values >  [ 'f7378d6f-d8d7-478e-a366-45aabe71cb41' ]
[cds] - error: column "rating" does not exist
    at Parser.parseErrorMessage (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/workspaces/cap-ideas/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:290:12)
    at readableAddChunk (internal/streams/readable.js:265:9)
    at Socket.Readable.push (internal/streams/readable.js:204:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    at TCP.callbackTrampoline (internal/async_hooks.js:131:17) {
  length: 107,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: '176',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3504',
  routine: 'errorMissingColumn',
  id: '1426341',
  level: 'ERROR',
  timestamp: 1632231135728
}

[vobu]

thanks again for that detailed analysis @pwasem!
regarding the virtual fields handling in odata draft mode...that has entirely changed in cds^5.4 - @sjvans, help, guidance, please! 😺
(wondering whether a this.before('READ', '*', this._virtual) in lib/pg/Service.js's init() would already do...)

[vobu]

@sjvans: prepped a feature branch, see https://github.com/vobu/cds-pg/blob/b179c3e84e4f2ffb1f1942ce0b014569c62a20c3/lib/pg/Service.js#L94

[pwasem]

@vobu @sjvans
Even with the this.before('READ', '*', this._virtual) handler I am getting the same error:

[cds] - POST /ideas/$batch
[cds] - > NEW Ideas 
[cds-pg] - sql >  INSERT INTO DRAFT_DraftAdministrativeData ( DraftUUID, CreationDateTime, CreatedByUser, LastChangeDateTime, LastChangedByUser, DraftIsCreatedByMe, DraftIsProcessedByMe, InProcessByUser ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8 ) Returning *
[cds-pg] - values >  [
  '597fdede-cb58-46f2-97c1-c2e357592931',
  '2021-09-23T07:58:36.041Z',
  'anonymous',
  '2021-09-23T07:58:36.041Z',
  'anonymous',
  true,
  true,
  'anonymous'
]
[cds-pg] - sql >  INSERT INTO IdeasService_Ideas_drafts ( ID, DraftAdministrativeData_DraftUUID, IsActiveEntity, HasDraftEntity, HasActiveEntity, createdAt, createdBy, modifiedAt, modifiedBy ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9 ) Returning *
[cds-pg] - values >  [
  '07b756d6-2b73-45ed-844e-1f92b3888289',
  '597fdede-cb58-46f2-97c1-c2e357592931',
  false,
  false,
  false,
  'NOW ()',
  'anonymous',
  'NOW ()',
  'anonymous'
]
[cds-pg] - sql >  SELECT ID AS "ID", createdAt AS "createdAt", createdBy AS "createdBy", modifiedAt AS "modifiedAt", modifiedBy AS "modifiedBy", title AS "title", description AS "description", rating AS "rating", IsActiveEntity AS "IsActiveEntity", HasActiveEntity AS "HasActiveEntity", HasDraftEntity AS "HasDraftEntity", DraftAdministrativeData_DraftUUID AS "DraftAdministrativeData_DraftUUID" FROM IdeasService_Ideas_drafts ALIAS_1 WHERE ID = $1
[cds-pg] - values >  [ '07b756d6-2b73-45ed-844e-1f92b3888289' ]
[cds] - error: column "rating" does not exist
    at Parser.parseErrorMessage (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/workspaces/cap-ideas/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:290:12)
    at readableAddChunk (internal/streams/readable.js:265:9)
    at Socket.Readable.push (internal/streams/readable.js:204:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    at TCP.callbackTrampoline (internal/async_hooks.js:131:17) {
  length: 107,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: '176',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3504',
  routine: 'errorMissingColumn',
  id: '1825034',
  level: 'ERROR',
  timestamp: 1632383916083
}

There is still an issue with the select statement to retrieve the ID of the newly created row:

[cds-pg] - sql >  SELECT ID AS "ID", createdAt AS "createdAt", createdBy AS "createdBy", modifiedAt AS "modifiedAt", modifiedBy AS "modifiedBy", title AS "title", description AS "description", rating AS "rating", IsActiveEntity AS "IsActiveEntity", HasActiveEntity AS "HasActiveEntity", HasDraftEntity AS "HasDraftEntity", DraftAdministrativeData_DraftUUID AS "DraftAdministrativeData_DraftUUID" FROM IdeasService_Ideas_drafts ALIAS_1 WHERE ID = $1
[cds-pg] - values >  [ '07b756d6-2b73-45ed-844e-1f92b3888289' ]

[sjvans]

thanks again for that detailed analysis @pwasem!
regarding the virtual fields handling in odata draft mode...that has entirely changed in cds^5.4 - @sjvans, help, guidance, please! 😺
(wondering whether a this.before('READ', '*', this._virtual) in lib/pg/Service.js's init() would already do...)

not sure what has "changed entirely" in 5.4, but it will do so in 5.5 (eta next week). there, we will always select null as <virtual property> bc, in part, too many stakeholders have issues with the ui wanting to apply filters, et al to virtual properties and the runtime needs to sanitize deep and complex wheres, orderbys, etc. hence, i'd recommend to wait until 5.5 was released.

[vobu]

thanks again for that detailed analysis @pwasem!
regarding the virtual fields handling in odata draft mode...that has entirely changed in cds^5.4 - @sjvans, help, guidance, please! 😺
(wondering whether a this.before('READ', '*', this._virtual) in lib/pg/Service.js's init() would already do...)

not sure what has "changed entirely" in 5.4, but it will do so in 5.5 (eta next week). there, we will always select null as <virtual property> bc, in part, too many stakeholders have issues with the ui wanting to apply filters, et al to virtual properties and the runtime needs to sanitize deep and complex wheres, orderbys, etc. hence, i'd recommend to wait until 5.5 was released.

right on, i'll see that i can whip up a virtual field test case this week we can run the ^5.5 against