Unable to search in a specific table due to field renaming : Unexpected error: function lower(boolean) does not exist

ChloeG · October 18, 2022, 11:01pm

Feature(s) impacted

Hi,

I have an issue while searching in a specific table, in which I’ve had to include field renaming. In more details:

My table is defined as follow:

  const ShopItem = sequelize.define('shopItem', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: Sequelize.literal('uuid_generate_v4()'),
      allowNull: false,
    },
    price: {
      type: DataTypes.DOUBLE,
      allowNull: false,
    },
    category: {
      type: DataTypes.ENUM(
        'discount',
        'print',
        'goodies',
        'undefined',
      ),
      field: 'category',
      defaultValue: "undefined",
      allowNull: false,
    },
    couponLink: {
      type: DataTypes.STRING,
    },
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: Sequelize.literal('now()'),
    },
    picture1: {
      type: DataTypes.STRING,
      field: 'picture_1',
      allowNull: false,
    },
    picture1ld: {
      type: DataTypes.STRING,
      field: 'picture_1_ld',
    },
    picture2: {
      type: DataTypes.STRING,
      field: 'picture_2',
    },
    picture2ld: {
      type: DataTypes.STRING,
      field: 'picture_2_ld',
    },
    picture3: {
      type: DataTypes.STRING,
      field: 'picture_3',
    },
    picture3ld: {
      type: DataTypes.STRING,
      field: 'picture_3_ld',
    },
    quantity: {
      type: DataTypes.INTEGER,
    },
    descriptionFr: {
      type: DataTypes.STRING,
    },
    descriptionEs: {
      type: DataTypes.STRING,
    },
    descriptionEn: {
      type: DataTypes.STRING,
    },
    howToUseFr: {
      type: DataTypes.STRING,
    },
    howToUseEs: {
      type: DataTypes.STRING,
    },
    howToUseEn: {
      type: DataTypes.STRING,
    },
    tag: {
      type: DataTypes.STRING,
      field: 'testTag',
    },
    isValidated: {
      type: DataTypes.BOOLEAN,
      field: 'validate',
      defaultValue: false,
    },
  }, {
    tableName: 'shop_item',
    underscored: true,
    timestamps: false,
    schema: process.env.DATABASE_SCHEMA,
  });

As you can see, the last two columns, “validate” and “testTag” has been renamed in respectively “isValidated” and “tag” to avoid previous issues I had:

I renamed “validate” to avoid interaction with Sequelize built-in method
I renamed “testTag” cause Forest will look for “test_tag” in DB instead of testTag

Anyway, it sounds to me that this renaming has unexpected side effects when searching within the table in certain cases.

Observed behavior

When searching within this table, I’ve got this error :

