Repository: mindsdb/mindsdb

Issue:
[Bug]: Invalid TimeGPT API key
### Short description of current behavior

MindsDB returns an error `AssertionError: Invalid TimeGPT token provided., raised at: /Users/martynaslawinska/mindsdb/./mindsdb/integrations/libs/ml_handler_process/learn_process.py#122` with a newly generated API key from Nixtla TimeGPT.

Steps:

1. Created an ML engine, providing a newly generated TimeGPT API key:
```
CREATE ML_ENGINE timegpt_new_api_key
FROM timegpt
USING
    timegpt_api_key = 'nixak-xxx';
```
2. Connected a data source:
```
CREATE DATABASE my_binance
WITH
  ENGINE = 'binance';
```
3. Created a model, which resulted in the model status being `error`:
```
CREATE MODEL cryptocurrency_forecast_model
FROM my_binance
  (
    SELECT *
    FROM aggregated_trade_data
    WHERE symbol = 'BTCUSDT'
  )
PREDICT open_price
ORDER BY open_time
HORIZON 10
USING ENGINE = 'timegpt_new_api_key';
```
4. Described the model and found this error in the `error` column: `AssertionError: Invalid TimeGPT token provided., raised at: /Users/martynaslawinska/mindsdb/./mindsdb/integrations/libs/ml_handler_process/learn_process.py#122`
```
DESCRIBE cryptocurrency_forecast_model;
```

### Video or screenshots

_No response_

### Expected behavior

_No response_

### How to reproduce the error

_No response_

### Anything else?

_No response_

Discussion:
@martyna-mindsdb: @ZoranPandovski 
Please reassign if required.@aliframdani-jbl: Is there any updates from this? @ZoranPandovski: This will be solved by the release tomorrow. For now you can manually upgrade nixtla dependency to latest `pip install --upgrade nixtla`@aliframdani-jbl: @ZoranPandovski thank you for the fix!

