sequenceExists became case sensitive from 4.24 on

[davidecavestro]

Search first

• I searched and no similar issues were found

Description

We were used to create sequences using uppercase name on changesets, as this is automatically transalted to lowercase for postgres. After switching Liquibase from 4.11 to 4.27 we noticed it fails to recognize existing sequences

Steps To Reproduce

Given a docker compose file creating a postgres db on ephemeral storage, and launching liquibase on it as follows

compose.yml:

version: '3.8'

services:
  postgres:
    image: postgres:16.2
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: my_database
      POSTGRES_USER: my_user
      POSTGRES_PASSWORD: my_password

  liquibase:
    image: liquibase/liquibase:4.27 # this fails
#    image: liquibase/liquibase:4.23 # this works
    depends_on:
      - postgres
    volumes:
      - ./changelog.xml:/liquibase/changelog.xml
    command: ["--changeLogFile=changelog.xml", "update"]
    environment:
      - LIQUIBASE_COMMAND_URL=jdbc:postgresql://postgres:5432/my_database
      - LIQUIBASE_COMMAND_USERNAME=my_user
      - LIQUIBASE_COMMAND_PASSWORD=my_password

if the changeset creates a sequence having uppercase name and afterwards tests for its existence using the same uppercase name, it fails, as the sequence is by default translated to its lowercase counterpart by the db engine.

This fails with liquibase versions from 4.24 to 4.27, while with 4.23 it works

changelog.xml:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd">

	<changeSet author="me" id="test-foo">
		<!-- create a new sequence --> 
		<createSequence sequenceName="SQ_TEST_FOO"/>
	</changeSet>

	<changeSet author="me" id="test-bar">
		<preConditions onFail="HALT">
			<!-- fail if the just created sequence doesn't exist --> 
			<sequenceExists sequenceName="SQ_TEST_FOO"/>
		</preConditions>

		<createSequence sequenceName="SQ_TEST_BAR"/><!-- doesn't matter -->
	</changeSet>

</databaseChangeLog>

Expected/Desired Behavior

The sequence created by liquibase should be found by the precondition with default settings, otherwise there's a breaking change preventing us to upgrade liquibase.

Liquibase Version

4.27

Database Vendor & Version

PostgreSQL 16.2

Liquibase Integration

CLI

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

official docker image

Additional Context

It also sworks on Liquibase 4.11.
It probably fails also for Oracle, using lowercase names (not tested yet).
This case sensitivity issue possibly affects other checks, though we've not tested yet.

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[tati-qalified]

Hi @davidecavestro, thank you for reporting this issue.

I have been able to replicate the bug. The source of this seems to be that when creating the sequence, its name is automatically set to lowercase, but the precondition query doesn't do that.

A workaround is using the lowercase name in your preconditions.

We'll be leaving this issue open for the community to propose a fix for. Our develompent team will be available to provide guidance if necessary.

Thank you,
Tatiana

[davidecavestro]

Please note it seems a regression, as it used to work with previous versions, and it possibly affects checks on other object types. That said, I can dissect history to find where and why the behavior changed, so that we understand which fix makes sense.

[davidecavestro]

@tati-qalified after a quick check, I suppose the regression has been inadvertedly introduced with PR #4613, and specifically with the SQL query introduced as constant SQL_CHECK_POSTGRES_SEQUENCE_EXISTS
where the check for PostgreSQL switched to case-sensitive.

[tati-qalified]

@davidecavestro would you be willing to propose a fix and submit a PR?