Parser Stack Overflow

[AdamHeavens]

I am getting the above error when trying to create a trigger, this only seems to be the case with complex queries. For example

CREATE TRIGGER delete_oldest_logs AFTER INSERT ON Logs WHEN (SELECT COUNT(*) FROM Logs) > 100000 BEGIN DELETE FROM Logs WHERE id = (SELECT id FROM Logs ORDER BY timestamp LIMIT 1); END; E (2126) LogDatabase: Trigger query preparation failed: parser stack overflow

The above query results in a parser stack overflow, All other queries are working correctly, include creating the table

		void LogDatabase::initDatabaseTable()
		{
			// Create database
			auto query = m_database.query("CREATE TABLE IF NOT EXISTS Logs ("
										  "[id]    INTEGER,"
										  "[timestamp] BIGINT,"
										  "[level] INT UNSIGNED,"
										  "[file] VARCHAR(64),"
										  "[message] VARCHAR(1024),"
										  "PRIMARY KEY([id] AUTOINCREMENT));");
			if (query)
			{
				if (query->exec())
				{
					ESP_LOGI(TAG, "Logs table created successfully or already exists");
				}
				else
				{
					ESP_LOGE(TAG, "Could not create Logs table : %s", query->lastError().c_str());
				}
			}
			else
			{
				ESP_LOGE(TAG, "Query preparation failed");
				return;
			}

			std::string trigger_sql =
				"CREATE TRIGGER delete_oldest_logs "
                "AFTER INSERT ON Logs "
                "WHEN (SELECT COUNT(*) FROM Logs) > 100000 "
                "BEGIN "
                "DELETE FROM Logs WHERE id = (SELECT id FROM Logs ORDER BY timestamp LIMIT 1); "
                "END;";

			ESP_LOGI(TAG, "Trigger SQL: %s", trigger_sql.c_str()); // Log the SQL query
			query->reset();
			query = m_database.query(trigger_sql);
			
			if (query)
			{
				if (query->exec())
				{
					ESP_LOGI(TAG, "Trigger created successfully or already exists");
				}
				else
				{
					ESP_LOGE(TAG, "Could not create Trigger : %s", query->lastError().c_str());
				}
			}
			else
			{
				ESP_LOGE(TAG, "Trigger query preparation failed: %s", m_database.lastError().c_str());
			}

			// Create parametrized queries
			m_insert_query = m_database.query("INSERT INTO Logs VALUES (NULL, ?, ?, ?, ?);");
			if (!m_insert_query)
			{
				ESP_LOGE(TAG, "Could not prepare insert query: %s", m_insert_query->lastError().c_str());
			}
		}

I have tried also with the later version of SQLite from the PR (Upgrade to SQLite 3.46.0)

Any help appreciated!

[siara-cc]

@AdamHeavens please see this: https://github.com/siara-cc/esp32_arduino_sqlite3_lib/issues/94 I have kept it pending because I am not sure if increasing YYSTACKDEPTH is the right solution.

[AdamHeavens]

Thank you, I have changed YYSTACKDEPTH to 30 and reverting to version 3.25 has resolved the issue