Solution patch:
diff --git a/docs/integrations/ai-engines/timegpt.mdx b/docs/integrations/ai-engines/timegpt.mdx
index a8b77f775..4a0471066 100644
--- a/docs/integrations/ai-engines/timegpt.mdx
+++ b/docs/integrations/ai-engines/timegpt.mdx
@@ -112,9 +112,8 @@ CREATE VIEW btcusdt_recent AS (
 This view is going to be joined with the model to get forecasts:
 
 ```sql
-SELECT to_timestamp(cast(m.open_time as bigint)) AS open_time,
-       m.open_price,
-       m.open_price_explain
+SELECT m.open_time ,
+       m.open_price
 FROM btcusdt_recent AS d
 JOIN cryptocurrency_forecast_model AS m
 WHERE d.open_time > LATEST;
diff --git a/mindsdb/integrations/handlers/timegpt_handler/README.md b/mindsdb/integrations/handlers/timegpt_handler/README.md
index 2d615bbf7..4a0471066 100644
--- a/mindsdb/integrations/handlers/timegpt_handler/README.md
+++ b/mindsdb/integrations/handlers/timegpt_handler/README.md
@@ -1,68 +1,120 @@
-# Briefly describe what ML framework does this handler integrate to MindsDB, and how?
-TimeGPT is a zero-shot forecasting model developed by Nixtla, offered through their `nixtla` Python package.
+---
+title: Nixtla's TimeGPT Integration with MindsDB
+sidebarTitle: TimeGPT
+---
 
-This handler provides a simple wrapper around TimeGPT, and provides easy ingestion of time series data from other MindsDB data sources into the TimeGPT API. User requires a Nixtla API key to use this handler.
+TimeGPT by Nixtla is a generative pre-trained model specifically designed for predicting time series data. TimeGPT takes time series data as input and produces forecasted outputs. TimeGPT can be effectively employed in various applications, including demand forecasting, anomaly detection, financial prediction, and more.
 
-Call this handler by `USING ENGINE="timegpt"`.
+You can learn more about its features [here](https://nixtla.github.io/nixtla/).
 
-# Why is this integration useful? What does the ideal predictive use case for this integration look like? When would you definitely not use this integration?
-Zero-shot forecasting models can produce forecasts for novel data without a previous training phase a pre-requisite. However, these models can be finetuned for improved accuracy on any specific domain.
+## How to bring TimeGPT Models to MindsDB
 
-# Are models created with this integration fast and scalable, in general?
-Yes.
+Before creating a model, you will need to create an ML engine for TimeGPT using the `CREATE ML_ENGINE` statement and providing the TimeGPT API key:
 
-# What are the recommended system specifications for models created with this framework?
-N/A - no model training or inference is done on premise.
+```sql
+CREATE ML_ENGINE timegpt
+FROM timegpt
+USING
+    timegpt_api_key = 'timegpt_api_key';
+```
 
-# To what degree can users control the underlying framework by passing parameters via the USING syntax?
-Usual time series related arguments can be passed (forecast "horizon" and what columns to order and group by).
+Once the ML engine is created, we use the `CREATE MODEL` statement to create the TimeGPT model in MindsDB.
 
-Frequency of the series can be manually set, although by default it is automatically inferred from the data.
+```sql
+CREATE MODEL model_name
+FROM data_source
+  (SELECT * FROM table_name)
+PREDICT column_to_be_predicted
+GROUP BY column_name, column_name, ...
+ORDER BY date_column
+HORIZON 3 -- model forecasts the next 3 rows
+USING ENGINE = 'timegpt';
+```
 
-The data frequency can be specified with the "frequency" arg. If no frequency is specified, MindsDB tries to infer this automatically from the dataframe.
+To ensure that the model is created based on the TimeGPT engine, include the `USING` clause at the end, which defines the `engine` and lists all parameters used with time-series models, including `GROUP BY`, `ORDER BY`, `HORIZON`.
 
-The confidence level for the prediction intervals can be specified with the "level" argument. The default value is `90`.
+What's different about the TimeGPT engine is that it does not expose the `WINDOW` parameter in its API, so as a user you need to send a payload with at least N rows, where N depends on the model and the frequency of the series. This is automatically handled by MindsDB in the [TimeGPT handler code](https://github.com/mindsdb/mindsdb/tree/main/mindsdb/integrations/handlers/timegpt_handler).
 
-# Does this integration offer model explainability or insights via the DESCRIBE syntax?
-Minimal, but yes.
+## Example
 
-# Does this integration support fine-tuning pre-existing models (i.e. is the update() method implemented)? Are there any caveats?
-Not yet. This is a planned feature.
+Nixtla's TimeGPT model can be used to obtain real-time forecasts of the trading data from Binance.
 
-# Are there any other noteworthy aspects to this handler?
-No.
+<Tip>
+Follow [this link](https://www.youtube.com/watch?v=8LfpFocdyEo&list=PLq3sJIV6w5BoHJ9gFSedwtb_pqk--4K89&index=3) to watch a video on integrating TimeGPT model with Binance data.
+</Tip>
 
-# Any directions for future work in subsequent versions of the handler?
-Mostly achieving full coverage of all options provided by the TimeGPT API.
+First, connect to Binance from MindsDB executing this command:
 
-# Please provide a minimal SQL example that uses this ML engine (pointers to integration tests in the PR also valid)
 ```sql
 CREATE DATABASE my_binance
-WITH
-  ENGINE = 'binance'
-  PARAMETERS = {};
+WITH ENGINE = 'binance';
+```
+
+Please note that before using the TimeGPT engine, you should create it from the MindsDB editor, or other clients through which you interact with MindsDB, with the below command:
+
+```sql
+CREATE ML_ENGINE timegpt
+FROM timegpt
+USING
+    timegpt_api_key = 'timegpt_api_key';
+```
+
+You can check the available engines with this command:
+
+```sql
+SHOW ML_ENGINES;
+```
+
+If you see the TimeGPT engine on the list, you are ready to follow the tutorials.
+
+Now let's create a TimeGPT model and train it with data from Binance.
+
+```sql
+CREATE MODEL cryptocurrency_forecast_model
+FROM my_binance
+  (
+    SELECT *
+    FROM aggregated_trade_data
+    WHERE symbol = 'BTCUSDT'
+  )
+PREDICT open_price
+ORDER BY open_time
+HORIZON 10
+USING ENGINE = 'timegpt';
+```
+
+Use the `CREATE MODEL` statement to create, train, and deploy a model. The `FROM` clause defines the training data used to train the model - here, the latest Binance data is used. The `PREDICT` clause specifies the column to be predicted - here, the open price of the BTC/USDT trading pair is to be forecasted.
+
+As it is a time-series model, you should order the data by a date column - here, it is the open time when the open price takes effect. Finally, the `HORIZON` clause defines how many rows into the future the model will forecast - here, it forecasts the next 10 rows (the next 10 minutes, as the interval between Binance data rows is one minute).
+
+<Warning>
+Please note that the TimeGPT engine is sensitive to inconsistent intervals between data rows. Please check your data for missing, duplicated or irregular timestamps to mitigate errors that may arise if the intervals between data rows are inconsistent.
 
-CREATE VIEW binance_view (
-  SELECT symbol, open_time, open_price
+In this example, the intervals between Binance data rows are consistently equal to one minute.
+</Warning>
+
+Before proceeding, make sure that the model status reads `complete`.
+
+```sql
+DESCRIBE cryptocurrency_forecast_model;
+```
+
+To make forecasts, you must save the Binance data into a view:
+
+```sql
+CREATE VIEW btcusdt_recent AS (
+  SELECT *
   FROM my_binance.aggregated_trade_data
   WHERE symbol = 'BTCUSDT'
 );
+```
 
-CREATE ML_ENGINE timegpt FROM timegpt;
+This view is going to be joined with the model to get forecasts:
 
-CREATE MODEL mindsdb.timegpt_binance
-FROM mindsdb
-  (SELECT symbol, open_time, open_price FROM binance_view)
-PREDICT open_price
-ORDER BY open_time
-GROUP BY symbol
-HORIZON 15
-USING engine = 'timegpt';
-
-SELECT m.symbol, m.open_time, m.open_price, m.confidence, m.lower, m.upper
-FROM binance_view as t
-JOIN
-timegpt_binance as m
-WHERE
-t.open_time > LATEST;
-```
\ No newline at end of file
+```sql
+SELECT m.open_time ,
+       m.open_price
+FROM btcusdt_recent AS d
+JOIN cryptocurrency_forecast_model AS m
+WHERE d.open_time > LATEST;
+```
diff --git a/mindsdb/integrations/handlers/timegpt_handler/requirements.txt b/mindsdb/integrations/handlers/timegpt_handler/requirements.txt
index a62a7ae49..76e68a614 100644
--- a/mindsdb/integrations/handlers/timegpt_handler/requirements.txt
+++ b/mindsdb/integrations/handlers/timegpt_handler/requirements.txt
@@ -1 +1 @@
-nixtla==0.5.0
+nixtla==0.6.6
diff --git a/mindsdb/utilities/render/sqlalchemy_render.py b/mindsdb/utilities/render/sqlalchemy_render.py
index 7e7179b18..1d06fe0d1 100644
--- a/mindsdb/utilities/render/sqlalchemy_render.py
+++ b/mindsdb/utilities/render/sqlalchemy_render.py
@@ -93,9 +93,6 @@ class SqlalchemyRender:
         if hasattr(dialect, 'preparer'):
             class Preparer(dialect.preparer):
 
-                def __init__(self, *args, **kwargs):
-                    super().__init__(*args, **kwargs)
-
                 def _requires_quotes(self, value: str) -> bool:
                     # check force-quote flag
                     if isinstance(value, AttributedStr):
@@ -242,6 +239,8 @@ class SqlalchemyRender:
 
             op = t.op.lower()
             if op in ('in', 'not in'):
+                if t.args[1].parentheses:
+                    arg1 = [arg1]
                 if isinstance(arg1, sa.sql.selectable.ColumnClause):
                     raise NotImplementedError(f'Required list argument for: {op}')
 
@@ -536,12 +535,19 @@ class SqlalchemyRender:
                 query = query.select_from(table)
 
                 # other tables
+                has_explicit_join = False
                 for item in join_list[1:]:
                     table = self.to_table(item['table'])
                     if item['is_implicit']:
                         # add to from clause
-                        query = query.select_from(table)
+                        if has_explicit_join:
+                            # sqlalchemy doesn't support implicit join after explicit
+                            # convert it to explicit
+                            query = query.join(table, sa.text('1=1'))
+                        else:
+                            query = query.select_from(table)
                     else:
+                        has_explicit_join = True
                         if item['condition'] is None:
                             # otherwise, sqlalchemy raises "Don't know how to join to ..."
                             condition = sa.text('1=1')
@@ -564,7 +570,7 @@ class SqlalchemyRender:
                             condition,
                             full=is_full
                         )