Executing (default): SELECT "id", "price", "category", "coupon_link" AS "couponLink", "created_at" AS "createdAt", "picture_1" AS "picture1", "picture_1_ld" AS "picture1ld", "picture_2" AS "picture2", "picture_2_ld" AS "picture2ld", "picture_3" AS "picture3", "picture_3_ld" AS "picture3ld", "quantity", "description_fr" AS "descriptionFr", "description_es" AS "descriptionEs", "description_en" AS "descriptionEn", "how_to_use_fr" AS "howToUseFr", "how_to_use_es" AS "howToUseEs", "how_to_use_en" AS "howToUseEn", "testTag" AS "tag", "validate" AS "isValidated", "owner_id" AS "ownerIdKey" FROM "shop_item" AS "shopItem" WHERE (lower("shopItem"."coupon_link")  LIKE  lower('%s%') OR lower("shopItem"."picture_1")  LIKE  lower('%s%') OR lower("shopItem"."picture_1_ld")  LIKE  lower('%s%') OR lower("shopItem"."picture_2")  LIKE  lower('%s%') OR lower("shopItem"."picture_2_ld")  LIKE  lower('%s%') OR lower("shopItem"."picture_3")  LIKE  lower('%s%') OR lower("shopItem"."picture_3_ld")  LIKE  lower('%s%') OR lower("shopItem"."description_fr")  LIKE  lower('%s%') OR lower("shopItem"."description_es")  LIKE  lower('%s%') OR lower("shopItem"."description_en")  LIKE  lower('%s%') OR lower("shopItem"."how_to_use_fr")  LIKE  lower('%s%') OR lower("shopItem"."how_to_use_es")  LIKE  lower('%s%') OR lower("shopItem"."how_to_use_en")  LIKE  lower('%s%') OR lower("shopItem"."testTag")  LIKE  lower('%s%')) LIMIT 10 OFFSET 0;
[forest] 🌳🌳🌳  Unexpected error: function lower(boolean) does not exist
{
  "name": "SequelizeDatabaseError",
  "parent": {
    "length": 209,
    "name": "error",
    "severity": "ERROR",
    "code": "42883",
    "hint": "No function matches the given name and argument types. You might need to add explicit type casts.",
    "position": "1335",
    "file": "parse_func.c",
    "line": "627",
    "routine": "ParseFuncOrColumn",
    "sql": "SELECT \"id\", \"price\", \"category\", \"coupon_link\" AS \"couponLink\", \"created_at\" AS \"createdAt\", \"picture_1\" AS \"picture1\", \"picture_1_ld\" AS \"picture1ld\", \"picture_2\" AS \"picture2\", \"picture_2_ld\" AS \"picture2ld\", \"picture_3\" AS \"picture3\", \"picture_3_ld\" AS \"picture3ld\", \"quantity\", \"description_fr\" AS \"descriptionFr\", \"description_es\" AS \"descriptionEs\", \"description_en\" AS \"descriptionEn\", \"how_to_use_fr\" AS \"howToUseFr\", \"how_to_use_es\" AS \"howToUseEs\", \"how_to_use_en\" AS \"howToUseEn\", \"testTag\" AS \"tag\", \"validate\" AS \"isValidated\", \"owner_id\" AS \"ownerIdKey\" FROM \"shop_item\" AS \"shopItem\" WHERE (lower(\"shopItem\".\"coupon_link\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_1\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_1_ld\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_2\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_2_ld\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_3\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_3_ld\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"description_fr\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"description_es\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"description_en\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"how_to_use_fr\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"how_to_use_es\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"how_to_use_en\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"testTag\")  LIKE  lower('%s%')) LIMIT 10 OFFSET 0;"
  },
  "original": {
    "length": 209,
    "name": "error",
    "severity": "ERROR",
    "code": "42883",
    "hint": "No function matches the given name and argument types. You might need to add explicit type casts.",
    "position": "1335",
    "file": "parse_func.c",
    "line": "627",
    "routine": "ParseFuncOrColumn",
    "sql": "SELECT \"id\", \"price\", \"category\", \"coupon_link\" AS \"couponLink\", \"created_at\" AS \"createdAt\", \"picture_1\" AS \"picture1\", \"picture_1_ld\" AS \"picture1ld\", \"picture_2\" AS \"picture2\", \"picture_2_ld\" AS \"picture2ld\", \"picture_3\" AS \"picture3\", \"picture_3_ld\" AS \"picture3ld\", \"quantity\", \"description_fr\" AS \"descriptionFr\", \"description_es\" AS \"descriptionEs\", \"description_en\" AS \"descriptionEn\", \"how_to_use_fr\" AS \"howToUseFr\", \"how_to_use_es\" AS \"howToUseEs\", \"how_to_use_en\" AS \"howToUseEn\", \"testTag\" AS \"tag\", \"validate\" AS \"isValidated\", \"owner_id\" AS \"ownerIdKey\" FROM \"shop_item\" AS \"shopItem\" WHERE (lower(\"shopItem\".\"coupon_link\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_1\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_1_ld\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_2\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_2_ld\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_3\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_3_ld\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"description_fr\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"description_es\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"description_en\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"how_to_use_fr\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"how_to_use_es\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"how_to_use_en\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"testTag\")  LIKE  lower('%s%')) LIMIT 10 OFFSET 0;"
  },
  "sql": "SELECT \"id\", \"price\", \"category\", \"coupon_link\" AS \"couponLink\", \"created_at\" AS \"createdAt\", \"picture_1\" AS \"picture1\", \"picture_1_ld\" AS \"picture1ld\", \"picture_2\" AS \"picture2\", \"picture_2_ld\" AS \"picture2ld\", \"picture_3\" AS \"picture3\", \"picture_3_ld\" AS \"picture3ld\", \"quantity\", \"description_fr\" AS \"descriptionFr\", \"description_es\" AS \"descriptionEs\", \"description_en\" AS \"descriptionEn\", \"how_to_use_fr\" AS \"howToUseFr\", \"how_to_use_es\" AS \"howToUseEs\", \"how_to_use_en\" AS \"howToUseEn\", \"testTag\" AS \"tag\", \"validate\" AS \"isValidated\", \"owner_id\" AS \"ownerIdKey\" FROM \"shop_item\" AS \"shopItem\" WHERE (lower(\"shopItem\".\"coupon_link\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_1\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_1_ld\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_2\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_2_ld\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_3\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"picture_3_ld\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"description_fr\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"description_es\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"description_en\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"how_to_use_fr\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"how_to_use_es\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"how_to_use_en\")  LIKE  lower('%s%') OR lower(\"shopItem\".\"testTag\")  LIKE  lower('%s%')) LIMIT 10 OFFSET 0;",
  "stack": "SequelizeDatabaseError: function lower(boolean) does not exist\n    at Query.formatError

However, if I remove these two alias fields “isValidated” and “tag” from my schema, I no longer have any error and everything is fine.

Do you think that the alias could induced unexpected behavior in SQL request? I’ve also have this issue with another table having an alias, while I don’t have any issues when tables does not have any alias.

Thanks a lot for your feedback.

Have a nice day,
C.

Context

Project name: BavAR[t]
Team name: Operation
Environment name: Dev & production
Agent type & version: …
Recent changes made on your end if any: …

anon16419211 · October 19, 2022, 9:07am

Hello @ChloeG,

I might have some questions to better understand your issuer:

Did you also change the name in the database or only in the model schema ?
What is your forest-express version ?

Looking at the query it seems that all your attributes on the shop item table are defined using snake_case while your last one tag is defined using camelCase ?

Can you try to replace:

tag: {
      type: DataTypes.STRING,
      field: 'testTag',
    },

with this:

tag: {
      type: DataTypes.STRING,
      field: 'test_tag',
    },

Kind regards,

ChloeG · October 19, 2022, 9:28am

Hi,
As as explained, I’ve just introduced an alias in Forest only, to not have interaction with the built-in function.

I didn’t change anything in my DB. And in my DB, my attributes names are respectively “validate” and “testTag”. So of course, If I do the suggested change it doesn’t work. Yes, for this attribute I didn’t use the snake case, but the camelCase (I forgot), but that’s absolutely not the issue here.

The issue is :

When I remove this two attributes from the Forest Schema, it works, while when I use my two attributes having alias, I have the error described in my previous message.

Thanks a lot,
Chloé

ChloeG · October 19, 2022, 9:31am

I forgot to reply to your version request: I am using the forest express 9.1.1 version.
Thanks !
C.

anon16419211 · October 19, 2022, 10:27am

Thanks for the clarification, indeed the behavior is totally fine as long as you added the underscored: true options to your sequelize model. Here is the explanation

Sorry if I misunderstood your issue at a first glance but in my opinion your alias field should work.

The error raised by forest is about a lower function applied on a field that cannot be executed:

Unexpected error: function lower(boolean) does not exist

Knowing that removing your two fields with alias from your forest schema makes the search work again I’m wondering if the issue is more about your isValidated field.

I’m looking on my side and will get back to you asap once I have a new idea.

Thanks,
Louis

ChloeG · October 19, 2022, 10:50am

Thanks!

Apart from this specific search for which there is a bug, my aliases does work. I can see correct values from my DB, everything else is fine, it is only the search that gives me an error.

Nope, at first glance I thought too it was because of my boolean IsValidated alias too.
But I’ve tried removing it, and letting tag only, but I still have the same issue. Removing both tag and validated works perfectly.

This is why I think that this is the alias that really induces the problem here!

FYI, I have this problem while searching the shop_item table from another Table, where the shop_item belongs to.

Thanks for checking the issue,
C.

vince · October 20, 2022, 8:06am

Hi @ChloeG,

I’m pretty sure you issue is that testTag is of type boolean but you are declaring it as of type string in Sequelize. Could you please confirm or show me your table definition ?

Just so you know, normally this should work:

    testTag: {
      type: DataTypes.STRING,
      field: 'testTag',
    },

ChloeG · October 20, 2022, 8:55pm

Oh my god, such a shame!!!

You’re right! I’m so sorry to have bother you for this!

I think I’ve checked like hundred of times my table definition, and I didn’t notice it… I’ve spent too much time on it, I’m no longer seeing anything!

Thanks a lot to have spent time to check my ““issue””, and sorry again for this mistake!

Have a nice one,
C.

vince · October 21, 2022, 7:42am

Ahah no worries . Glad I could help you

Topic		Replies	Views
Search on collection is sending an error - "function lower(uuid) does not exist" Help me!	4	1372	May 27, 2020
Search error on tables with PostGIS fields Help me!	1	340	August 21, 2020
Search on collection is sending an error - sequelize-typescript UUID are interpreted as string Help me! typescript	12	2282	February 3, 2022
Enable to research on a specific table Help me!	13	914	August 31, 2021
Woodshop tutorial break sv7 Help me!	2	340	May 25, 2021

Unable to search in a specific table due to field renaming : Unexpected error: function lower(boolean) does not exist

Feature(s) impacted

Observed behavior

Context

Related topics