-            elif isinstance(from_table, ast.Union):
+            elif isinstance(from_table, (ast.Union, ast.Intersect, ast.Except)):
                 alias = None
                 if from_table.alias:
                     alias = self.get_alias(from_table.alias)


Test patch:
diff --git a/tests/unit/render/test_sqlalchemyrender.py b/tests/unit/render/test_sqlalchemyrender.py
index f732a76ff..ad4260552 100644
--- a/tests/unit/render/test_sqlalchemyrender.py
+++ b/tests/unit/render/test_sqlalchemyrender.py
@@ -1,4 +1,5 @@
 import datetime as dt
+from textwrap import dedent
 
 from mindsdb_sql_parser.ast import (
     Identifier, Select, Star, Constant, Tuple, BinaryOperation, CreateTable, TableColumn, Insert
@@ -124,3 +125,39 @@ class TestRender:
 
         # check queries are the same after render
         assert rendered.replace('\n', '') == 'SELECT "A".*, A."B" FROM Tbl."Tab" AS t'
+
+    def test_intersect_except(self):
+        for op in ('EXCEPT', 'INTERSECT'):
+            sql = dedent(f"""
+            SELECT * FROM tbl1
+            {op} SELECT * FROM tbl2
+            """).strip()
+
+            query = parse_sql(sql)
+            rendered = SqlalchemyRender('postgres').get_string(query, with_failback=False)
+
+            assert rendered.replace('\n', '') == sql.replace('\n', ' ')
+
+    def test_in_with_single_value(self):
+        sql = "SELECT * FROM tbl1 WHERE x IN (1)"
+        query = parse_sql(sql)
+        rendered = SqlalchemyRender('postgres').get_string(query, with_failback=False)
+
+        assert rendered.replace('\n', '') == sql
+
+    def test_mixed_join(self):
+        sql = """
+            SELECT * FROM tbl1
+            join tbl2 on tbl1.x = tbl2.x,
+            tbl3
+        """
+        query = parse_sql(sql)
+        rendered = SqlalchemyRender('postgres').get_string(query, with_failback=False)
+
+        expected = dedent("""
+            SELECT * FROM tbl1
+            JOIN tbl2 ON tbl1.x = tbl2.x
+            JOIN tbl3 ON 1=1
+        """).strip()
+
+        assert rendered.replace('\n', '') == expected.replace('\n', ' ')